In addition to the parallelization and piping advice below, typically with default settings about 75% of the processor
timeis 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
indexesto be rebuilt in parallel (this is usually the slowest part of a restore).
-----Original Message-----
From: Thomas Kellerer <spam_eater@gmx.net>
Sent: Friday, June 8, 2018 12:04 AM
To: pgsql-novice@postgresql.org
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
