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

On 9/12/18 6: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
>
> 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

Did you see errors in the restore?

In particular about not finding roles(users) for the permissions?

I ask because I do not see in the above anything about dumping objects
global to the cluster. That would include roles. I use:

pg_dumpall -g -f globals.sql

See:

https://www.postgresql.org/docs/10/static/app-pg-dumpall.html

> 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!
>
> *Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate*
> One La-Z-Boy Drive | Monroe, Michigan 48162  |( 734-384-6403|
> |)7349151444| * Scot(dot)Kreienkamp(at)la-z-boy(dot)com <mailto:%7BE-mail%7D>
> www <http://www.la-z-boy.com/>.la-z-boy.com <http://www.la-z-boy.com/> |
> facebook. <https://www.facebook.com/lazboy>com
> <https://www.facebook.com/lazboy>/
> <https://www.facebook.com/lazboy>lazboy <http://facebook.com/lazboy> |
> twitter.com/lazboy <https://twitter.com/lazboy> | youtube.com/
> <https://www.youtube.com/user/lazboy>lazboy
> <https://www.youtube.com/user/lazboy>
>
> This messageis intended onlyfor the individual or entity to which it is
> addressed.  It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws.  If you are not the
> intended recipient, you are strictly prohibited from disseminating or
> distributing this information (other than to the intended recipient) or
> copying this information.  If you have received this communication in
> error, please notify us immediately by e-mail or by telephone at the
> above number. Thank you.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com