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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Upgrade PGSQL main version without backup/restore all databases?
Date: 2019-11-06 13:21:20
Message-ID: 45981fe3-34d4-e372-7d6d-af70e84e732a@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Hugh Ranalli 2019-11-06 15:49:48 Re: Hunspell as filtering dictionary
Previous Message Durumdara 2019-11-06 13:09:42 Upgrade PGSQL main version without backup/restore all databases?