Date: Wed, 2 Oct 2019 02:16:51 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Vikas Sharma <shavikas@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_receivexlog or archive_command
Message-ID: <20191002061651.GD6962@tamriel.snowman.net>
References: 
 <CAN6gwKwx865jv0fqc7SfwsG3utPhSoFF_tFaptcoDUW+T0uP8w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="2NsnjZhqPSuig5yr"
Content-Disposition: inline
In-Reply-To: 
 <CAN6gwKwx865jv0fqc7SfwsG3utPhSoFF_tFaptcoDUW+T0uP8w@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Vikas Sharma (shavikas@gmail.com) wrote:
> I am wondering which one is the best way to archive the xlogs for Backup
> and Recovery - pg_receivexlog or archive_command.
>=20
> pg_receivexlog seems best suited because the copied/archived file is
> streamed as it is being written to in xlog while archive_command only
> copies when the WAL is fully written to.

This really ends up depending on what your WAL volume is.  As mentioned,
you really don't want to write your own utility for archive_command,
since you really want to make sure that the WAL has actually been
archived and sync'd- so definitely use an existing tool which does that
for you.  The downside of pg_receivewal when it comes to high WAL volume
is that a single-threaded process just simply can't keep up, while
something being called from archive_command can be parallelized.
Perhaps, down the road, there'll be a tool that could parallelize
streaming of WAL also, though it would be pretty tricky to get right,
and if you're doing that much WAL, is it really an issue that it's
already chunked up nicely for archive_command..?

Note that there is also archive_timeout which you can set, to make sure
that you don't go too long with writes on the primary that haven't been
sent to the archive and stored.  If your transations are particularly
valuable, then having a synchronous standby setup (likely with two
replicas in a quorum-based sync setup) is probably the direction to
go in, so you can have a highly available environment.  Anything that's
async will mean you have a good chance of having some data loss if
things go wrong (even with pg_receivewal..).

Thanks,

Stephen

--2NsnjZhqPSuig5yr
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJdlEDTAAoJEO1sijiDR2RV1cUP/1idvInENlJSyu/HJUrMhOg5
Sf8RZlIw5qRxI6t4Q44/2WSySPMaNCOEN/HIP71XBJee/BeMHvEDwdm2ahtFfX0z
xTXrheMKymAreysbn41r5XmU2XegKSZgXFp0x5YsKIQKeR+pshqGWSOIdzOYukJn
+RyiUgTXCSerjaS6xXVoY49nx1fm81v6lj6fgjOZI3x3hFr5eiD+tC8Fs3dtswIj
ESGFAQ+gWu60R0c0Q8QrvZh3/nftRA7rYevp6ZV92xVw0lPGDsARnL0rjiv7otIK
Mx8oQyFU0yh3yXRDbqVeFbm8skXJwf18PZDpFuJoAEI5maXGcs+XTRvyPPDWNIL/
8E2AadffrSntdkrCjUvtrf30tV++qZmk001Gl6bCojb71WlJgye+ISG6O/PCe6/a
GQA1z08zx+l7/QNnntlMvCFU8b07ik33ohHPuFVYHLidC0yhPU/vXGvDaslnGgeZ
sTyEicVVBKXZK9TY5CAbIws7fwYW9gGhaeL3AvEqwOEQK9tC/QIeGsLw8xRr6kqk
C5jbR3nWH4SpCT6PNapzuIdVTZ2g66mW3f3gygsZmXdzJA9ibqg5WxG55ggM16Rh
yK5LlaVr/CJH8UMVg/11pqpYaKgAWIwtNKduAZI1HDIw9XAKbiAEAxC9g/O+kLkz
rDJn7m6Hrldg2fJlgCSK
=fqZd
-----END PGP SIGNATURE-----

--2NsnjZhqPSuig5yr--