Date: Wed, 23 Nov 2016 15:41:39 -0500
From: Stephen Frost <sfrost@snowman.net>
To: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
Cc: Kevin Grittner <kgrittn@gmail.com>,
	"pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>
Subject: Re: Missing important information in backup.sgml
Message-ID: <20161123204139.GZ13284@tamriel.snowman.net>
References: <cb648ec1-5212-60c5-b73a-9572d21b2ae9@elster.de>
 <cec57bd3-3742-9bae-a76f-f3414dcf135e@elster.de>
 <20161116143651.GC13284@tamriel.snowman.net>
 <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de>
 <cd880db6-1ff6-8fa7-4868-249421cd6fb2@pro-open.de>
 <CACjxUsMAoDNAMvE_bHpb0OzoamVDk93fESGMOfv_aR6E-Q6vUg@mail.gmail.com>
 <a5a614b8-9ce2-ae3b-1141-c09e391cbba2@pro-open.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="3oCBjgol8nyYElwb"
Content-Disposition: inline
In-Reply-To: <a5a614b8-9ce2-ae3b-1141-c09e391cbba2@pro-open.de>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org


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

Greetings,

* Gunnar "Nick" Bluth (gunnar.bluth@pro-open.de) wrote:
> Now, what could happen is

All you need is for the archive server to be reset at the wrong time
(and there are a lot of potential "wrong times" to choose from) and
you'll end up with an incomplete archive.

> a) complete DC power outage
> b) outage of DB server
> c) outage of archive server (or the network connection to it)
> d) outage of storage system
> e) complete DC outage caused by your DB server vanishing (burning down,
> exploding, melting, ...),
> f) a complete _loss_ of the DC (atomar strike, plane crash, ...)
>=20
> In case a), your DB server would have fsync'd all committed transactions
> =3D> no _data_ loss, but your _archive_ is potentially incomplete.

To be clear, the concern that I was pointing out is primairly that the
archive could end up incomplete and potentially render significant
portions of your archive as unusable (as in, everything since the event
til the next backup).

> In case b), the same applies, but your archive should be intact.

That isn't entirely accurate as you'll lose whatever happened since the
last WAL segment was shipped to the archive server, but that's true in
general unless you're using pg_receivexlog with sync mode.  Of course,
anything archive_command-based will have this issue.

On the other hand, you mentioned DB and archive on the same system, in
which case an inopportune reset of that server could result in an
incomplete archive, though you shouldn't lose any data in the database
assuming you can get the disks back.

> In case c), the archiver would retry until your archiving server comes
> back online =3D> no _data_ loss, no _archive_ loss.

That depends entirely upon the circumstances of the archive server
outage- if the archive server is reset at the wrong time, you will
almost certainly lose some about of your archive.  If you just lose
network connectivity then you should be ok- if the command you're
using for archive_command returns the correct error code in that case.

> So, losing actual _data_ is unlikely (at least from the archiving point
> of view...), but not explicitly fsync'ing the archive _may_ lead to
> incomplete archives. Which is exactly what I tried to point out by
> "[...], rendering your archive incomplete in case of a power outage".

One of the very important things that should be done as part of a backup
is to ensure that all of the archive files required to restore the
database to a consistent state are safely stored in the archive.  If
that isn't done then it's possible that an incomplete archive may also
render backups invalid.

> Am I missing something?

For my 2c, at least, the archive should be viewed with nearly the same
care and consideration as the primary data.  As with your database, you
really want your backups to work when you need them.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYNf8DAAoJEO1sijiDR2RVQYcP/jdpwSF60hC5BASXiruEjhwU
kYn6U49B+/tjlP2IU6B+/tsnx6DVoe9RcskP4n2SKnsPM7X2JdWdgzWI6moSJEWe
9qL2lA9eHwAEHCJO2z0ITBwQlXv4+X6qjUwGVF8Dq4u1JwxDHlDfC/HRjBHTBIx3
TpkcjWx+AVyXYkAIs3TuDn2WDMHKMKILJ6te71YQUgbMmZd0pbsRQwW1BaC4jEX7
EFP3AsnFZ+BEkFyjNY9UjaqXZKYO+iM7XFR8y8TJG7RuKh6EXaN+NwxDi/v72UVW
Yr3AzW2NsLFdOZ5iZVqzG4CUWGAhbXye3GFsuCuxaOmVdOzSVAReu0sdvZphl268
IXcy4po0q4VWzdkKNHeyWJi3KSBjSUBMsUvAO1YnI/BlpklV/QmnNYLrsxvONmwp
adZzUSeJLXfwggMQniCdt5ipE1tLihNsnefxjKL+3KXS5FVXZAjBnvQzSJ6AfvIr
EFiD6KjeMgKVIFasYwVa+4E6/LXMbAf2hBl4vN7VadTb+NwU+35sEkNA3Jxavs3w
RveKAwRYURXYTGHICoXzwHPV3fxTeRniEbckJAM3leDW91bROL0O6NyRoO40pylx
2CZkdZtgBgtRwv9/0bBU2Xj+voJ1mYnIC2RG8X2KeMapCPzsCT+vnRcY9upsHh9x
TrAJM9425cGIv4yJo0jG
=bO2J
-----END PGP SIGNATURE-----

--3oCBjgol8nyYElwb--