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                            okSync data directory to disk
 okCreating script to analyze new cluster                      okWARNING:  user-defined tablespace locations should not
beinside the data directory, e.g. /u/pgsql.old/data/pg_tblspcUpgrade Complete----------------Optimizer statistics are
nottransferred by pg_upgrade so,once you start the new server, consider running:    ./analyze_new_cluster.shCould not
createa script to delete the old cluster's datafiles because user-defined tablespaces exist in the old
clusterdirectory. 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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +