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

Want faster answers from InfoTech? Check out www.portal.jims.net

Peter Neave | DevOps Lead | Peter.Neave@jims.net  Jim's Group Pty Ltd  48 Edinburgh Rd | Mooroolbark | VIC, 3138  P 1300 130 490 | Intl +61 3 8419 2910

This email and any attachment(s) are confidential. If you are not the intended recipient you must not copy, use, disclose, distribute or rely on the information contained in it. If you have received this email in error, please notify the sender immediately by reply email and delete the email from your system. Confidentiality and legal privilege attached to this communication are not waived or lost by reason of mistaken delivery to you. While Jim's Group employs Anti-Virus Software, we cannot guarantee that this email or the attachment(s) are unaffected by computer virus, corruption or other defects and we recommend that this email and any attachments be tested before opening.

Please consider the environment before printing this email.

---

Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
 
 

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

On 8 June 2018 07:55:26 CEST, Peter Neave <Peter.Neave@jims.net> wrote:
>Hi,
>
>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 can use the pg_dump from the linux-box to take the dump using directory-format and multiple processes, and also
multipleprocesses for the restore. 

You can also try to use a trigger-based replication (londiste, slony) to build a replication from 9.0 to 10, but i'm
notsure if those tools are available under windows. 

Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

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