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

Discussion:

Andreas Kretschmer

2018-06-08 06:08:09 UTC

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 multiple processes 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 not sure if those tools are available under windows.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company

Thomas Kellerer

2018-06-08 06:04:01 UTC

Permalink

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

Stephen Froehlich

2018-06-08 15:15:28 UTC

Permalink

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 <***@gmx.net>
Sent: Friday, June 8, 2018 12:04 AM
To: pgsql-***@postgresql.org
Subject: Re: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux

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?

Ahmed, Nawaz

2018-06-08 22:07:34 UTC

Permalink

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Exchange Server">
<style></style>
</head>
<body>
<div>
<div style="font-family:Calibri,HelveticaNeue,sans-serif">
<div dir="auto">
<div dir="auto">
<div dir="auto">Slony for windows is available but Iam not sure how well the replication between windows and Linux will go, I haven’t tried (on my list of to do for ages). Based on the theory, it should work because you will use connectivity details to connect
two nodes and there is no dependency on the architecture of the host. </div> <div dir="auto"><br> </div> <div dir="auto">There are links to developer.pgadmin.org in the installation chapter of slony manuals, which has a sample screen shot of GUI installation for windows, although I could only see references to win32.</div> <div dir="auto"><br> </div> <div dir="auto">Another triggers based replication tool is SymmetricDS, again windows to Linux needs to be tested.</div> <div dir="auto"><br> </div> <div dir="auto">Regards, </div> <div dir="auto">Nawaz</div> <div dir="auto"><br> </div> <div dir="auto"><br> </div> <div dir="auto"> <div dir="auto"><br> </div> </div> </div> </div> </div> <hr> <div dir="auto" style="font-family:Calibri,HelveticaNeue,sans-serif"><br> <b>From:</b> Andreas Kretschmer <***@a-kretschmer.de><br> <b>Date:</b> 8 June 2018 at 4:08:30 pm AEST<br> <b>To:</b> Peter Neave <***@jims.net>, pgsql-***@postgresql.org <pgsql-***@postgresql.org><br> <b>Subject:</b> Re: Postgres Migration from Postgres 9.0 on Windows to Postgres 10.0 on Linux<br> </div> <br> </div> <font size="2"><span style="font-size:10pt;"> <div class="PlainText">On 8 June 2018 07:55:26 CEST, Peter Neave <***@jims.net> wrote:<br>
>Hi,<br>
><br>
>I’ve been tasked with migrating our production database from Postgres<br>
>9.0 on Windows to Postgres 10.0 on Linux. I’ve used pg_dump and<br>
>pg_restore and it works fine but the time taken for my dry run of the<br>
>migration is about 12 hours (8 hours backup and 4 hours restore)<br>
><br>
>What can I do to reduce the migration time so that I can get production<br>
>up and running again as soon as possible? I have the option to upgrade<br>
>either machine if that helps and in that case what would help most<br>
>faster disk IOPS? RAM? CPU?<br>
><br>
You can use the pg_dump from the linux-box to take the dump using directory-format and multiple processes, and also multiple processes for the restore.<br>
<br>
You can also try to use a trigger-based replication (londiste, slony) to build a replication from 9.0 to 10, but i'm not sure if those tools are available under windows.<br>
<br>
Regards, Andreas<br>
<br>
<br>
-- <br>
2ndQuadrant - The PostgreSQL Support Company<br>
<br>
</div>
</span></font>
<h3>Disclaimer</h3>
<p>The information in this e-mail is confidential and may contain content that is subject to copyright and/or is commercial-in-confidence and is intended only for the use of the above named addressee. If you are not the intended recipient, you are hereby notified
that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document
and all copies thereof.</p>
<br>
<p>Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files attached for computer viruses and other defects. Fujitsu
Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic) however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication
or any files attached.</p>
<br>
<p>If you do not wish to receive commercial and/or marketing email messages from Fujitsu Australia Software Technology Pty Ltd, please email ***@fast.au.fujitsu.com</p>
<p></p>
</body>
</html>