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