Re: pg_update to a new machine? - Mailing list pgsql-general
| From | Ron Johnson |
|---|---|
| Subject | Re: pg_update to a new machine? |
| Date | |
| Msg-id | 74ef8c30-0db7-84ad-b04e-1975fe2977e4@cox.net Whole thread Raw |
| In response to | pg_update to a new machine? (Ron Johnson <ron.l.johnson@cox.net>) |
| Responses |
Re: pg_update to a new machine?
|
| List | pgsql-general |
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 thenYou 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.
Angular momentum makes the world go 'round.
pgsql-general by date: