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.

"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
stilltake 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