Greetings, * Martín Fernández (fmartin91@xxxxxxxxx) wrote: > I've wrote a couple of questions around pg_upgrade and updating standbys using rsync last week. We were able to successfully upgrade half of our cluster (the other half was kept for failover) from pg92 with postgis 1.5.8 to pg10 with postgis 2.4. It was a really interesting challenge because of postgis binary incompatibility for geometry data types. > > The rsync call that we used looked exactly like this (taken from pg_upgrade man page basically): > > `rsync --verbose --verbose --progress --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/postgres/9.2 /var/lib/postgres/10 $REPLICA_IP:/var/lib/postgres` > > We are now in the journey of upgrading the other half of the cluster since we have concluded that the upgrade was successful. > > We are planning on using the same rsync call to upgrade the rest of the standbys (in combination with pg_start_backup/pg_stop_backup low level api). My only concern is that I'm not 100% sure if the `--size-only` flag will be enough to guarantee that files are the same. On the initial set of standbys that we upgraded this shouldn't generate an issue since the standbys were at the same last checkpoint than the master and we did the rsync call before starting the primary (after running pg_upgrade). No, you can't use --size-only to rebuild those replicas while the primary is online, even if you're using pg_start/stop_backup, you should really enable the rsync checksums-based check to make sure that you're copying all of the files that you need to from the primary to the replica during the pg_start/stop_backup, and then you need to make sure and have an appropriate backup_label installed on all the replicas to get them to replay from the pg_start_backup checkpoint and to replay through to the end of the pg_stop_backup. You would have a recovery.conf file already but you might need to make sure it has a restore_command which can pull back WAL that might have already been archived by the primary. Note that this method of rebuilding the replicas will likely be time-consuming but unfortunately it's necessary. There are alternatives to using rsync to perform this if you need to get it done faster. > Is there any potential issues that could show up if we do it with --size-only ? Should we use the default rsync mechanism that would check for size and timestamps ? I wouldn't trust just size/timestamp in this case, you really should use checksums. > Hoping someone has some better experience than me on upgrading standbys using rsync. The rsync-based pg_upgrade mechanism for replicas *only* works when it's done after all of the systems have been shut down and you've verified that all the nodes reached the same shutdown checkpoint, it is *not* appropriate for online rebuilding of replicas. Thanks! Stephen
Attachment:
signature.asc
Description: PGP signature
