Subject: Re: Missing important information in backup.sgml
To: Kevin Grittner <kgrittn@gmail.com>
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>
Cc: "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>,
 Stephen Frost <sfrost@snowman.net>
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
Message-ID: <a5a614b8-9ce2-ae3b-1141-c09e391cbba2@pro-open.de>
Date: Wed, 23 Nov 2016 21:12:56 +0100
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.5.0
MIME-Version: 1.0
In-Reply-To: 
 <CACjxUsMAoDNAMvE_bHpb0OzoamVDk93fESGMOfv_aR6E-Q6vUg@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="n5I4jc0Wsip05G9K92sBaxWt7XqCtm9m2"
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--n5I4jc0Wsip05G9K92sBaxWt7XqCtm9m2
Content-Type: multipart/mixed; boundary="R8XQQQTEgat6Iu22rA577XBVlI53VK2N6";
 protected-headers="v1"
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
To: Kevin Grittner <kgrittn@gmail.com>
Cc: "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>,
 Stephen Frost <sfrost@snowman.net>
Message-ID: <a5a614b8-9ce2-ae3b-1141-c09e391cbba2@pro-open.de>
Subject: Re: [DOCS] Missing important information in backup.sgml
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>
In-Reply-To: <CACjxUsMAoDNAMvE_bHpb0OzoamVDk93fESGMOfv_aR6E-Q6vUg@mail.gmail.com>

--R8XQQQTEgat6Iu22rA577XBVlI53VK2N6
Content-Type: multipart/mixed;
 boundary="------------1B0EBEBABF379F6E12C0CB47"

This is a multi-part message in MIME format.
--------------1B0EBEBABF379F6E12C0CB47
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Am 23.11.2016 um 20:21 schrieb Kevin Grittner:
> On Wed, Nov 23, 2016 at 12:24 PM, Gunnar "Nick" Bluth
> <gunnar.bluth@pro-open.de> wrote:
>=20
>> mentions Stephen's
>> remarks on rsync (although to get actual _data loss_, you'd have to ha=
ve
>> a power outage in the DC caused by your PG server exploding... ;-).
>=20
> I have seen power loss between the UPS and a server; including a
> tech tripping on the power cord.  I have also seen servers abruptly
> shut down due to high temperatures in spite of having a UPS.  I
> have also seen an OS bug lock up a system such that it was
> impossible to get a clean shutdown before having to cycle power to
> recover.
>=20
> No explosion needed.
>=20
> If you value the data in your database you should assume that the
> OS could go down at any instant without proper shutdown, and that
> your storage system(s) could be lost without warning at any time.

Kevin, all,

I've been in this business for 15 years, and had my share of outages.
The worst case being an AC service guy pushing the big red button next
to the DC entrance, assuming it was the light switch...

It's not like I've not gone through the possible scenarios in my head
before writing such a broad statement. Let me explain.

Assertions (that I take as givens for anyone valueing his data...):
- you have decent HW (BBU controller, HDD cache off, ECC RAM, redundant
PSUs, ...)
- you have a decent DC (UPS, AC, ...)
- you use a single DB server and/or no (synchronous) replication in place=

- your archive server is in the same DC (potentially the same machine as
the DB server)
- (in case of SAN) your storage correctly reports when it has written to
disk/BBU cache
- your OS (and/or archive_script) does not report RC=3D0 before all data
has been _transmitted_ (think MongoDB... ;-)
- (for the sake of completeness) fsync=3Don for PG

Now, what could happen is
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, ...)

In case a), your DB server would have fsync'd all committed transactions
=3D> no _data_ loss, but your _archive_ is potentially incomplete.
In case b), the same applies, but your archive should be intact.
In case c), the archiver would retry until your archiving server comes
back online =3D> no _data_ loss, no _archive_ loss.
In case d), see a), if you're lucky b)
In case e), you'd have lost your DB _and_ your archive may be incomplete.=

In case f), your f)....d anyway (oh, the coincidence! ;-).

Protecting yourself from case f) will involve a 2nd (3rd, ...) DC (or
some cloud thingie) anyway. In my experience, users that do have more
than one DC also have a policy in place saying that backups (which
archive logs would probably be counted as) have to be placed in a
different DC.

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".

Am I missing something?

P.S.: just to point that out... my patch does _not_ mention exploding
servers ;-)

Cheers,
--=20
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne


--------------1B0EBEBABF379F6E12C0CB47
Content-Type: application/pgp-keys;
 name="0x3289338C.asc"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
 filename="0x3289338C.asc"

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2

