Thread

1. pg_update to a new machine?

   Ron Johnson <ron.l.johnson@cox.net> — 2018-02-24T03:57:26Z

   The 2.8TB database must be moved to a new server in a new Data Center, and 
   upgraded from 8.4.17 to 9.6.6
   
   Will this work?
   
   pg_upgrade
            --old-datadir "CURSERVER://var/lib/pgsql/data"
            --new-datadir "NEWSERVER://var/lib/pgsql/data"
            --old-bindir "CURSERVER://usr/bin"
            --new-bindir "NEWSERVER://usr/bin"
   
   Or must I:
   1. temporarily allocate 3TB of scratch space on the new server,
   2. install 8.4 on the new server,
   3. install 9.6.6 on the new server,
   2. rsync CURSERVER://var/lib/pgsql/data to 
   NEWSERVER://var/lib/pgsql/8.4/data, and then
   3. pg_upgrade?
   
   Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)
   
   Thanks
   
   -- 
   Angular momentum makes the world go 'round.
   

2. Re: pg_update to a new machine?

   Andreas Kretschmer <andreas@a-kretschmer.de> — 2018-02-24T08:29:12Z

   
   Am 24.02.2018 um 04:57 schrieb Ron Johnson:
   > The 2.8TB database must be moved to a new server in a new Data Center, 
   > and upgraded from 8.4.17 to 9.6.6
   >
   > Will this work?
   >
   > pg_upgrade
   >          --old-datadir "CURSERVER://var/lib/pgsql/data"
   >          --new-datadir "NEWSERVER://var/lib/pgsql/data"
   >          --old-bindir "CURSERVER://usr/bin"
   >          --new-bindir "NEWSERVER://usr/bin"
   > Or must I:
   > 1. temporarily allocate 3TB of scratch space on the new server,
   > 2. install 8.4 on the new server,
   > 3. install 9.6.6 on the new server,
   > 2. rsync CURSERVER://var/lib/pgsql/data to 
   > NEWSERVER://var/lib/pgsql/8.4/data, and then
   > 3. pg_upgrade?
   >
   > Are there better ways?  (The pipe from current DC to new DC will be 
   > 10Gbps.)
   >
   
   it's a big step from 8.4 to 9.6... If you can do that with a downtime i 
   would prefer dump & restore.
   A solution without (big) downtime would be replication with slony or 
   londiste.
   
   
   Regards, Andreas
   
   -- 
   2ndQuadrant - The PostgreSQL Support Company.
   www.2ndQuadrant.com
   
   
   
   

3. Re: pg_update to a new machine?

   Stephen Frost <sfrost@snowman.net> — 2018-02-24T14:18:52Z

   Greetings,
   
   * Ron Johnson (ron.l.johnson@cox.net) wrote:
   > The 2.8TB database must be moved to a new server in a new Data Center, and
   > upgraded from 8.4.17 to 9.6.6
   > 
   > Will this work?
   > 
   > pg_upgrade
   >         --old-datadir "CURSERVER://var/lib/pgsql/data"
   >         --new-datadir "NEWSERVER://var/lib/pgsql/data"
   >         --old-bindir "CURSERVER://usr/bin"
   >         --new-bindir "NEWSERVER://usr/bin"
   
   No, you can't specify hostnames to pg_upgrade.  You could possibly use
   NFS or something else to be able to facilitate the above.
   
   > Or must I:
   > 1. temporarily allocate 3TB of scratch space on the new server,
   
   If you use --link mode during pg_upgrade, you don't actually need to
   allocate that scratch space on the new server.
   
   > 2. install 8.4 on the new server,
   > 3. install 9.6.6 on the new server,
   
   For pg_upgrade, you do need both versions installed on the server you're
   running pg_upgrade on, yes.  Please be sure to use the latest minor
   version of each major version if you go that route.
   
   > 2. rsync CURSERVER://var/lib/pgsql/data to
   > NEWSERVER://var/lib/pgsql/8.4/data, and then
   
   You can only perform this rsync with the database shut down, just to be
   clear.  If you wanted to pull the data across with the database online,
   you'd need to set up an archive_command and use a tool which works with
   8.4 to perform an online backup (such as pgBackRest).
   
   > 3. pg_upgrade?
   
   If you perform an online backup and then capture all of the WAL using
   archive_command, you could stand up a warm standby with 8.4 on the new
   server which is replaying the WAL as it's generated on the primary by
   specifying a restore_command on the new server.  Doing this, combined
   with using pg_upgrade in --link mode, you would be able to perform the
   flip from the old-server-on-8.4 to the new-server-with-9.6 in a
   relatively short period of time (on the order of minutes-to-an-hour,
   potentially).
   
   > Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)
   
   The above approach would work, but you wouldn't be able to enable
   checksums on the new server, which is something I'd certainly recommend
   doing if you're able to.  To get page-level checksums, you would need to
   make sure you initdb the new server with them and then use the newer
   pg_dump version to dump the 8.4 data out and then into the 9.6 server.
   This could possibly be done as a pipe, but I'd probably find 1TB of
   space somewhere and use parallel pg_dump to extract the data out and
   into a compressed logical dump and then parallel pg_restore to pull it
   into the new server.  This would also re-check all constraints in the
   system and rebuild all indexes, but would naturally require more
   downtime.
   
   Thanks!
   
   Stephen
   

