Re: tablespaces inside $PGDATA considered harmful

On Wed, Apr 22, 2015 at 10:41:02PM -0400, Bruce Momjian wrote:
> > josh=# create tablespace tbl2 location '/home/josh/pg94/data/pg_xlog/';
> > CREATE TABLESPACE
> >
> > It really seems like we ought to block *THAT*. Of course, if we block
> > tablespace creation in PGDATA generally, then that's covered.
>
> I have developed the attached patch to warn about creating tablespaces
> inside the data directory. The case this doesn't catch is referencing a
> symbolic link that points to the same directory. We can't make it an
> error so people can use pg_upgrade these setups. This would be for 9.5
> only.

OK, based on later discussions, I have updated my 9.5 patch to have
pg_upgrade also display a warning (the warning will also appear in the
pg_upgrade logs, but I doubt the user will see it), e.g.:

Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok

WARNING: user-defined tablespace locations should not be inside the data directory, e.g. /u/pgsql.old/data/pg_tblspc

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh

Could not create a script to delete the old cluster's data
files because user-defined tablespaces exist in the old cluster
directory. The old cluster's contents must be deleted manually.

This way, both pg_dump and pg_upgrade will issue warnings, though, of
course, those warnings can be ignored. I am hopeful these two warnings
will be sufficient and we will not need make these errors, with the
possible inconvenience it will cause. I am still afraid that someone
will ignore the new errors pg_dump would generate and lose data. I just
don't remember enough cases where we threw new errors on _data_ restore.

Frankly, those using pg_upgrade already will have to move the old
tablespaces out of the old cluster if they ever want to delete those
clusters, so I am hopeful these additional warnings will help eliminate
this practice, which is already cumbersome and useless. I am not
planning to revisit this for 9.6.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +