Upgrade PGSQL main version without backup/restore all databases?

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

Upgrade PGSQL main version without backup/restore all databases?

durumdara
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
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PGSQL main version without backup/restore all databases?

Thomas Kellerer
Durumdara schrieb am 06.11.2019 um 14:09:
> 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.

"Shortly" is relative. It will fall out of support in 2021: https://www.postgresql.org/support/versioning/

But it's a good idea to plan the upgrade now.


> 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?

Yes, you can use pg_upgrade. However it will still copy 200GB (but using a filecopy, not dump/restore) so it could still take some time.

If you use it with the --link option, the upgrade will be very quick as only the catalog tables need to be copied (export/import).

 
> 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...

There are some ways to do a near-zero upgrade using logical replication, but it's not easy to configure.

See this blog post for example:
https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/

Thomas




Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PGSQL main version without backup/restore all databases?

Ravi__Krishna
In reply to this post by durumdara
-k option is kept precisely for this.  The upgrades are pretty fast, but still with some downtime. may be 30-45 min tops.