Date: Wed, 29 Nov 2017 09:54:55 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Alex Kliukin <alexk@hintbits.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: 'replication checkpoint has wrong magic' on the newly cloned
 replicas
Message-ID: <20171129145455.GL4628@tamriel.snowman.net>
References: 
 <1511966240.1459861.1188109744.2D26AD08@webmail.messagingengine.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="P5XG86SG82n9iZoY"
Content-Disposition: inline
In-Reply-To: 
 <1511966240.1459861.1188109744.2D26AD08@webmail.messagingengine.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk


--P5XG86SG82n9iZoY
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Alex Kliukin (alexk@hintbits.com) wrote:
> The cloning itself is done by copying a compressed image via ssh,
> running the
> following command from the replica:
>=20
>  """ssh {master} 'cd {master_datadir} && tar -lcp --exclude "*.conf" \
>          --exclude "recovery.done" \
>          --exclude "pacemaker_instanz" \
>          --exclude "dont_start" \
>          --exclude "pg_log" \
>          --exclude "pg_xlog" \
>          --exclude "postmaster.pid" \
>          --exclude "recovery.done" \
>            * | pigz -1 -p 4' | pigz -d -p 4 | tar -xpmUv -C
>            {slave_datadir}""
>=20
> The WAL archiving starts before the copy starts, as the script that
> clones the
> replica checks that the WALs archiving is running before the cloning.

Maybe you've doing it and haven't mentioned it, but you have to use
pg_start/stop_backup because otherwise PG is going to think it's doing
crash recovery from the last checkpoint written, rather than having to
go back to when the backup started and replay all of the WAL from that
point.

Basically, this process is entirely broken unless you're actually taking
a filesystem-level atomic snapshot first (and that has to be atomic
across all tablespaces too).  Perhaps that's what you meant when you
mentioned a snapshot, but if it, then this definitely isn't good.

Note that if you use pg_start/stop_backup, you need to make sure to wait
for the replica to be all the way caught up with where the
'pg_start_backup' was issued on the primary before you start copying
files on the replica.

> We have cloned hundreds of replicas with that procedure and never saw
> any
> issues, also never saw the "replication checkpoint has wrong magic"
> error, so
> we are wondering what could be the possible reason behind that failure?
> We also
> saw the disk error on another shard not long after the initial copy (but
> not on
> those that had the "replication checkpoint error"), so hardware issues
> are on
> our list as well (but then how comes both had the same wrong value for
> the
> "wrong magic"?)

If you've not seen any other corruption due to this, I'd call you
extremely lucky.  I'd strongly suggest you look at some of the existing
tools for doing backup/recovery of PG and use them to build out
replicas.

Thanks!

Stephen

--P5XG86SG82n9iZoY
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJaHso/AAoJEO1sijiDR2RVehoP/3+ycGOMOCyonvCvC1KJBYeF
GhGOz4SGsv0iy+Lk7J7YxNLthbMO5bai+y2c9R5APeEVcKcO+El+EKckiXM79Prx
sJ+cSmhnAYiWVRb0V9xtG9UvMIIG8D/ngORQQXtE9L3NHqPgAmz3kXtxKXYQjwAN
BSOo3/DFsyQ5nalDKRrIJJxmxnky/+6+Q74mX49qkdHBPmkOHe3JdwR99VVgTGau
gAbZFYjRgvAnZuo7i4XMz64dM/ImLZOXrUzejavEMuS7G6QywgLmQi5EIA6KJONV
EUWvoEQq8usSlulF2kTNrFCZtNpdKfVh7S21iI5liG5Vl7svSZI1PtCU3VT6PjCF
faPbAyik+aw/g1nxyegKC7LQ9SudGjEHDzF7KpiR66OxasTaN9OVq8Yo1K8NYI6g
TDdM/5PnzM+W5ppzbW6UfbQuuZRB2DRIdx5swHW4VXdV9hD1TXxwJmgIeCuPPNwb
79twXOEYHcgHyddaaalWaYF47MVViTkuuzZFWxta2Wo3adcrgtJlcEPJ0uHEyKX6
zhBzjrhN/yggtPpgC0UFJuTU8q6LyNBqJGtlMauh+EcncBiF3vmsaohRZldSs7+0
59YBuztOmIW1r76MO42fgfRPZpOhng5uFJteQYFZYTvbxDtqKzkdi+wo6mN3oins
MX6SXITZRjYgaR43PvWy
=6kei
-----END PGP SIGNATURE-----

--P5XG86SG82n9iZoY--