4. Re: pg_update to a new machine?

   Ron Johnson <ron.l.johnson@cox.net> — 2018-02-24T15:09:56Z

   On 02/24/2018 08:18 AM, Stephen Frost wrote:
   > Greetings,
   >
   > * Ron Johnson (ron.l.johnson@cox.net) wrote:
   >> The 2.8TB database must be moved to a new server in a new Data Center, and
   >> upgraded from 8.4.17 to 9.6.6
   >>
   >> Will this work?
   >>
   >> pg_upgrade
   >>          --old-datadir "CURSERVER://var/lib/pgsql/data"
   >>          --new-datadir "NEWSERVER://var/lib/pgsql/data"
   >>          --old-bindir "CURSERVER://usr/bin"
   >>          --new-bindir "NEWSERVER://usr/bin"
   > No, you can't specify hostnames to pg_upgrade.
   
   I didn't think it would work, but it was worth an ask...
   
   > You could possibly use
   > NFS or something else to be able to facilitate the above.
   >
   >> Or must I:
   >> 1. temporarily allocate 3TB of scratch space on the new server,
   > If you use --link mode during pg_upgrade, you don't actually need to
   > allocate that scratch space on the new server.
   
   I'll look into that.
   
   >> 2. install 8.4 on the new server,
   >> 3. install 9.6.6 on the new server,
   > For pg_upgrade, you do need both versions installed on the server you're
   > running pg_upgrade on, yes.  Please be sure to use the latest minor
   > version of each major version if you go that route.
   >
   >> 2. rsync CURSERVER://var/lib/pgsql/data to
   >> NEWSERVER://var/lib/pgsql/8.4/data, and then
   > You can only perform this rsync with the database shut down, just to be
   > clear.  If you wanted to pull the data across with the database online,
   > you'd need to set up an archive_command and use a tool which works with
   > 8.4 to perform an online backup (such as pgBackRest).
   
   To set up log shipping on 8.4, I do this, which works well:
   
   select pg_start_backup('some_meaningful_tag');
   nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
   select pg_stop_backup();
   
   Would I, essentially (or in fact), have to set up log shipping from old to new?
   
   >> 3. pg_upgrade?
   > If you perform an online backup and then capture all of the WAL using
   > archive_command, you could stand up a warm standby with 8.4 on the new
   > server which is replaying the WAL as it's generated on the primary by
   > specifying a restore_command on the new server.
   
   I guess that means "yes, set up log shipping"?
   
   >    Doing this, combined
   > with using pg_upgrade in --link mode, you would be able to perform the
   > flip from the old-server-on-8.4 to the new-server-with-9.6 in a
   > relatively short period of time (on the order of minutes-to-an-hour,
   > potentially).
   
   
   >> Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)
   > The above approach would work, but you wouldn't be able to enable
   > checksums on the new server, which is something I'd certainly recommend
   > doing if you're able to.
   
   We'll benchmark it.
   
   >    To get page-level checksums, you would need to
   > make sure you initdb the new server with them and then use the newer
   > pg_dump version to dump the 8.4 data out and then into the 9.6 server.
   > This could possibly be done as a pipe, but I'd probably find 1TB of
   > space somewhere and use parallel pg_dump
   
   Is parallel pg_dump available on 8.4, or am I misinterpreting you?
   
   >   to extract the data out and
   > into a compressed logical dump and then parallel pg_restore to pull it
   > into the new server.  This would also re-check all constraints in the
   > system and rebuild all indexes, but would naturally require more
   > downtime.
   >
   > Thanks!
   >
   > Stephen
   
   
   -- 
   Angular momentum makes the world go 'round.
   

