Date: Mon, 5 Mar 2018 10:02:18 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Rui DeSousa <rui.desousa@icloud.com>
Cc: scott ribe <scott_ribe@elevated-dev.com>,
	Mark Kirkwood <mark.kirkwood@catalyst.net.nz>,
	Dianne Skoll <dfs@roaringpenguin.com>,
	pgsql-admin@lists.postgresql.org
Subject: Re: Reliable WAL file shipping over unreliable network
Message-ID: <20180305150218.GY2416@tamriel.snowman.net>
References: <6056907B-551F-4ED0-804B-0FCACD39EB23@icloud.com>
 <ea674584-85bc-3f63-c182-0d45123bb3eb@catalyst.net.nz>
 <988AB20C-9FDE-4197-BF3F-C54D80C2ED78@elevated-dev.com>
 <E6AB850C-D05E-405B-8D4E-DE18E128F402@icloud.com>
 <20180302180911.GO2416@tamriel.snowman.net>
 <BB8E0E71-D65F-409F-B9AD-DDF2237CDF99@icloud.com>
 <20180304150640.GR2416@tamriel.snowman.net>
 <3D9CC2FB-57E1-45B2-B5FC-1B214F5DB068@icloud.com>
 <20180305111521.GV2416@tamriel.snowman.net>
 <ABC9F093-7249-498E-901B-6ADB7BBA3B22@icloud.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="H/msxwYr0hVD2Hfm"
Content-Disposition: inline
In-Reply-To: <ABC9F093-7249-498E-901B-6ADB7BBA3B22@icloud.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--H/msxwYr0hVD2Hfm
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Rui DeSousa (rui.desousa@icloud.com) wrote:
> > On Mar 5, 2018, at 6:15 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > Using replication slots and pg_receivewal can be a good solution if
> > you're on a version which supports the combination of both.  That
> > doesn't mean the proposed archive commands are any good though, just to
> > be clear.  An issue with pg_receivewal though is that it's
> > single-threaded.
>=20
> I must say this is a bit absurd; I didn=E2=80=99t realize that telling so=
meone not to delete Postgres WAL files from underneath Postgres would requi=
re me to provide a complete redundant backup solution.

It doesn't- but please don't encourage partial solutions which have very
clear issues.

> Pg_receivxlog or the archive_command being single threaded is not an issu=
e; at least not for me and I=E2=80=99m generating 1/2TB of WALs a day.  The=
 real problem is that applying the WALs is single threaded =E2=80=94 trying=
 to apply a single days worth of WALs takes too long and is one of the reas=
ons I take multiple backups a day to reduce the number of WALs required dur=
ing a PITR.

I agree that the single-threaded nature of pg_basebackup and
pg_receivewal isn't an issue for all environments, and that it would be
good to have a way to replay WAL faster, and that performing frequent
incrementals is a way to reduce the amount of WAL to be replayed.

> The solution you proposed; would not be able to keep up with the rate of =
backups I issue daily nor is it capable of taking a backups on the replica =
at least not yet from the presentation I reviewed.  I do my backups on repl=
icas at multiple sites with WAL files also being stored at multiple sites. =
 I also do daily restores in a lower environment which take less than 5 min=
utes to do =E2=80=94 obviously I=E2=80=99m making extensive use of snapshot=
s and snapshot replication.

The only solution which I mentioned on this thread was pgBackRest, which
keeps up very well with very frequent backups, including those which
utilize a replica, so I'm not sure what you're referring to here.
pgBackRest is also able to perform fast restores when there have been
few changes.  Note that you had suggested pg_receivewal- I was just
commenting on it.  Snapshots and snapshot replication can be useful, but
it's vital to ensure that the entire snapshot is taken atomically across
all tablespaces (unless you're doing both snapshots *and* using
pg_start/stop_backup, collecting necessary WAL, and being sure to put
the backup_label file into place during restore).

> If your archive server is crashing then you have other issues and one sho=
uld work to remove single points of failure.  I=E2=80=99m not sure what fil=
esystem you=E2=80=99re using but the one I use sync to disk every 30 second=
s.

This thread began with a concern about an unreliable network, I don't
think it's unreasonable to consider other failure scenarios.  PostgreSQL
performs a great deal of work to ensure that data is written out in a
manner which can be recovered in the event of a server crash- utilities
which are also working with that data should be similairly resiliant.

> The original poster stated that rsync wasn=E2=80=99t even an option and i=
t not even using it.  I=E2=80=99m not here trying to push a backup solution=
 or anything else; I was just trying give some simple advice to the given p=
roblem.

I agree that rsync is a bad approach.  Similairly, proposing archive
commands which don't do the basics, such as ensure the WAL file has been
sync'd to disk before returning success, isn't good.

Thanks!

Stephen

--H/msxwYr0hVD2Hfm
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJanVv6AAoJEO1sijiDR2RVrgoQAKaaxgqZn5NAvyHWGtXEMKq6
GtAqnprpWsPUL+Z2N4YAxiqY8S9MZNeikRJW5utZx73C+Fz5w+KqsPoolzkPFCRS
XApgj+NknDmCRbtL4xXTAqJXp95+nBhOeLKZ3VFabKWG7Cq6KL2XZUBK2vE7EZ/g
G+5xk7E4s1zpjgK6qizIJePzTI84ev1AZDDU+XoY9s7HFllAKxOnA/iSTOPa92Zr
ueUpvfJmS4zCcH8NufWTIKcKi3XfGed5qe5lEQ7zx1uuoVm/MxxEZBf0Y5tjxAF9
whYIYZxZx5ah86A9hJvoHK4ZSlAaCQ/vmuA6jTbrhQgCILi2P/gJitw/KahuoBVf
uFo7Fb32vbdEKnFh1Vim8rhDwMHGUo+ziL5wjftLGuJNjh44fi76CK8dSmO9BCVY
48dGKRPBaVI5esISjHztn6/UUhcJXAMtRLXXDyMYMOQC28kaRIbr6+JU5yIizmNd
V5Oa3vTsZqouZyy9YXlaWrFtcuy2qNf4mP4ynSOAvzc520fghNZUHrP1Qj8+7c8w
UljgDf1E4tEPMknccmcXfxeZ6iTtRHWSt15LeRUV8ZWsCX+Ec7uz0rN7rARqH8cN
VQfxylt/l7ZRl2ET61J34OyOe0TyMByJbMeXTPZu7RRX8Kc56ad+YFsrybsrekFE
5OsxMd39nv/3RPorCNzS
=msa1
-----END PGP SIGNATURE-----

--H/msxwYr0hVD2Hfm--