Re: pg_upgrade FAIL: can't find tablespaces

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.

Craig

> Urgent question: Having started 9.3 (briefly), is it possible to revert to
> 8.4? Or do I have to fully revert my 750GB database from a backup?
>
> Question 2: Is there a way to get 9.3 to work? Something I did wrong with
> table spaces, or a step I missed?
>
> I pretty much ran pg_upgrade per the instructions. See complete command
> and output below.
>
> Thanks,
> Craig
>
>
> /usr/local/pgsql-9.3.5/bin/pg_upgrade \
> --link \
> --user=postgres \
> --old-bindir=/usr/local/pgsql-8.4.17/bin \
> --new-bindir=/usr/local/pgsql-9.3.5/bin \
> --old-datadir=/data/postgres/main \
> --new-datadir=/data/postgres-9.3/main \
> --jobs=4
>
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
> Checking database user is a superuser ok
> Checking for prepared transactions ok
> Checking for reg* system OID user data types ok
> Checking for contrib/isn with bigint-passing mismatch ok
> Checking for large objects warning
>
> Your installation contains large objects. The new database has an
> additional large object permission table. After upgrading, you will be
> given a command to populate the pg_largeobject permission table with
> default permissions.
>
> Creating dump of global objects ok
> Creating dump of database schemas
> ok
> Checking for presence of required libraries ok
> Checking database user is a superuser ok
> Checking for prepared transactions ok
>
> If pg_upgrade fails after this point, you must re-initdb the
> new cluster before continuing.
>
> Performing Upgrade
> ------------------
> Analyzing all rows in the new cluster ok
> Freezing all rows on the new cluster ok
> Deleting files from new pg_clog ok
> Copying old pg_clog to new server ok
> Setting next transaction ID for new cluster ok
> Deleting files from new pg_multixact/offsets ok
> Setting oldest multixact ID on new cluster ok
> Resetting WAL archives ok
> Setting frozenxid and minmxid counters in new cluster ok
> Restoring global objects in the new cluster ok
> Adding support functions to new cluster ok
> Restoring database schemas in the new cluster
> ok
> Setting minmxid counter in new cluster ok
> Removing support functions from new cluster ok
> Adding ".old" suffix to old global/pg_control ok
>
> If you want to start the old cluster, you will need to remove
> the ".old" suffix from /data/postgres/main/global/pg_control.old.
> Because "link" mode was used, the old cluster cannot be safely
> started once the new cluster has been started.
>
> Linking user relation files
> ok
> Setting next OID for new cluster ok
> Sync data directory to disk ok
> Creating script to analyze new cluster ok
> Creating script to delete old cluster ok
> Checking for large objects warning
>
> Your installation contains large objects. The new database has an
> additional large object permission table, so default permissions must be
> defined for all large objects. The file
> pg_largeobject.sql
> when executed by psql by the database superuser will set the default
> permissions.
>
>
> Upgrade Complete
> ----------------
> Optimizer statistics are not transferred by pg_upgrade so,
> once you start the new server, consider running:
> analyze_new_cluster.sh
>
> Running this script will delete the old cluster's data files:
> delete_old_cluster.sh
>
>

--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------