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@xxxxxxxxxxxx <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@xxxxxxxxxxx
