Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er6Cx-0007t6-8J for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 18:12:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er6Cv-0002vt-VG for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 18:12:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er6Cv-0002vj-FF for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 18:12:01 +0000 Received: from mr28p48im-tydg10101201.me.com ([17.110.88.44]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1er6Cs-0007FE-O4 for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 18:12:00 +0000 Received: from process-dkim-sign-daemon.mr28p48im-tydg10101201.me.com by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) id <0P4V00100H7FIJ00@mr28p48im-tydg10101201.me.com> for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 18:11:57 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1519841517; bh=JIOjRwpYtQhEM8S+RpT+BrfRKf1a1mW4oH1Lqw8iGXE=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=PoytkYIEoug66iDs72RoPnYikHxGwJqjy/kMEnvRfWvFQ6eKhwihF8C3mT8kWzQYh 89qK8UQmwTh4DLHrxT6gATlz/yMCMFgN5gliEpg/bJ/5G9S+v5paGbvnNs1XDyOTw9 REZp+WIKKgVG4iVDCf4SE1DnPXj+t6fBy48qmxjWbvEjR4za7gKFKISOhdKyiURx2V HXxvuaRuQqwY/+C8AWKRsPje71zr4mZilf+pIHpfo0Z/pzjGVpmZepNs9KVi9qJ2x/ Pam11d/IjNeovByXHWPnDnwcPfcxl+5bwo6A84eJFvEXKwVgyJDfqqe1+LI2NWvROc uVtMDrgAbVHLg== Received: from icloud.com ([127.0.0.1]) by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) with ESMTPSA id <0P4V00CFQH7VC850@mr28p48im-tydg10101201.me.com>; Wed, 28 Feb 2018 18:11:57 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-02-28_10:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1015 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1802280221 From: Rui DeSousa Message-id: <285DC762-7035-4313-B550-5DA2273D7066@icloud.com> Content-type: multipart/alternative; boundary="Apple-Mail=_F6A62AD3-9D1F-4AA5-B115-E9D8A4668F66" MIME-version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: Reliable WAL file shipping over unreliable network Date: Wed, 28 Feb 2018 13:11:54 -0500 In-reply-to: <954e01b6-b240-eb9a-5feb-8efd202fef5a@shopzeus.com> Cc: pgsql-admin@lists.postgresql.org To: =?utf-8?Q?Nagy_L=C3=A1szl=C3=B3_Zsolt?= References: <400AC9CD-1E43-422C-A689-70BDBAFF231E@elevated-dev.com> <954e01b6-b240-eb9a-5feb-8efd202fef5a@shopzeus.com> X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_F6A62AD3-9D1F-4AA5-B115-E9D8A4668F66 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Easiest thing to do is to keep the replica from seeing the file until = it=E2=80=99s successfully transferred; i.e. 1. Transfer the file successfully first with a temporary extension: = $ARCH_DIR/$WALFILE.swap 2. If successfully transferred then move it into place: mv $ARCH_DIR/$WALFILE.swap $ARCH_DIR/$WALFILE 3. Return status success/failure code back to primary > On Feb 28, 2018, at 12:53 PM, Nagy L=C3=A1szl=C3=B3 Zsolt = wrote: >=20 >>=20 >>=20 >> Just use "-ac=E2=80=9D; you want -c option to ensure no data = corruption during the transfer. Do not delete the file; let Postgres = manage that. >>=20 >> Here is a snippet from I script I use for archiving. You also want = to make your script returns failure or success correctly. =20 >>=20 >> # SSH Command and options >> SSH_CMD=3D"ssh -o ServerAliveInterval=3D20 $ARCH_SERVER" >> STS=3D3 >>=20 >> OUTPUT=3D$(rsync -ac --rsync-path=3D"mkdir -p $ARCH_DIR && rsync" = $XLOGFILE $ARCH_SERVER:$ARCH_DIR/$WALFILE) >> if [ $? =3D=3D 0 ]; then=20 >> STS=3D0 >> fi >>=20 >> exit $STS >>=20 > Thanks for the script. So I need to use this on the master side in = archive_command. It ensures that postgres will retry to transfer = partially transferred files until it succeeds. Unfortunately, I cannot = use this in my docker container which is isolated and does not contain = SSH or rsync. But I get the idea and I can come up with a simple script = that runs on the host machine and transfers these files reliably to the = slave side. >=20 > But I still don't understand what happens on the slave side when the = slave tries to use a partially transferred WAL file. I have this = recovery.conf on the slave side: >=20 > standby_mode=3D'on' > primary_conninfo=3D'host=3Dpostgres-master port=3D5432 = user=3Dnot_telling password=3Dnot_telling' > trigger_file=3D'/backup/trigger' > restore_command =3D 'cp /transfer/%f %p' > archive_cleanup_command =3D 'pg_archivecleanup /transfer %r' >=20 > So what happens when the slave postgres executes restore_command on a = WAL file that was transferred partially? It cannot test the file for = completeness before it was already copied to pg_wal. The restore command = itself also cannot tell if the file is complete or not. What does = PostgreSQL do when it sees an incomplete file in pg_wal? Detects that = the file was incomplete, and tries to execute the restore_command again? = When? How often? >=20 > It seems inefficient to execute the restore_command multiple times, = just to find out that the file was not yet complete, but if this is how = it works, then it is fine with me. But does it really work that way? I = don't see it documented (or maybe I'm looking at the wrong place.) >=20 > Sorry for the many questions. I need to understand every detail of = this before we do it in production. >=20 --Apple-Mail=_F6A62AD3-9D1F-4AA5-B115-E9D8A4668F66 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Easiest thing to do is to keep the replica from seeing the = file until it=E2=80=99s successfully transferred; i.e.

1. = Transfer the file successfully first with a temporary extension:   = $ARCH_DIR/$WALFILE.swap
2. If successfully = transferred then move it into place:
    = mv $ARCH_DIR/$WALFILE.swap $ARCH_DIR/$WALFILE
3. = Return status success/failure code back to primary


On Feb = 28, 2018, at 12:53 PM, Nagy L=C3=A1szl=C3=B3 Zsolt <gandalf@shopzeus.com> wrote:



Just use "-ac=E2=80=9D;  you want -c option to ensure no = data corruption during the transfer.  Do not delete the file; let = Postgres manage that.

Here is a snippet from I script I use for archiving. =  You also want to make your script returns failure or success = correctly.  

# SSH Command and = options
SSH_CMD=3D"ssh -o ServerAliveInterval=3D20 = $ARCH_SERVER"
STS=3D3

OUTPUT=3D$(rsync -ac --rsync-path=3D"mkdir -p = $ARCH_DIR && rsync" $XLOGFILE = $ARCH_SERVER:$ARCH_DIR/$WALFILE)
if [ $? =3D=3D 0 = ]; then 
   STS=3D0
fi

exit $STS

Thanks for the script. So I need to use this on the master = side in archive_command. It ensures that postgres will retry to transfer = partially transferred files until it succeeds. Unfortunately, I cannot = use this in my docker container which is isolated and does not contain = SSH or rsync. But I get the idea and I can come up with a simple script = that runs on the host machine and transfers these files reliably to the = slave side.

But I still don't = understand what happens on the slave side when the slave tries to use a = partially transferred WAL file. I have this recovery.conf on the slave = side:

standby_mode=3D'on'
primary_conninfo=3D'host=3Dpostgres-master port=3D5432 = user=3Dnot_telling password=3Dnot_telling'
trigger_file=3D'/backup/trigger'
restore_command =3D 'cp /transfer/%f = %p'
archive_cleanup_command =3D 'pg_archivecleanup /transfer = %r'

So what happens = when the slave postgres executes restore_command on a WAL file that was = transferred partially? It cannot test the file for completeness before = it was already copied to pg_wal. The restore command itself also cannot = tell if the file is complete or not. What does PostgreSQL do when it = sees an incomplete file in pg_wal? Detects that the file was incomplete, = and tries to execute the restore_command again? When? How = often?

It seems = inefficient to execute the restore_command multiple times, just to find = out that the file was not yet complete, but if this is how it works, = then it is fine with me. But does it really work that way? I don't see = it documented (or maybe I'm looking at the wrong place.)

Sorry for the many questions. I need to = understand every detail of this before we do it in production.


= --Apple-Mail=_F6A62AD3-9D1F-4AA5-B115-E9D8A4668F66--