Re: pg_upgrade FAIL: can't find tablespaces

On Wed, Aug 20, 2014 at 7:23 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Mon, Aug 18, 2014 at 12:15:57PM -0700, Craig James wrote:
> >
> >
> > On Mon, Aug 18, 2014 at 11:59 AM, Craig James <cjames(at)emolecules(dot)com>
> wrote:
> >
> > I did pg_upgrade from 8.4.17 to 9.3.5. The upgrade claimed it was
> > successful. However, when I start Postgres 9.3.5, I get an error
> message
> > for every one of the roughly 250 databases:
> >
> > LOG: could not open tablespace directory "pg_tblspc/16828/
> > PG_9.3_201306121": No such file or directory
> > LOG: could not open tablespace directory "pg_tblspc/16523/
> > PG_9.3_201306121": No such file or directory
> > LOG: could not open tablespace directory "pg_tblspc/16768/
> > PG_9.3_201306121": No such file or directory
> > LOG: could not open tablespace directory "pg_tblspc/16715/
> > PG_9.3_201306121": No such file or directory
> > ... and so forth
> >
> > My Postgres looks like this
> >
> > /data/postgres/main - the PGDATA directory
> > /data/postgres/tablespaces - where most of the data live
> >
> >
> > OK, I figured this out: pg_upgrade didn't follow symbolic links. Each of
> the
> > missing files pointed to /data/postgres/tablespaces, but the actual
> directory
> > was in /data/postgres-8.4/tablespaces. When I replaced /data/postgres/
> > tablespaces with a symbolic link to /data/postgres-8.4/tablespaces,
> everything
> > worked.
> >
> > Once everything is in order and I'm convinced 9.3.5 is working, I'll
> shut down,
> > remove the symlink, and move the tablespaces directory to the new
> location.
>
> Uh, I am not totally clear what happened here. I am unclear how
> pg_upgrade would have moved the files yet the new server would not
> start. Did you do anything between the completion of pg_upgrade and
> trying to start the new server?
>

It's complicated. My situation is probably not relevant to anyone else.
Here is the history, which explains the how this occurred.

For consistency with our older systems, we built the database with PGDATA =
/postgres, even though it was actually in /data/postgres. /postgres was a
symlink.

Similarly, our tablespaces were specified as /postgres/tablespaces/xxx, but
in fact they were in /data/postgres/tablespaces/xxx (where xxx is the
tablespace name).

Prior running pg_upgrade, I renamed /data/postgres to /data/postgres-8.4
and updated the symlink /postgres. I verified that 8.4 still ran with no
problems.

pg_upgrade ran with no complaints. The new directory was /data/postgres-9.3.

Since I wasn't going to run 8.4 any more, I updated the symlink /postgres
to point to /data/postgres-9.3. Unfortunately, pg_upgrade hadn't created
/data/postgres-9.3/tablespaces, since it thought they were in
/postgres/tablespaces:

/data/postgres-9.3/main/pg_tblspc/nnnnn -> /postgres/tablespaces/xxx/

where "nnnnn" is the ID of the tablespace and "xxx" was the original
tablespace name. When I tried to start 9.3, I got the errors shown in my
original email.

To fix it, I simply moved /data/postgres-8.4/tablespaces to
/data/postgres-9.3/tablespaces.

One scary side effect: if I had run the cleanup script that pg_upgrade
produced, it would have deleted my entire database since
/data/postgres-8.4/tablespaces was NOT hard linked into the new
/data/postgres-9.3 directory.

Craig

> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>