5. Re: pg_update to a new machine?

   Stephen Frost <sfrost@snowman.net> — 2018-02-24T21:10:08Z

   Greetings,
   
   * Ron Johnson (ron.l.johnson@cox.net) wrote:
   > On 02/24/2018 08:18 AM, Stephen Frost wrote:
   > >* Ron Johnson (ron.l.johnson@cox.net) wrote:
   > >>2. install 8.4 on the new server,
   > >>3. install 9.6.6 on the new server,
   > >For pg_upgrade, you do need both versions installed on the server you're
   > >running pg_upgrade on, yes.  Please be sure to use the latest minor
   > >version of each major version if you go that route.
   > >
   > >>2. rsync CURSERVER://var/lib/pgsql/data to
   > >>NEWSERVER://var/lib/pgsql/8.4/data, and then
   > >You can only perform this rsync with the database shut down, just to be
   > >clear.  If you wanted to pull the data across with the database online,
   > >you'd need to set up an archive_command and use a tool which works with
   > >8.4 to perform an online backup (such as pgBackRest).
   > 
   > To set up log shipping on 8.4, I do this, which works well:
   > 
   > select pg_start_backup('some_meaningful_tag');
   > nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
   > select pg_stop_backup();
   
   That's not log shipping, for log shipping you need to specify an
   archive_command and actually capture all of the WAL generated, or, at a
   minimum, the WAL generated between the start and stop backup calls.
   
   Note that the above also doesn't do anything to verify that the data is
   written out to the disk on the destination side.
   
   > Would I, essentially (or in fact), have to set up log shipping from old to new?
   
   You must capture the WAL generated between the start and stop backup for
   a backup to be valid.  Beyond that, if you want the warm standby to
   replay the changes made to the primary through WAL, you need to be
   capturing the WAL generated on the primary using archive_command and
   then specify a restore_command on the warm standby which will get the
   WAL segments to be replayed.
   
   > >>3. pg_upgrade?
   > >If you perform an online backup and then capture all of the WAL using
   > >archive_command, you could stand up a warm standby with 8.4 on the new
   > >server which is replaying the WAL as it's generated on the primary by
   > >specifying a restore_command on the new server.
   > 
   > I guess that means "yes, set up log shipping"?
   
   I'm not sure what you're asking here, but if you wish to minimize
   downtime, then, yes, set up a warm standby which is being updated
   through log shipping.
   
   > >   Doing this, combined
   > >with using pg_upgrade in --link mode, you would be able to perform the
   > >flip from the old-server-on-8.4 to the new-server-with-9.6 in a
   > >relatively short period of time (on the order of minutes-to-an-hour,
   > >potentially).
   > 
   > >>Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)
   > >The above approach would work, but you wouldn't be able to enable
   > >checksums on the new server, which is something I'd certainly recommend
   > >doing if you're able to.
   > 
   > We'll benchmark it.
   > 
   > >   To get page-level checksums, you would need to
   > >make sure you initdb the new server with them and then use the newer
   > >pg_dump version to dump the 8.4 data out and then into the 9.6 server.
   > >This could possibly be done as a pipe, but I'd probably find 1TB of
   > >space somewhere and use parallel pg_dump
   > 
   > Is parallel pg_dump available on 8.4, or am I misinterpreting you?
   
   The pg_dump you'll be using is from the version you're upgrading *to*,
   so it will support parallel jobs.  With an 8.4 server, you'll need to
   stop all write traffic before running the pg_dump and you'll have to
   specify '--no-synchronized-snapshots' as that's a feature which the 8.4
   server doesn't support.
   
   Thanks!
   
   Stephen
   

