Re: [GENERAL] pg_upgrade & tablespaces

On Sun, Jan 12, 2014 at 11:04:41PM -0500, Bruce Momjian wrote:
> > In the pgsql_old installation you have symlinks pointing back to the
> > current default location. As well pg_tablespace points back to
> > /usr/local/pgsql/data/ The issue is that there is not actually
> > anything there in the way of a tablespace. So when pg_upgrade runs
> > it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to
> > /usr/local/pgsql/data/tblspc_dir where the first directory either
> > does not exist. or if the user went ahead and created the directory
> > in the new installation, is empty. What is really wanted is to
> > upgrade from /usr/local/pgsql_old/data/tblspc_dir to
> > /usr/local/pgsql/data/tblspc_dir. Right now the only way that
> > happens is with user intervention.
>
> Right, it points to _nothing_ in the _new_ cluster.  Perhaps the
> simplest approach would be to check all the pg_tablespace locations to
> see if they point at real directories.  If not, we would have to have
> the user update pg_tablespace and the symlinks.  :-(  Actually, even in
> 9.2+, those symlinks are going to point at the same "nothing".  That
> would support checking the symlinks in all versions.

I have developed the attached patch which checks all tablespaces to make
sure the directories exist.  I plan to backpatch this.

The reason we haven't seen this bug reported more frequently is that a
_database_ defined in a non-existent tablespace directory already throws
an backend error, so this check is only necessary where tables/indexes
(not databases) are defined in non-existant tablespace directories.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +