Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eK7mh-0000Ys-QZ for pgsql-admin@arkaria.postgresql.org; Wed, 29 Nov 2017 19:12:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eK7mg-0007Ma-T4 for pgsql-admin@arkaria.postgresql.org; Wed, 29 Nov 2017 19:12:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eK7mg-0007MQ-Ks for pgsql-admin@lists.postgresql.org; Wed, 29 Nov 2017 19:12:38 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eK7mc-0000eR-6A for pgsql-admin@postgresql.org; Wed, 29 Nov 2017 19:12:38 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 6F95820C6A; Wed, 29 Nov 2017 14:12:32 -0500 (EST) Received: from frontend2 ([10.202.2.161]) by compute4.internal (MEProxy); Wed, 29 Nov 2017 14:12:32 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fastmail.com; h= cc:content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; bh=T21d2nSj1OI5dr8LTtjVLgSHjaO2hy15N5/gb/Jfk5M=; b=oJncjAEq oSnEzPTjGtQUEJtZQmoMpLVKWMTrIeHHW3+Q3h9Se4k/tljAm+Isgk5UUb4Z8hIQ sRbvLeKX7CDlUimISlnVBuzppFvXNpvMGFmzeoGeld8SdXCn4BcSDw2AY0oomRq2 UgZzoJW9JjRfVTpbgALpT76BQZT7TWWvzQXXDAOdZqAr/ZCYxhhU1MNolPcwAYFi 2FAz4+rnzVwKF/RrWh9i4Tk+N05GwbnpYp6603QFC+4b4EVspMQk6ejzyyHxmrmt 36bBukjxi3/599ABQmIqwgt1W3ZBj88cSA10bou+E3R/bmgkQuJLPc0Vm7/ki4zm 6Wxz54aq3A3qFQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; bh=T21d2nSj1OI5dr8LTtjVLgSHjaO2h y15N5/gb/Jfk5M=; b=Ma749ajFXrWUFOh1xVnJFxJPczxhJ2pQVyR3s0flpcVMt Gr9cWqm7I5NH1JW1BuF66EQ1vaxizABSm4gIvve9Od0AolY9fKtkuKYCnSZcgOEc wfoaAYrfQNoYvrR1HQXniy+rUmLmPfsi4vLWeXE/u9/oqCv4WapUAUgr8SMGNUqE RnJ1GhAijHk5+Kh3nU864SbLKFB+pylNWz4SDdN8b9Z/rbrvYC0nSzzMl3ym/LMz 3YEgBzX4MneJY2rEXuLknYBuYJdjiBl11EKqtP7Ki1Bg/u69D1BzAqLfWW/qlsuZ ZZfpNeUDqhaJVggW4gu4ETAPltilxu2/zhg6/zYmQ== X-ME-Sender: Received: from [192.168.178.37] (p5b35932b.dip0.t-ipconnect.de [91.53.147.43]) by mail.messagingengine.com (Postfix) with ESMTPA id 29E1D24E84; Wed, 29 Nov 2017 14:12:30 -0500 (EST) From: Alex Kliukin Message-Id: <438F71B3-DCB9-4BFD-9D4C-D3CDD36734A6@fastmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_83AA137A-75BB-4DD1-AC80-1E581AF04ED0" Mime-Version: 1.0 (Mac OS X Mail 11.1 \(3445.4.7\)) Subject: Re: 'replication checkpoint has wrong magic' on the newly cloned replicas Date: Wed, 29 Nov 2017 20:12:28 +0100 In-Reply-To: Cc: pgsql-admin@postgresql.org To: Stephen Frost References: <1511966240.1459861.1188109744.2D26AD08@webmail.messagingengine.com> <20171129145455.GL4628@tamriel.snowman.net> X-Mailer: Apple Mail (2.3445.4.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_83AA137A-75BB-4DD1-AC80-1E581AF04ED0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 29. Nov 2017, at 19:44, Stephen Frost wrote: >=20 > Greetings, >=20 > On Wed, Nov 29, 2017 at 13:33 Alex Kliukin > wrote: >=20 >> On 29. Nov 2017, at 18:52, Stephen Frost > wrote: >>=20 >> Greetings, >>=20 >> On Wed, Nov 29, 2017 at 12:41 Oleksii Kliukin > wrote: >> Hi Stephen, >>=20 >> > On 29. Nov 2017, at 15:54, Stephen Frost > wrote: >> > >> > 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: >> >> >> >> """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}"" >> >> >> >> 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 >>=20 >> Sorry for not mentioning it, as it seemed obvious, but we are calling = pg_start_backup and pg_stop_backup at the right time. >>=20 >> Ah, not something I can assume, heh.=20 >>=20 >> Then it depends on which version of PG and if you=E2=80=99re able to = run start/stop on the replica or not. If you can=E2=80=99t run it on the = replica and have to run it on the primary (prior to 9.6) then you need = to make sure to wait for things to happen on the primary and for that to = be replicated before you can start. =20 >=20 > We are using exclusive backups from the master. First, the script = checks that WAL files are shipped to the NFS, where the replica expects = to find them (we check the md5 checksum of the file in order to make = sure that the NFS actually delivers the file that the master has = archived) . Then pg_start_backup runs on the master and its status is = checked. On success, the copy command runs. When the copy command = finishes, pg_stop_backup is executed. Once pg_stop_backup finishes = successfully, replica configuration files (postgesql.conf, pg_hba.conf. = pg_ident.conf) are linked from their location in the repository and the = replica is started. >=20 > No, you must wait until the replica has moved forward far enough and = you have to copy the backup_label file from the primary as well, = otherwise PG won=E2=80=99t realize you=E2=80=99re doing a backup-based = recovery Are you talking about the exclusive base backup from the master (the = master being the source for the backup)? At least the backup label is = written by pg_start_backup to the data directory and is being copied = together with the data directory. The necessary WAL files are archived = once pg_stop_backup returns, and the replica cannot move anywhere in = recovery without being started.=20 >=20 > This is a fairly typical procedure, which, I believe, is also well = described in the docs. >=20 > Please provide a link to where that is because if that=E2=80=99s the = case then we need to correct it or remove it. This is absolutely not = safe without additional checks being done and various other magic = happening (like copying the backup_label off the primary where it=E2=80=99= s created).=20 = https://www.postgresql.org/docs/current/static/continuous-archiving.html#B= ACKUP-LOWLEVEL-BASE-BACKUP-EXCLUSIVE = It has been there for years and I don=E2=80=99t think there is anything = terribly wrong there. >=20 >> If you=E2=80=99re on 9.6 and using non-exclusive backup, you need to = be sure to capture the contents of the stop backup and write it into = backup_label before you start the system back up.=20 >=20 > We don=E2=80=99t use non-exclusive backups altogether. >=20 > All the more likely that your procedure is causing more corruption = than you realize then.=20 How does exclusive backups make the procedure more prone to corruption? >=20 > Seriously, again, this is not easy to get right, especially when = you=E2=80=99re doing things that weren=E2=80=99t explicitly documented = and supported. Using existing tools from those versed in why the = processes used are safe and have written lots of tests to verify that it = is safe is really the recommendation that you should take away from = this.=20 =20 I believe what we are doing is rather simple and well documented by the = link above.=20 >=20 > At least with 9.6 there=E2=80=99s proper documentation on how to run a = non-exclusive backup on a replica properly and if you very carefully = follow the procedure then you may get it right, but you will still want = to test extensively.=20 We are not doing non-exclusive backups from the replica. Cheers, Alex= --Apple-Mail=_83AA137A-75BB-4DD1-AC80-1E581AF04ED0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 29. Nov 2017, at 19:44, Stephen Frost <sfrost@snowman.net> = wrote:

Greetings,

On = Wed, Nov 29, 2017 at 13:33 Alex Kliukin <oleksii@fastmail.com> wrote:

On 29. Nov 2017, at 18:52, Stephen Frost = <sfrost@snowman.net> wrote:

Greetings,

On Wed, Nov 29, = 2017 at 12:41 Oleksii Kliukin <oleksii@fastmail.com> wrote:
Hi = Stephen,

> On 29. Nov 2017, at 15:54, = Stephen Frost <sfrost@snowman.net> wrote:
>
> 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:
>>
>> """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}""
>>
>> 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

Sorry for not = mentioning it, as it seemed obvious, but we are calling pg_start_backup = and pg_stop_backup at the right time.

Ah, not = something I can assume, heh. 

Then it depends on which = version of PG and if you=E2=80=99re able to run start/stop on the = replica or not. If you can=E2=80=99t run it on the replica and have to = run it on the primary (prior to 9.6) then you need to make sure to wait = for things to happen on the primary and for that to be replicated before = you can start.  

We are using exclusive backups from the master.  First, = the script checks that WAL files are shipped to the NFS, where the = replica expects to find them (we check the md5 checksum of the file in = order to make sure that the NFS actually delivers the file that the = master has archived) . Then pg_start_backup runs on the master and its = status is checked. On success, the copy command runs. When the copy = command finishes, pg_stop_backup is executed. Once pg_stop_backup = finishes successfully, replica configuration files (postgesql.conf, = pg_hba.conf. pg_ident.conf)  are linked from their location in the = repository and the replica is = started.

No, you must wait until = the replica has moved forward far enough and you have to copy the = backup_label file from the primary as well, otherwise PG won=E2=80=99t = realize you=E2=80=99re doing a backup-based = recovery


Are you  talking = about the exclusive base backup from the master (the master being the = source for the backup)? At least the backup label is written by = pg_start_backup to the data directory and is being copied together with = the data directory. The necessary WAL files are archived once = pg_stop_backup returns, and the replica cannot move anywhere in recovery = without being started. 



This is = a fairly typical procedure, which, I believe, is also well described in = the docs.

Please provide a link to = where that is because if that=E2=80=99s the case then we need to correct = it or remove it. This is absolutely not safe without additional checks = being done and various other magic happening (like copying the = backup_label off the primary where it=E2=80=99s = created). 


It has been there for years and I don=E2=80=99t = think there is anything terribly wrong there.



If you=E2=80=99re on = 9.6 and using non-exclusive backup, you need to be sure to capture the = contents of the stop backup and write it into backup_label before you = start the system back up. 

We don=E2=80=99t use non-exclusive backups = altogether.
All the more likely that = your procedure is causing more corruption than you realize = then. 

How does exclusive backups make the procedure more = prone to corruption?



Seriously, again, this is = not easy to get right, especially when you=E2=80=99re doing things that = weren=E2=80=99t explicitly documented and supported. Using existing = tools from those versed in why the processes used are safe and have = written lots of tests to verify that it is safe is really the = recommendation that you should take away from = this. 
 
I = believe what we are doing is rather simple and well documented by the = link above. 



At least with 9.6 = there=E2=80=99s proper documentation on how to run a non-exclusive = backup on a replica properly and if you very carefully follow the = procedure then you may get it right, but you will still want to test = extensively. 

We are not doing non-exclusive backups from the = replica.

Cheers,
Alex
= --Apple-Mail=_83AA137A-75BB-4DD1-AC80-1E581AF04ED0--