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 1er5vU-0006Zf-7s for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 17:54:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er5vT-0001A3-6Q for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 17:53:59 +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.89) (envelope-from ) id 1er5vS-00019S-VJ for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 17:53:59 +0000 Received: from shopzeus.com ([87.229.70.149]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er5vP-00067q-FP for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 17:53:58 +0000 Received: from [127.127.127.127] (localhost [127.127.127.127]) (Authenticated sender: gandalf) by shopzeus.com (Postfix) with ESMTPSA id 9C1FE889B83C for ; Wed, 28 Feb 2018 11:51:23 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=shopzeus.com; s=shopzeus_com; t=1519836683; bh=5yS3/OsG2P7SqMj4dzQ+PJQ9mHjq+VcxuPD0im3Bzh8=; h=Subject:To:References:From:Date:In-Reply-To:From; b=MSGKJdIrbARcI2olT2ps+xzPJ3w5XTzkOkx6puvomLj2M/U9AQVwIiw9zwTq/mhfz CfqDPDVaMtjxcgI7Hl9jw1nxpO2rMNvBWFJ4INR106PmFp0nPSxcRTAPymvO2NiTbJ 9wfq4brao3QQ13Cpl2Db7EbX/qBX/U35ZDf/2scM/d7Pjcu/lWhkIF5fIhC1+wlTd7 yildzSi2u8+jhYL1Dz/yOeoKypDAff5shGJeKjYzVHV6gm7ZCxhUL6p+7iqc7lt9oS 06M1sGOf+JVlMXsoxwK5AkuUoEN1v7luEr9LX0U5BVoJ8W80BNypDzXZ/qfqP0IGRd g9aQyXc2yfmOg== Subject: Re: Reliable WAL file shipping over unreliable network To: pgsql-admin@lists.postgresql.org References: <400AC9CD-1E43-422C-A689-70BDBAFF231E@elevated-dev.com> From: =?UTF-8?Q?Nagy_L=c3=a1szl=c3=b3_Zsolt?= Message-ID: <954e01b6-b240-eb9a-5feb-8efd202fef5a@shopzeus.com> Date: Wed, 28 Feb 2018 18:53:53 +0100 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9251FA3729D6BAD821C7387A" Content-Language: hu List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9251FA3729D6BAD821C7387A Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable > > Just use "-ac=E2=80=9D; =C2=A0you want -c option to ensure no data corr= uption > during the transfer. =C2=A0Do not delete the file; let Postgres manage = that. > > Here is a snippet from I script I use for archiving. =C2=A0You also wan= t to > make your script returns failure or success correctly. =C2=A0 > > # 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=C2=A0 > =C2=A0=C2=A0 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. --------------9251FA3729D6BAD821C7387A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Just use "-ac”;  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="ssh -o ServerAliveInterval=20 $ARCH_SERVER"
STS=3

OUTPUT=$(rsync -ac --rsync-path="mkdir -p $ARCH_DIR && rsync" $XLOGFILE $ARCH_SERVER:$ARCH_DIR/$WALFILE)
if [ $? == 0 ]; then 
   STS=0
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='on'
primary_conninfo='host=postgres-master port=5432 user=not_telling password=not_telling'
trigger_file='/backup/trigger'
restore_command = 'cp /transfer/%f %p'
archive_cleanup_command = '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.

--------------9251FA3729D6BAD821C7387A--