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