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