upgrade and migrate

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

upgrade and migrate

Julie Nishimura
Hello, what is the best way to migrate from PostgreSQL 8.3.11 on x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu server, with minimal downtime?
The caveat is the source has about 80 databases overall almost 30 TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and pg_restore, but the largest hot database is almost 17 tb, and I am not sure how to approach this effort in a better and efficient way?

Thank you!
-Julie N
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Michael Paquier-2
On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
> server, with minimal downtime?
> The caveat is the source has about 80 databases overall almost 30
> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
> pg_restore, but the largest hot database is almost 17 tb, and I am
> not sure how to approach this effort in a better and efficient way?

pg_upgrade could be one way to go here.  That's not the scale pg_dump
would be very good at.  I would have personally avoided using pg_dump
above 10~20GB.  Depending on the downtime you are ready to accept,
a migration based on Slony could be something to investigate.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Ron-2
On 12/3/19 10:48 PM, Michael Paquier wrote:

> On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
>> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
>> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
>> server, with minimal downtime?
>> The caveat is the source has about 80 databases overall almost 30
>> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
>> pg_restore, but the largest hot database is almost 17 tb, and I am
>> not sure how to approach this effort in a better and efficient way?
> pg_upgrade could be one way to go here.  That's not the scale pg_dump
> would be very good at.  I would have personally avoided using pg_dump
> above 10~20GB.

I've successfully migrated three 1-2 TB databases across data centers in
about 12 hours each, from 8.4 to 9.6.  Used eight threads, and the 9.6
pg_dump against the 8.4 database.  17TB is a "bridge too far", though...

> Depending on the downtime you are ready to accept,
> a migration based on Slony could be something to investigate.
> --
> Michael

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Thomas Kellerer
In reply to this post by Michael Paquier-2
Michael Paquier schrieb am 04.12.2019 um 05:48:

> On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
>> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
>> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
>> server, with minimal downtime?
>> The caveat is the source has about 80 databases overall almost 30
>> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
>> pg_restore, but the largest hot database is almost 17 tb, and I am
>> not sure how to approach this effort in a better and efficient way?
>
> pg_upgrade could be one way to go here.  That's not the scale pg_dump
> would be very good at.  

But pg_upgrade only supports 8.4+

At least according to the manual.



Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Laurenz Albe
In reply to this post by Michael Paquier-2
On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote:

> On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
> > Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
> > x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
> > server, with minimal downtime?
> > The caveat is the source has about 80 databases overall almost 30
> > TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
> > pg_restore, but the largest hot database is almost 17 tb, and I am
> > not sure how to approach this effort in a better and efficient way?
>
> pg_upgrade could be one way to go here.  That's not the scale pg_dump
> would be very good at.  I would have personally avoided using pg_dump
> above 10~20GB.  Depending on the downtime you are ready to accept,
> a migration based on Slony could be something to investigate.

Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.

I would upgrade to a version more recent than 9.6.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Michael Paquier-2
In reply to this post by Thomas Kellerer
On Wed, Dec 04, 2019 at 08:38:01AM +0100, Thomas Kellerer wrote:
> But pg_upgrade only supports 8.4+

Ditto.  You're right here.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Stephen Frost
In reply to this post by Laurenz Albe
Greetings,

* Laurenz Albe ([hidden email]) wrote:

> On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote:
> > On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
> > > Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
> > > x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
> > > server, with minimal downtime?
> > > The caveat is the source has about 80 databases overall almost 30
> > > TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
> > > pg_restore, but the largest hot database is almost 17 tb, and I am
> > > not sure how to approach this effort in a better and efficient way?
> >
> > pg_upgrade could be one way to go here.  That's not the scale pg_dump
> > would be very good at.  I would have personally avoided using pg_dump
> > above 10~20GB.  Depending on the downtime you are ready to accept,
> > a migration based on Slony could be something to investigate.
>
> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>
> I would upgrade to a version more recent than 9.6.
So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
support upgrading from 8.3.X.  Support for upgrading from 8.3 was
removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Tom Lane-2
Stephen Frost <[hidden email]> writes:
> * Laurenz Albe ([hidden email]) wrote:
>> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>> I would upgrade to a version more recent than 9.6.

> So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
> support upgrading from 8.3.X.  Support for upgrading from 8.3 was
> removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.

Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
huge jump in terms of minor compatibility issues (have you read
all the relevant release notes?).  So there's something to be said
for breaking this down into two steps: update to 9.4, test/fix
your applications against that, then make a second jump to something
current.  Each of those jumps could be handled by the respective
version of pg_upgrade.  I concur with Laurenz's advice that stopping
at 9.6 is probably not your best choice for a migration today.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Peter Eisentraut-6
In reply to this post by Laurenz Albe
On 2019-12-04 08:56, Laurenz Albe wrote:

