RE: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux

In addition to the parallelization and piping advice below, typically with default settings about 75% of the processor time is spent gzipping the output.  You might see if its faster using --compress=0 or --compress=1.

(though the compression wouldn't apply to the piped solution)

I also believe 'pg_restore --jobs=[something greater than 1]' will speed up the restore of even one table as it allows indexes to be rebuilt in parallel (this is usually the slowest part of a restore).



-----Original Message-----
From: Thomas Kellerer <[email protected]> 
Sent: Friday, June 8, 2018 12:04 AM
To: [email protected]
Subject: Re: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux

Peter Neave schrieb am 08.06.2018 um 07:55:
> I’ve been tasked with migrating our production database from Postgres
> 9.0 on Windows to Postgres 10.0 on Linux. I’ve used pg_dump and 
> pg_restore and it works fine but the time taken for my dry run of the 
> migration is about 12 hours (8 hours backup and 4 hours restore)
> 
> What can I do to reduce the migration time so that I can get 
> production up and running again as soon as possible? I have the option 
> to upgrade either machine if that helps and in that case what would 
> help most faster disk IOPS? RAM? CPU?

You could try to do the dump/restore without the intermediate file and pipe pg_dumps output to psql

    pg_dump -h oldserver ... | psql -h newserver ....

Another thing you could try, is to use the "directory" format (-F d) of pg_dump which lets you use multiple threads. 
The directory format also enables you to use multiple threads for pg_restore. 

But that would only improve the speed if you have many tables that are similar in size. 
If the 8 hours are spent mostly on one table that won't help