Upgrade PGSQL main version without backup/restore all databases?

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Upgrade PGSQL main version without backup/restore all databases?
Date: 2019-11-06 13:09:42
Message-ID: CAEcMXh=XHfFpJKM+0B+gfYxrES8T1cqghofenT6c6eW-P00RWg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Members!

We have PGSQL 9.6.xxx on a Linux server which heavily used.
More than 100 databases, and more than 300 active users, and it is a master
of a cluster (the data replicated on a slave).

Somewhere we have read that 9.6 will become unsupported shortly.

We need to prepare upgrade.

In Windows test environment I experienced long time ago that new versions
installed in different folders, so I can't upgrade the PG and the database
simply...
I must dump all databases and restor under new PG - what is very wrong.

This Linux is heavily used.
We can't stop to lock out all users, make a dumpall, upgrade, restore them
all in new version, and then leave them to connect (200 GB of data),
because it is too long.

Is there any way to upgrade PG and databases without backup/restore?

Maybe the solution is to upgrade slave without sync the data changes
before; and if all is newer, leave to get the new data from the master. I
don't know it's possible or not.
The newer PG slave could make mistakes if the master have lower PG
version...

Do you know any idea for this operation?

Thank you!

Best regards
dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2019-11-06 13:21:20 Re: Upgrade PGSQL main version without backup/restore all databases?
Previous Message Durumdara 2019-11-06 12:51:49 Re: Vacuum very big table - how the full vacuum works in background/internally?