Re: pg_update to a new machine?

On 02/24/2018 08:18 AM, Stephen Frost wrote:
> Greetings,
>
> * Ron Johnson (ron(dot)l(dot)johnson(at)cox(dot)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(at)${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.