Re: pg_upgrade + replica servers + rsync --size-only is unsafe - Mailing list pgsql-admin

Hello Stephen,

>Then you run the rsync, at the /srv level, and all those 'new files' in
>/srv/new_cluster on the primary should get copied over to
>/srv/new_cluster on the replica, while any files which are hard-linked
>between old_cluster and new_cluster should end up as hard links on the
>replica.
>
>Thanks,

Thanks very much for your reply.  

We've re-ran our test upgrade this morning just to be certain and we reproduced the same result once again, it appears that the issue is not that the pg_controlfile is hardlinked, but that it exists at all.

The output of pg_controldata shows a different ID on the master and the secondary as you suspected:

Master:

$ ./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number:            960
Catalog version number:               201608131
Database system identifier:           6901965895850147745
Database cluster state:               in production
pg_control last modified:             Thu 03 Dec 2020 10:11:21 GMT

Secondary:

$ ./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number:            960
Catalog version number:               201608131
Database system identifier:           6901966223447496344
Database cluster state:               shut down
pg_control last modified:             Thu 03 Dec 2020 09:54:00 GMT

The pg_control file does exist on the secondary 9.6 prior to the rsync because it is created by the initdb command (which is step 4. of the instructions - https://www.postgresql.org/docs/9.6/pgupgrade.html; we verified that by running `/usr/lib/postgresql/9.6/bin/initdb /var/lib/postgresql/999/main` and it creates a pg_control file).  

The pg_control files are both exactly 8192 bytes; removing the --size-only option resolves this and causes rsync to copy the control file across, which can be confirmed by the --dry-run option:

$ rsync --verbose --dry-run --archive --delete --hard-links --no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6 PGRETESTA02:/media/postgresql/data
building file list ... done

... <snip>

9.6/main/global/pg_control

... <snip>

sent 252,838 bytes  received 666 bytes  507,008.00 bytes/sec
total size is 12,061,021,649  speedup is 47,577.24 (DRY RUN)

So it seems like setting --size-only guarantees disaster in this flow? However we are definitely confused as to why this isn't hit more often, as it seems that this would always happen?

I've attached the following that might clarify:
- raw output of the rsync that we ran during step 12.a of our instructions (master-rsync.txt)
- pg_controldata raw output from both master and secondary once the error occurs (master-pgcontroldata.txt and secondary-pgcontroldata.txt)
- A copy of our internal instructions with the commands that we're using at each step to build our script. We've based this on the instructions at https://www.postgresql.org/docs/9.6/pgupgrade.html with some tweaks for our configuration.

Some things to note about our setup which I'm adding for completeness as it may have a bearing on the result we're seeing:
- the 9.5 database is currently in /media/postgresql/data/main and we are moving to a /media/postgresql/data/main/$version structure to make it easier for upgrades as part of this move (hence why the rsync command isn't 'symmetrical' in the arguments)
- additionally we symlink the /var/lib/postgresql/$version/main directory off to that location.
- We're using Debian 9 and the postgresql apt packages
- As we are using Debian, the configuration for the postgresql DB's lives in /etc/postgresql/$version/main

Many Thanks
Rob