Date: Wed, 2 Dec 2020 12:28:05 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Rob Emery <re-pgsql@codeweavers.net>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pg_upgrade + replica servers + rsync --size-only is unsafe
Message-ID: <20201202172805.GV16415@tamriel.snowman.net>
References: 
 <CAPCETpv6esrADG8koCxzwUWAz4iJCjKt4aCcdZS09mB-y9TwsQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="pIsg+b+kdtx88zQh"
Content-Disposition: inline
In-Reply-To: 
 <CAPCETpv6esrADG8koCxzwUWAz4iJCjKt4aCcdZS09mB-y9TwsQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--pIsg+b+kdtx88zQh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Rob Emery (re-pgsql@codeweavers.net) wrote:
> We're pretty sure that we've just hit a scenario where the instructions f=
or
> pg_upgrade with standby servers aren't quite safe.

I've pretty much always felt those instructions should come with a
"expert use only" note.

> We were testing our upgrade process with a copy of our live cluster in a
> lab environment and we found that using
>=20
> rsync --verbose --archive --delete --hard-links --size-only
> --no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6
> PGRETESTA02:/media/postgresql/data
>=20
> Resulted in:
>=20
> 2020-12-02 14:49:11.513 GMT [20884-1] LOG:  database system was shut
> down in recovery at 2020-12-02 13:57:56 GMT
> 2020-12-02 14:49:11.513 GMT [20884-2] LOG:  entering standby mode
> 2020-12-02 14:49:11.557 GMT [20884-3] LOG:  consistent recovery state
> reached at 19E/25000098
> 2020-12-02 14:49:11.557 GMT [20884-4] LOG:  invalid record length at
> 19E/25000098: wanted 24, got 0
> 2020-12-02 14:49:11.559 GMT [20883-1] LOG:  database system is ready
> to accept read only connections
> 2020-12-02 14:49:11.593 GMT [20888-1] FATAL:  database system
> identifier differs between the primary and standby
> 2020-12-02 14:49:11.593 GMT [20888-2] DETAIL:  The primary's
> identifier is 6901669428825624285, the standby's identifier is
> 690161835164
> 1138930.
>=20
> however if we don't use --size-only, then the process worked fine and we a
> smooth upgrade.

This doesn't look like a --size-only issue though- how did you end up
with this when the pg_controldata file (where the system identifier is
pulled from) shouldn't even exist and won't be a hardlink to the
existing one on the old system since it's a new cluster, and therefore
should definitely be copied?

When you go to run the rsync to get the replica up to date you should
have a system that looks like this:

PRIMARY:

/srv/old_cluster
   ... lots of files
/srv/new_cluster
   ... lots of *new* files, including pg_controldata
   ... hardlinks to PG table/index files that aren't part of the catalog

REPLICA:

/srv/old_cluster
   ... lots of files
/srv/new_cluster
   ... entirely empty

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,

Stephen

--pIsg+b+kdtx88zQh
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJfx86lAAoJEO1sijiDR2RV5/kP/RjCmv0maVPYm+/Knv6Ebnti
KRrecY+WkRj+MAUoDvL9Xm3iggjwGH79c3Hf1VZF6kkot/UpVPyp0b5MA+6P13OF
D43OBSh7g8W/RitsCK9gXcmxhC+Vtm+kUge5tvy+jKxakbPZR2tLD4E3+i1Vqljn
hDXgFIbhvcOIjDl5RJj/ixERJldPeWyz8S3Ws5vObEy1WUKWN7E/G0+rKt0cCjDH
aGe2UW17eIlE2Y3ZQyRRwpn2ddKQ1SlNno2ftWtfOLqf2qjPEVSt1Hs/Zy1Zs6fe
GjL4U8b0ZlVkm4eyuNJC7L+QpuNRxHGbYZhJMcgbiZc496W6NPl3vF2VED90v/2T
ligir+J/000s+GkD+EFmFr5Hw0I4auM1c2wuLofZJ1+nK9uK8Zbmlphv+GPQvZl5
4ZDYyP+ru70WFGyM/YcJeWMasMOiTfWKPtoZU4CaTfFdEb2ix6jSuh7qCNArY1b7
TnaPJmIyEKi/xI9Lhxv/sIRIhCsM1E1rvd5P0eJrY3dGKoL/nPnWVsQDdUbHn7f9
0d865LoEMZOj4J6XAd8Ddi4E/1ktRClQgvD2MUzZ24wBDlIGjTzwfJVVVJMVZ10s
3Rg2pJAjTemRFjFnyOT6vwOi1sLsdgeEitFI7kugGS4PTm5Z76UBT6jWynzanfVZ
JVGhnqrJg5QtYj2qAfGK
=gLIt
-----END PGP SIGNATURE-----

--pIsg+b+kdtx88zQh--