Обсуждение: upgrade and migrate

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

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.

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.

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

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

Greetings,

* Laurenz Albe (laurenz.albe@cybertec.at) 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

Stephen Frost <sfrost@snowman.net> writes:
> * Laurenz Albe (laurenz.albe@cybertec.at) 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

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

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 <tgl@sss.pgh.pa.us>
> *Sent:* Wednesday, December 4, 2019 5:28 AM
> *To:* Stephen Frost <sfrost@snowman.net>
> *Cc:* Laurenz Albe <laurenz.albe@cybertec.at>; Michael Paquier 
> <michael@paquier.xyz>; Julie Nishimura <juliezain@hotmail.com>; 
> pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; 
> pgsql-general <pgsql-general@postgresql.org>
> *Subject:* Re: upgrade and migrate
> Stephen Frost <sfrost@snowman.net> writes:
>> * Laurenz Albe (laurenz.albe@cybertec.at) 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
adrian.klaver@aklaver.com