PG9.1 migration to PG9.6, dump/restore issues - Mailing list pgsql-general

Hi Everyone,

I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime in on my plans as I am running into some restore issues.

We are upgrading to a new version of PG and migrating to new hardware with RHEL 7, so I am planning on doing a dump and restore to get moved to the new server.  My database is about 300 gigs, not huge but big enough that doing a single threaded dump with multi-threaded restore is going to take too much time for the window of opportunity I’ve been given.  I know I can use multi-threaded restore on PG9.6 using the custom or directory formats, but PG9.1 only supports single threaded dump.  To get around this I’m going to disable all database access to the PG9.1 databases, then use the PG9.6 tools to do a multi-threaded dump and then multi-threaded restore. 

These are the commands I was using:

pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE --no-synchronized-snapshots

created $DATABASE

pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   --disable-triggers

Restore completes successfully, but I noticed that the schema permissions are missing, possibly others as well (Is this a known issue?).  So instead, I tried backing up and restoring the schema only as single threaded dump and restore, then dumping the data multi-threaded using the PG9.6 tools, then doing a multi-threaded data-only restore using PG9.6 tools into the already existing schema. 

These are the commands I’m using now:

pg_dump -sh $OLDSERVER  $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql

pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE --no-synchronized-snapshots

createdb $DATABASE

psql –d $DATABASE –f $BACKUPPATH/$DATABASE.schema.sql

pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   -a --disable-triggers

That seemed to work OK so far, but the missing schema permissions from my first try has me spooked.  Are there any problems with attempting this type of backup/restore?  Would I be better off using the commands from my first attempt and reapplying permissions?  Or is doing a single threaded dump my only option to get a good backup?  I have to be able to revert to the old server as this is production, so doing in place upgrades are not possible… the original server has to remain pristine. 

Thanks!