mQENBFOuo7ABCADU3rFM4gBv4l/Xjfry27pfdYgvWrcR1RA/vLfOgjqivSbMQEHH
bTWprJRjB2EbzmTS+faSa/V+mH/jIKBytCc5+hprBd/m5if3/v2PKA7+MHsy1H9T
7FqTKRccNl7RIcKFOaonIbY4qMyH6cFafZOgqaGlc3ibfX7PiCL+mdl1mPoyWeVR
sKoddSobI40noE+se/FrnKZRDRWqqx7zvTRRK9vKUVDFM+Y3Kg3EDL3Qmt3MocM7
FtBZDaeyFZiKdf7bNaWM977aGwiA1gQQp+/TmB1qqSyOTzbPhxfr7G9vWdbVS2ph
80RSrxI/sF3LUNeJtTjB/jtKiG49arBqC1eDABEBAAG0Lkd1bm5hciAiTmljayIg
Qmx1dGggPGd1bm5hci5ibHV0aEBwcm8tb3Blbi5kZT6JAT8EEwECACkFAlOuo7AC
GyMFCQlmAYAHCwkIBwMCAQYVCAIJCgsEFgIDAQIeAQIXgAAKCRAQEK5rMokzjLin
CACaTXqjA7Qc6lpljpz3/5SpmPlvEDdX5mR76jN8326qkSQw7i8WBGW/5NO669OY
4ITlLbu/XL11x6hzCh7ZxMJo3fSYN+fh1tfEnNGw0NjUoy1JdBB+2B3beY+0LEBG
ZMm56HZC1u4qZq2drLM+sZIi6f8ML0NCh41s01SeX6hGQ609zCcFRNNk98pXOBER
a2kDi9wn1UvK1tECxCXuiO6HhPzhme/nFAxoBdBdATCl5EEnnLD+JfhSr2LShgXu
LQxJA6odvggfDVlS913rb0zDtV6s+6bfR+qmD4igBgC6Xl8ceDJpbS4ER7Uvw78n
Sa2i3Yub28FAGr+RvpFLi9PruQENBFOuo7ABCAC6E2x7GZ1LrIQMlNr2XYvCG9DB
p181PJJ95w4BwuaKmFBDHc5tE7p5S2sNfNfYNUjDzpr47lU5+85Ffqtf27gV8Ftd
rqv/d2lbH9+w6wShB9QOy11gPPdkB62o9CKwk+XInN2DJITWsgI8hk6NNzbotD27
x7x4fxQxBUE6YkHnJMziqwZ5emLFP0A2XPIDK6tw87UZNOnEaJ9kP3WOKihNOtMq
mr7+4BQQCyfj13k32219UZglnHtYaGuNmXOc0KHoFEIA2pkgeXRQShrwz4gdCaSN
FUZPAVeeWmdqAmnKKb+Sk7czft/wdtmBD0BykYRoZxQ3JlkHG7oH7wGXmi1TABEB
AAGJASUEGAECAA8FAlOuo7ACGwwFCQlmAYAACgkQEBCuazKJM4xN1wf+MPLXrQ10
LBRtYjD16LsvxDyEcSMoWGc26DO/TDsGwme2NVfL0JufExgdpvkk/x4wzBwdkYfF
dNKU3CyA+3T4FuKGL7RPrICJa+VdCvwMk8dzLqF0UgdfVHtCnUQoQmNnDD7gODw/
GTIKpWw+iEE2/EANAONXj7EC+VsW895/C5aQEZx++cSonrHFSSIUp1OGJoVlmFnJ
MFCTTElmayTcmfOwGHrn97Et5YePMOsyqWZas+URuJveoTdGhDToOIufECGb3WJa
J8rFL+ni3NKoiZhZMYVQ+XC8f64Mi5kXlfsylVu6kgCoFqnq7FTpwqOiRml4APeX
GBC6uZW6fUGNDQ=3D=3D
=3D//tm
-----END PGP PUBLIC KEY BLOCK-----

--------------1B0EBEBABF379F6E12C0CB47--

--R8XQQQTEgat6Iu22rA577XBVlI53VK2N6--

--n5I4jc0Wsip05G9K92sBaxWt7XqCtm9m2
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBCAAGBQJYNfhPAAoJEBAQrmsyiTOM448IALGh+nyXjE0vgNv6cUazAOEv
ewZihQtXDbCh04rs5SLxmdDrqIy6LO/0KsnApicH6hoSjbGavZt6NQro6/EUnMS8
jgx6JI+JI9Qd7frJ5/IKT9d5GuUl74awrhy2x4bh5cqAIAxo/lsbHpzgHUo8TmGh
CeBk0PTbtFiOFqSqx1/2U2wvAVMZx8FGxN+E1ApwTMr5rmLSwPgpfAH+sEAZNUba
YWi1LnMKwQ+oCF44xorHCJKzQ0qgETOWu+OMWAhmS7Syw8OwfhzvaBnVIg7kT+gI
Xu/Cawo6Iyn2zIrenoqHG/+NNo8aPZ7S/C5eGInMzUy01LHUyw+fcg3NGaUIurw=
=HHaa
-----END PGP SIGNATURE-----

--n5I4jc0Wsip05G9K92sBaxWt7XqCtm9m2--