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
