From: | Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | RE: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux |
Date: | 2018-06-08 15:15:28 |
Message-ID: | DM5PR06MB3436E8A379C8FEF70CB182B9E57B0@DM5PR06MB3436.namprd06.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 <spam_eater(at)gmx(dot)net>
Sent: Friday, June 8, 2018 12:04 AM
To: pgsql-novice(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ahmed, Nawaz | 2018-06-08 22:07:34 | Re: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux |
Previous Message | Andreas Kretschmer | 2018-06-08 06:08:09 | Re: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux |