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 1etGoG-0000Y7-0w for pgsql-admin@arkaria.postgresql.org; Tue, 06 Mar 2018 17:55:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1etGoE-0002Aw-Hy for pgsql-admin@arkaria.postgresql.org; Tue, 06 Mar 2018 17:55:30 +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 1etGoE-0002An-3q for pgsql-admin@lists.postgresql.org; Tue, 06 Mar 2018 17:55:30 +0000 Received: from mr28p48im-tydg10101201.me.com ([17.110.88.44]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1etGoA-0006XZ-KK for pgsql-admin@lists.postgresql.org; Tue, 06 Mar 2018 17:55:29 +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 <0P5600200KBE9900@mr28p48im-tydg10101201.me.com> for pgsql-admin@lists.postgresql.org; Tue, 06 Mar 2018 17:55:23 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1520358923; bh=kn+VejLUWx+858PjduzucVprXMNJbkeolhdV4oi2IlA=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=J2uTCTWXvRPcNHGQTteBtVmbPEWokJuOPmFIflcnF+TlvMCOkNrJFwqZeOpyAOjGK 500nXS0YmQkY1x9xTaGhz/YaOg99RmbNGozbLtcq42KEdNPVJbrVb/42HHdc+iGdsG AudoCpCN3iRxMURddH1Suwn0tuuHtk4r8siHTE0SnYSgdyz1oLioeHaq64P1UFTTdg QT9jrLFQu4iEKf73osw9DlaacWAL772Bquh6BZbQjJQqGgdf9k1vKSIxLJswQEv/Pg phiKbcMFr3khowZI1R3Y8JR87e+e69N54bzPqEfgzIGgfl8CY+hpxk4XTdN6QJm4xq 45djd/3BvpgbQ== 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 <0P56007LLKG9JM10@mr28p48im-tydg10101201.me.com>; Tue, 06 Mar 2018 17:55:23 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-03-06_09:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1015 suspectscore=2 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1803060198 From: Rui DeSousa Message-id: <15F9D550-9909-40B3-A384-417AFCD745DF@icloud.com> Content-type: multipart/alternative; boundary="Apple-Mail=_BDDDFE0F-B4EC-4099-99FE-894B12E9DF5B" MIME-version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: Reliable WAL file shipping over unreliable network Date: Tue, 06 Mar 2018 12:55:20 -0500 In-reply-to: <20180305150218.GY2416@tamriel.snowman.net> Cc: pgsql-admin@lists.postgresql.org To: Stephen Frost References: <6056907B-551F-4ED0-804B-0FCACD39EB23@icloud.com> <988AB20C-9FDE-4197-BF3F-C54D80C2ED78@elevated-dev.com> <20180302180911.GO2416@tamriel.snowman.net> <20180304150640.GR2416@tamriel.snowman.net> <3D9CC2FB-57E1-45B2-B5FC-1B214F5DB068@icloud.com> <20180305111521.GV2416@tamriel.snowman.net> <20180305150218.GY2416@tamriel.snowman.net> X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_BDDDFE0F-B4EC-4099-99FE-894B12E9DF5B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Mar 5, 2018, at 10:02 AM, Stephen Frost wrote: >=20 > It doesn=E2=80=99t- but please don't encourage partial solutions which = have ver clear issues. Then problem is there are no good base utilities that is useful with = archive_command; unless you=E2=80=99re writing directly to an NFS mount = or a tape library. Even Barman recommends rsync with the = archive_command; if you are unable to use pg_receivexlog solution. = There are countless Postgres documentation out there that recommends use = of rsync with the archive_command. =20 Here a solution that will fsync() file on the other end. SSH_CMD=3D"ssh -o ServerAliveInterval=3D20 $ARCH_SERVER" STS=3D3 OUTPUT=3D$(cat $XLOGFILE | $SSH_CMD "(mkdir -p $ARCH_DIR && ~/bin/fwrite = $ARCH_DIR/$WALFILE)") if [ $? =3D=3D 0 ]; then=20 STS=3D0 fi exit $STS fwrite code: #include #include #include #include #include #include #include #define BUFSIZE 131072 int main(int argc, char *argv[]) { int fd, r, w; char *buf; char *name; =20 if (argc !=3D 2) { fprintf(stderr, "usage: fwrite [file]\n"); exit(1); } if ((buf =3D malloc(BUFSIZE)) =3D=3D NULL) err(1, "malloc"); ++argv; if ((name =3D (char *) malloc(strlen(*argv) + 10)) =3D=3D NULL) err(1, "malloc"); strcat(strcpy(name, *argv), ".XXXXXX"); if ((fd =3D mkstemp(name)) < 0) err(1, "mkstemp"); while ((r =3D read(STDIN_FILENO, buf, BUFSIZE)) > 0) if ((w =3D write(fd, buf, r)) =3D=3D -1) err(1, "write"); if (r < 0) err(1, "read"); if (fsync(fd) !=3D 0) err(1, "fsync"); if (close(fd) !=3D 0) err(1, "close"); if (rename(name, *argv) !=3D 0) err(1, "rename"); =20 free(name); exit(0); } --Apple-Mail=_BDDDFE0F-B4EC-4099-99FE-894B12E9DF5B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On Mar 5, 2018, at 10:02 AM, Stephen Frost <sfrost@snowman.net> = wrote:

It doesn=E2=80=99t- = but please don't encourage partial solutions which have = ver clear issues.

Then problem is there are no good base utilities that is = useful with archive_command; unless you=E2=80=99re writing directly to = an NFS mount or a tape library.  Even Barman recommends rsync with = the archive_command; if you are unable to use pg_receivexlog solution. =  There are countless Postgres documentation out there = that recommends use of rsync with the archive_command. =  

Here a solution that = will fsync() file on the other end.

SSH_CMD=3D"ssh -o ServerAliveInterval=3D20 = $ARCH_SERVER"
STS=3D3

OUTPUT=3D$(cat $XLOGFILE | $SSH_CMD "(mkdir = -p $ARCH_DIR && ~/bin/fwrite = $ARCH_DIR/$WALFILE)")
if [ $? =3D=3D 0 ]; then 
  =  STS=3D0
fi

exit $STS


fwrite code:

#include = <sys/stat.h>
#include <err.h>
#include <fcntl.h>
#include = <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include = <string.h>


#define BUFSIZE = 131072

int
main(int argc, char *argv[])
{
  int fd, r, w;
  char = *buf;
 = char *name;
  
  if (argc !=3D 2) {
    fprintf(stderr, "usage: fwrite = [file]\n");
    exit(1);
  }

  if ((buf =3D = malloc(BUFSIZE)) =3D=3D NULL)
    err(1, = "malloc");

  ++argv;
  if ((name =3D (char = *) malloc(strlen(*argv) + 10)) =3D=3D NULL)
    err(1, = "malloc");

  strcat(strcpy(name, *argv), = ".XXXXXX");

  if ((fd =3D mkstemp(name)) < = 0)
  =   err(1, "mkstemp");

  while ((r =3D = read(STDIN_FILENO, buf, BUFSIZE)) > 0)
    if ((w =3D = write(fd, buf, r)) =3D=3D -1)
      err(1, = "write");

  if (r < 0)
   err(1, = "read");
  if (fsync(fd) !=3D 0)
   err(1, = "fsync");

  if (close(fd) !=3D = 0)
  =  err(1, "close");

  if (rename(name, *argv) !=3D = 0)
  =  err(1, "rename");
 
  free(name);
  exit(0);
}


= --Apple-Mail=_BDDDFE0F-B4EC-4099-99FE-894B12E9DF5B--