Re: PG9.1 migration to PG9.6, dump/restore issues

[Ron <ronljohnsonjr@xxxxxxxxx>]

On 09/12/2018 08:55 AM, Scot Kreienkamp wrote:

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

This is almost exactly what I did when migrating from 8.4 to 9.6.  As Adrian Klaver mentioned, you need to dump the globals and then run that script on the new database.

No need to disable triggers, since it's "relevant only when performing a data-only restore", and you aren't doing a data-only restore.  Besides, pg_restore adds all that metadata -- including PKs, FKs, indexes, etc. to the db *after* the data is loaded.

--
Angular momentum makes the world go 'round.