6. Re: pg_update to a new machine?

   Ron Johnson <ron.l.johnson@cox.net> — 2018-02-25T00:35:43Z

   On 02/24/2018 03:10 PM, Stephen Frost wrote:
   [snip]
   >> To set up log shipping on 8.4, I do this, which works well:
   >>
   >> select pg_start_backup('some_meaningful_tag');
   >> nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
   >> select pg_stop_backup();
   > That's not log shipping, for log shipping you need to specify an
   > archive_command and actually capture all of the WAL generated, or, at a
   > minimum, the WAL generated between the start and stop backup calls.
   
   That's steps #8, 9 and 10 (in our checklist) of setting up log shipping.  
   Step #4 is configuring the archive_* statements in postgresql.conf.
   
   
   
   -- 
   Angular momentum makes the world go 'round.
   
   
   

7. Re: pg_update to a new machine?

   Stephen Frost <sfrost@snowman.net> — 2018-02-25T00:40:09Z

   Ron,
   
   * Ron Johnson (ron.l.johnson@cox.net) wrote:
   > On 02/24/2018 03:10 PM, Stephen Frost wrote:
   > [snip]
   > >>To set up log shipping on 8.4, I do this, which works well:
   > >>
   > >>select pg_start_backup('some_meaningful_tag');
   > >>nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
   > >>select pg_stop_backup();
   > >That's not log shipping, for log shipping you need to specify an
   > >archive_command and actually capture all of the WAL generated, or, at a
   > >minimum, the WAL generated between the start and stop backup calls.
   > 
   > That's steps #8, 9 and 10 (in our checklist) of setting up log shipping. 
   > Step #4 is configuring the archive_* statements in postgresql.conf.
   
   I would still recommend you use a tool developed specifically for taking
   proper PG backups which validates that all WAL generated during a backup
   is properly archived, such as pgBackRest, which also makes sure to sync
   all the data out to persistent storage and can also operate in parallel
   and perform incremental restores.
   
   Thanks!
   
   Stephen
   

8. Re: pg_update to a new machine?

   Ron Johnson <ron.l.johnson@cox.net> — 2018-02-25T00:48:11Z

   On 02/24/2018 06:40 PM, Stephen Frost wrote:
   > Ron,
   >
   > * Ron Johnson (ron.l.johnson@cox.net) wrote:
   >> On 02/24/2018 03:10 PM, Stephen Frost wrote:
   >> [snip]
   >>>> To set up log shipping on 8.4, I do this, which works well:
   >>>>
   >>>> select pg_start_backup('some_meaningful_tag');
   >>>> nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
   >>>> select pg_stop_backup();
   >>> That's not log shipping, for log shipping you need to specify an
   >>> archive_command and actually capture all of the WAL generated, or, at a
   >>> minimum, the WAL generated between the start and stop backup calls.
   >> That's steps #8, 9 and 10 (in our checklist) of setting up log shipping.
   >> Step #4 is configuring the archive_* statements in postgresql.conf.
   > I would still recommend you use a tool developed specifically for taking
   > proper PG backups which validates that all WAL generated during a backup
   > is properly archived, such as pgBackRest, which also makes sure to sync
   > all the data out to persistent storage and can also operate in parallel
   > and perform incremental restores.
   
   OK.  I'll try to get it installed.  (Lots of paperwork, etc.)
   
   
   -- 
   Angular momentum makes the world go 'round.