Date: Tue, 8 Nov 2016 10:47:01 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Joao Junior <jcoj2006@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: archive_command too slow.
Message-ID: <20161108154701.GD13284@tamriel.snowman.net>
References: 
 <CABnPa_gPL8hxYwrLUmtaLUiN_KyVaJSEWE_LD+VzNJBNS4ELcw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="bTCLKwt008Jco3B7"
Content-Disposition: inline
In-Reply-To: 
 <CABnPa_gPL8hxYwrLUmtaLUiN_KyVaJSEWE_LD+VzNJBNS4ELcw@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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

Greetings,

* Joao Junior (jcoj2006@gmail.com) wrote:
> I am running 2 Linux machines, kernel  3.13.0-45-generic #74-Ubuntu SMP.
> Postgresql version 9.4 in both machine, in a Hot Standby cenario.
>=20
> Master-Slave using WAL files, not streaming replication.
>=20
> The archive_command from master is:
>=20
> archive_command =3D '/usr/bin/rsync -a -e "ssh" "%p"
> slave:/data2/postgres/standby/main/incoming/"%f"' #

Admittedly, you're talking about this for only WAL replay, but I wanted
to point out that this is not a safe archive_command setting because
you are not forcing an fsync() on the remote side to be sure that the
WAL is saved out to disk before telling PG that it can remove that WAL
segment.

What that means is that a crash on the remote system at just the wrong
time will create a hole in your WAL, possibly meaning that you wouldn't
be able to replay WAL on the remote side past that point and would have
to rebuild/re-sync your replica.

There's a lot more that needs to be done carefully and correctly to
ensure proper backups than this also.

In short, don't try to write your own software for doing this.  Use one
of the existing tools which do the correct things for you.  In
particular, when it comes to dealing with archive_command being a
bottleneck, pgbackrest has an async mode where it will keep open a
long-running SSH session to the remote side to avoid the overhead
associated with re-exec'ing SSH.  The ControlMaster approach helps too,
but you still have to exec rsync and ssh and it's ultimately expensive
to even do that.  Unfortunatly, restore_command is single threaded and
is going to be pretty expensive too, though pgbackrest's archive-get
tries to be as fast as it can be.

pgbackrest also has the ability to perform incremental backups and
incremental restores, in parallel, allowing you to periodically bring
your replica up to speed very quickly using file transfers instead of
using WAL replay.  This does mean that you have a period of downtime for
your replica, of course, but that might be a worthwhile trade-off.

For pure backups, another approach is to use pg_receivexlog and a tool
like barman which supports verifying that the WAL for a given backup has
reached the remote side.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYIfN1AAoJEO1sijiDR2RVr/4QALSNxilbpsvKCz4PDR32ZVnP
iOL/ly53g0Z6PS8t8NWeDgkj2lB9jjOy3qNsFb/MyMdVpKHubw1xIsDGFHBIFRkC
q8keEPmtSFbDzhCV8xkv3QighwHUdv79nDA+XUwI6uPriJuWS0gMsjQU4DjgrG+q
fDUfiuZsfI7hx1vSAKz/l6lycloD8FcSOgzPCPYzGe1T0PwKgG44QjVlrs1pveda
I4jm+NcXqAx6urscEdOweCcxZ7VItNNFHBJ1JV6JO99fx7m0ce0NAmQpudd7halU
bMVQKEsCpyNNLziBxgaLC9L2YcvqRiD9qu90PqDq7EKQNE9685AjDSCew7Q97OBQ
FCWcjcd6gFpbHb/wRO6QdNheRDwLOP73oknQS5mnmdUDvr8Bb0060hxqtpp8Aj9H
G9fnKhl/hSIb+q4rr6/oIPYNWAy9ow67ySotLDWVq4OIPAvuWQyPp+cOzZijOGmj
kFsSFMNrTWfOisHax71pCvy/dq5hHtHwayVKFR+UPEy64dbIWQG3s39qIa0OAYRO
nERpUs4SjmTA9gVgUt3LKaQRkGVD4LS8/oIlRLQLdRmdy1wA+piPxf+0UPNzdl35
S4cKJxjHmmCZTdYMf3mMifRyvTI3MSzDxOHLP3JUb2M0G0Xi+fY5RflsgYUPO7+d
gSxPozKSDBXvXix9UdrH
=pW2G
-----END PGP SIGNATURE-----

--bTCLKwt008Jco3B7--