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

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.Kreienkamp@la-z-boy.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.klaver@aklaver.com