> On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote:
>> On Tue, Dec 03, 2019 at 10:32:22PM +0000, Julie Nishimura wrote:
>>> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
>>> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
>>> server, with minimal downtime?
>>> The caveat is the source has about 80 databases overall almost 30
>>> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
>>> pg_restore, but the largest hot database is almost 17 tb, and I am
>>> not sure how to approach this effort in a better and efficient way?
>>
>> pg_upgrade could be one way to go here.  That's not the scale pg_dump
>> would be very good at.  I would have personally avoided using pg_dump
>> above 10~20GB.  Depending on the downtime you are ready to accept,
>> a migration based on Slony could be something to investigate.
>
> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.

Also consider Londiste.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Julie Nishimura
In reply to this post by Tom Lane-2
Thank you everybody for your suggestions.
So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix app related issues (if any), then migrate to a version more recent than 9.6, either through pg_basebackup or through logical replication (if we would upgrade to version 10).

First step requires downtime. Second does not. Correct?

-Julie


From: Tom Lane <[hidden email]>
Sent: Wednesday, December 4, 2019 5:28 AM
To: Stephen Frost <[hidden email]>
Cc: Laurenz Albe <[hidden email]>; Michael Paquier <[hidden email]>; Julie Nishimura <[hidden email]>; [hidden email] <[hidden email]>; pgsql-general <[hidden email]>
Subject: Re: upgrade and migrate
 
Stephen Frost <[hidden email]> writes:
> * Laurenz Albe ([hidden email]) wrote:
>> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>> I would upgrade to a version more recent than 9.6.

> So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
> support upgrading from 8.3.X.  Support for upgrading from 8.3 was
> removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.

Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
huge jump in terms of minor compatibility issues (have you read
all the relevant release notes?).  So there's something to be said
for breaking this down into two steps: update to 9.4, test/fix
your applications against that, then make a second jump to something
current.  Each of those jumps could be handled by the respective
version of pg_upgrade.  I concur with Laurenz's advice that stopping
at 9.6 is probably not your best choice for a migration today.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Adrian Klaver-4
On 12/6/19 8:14 AM, Julie Nishimura wrote:
> Thank you everybody for your suggestions.
> So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix
> app related issues (if any), then migrate to a version more recent than
> 9.6, either through pg_basebackup or through logical replication (if we
> would upgrade to version 10).
>
> First step requires downtime. Second does not. Correct?

Well pg_basebackup is a binary copy so I am pretty sure you cannot use
the copy from an old Postgres version with a new Postgres major version.
I am also pretty sure whatever you do there is going to be some
downtime. Left unsaid to date and relevant to downtime:

1) Space/machines available to juggle multiple Postgres instances?

2) Network proximity of above.

3) Whether it is essential all the databases remain in a single cluster?

>
> -Julie
>
> ------------------------------------------------------------------------
> *From:* Tom Lane <[hidden email]>
> *Sent:* Wednesday, December 4, 2019 5:28 AM
> *To:* Stephen Frost <[hidden email]>
> *Cc:* Laurenz Albe <[hidden email]>; Michael Paquier
> <[hidden email]>; Julie Nishimura <[hidden email]>;
> [hidden email] <[hidden email]>;
> pgsql-general <[hidden email]>
> *Subject:* Re: upgrade and migrate
> Stephen Frost <[hidden email]> writes:
>> * Laurenz Albe ([hidden email]) wrote:
>>> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>>> I would upgrade to a version more recent than 9.6.
>
>> So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
>> support upgrading from 8.3.X.  Support for upgrading from 8.3 was
>> removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.
>
> Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
> huge jump in terms of minor compatibility issues (have you read
> all the relevant release notes?).  So there's something to be said
> for breaking this down into two steps: update to 9.4, test/fix
> your applications against that, then make a second jump to something
> current.  Each of those jumps could be handled by the respective
> version of pg_upgrade.  I concur with Laurenz's advice that stopping
> at 9.6 is probably not your best choice for a migration today.
>
>                          regards, tom lane


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Justin-2
In reply to this post by Julie Nishimura
Off topic but food for thought given the jump in versions 8.3 to (9.6 or greater.)  

List of major changes i can think of to watch out for that can bite without warning..   

