Horton

Thread

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

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-09-12T13:55:15Z

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

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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-09-12T14:09:38Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-12T14:39:35Z

    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

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

    Ron <ronljohnsonjr@gmail.com> — 2018-09-12T14:40:19Z

    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.


-- 
Angular momentum makes the world go 'round.

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

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-09-12T18:28:16Z

    

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 first thing 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 is pg_dumpall -s, which creates the databases and tables as well.  I could drop the databases and create empty ones to do the 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, 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.

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

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-09-12T18:30:25Z

    > 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 schema permissions 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, 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.

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

    Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2018-09-12T18:31:55Z

    Thanks Ron, glad to hear it worked and someone was successful at it.  I’m on the right path then.



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
From: Ron [mailto:ronljohnsonjr@gmail.com]
Sent: Wednesday, September 12, 2018 10:40 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: PG9.1 migration to PG9.6, dump/restore issues

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.

--
Angular momentum makes the world go 'round.


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.

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-09-12T23:52:50Z

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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-09-12T23:53:01Z

    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 schema permissions 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, 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