Subject: Re: Missing important information in backup.sgml
To: Stephen Frost <sfrost@snowman.net>
References: <cb648ec1-5212-60c5-b73a-9572d21b2ae9@elster.de>
 <cec57bd3-3742-9bae-a76f-f3414dcf135e@elster.de>
 <20161116143651.GC13284@tamriel.snowman.net>
Cc: pgsql-docs@postgresql.org
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
Message-ID: <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de>
Date: Wed, 16 Nov 2016 22:07:41 +0100
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: <20161116143651.GC13284@tamriel.snowman.net>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="aGVPotDtBSxsOntHr9VXK2lxkd6XL6x8Q"
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--aGVPotDtBSxsOntHr9VXK2lxkd6XL6x8Q
Content-Type: multipart/mixed; boundary="nfxTqWTkBh9lNdOOnd7tASJ6v473BhAsI";
 protected-headers="v1"
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-docs@postgresql.org
Message-ID: <80e18b98-9b72-ddee-2a34-76302e5d8a0b@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>
In-Reply-To: <20161116143651.GC13284@tamriel.snowman.net>

--nfxTqWTkBh9lNdOOnd7tASJ6v473BhAsI
Content-Type: multipart/mixed;
 boundary="------------93C937AD28ECD9681D34E5AA"

This is a multi-part message in MIME format.
--------------93C937AD28ECD9681D34E5AA
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Am 16.11.2016 um 15:36 schrieb Stephen Frost:
> Gunnar, all,
>=20
> * Gunnar "Nick" Bluth (gunnar.bluth.extern@elster.de) wrote:
>> Am 16.11.2016 um 11:37 schrieb Gunnar "Nick" Bluth:
>>> I ran into this issue (see patch) a few times over the past years, an=
d
>>> tend to forget it again (sigh!). Today I had to clean up a few hundre=
d
>>> GB of unarchived WALs, so I decided to write a patch for the
>>> documentation this time.
>>
>> Uhm, well, the actual problem was a stale replication slot... and
>> tomatoes on my eyes, it seems ;-/. Ashes etc.!
>>
>> However, I still think a warning on (esp. rsync's) RCs >=3D 128 is wor=
th
>> considering (see -v2 attached).
>=20
> Frankly, I wouldn't suggest including such wording as it would imply
> that using a bare rsync command is an acceptable configuration of
> archive_command.  It isn't.  At the very least, a bare rsync does
> nothing to ensure that the WAL has been fsync'd to permanent storage
> before returning, leading to potential data loss due to the WAL
> segment being removed by PG before the new segment has been permanently=

> stored.

I for myself deem a UPS-backed server in a different DC a pretty good
starting point, and I reckon many others do as well... obviously it's
not a belt and bracers solution, but my guess would be that > 90% of
users have something similar in place, many of them actually using rsync
(or scp) one way or the other (or, think WAL-E et. al., how do you force
an fsync on AWS?!?).
In environments where there's a risk of the WAL segment being
overwritten before that target server has fsync'd, heck, yeah, you're
right. But then you'd probably have something quite sophisticated in
place, and hate to see allegedly random _FATAL_ errors that are _not
documented outside the source code_ even more. Esp. when you can't tell
for sure (from the docs) if archiving your WAL segment will be retried
or not.

> The PG documentation around archive command is, at best, a starting
> point for individuals who wish to implement their own proper backup
> solution, not as examples of good practice for production environments.=


True. Which doesn't mean there's no room for more hints, like "ok, we
throw a FATAL error sometimes, but they're not really a problem, you
know, it's just external software that basically everyone uses at one
point or the other doing odd things sometimes" ;-).

Alas, I've been hunting a red herring today, cause when you find your
pg_xlog cluttered with old files _and_ see FATAL archiving messages in
your logs, your first thought is not "there's prolly a replication slot
left over", but "uh oh, those archive_command calls failed, so something
might be somehow stuck now".

I'll try to come up with something more comprehensive, taking your
comments into account...

> Thanks!
>=20
> Stephen

Thank you for considering this! ;-)

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


--------------93C937AD28ECD9681D34E5AA
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-----

--------------93C937AD28ECD9681D34E5AA--

--nfxTqWTkBh9lNdOOnd7tASJ6v473BhAsI--

--aGVPotDtBSxsOntHr9VXK2lxkd6XL6x8Q
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

iQEcBAEBCAAGBQJYLMqnAAoJEBAQrmsyiTOM5EYH/1zuJb0009tUrSFHfI25ZwF1
RVjGy1VKXGiwPIm8laxUmCuM3mOtNrRHOE16JIp0koQfmymSxmvsYstaryugANHP
BWjT2GmjiYdhS9pVKAN7R/TAm8kDNUADG8zQORPsIKlAlbUUiq+kVLveGZZpu7J0
Y16ahDeNw92Dv8B5skIRzxMyzanPpf9Ewrw7FANrjqrdd8ZGVPbcNM5l2jyLty6T
psaP8txUadPRbC8gBenytsSV8taudIoQBqMFNpKqjijdoqD1aeAJ9Pbh9klFe6U6
o0odC18nNzFoGN7soaCIGMBWDYUyZfkYki0LWgkMj3kDXlAeQkcTDafI6Jv3wDI=
=PcN3
-----END PGP SIGNATURE-----

--aGVPotDtBSxsOntHr9VXK2lxkd6XL6x8Q--