Re: tablespaces and non-empty directories

On Thu, 17 Nov 2005, Philip Yarra wrote:

> I assume CREATE TABLESPACE refuses to use a non-empty directory because of the
> risk of trashing existing files. Makes sense, but consider the following:

Right, that was the reasoning.

>
> # mkfs -t ext2 /dev/sdc1
> # mount -t ext2 /dev/sdc1 /mnt/pg_tables
> # chown postgres /mnt/pg_tables
> # su -c psql pyarra
> pyarra=# CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/';
> ERROR: directory "/mnt/pg_tables" is not empty
>
> This is because lost+found exists. Since lost+found would be a reasonably
> common directory to find at a mount-point on Unix-like OSs*, would it make
> sense for CREATE TABLESPACE to ignore it if present?

This came up when tablespaces were being developed.

>
> Of course this isn't hard to get around:
> # mkdir /mnt/pg_tables/data
> # chown postgres /mnt/pg_tables/data
> CREATE TABLESPACE spc_tables LOCATION '/mnt/pg_tables/data/';

Right. We decided that this was easy for admins to do and also makes
things a little clearer: if /mnt/pg_tables was the data directory, you'd
have something like:

lost+found 1234132 12223132 [etc]

It might not be immediately obvious what the numeric named directories are
for.

>
> If consensus is that it is a bad idea to treat lost+found as a special case,
> would it be worth putting an explicit mention in the doco about the preferred
> way to set up a database with multiple disks?

Sounds like a good idea.

>
> Related question: are there plans afoot to allow specifying an alternate
> location for pg_xlog (or pg_delete-me-not) to save doing the shutdown-DB, mv
> directory to other disk, symlink, start-DB dance?

People have discussed it but I don't know of anyone working on it.

Gavin