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!



Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162  | * 734-384-6403 | | * 7349151444 
| *  scot.kreienk...@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>

[cid:lzbVertical_hres.jpg]



This message is intended only for 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.

Reply via email to