SQL operators~=~   for Like were drop in 8.4 => for hstore was drop in 9.0
in 9.1 standard_conforming_string is  ON by default   previous the default was Off..
9.4 affected  arrays. 
9.5 operator precedence was changed  this affects output and near impossible to find all the code it can affect.. 
10 removed contrib/tsearch2 (added to the core in 8.3)

There are allot more minor changes that can bit....

Has a test machine been setup and run yet??  just dumping the schema and restore to later version will point out several issues with custom data-types,  plpgsql, other PL languages and any missing extensions...



     




On Fri, Dec 6, 2019 at 11:16 AM Julie Nishimura <[hidden email]> wrote:
Thank you everybody for your suggestions.
So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix app related issues (if any), then migrate to a version more recent than 9.6, either through pg_basebackup or through logical replication (if we would upgrade to version 10).

First step requires downtime. Second does not. Correct?

-Julie


From: Tom Lane <[hidden email]>
Sent: Wednesday, December 4, 2019 5:28 AM
To: Stephen Frost <[hidden email]>
Cc: Laurenz Albe <[hidden email]>; Michael Paquier <[hidden email]>; Julie Nishimura <[hidden email]>; [hidden email] <[hidden email]>; pgsql-general <[hidden email]>
Subject: Re: upgrade and migrate
 
Stephen Frost <[hidden email]> writes:
> * Laurenz Albe ([hidden email]) wrote:
>> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>> I would upgrade to a version more recent than 9.6.

> So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
> support upgrading from 8.3.X.  Support for upgrading from 8.3 was
> removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.

Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
huge jump in terms of minor compatibility issues (have you read
all the relevant release notes?).  So there's something to be said
for breaking this down into two steps: update to 9.4, test/fix
your applications against that, then make a second jump to something
current.  Each of those jumps could be handled by the respective
version of pg_upgrade.  I concur with Laurenz's advice that stopping
at 9.6 is probably not your best choice for a migration today.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: upgrade and migrate

Julie Nishimura
These are great points. Thanks! I will push for some testing environment


From: Justin <[hidden email]>
Sent: Friday, December 6, 2019 10:06 AM
To: Julie Nishimura <[hidden email]>
Cc: Tom Lane <[hidden email]>; Stephen Frost <[hidden email]>; Laurenz Albe <[hidden email]>; Michael Paquier <[hidden email]>; [hidden email] <[hidden email]>; pgsql-general <[hidden email]>
Subject: Re: upgrade and migrate
 
Off topic but food for thought given the jump in versions 8.3 to (9.6 or greater.)  

List of major changes i can think of to watch out for that can bite without warning..   

SQL operators~=~   for Like were drop in 8.4 => for hstore was drop in 9.0
in 9.1 standard_conforming_string is  ON by default   previous the default was Off..
9.4 affected  arrays. 
9.5 operator precedence was changed  this affects output and near impossible to find all the code it can affect.. 
10 removed contrib/tsearch2 (added to the core in 8.3)

There are allot more minor changes that can bit....

Has a test machine been setup and run yet??  just dumping the schema and restore to later version will point out several issues with custom data-types,  plpgsql, other PL languages and any missing extensions...



     




On Fri, Dec 6, 2019 at 11:16 AM Julie Nishimura <[hidden email]> wrote:
Thank you everybody for your suggestions.
So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix app related issues (if any), then migrate to a version more recent than 9.6, either through pg_basebackup or through logical replication (if we would upgrade to version 10).

First step requires downtime. Second does not. Correct?

-Julie


From: Tom Lane <[hidden email]>
Sent: Wednesday, December 4, 2019 5:28 AM
To: Stephen Frost <[hidden email]>
Cc: Laurenz Albe <[hidden email]>; Michael Paquier <[hidden email]>; Julie Nishimura <[hidden email]>; [hidden email] <[hidden email]>; pgsql-general <[hidden email]>
Subject: Re: upgrade and migrate
 
Stephen Frost <[hidden email]> writes:
> * Laurenz Albe ([hidden email]) wrote:
>> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
>> I would upgrade to a version more recent than 9.6.

> So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
> support upgrading from 8.3.X.  Support for upgrading from 8.3 was
> removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.

Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
huge jump in terms of minor compatibility issues (have you read
all the relevant release notes?).  So there's something to be said
for breaking this down into two steps: update to 9.4, test/fix
your applications against that, then make a second jump to something
current.  Each of those jumps could be handled by the respective
version of pg_upgrade.  I concur with Laurenz's advice that stopping
at 9.6 is probably not your best choice for a migration today.

                        regards, tom lane