Обсуждение: PG9.1 migration to PG9.6, dump/restore issues

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
www.la-z-boy.com | facebook.com/lazboy | twitter.com/lazboy | youtube.com/lazboy

 

 

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.

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

Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> writes:
> Restore completes successfully, but I noticed that the schema
> permissions are missing, possibly others as well (Is this a known
> issue?).

If you're talking about custom modifications you made to the permissions
of the "public" schema in particular, then yeah, that won't be tracked
(IIRC, it will be with newer source server versions, but not 9.1).
Otherwise, no, that's not expected.  Would you provide more detail?

> 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

Also note that this recipe does not copy "global" objects (users and
tablespaces), nor does it restore any database-level properties.
You'd need to use pg_dumpall to transfer those things automatically.
(Possibly "pg_dumpall -g" would be a good starting point here.)

            regards, tom lane

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.

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  Mobile: 7349151444 | Email:
Scot.Kreienkamp@la-z-boy.com
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, September 12, 2018 10:40 AM
> To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: PG9.1 migration to PG9.6, dump/restore issues
>
> Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> writes:
> > Restore completes successfully, but I noticed that the schema
> > permissions are missing, possibly others as well (Is this a known
> > issue?).
>
> If you're talking about custom modifications you made to the permissions
> of the "public" schema in particular, then yeah, that won't be tracked
> (IIRC, it will be with newer source server versions, but not 9.1).
> Otherwise, no, that's not expected.  Would you provide more detail?
>
Yes, it's permissions on the public schema.  They were completely empty.  I didn't check the other schemas as the very
firstthing I noticed was the permissions changed on the public schema, but I believe they were empty as well.
 

> > 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
>
> Also note that this recipe does not copy "global" objects (users and
> tablespaces), nor does it restore any database-level properties.
> You'd need to use pg_dumpall to transfer those things automatically.
> (Possibly "pg_dumpall -g" would be a good starting point here.)
>
>                       regards, tom lane

I notice the pg_dumpall -g doesn't bring over the schema permissions either.  The only way I can get them to come over
ispg_dumpall -s, which creates the databases and tables as well.  I could drop the databases and create empty ones to
dothe restore I guess, it would only take a few extra seconds.
 




This message is intended only for the individual or entity to which it is addressed.  It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws.  If you are not the intended recipient,
youare strictly prohibited from disseminating or distributing this information (other than to the intended recipient)
orcopying this information.  If you have received this communication in error, please notify us immediately by e-mail
orby telephone at the above number. Thank you.
 

> 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
>

Nope, no errors in the restore that I could see.  As I as discussing with Tom Lane, the -g switch doesn't bring over
schemapermissions either.  I could use the -s switch and just re-create the databases as empty again after that's
applied. That brings over everything except data.
 


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  Mobile: 7349151444 | Email:
Scot.Kreienkamp@la-z-boy.com

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws.  If you are not the intended recipient,
youare strictly prohibited from disseminating or distributing this information (other than to the intended recipient)
orcopying this information.  If you have received this communication in error, please notify us immediately by e-mail
orby telephone at the above number. Thank you.
 

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.

On 9/12/18 11:28 AM, Scot Kreienkamp wrote:
> 
> 
> 
> Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
> One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  Mobile: 7349151444 | Email:
Scot.Kreienkamp@la-z-boy.com
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sent: Wednesday, September 12, 2018 10:40 AM
>> To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
>> Cc: pgsql-general@lists.postgresql.org
>> Subject: Re: PG9.1 migration to PG9.6, dump/restore issues
>>
>> Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> writes:
>>> Restore completes successfully, but I noticed that the schema
>>> permissions are missing, possibly others as well (Is this a known
>>> issue?).
>>
>> If you're talking about custom modifications you made to the permissions
>> of the "public" schema in particular, then yeah, that won't be tracked
>> (IIRC, it will be with newer source server versions, but not 9.1).
>> Otherwise, no, that's not expected.  Would you provide more detail?
>>
> Yes, it's permissions on the public schema.  They were completely empty.  I didn't check the other schemas as the
veryfirst thing I noticed was the permissions changed on the public schema, but I believe they were empty as well.
 

In the 9.1 instance, using psql, what does:

\dn+ public

show.

Repeat for other schemas.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/12/18 11:30 AM, Scot Kreienkamp wrote:
>> 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
>>
> 
> Nope, no errors in the restore that I could see.  As I as discussing with Tom Lane, the -g switch doesn't bring over
schemapermissions either.  I could use the -s switch and 
 

Using pg_dumpall -g is not about permissions it is about transferring 
the roles that the permissions depend on. To put it another way roles 
are global to a cluster and permissions are tied to objects in a database.

just re-create the databases as empty again after that's applied.  That 
brings over everything except data.
> 
> 
> Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
> One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  Mobile: 7349151444 | Email:
Scot.Kreienkamp@la-z-boy.com
> 
> This message is intended only for the individual or entity to which it is addressed.  It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws.  If you are not the intended recipient,
youare strictly prohibited from disseminating or distributing this information (other than to the intended recipient)
orcopying this information.  If you have received this communication in error, please notify us immediately by e-mail
orby telephone at the above number. Thank you.
 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com