Re: Upgrade PGSQL main version without backup/restore all databases?
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.
> 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.