tablespaces inside $PGDATA considered harmful - Mailing list pgsql-hackers

From Robert Haas
Subject tablespaces inside $PGDATA considered harmful
Date
Msg-id CA+TgmobZLyLU8tFCbMqbjMWB6t+=ERaDo820uQEJCVAQK_Paow@mail.gmail.com
Whole thread Raw
Responses Re: tablespaces inside $PGDATA considered harmful
Re: tablespaces inside $PGDATA considered harmful
Re: tablespaces inside $PGDATA considered harmful
List pgsql-hackers
I think everyone who has read this mailing list for a while is
probably already aware of this problem.  When you create a tablespace
somewhere inside the data directory, weird things happen. If you
pg_upgrade and then incautiously run the delete_old_cluster.sh script
thus created, you will blow away large chunks of your data.[1]  If you
try to use pg_basebackup, it will back up your data twice and maybe
throw some warnings.[2]  You can also induce pg_database_size() to
give wrong results --- it'll count pg_tblspace/$TABLESPACE_OID as well
as pg_tblspace/some-stupid-tablespace-name, the former being a symlink
to the latter.

Given all this, it seems like a good idea to at least give a warning
if somebody tries to create a tablespace instead the data directory.
Arguably, we should prohibit it altogether, but there are obviously
people that want to do it, and there could even be somewhat valid
reasons for that, like wanting to set per-tablespace settings
differently for different tablespaces.  Possibly we should prohibit it
anyway, or maybe there should be an option to create a tablespace
whose directory is a real directory, not a symlink.  So then:

CREATE TABLESPACE foo LOCATION '/home/rhaas/pgdata/pg_tblspc/foo';

...would fail, but if you really want a separate tablespace inside the
data directory, we could allow:

CREATE TABLESPACE foo NO LOCATION;

...which would just create a bare directory where the symlink would normally go.

In the short term, I favor just adding a warning, so that people get
some clue that they are doing something that might be a bad idea.  In
the long term, we might want to do more.  Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] http://www.postgresql.org/message-id/B6F6FD62F2624C4C9916AC0175D56D880CE46DB7@jenmbs01.ad.intershop.net
[2] http://www.postgresql.org/message-id/CABUevExkhE+KcQa+fLUeaizP5i5QVCnNjZ2j0ZZQcaMJFheQDw@mail.gmail.com



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Buildfarm broken for 9.3 and up
Next
From: Bruce Momjian
Date:
Subject: Re: tablespaces inside $PGDATA considered harmful