Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c9cDx-0001I2-Py for pgsql-docs@arkaria.postgresql.org; Wed, 23 Nov 2016 18:24:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c9cDx-0006bT-BA for pgsql-docs@arkaria.postgresql.org; Wed, 23 Nov 2016 18:24:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c9cDw-0006a0-8k for pgsql-docs@postgresql.org; Wed, 23 Nov 2016 18:24:48 +0000 Received: from pro-open.de ([87.106.136.39] helo=www.pro-open.de) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c9cDt-0004yO-ER for pgsql-docs@postgresql.org; Wed, 23 Nov 2016 18:24:47 +0000 Received: from [192.168.178.24] (x2f21b1b.dyn.telefonica.de [2.242.27.27]) by www.pro-open.de (Postfix) with ESMTP id 41E4B1045EF3; Wed, 23 Nov 2016 19:24:43 +0100 (CET) From: "Gunnar \"Nick\" Bluth" Subject: Re: Missing important information in backup.sgml To: pgsql-docs@postgresql.org References: <20161116143651.GC13284@tamriel.snowman.net> <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de> Cc: Stephen Frost Message-ID: Date: Wed, 23 Nov 2016 19:24:35 +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: <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="HAtfVjOP89GJLE9WXsgOP7GipbtXidMff" X-Pg-Spam-Score: -4.8 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --HAtfVjOP89GJLE9WXsgOP7GipbtXidMff Content-Type: multipart/mixed; boundary="L7VK2RwxwedgfOegfiwDksCmgvi7GHhH8"; protected-headers="v1" From: "Gunnar \"Nick\" Bluth" To: pgsql-docs@postgresql.org Cc: Stephen Frost Message-ID: Subject: Re: [DOCS] Missing important information in backup.sgml References: <20161116143651.GC13284@tamriel.snowman.net> <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de> In-Reply-To: <80e18b98-9b72-ddee-2a34-76302e5d8a0b@pro-open.de> --L7VK2RwxwedgfOegfiwDksCmgvi7GHhH8 Content-Type: multipart/mixed; boundary="------------D792AB92A0614EA4E38CE59F" This is a multi-part message in MIME format. --------------D792AB92A0614EA4E38CE59F Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Am 16.11.2016 um 22:07 schrieb Gunnar "Nick" Bluth: > Am 16.11.2016 um 15:36 schrieb Stephen Frost: >> Gunnar, all, >> >> * 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, a= nd >>>> tend to forget it again (sigh!). Today I had to clean up a few hundr= ed >>>> 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 wo= rth >>> considering (see -v2 attached). >> >> 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 permanentl= y >> stored. >=20 > 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 rsyn= c > (or scp) one way or the other (or, think WAL-E et. al., how do you forc= e > 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. >=20 >> 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= =2E >=20 > 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" ;-). >=20 > 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 somethin= g > might be somehow stuck now". >=20 > I'll try to come up with something more comprehensive, taking your > comments into account... So, attached is what I came up with. It's obviously not "complete", however it points out the RC >=3D 128 "quirk" and also mentions Stephen's= remarks on rsync (although to get actual _data loss_, you'd have to have a power outage in the DC caused by your PG server exploding... ;-). 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 --------------D792AB92A0614EA4E38CE59F Content-Type: text/plain; charset=UTF-8; name="archiving_doc_v4.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="archiving_doc_v4.patch" ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9iYWNrdXAuc2dtbCBiL2RvYy9zcmMvc2dtbC9i YWNrdXAuc2dtbA0KaW5kZXggNmVhZWQxZS4uMDlmMDc4NyAxMDA2NDQNCi0tLSBhL2RvYy9z cmMvc2dtbC9iYWNrdXAuc2dtbA0KKysrIGIvZG9jL3NyYy9zZ21sL2JhY2t1cC5zZ21sDQpA QCAtNTg3LDcgKzU4Nyw4IEBAIHRhciAtY2YgYmFja3VwLnRhciAvdXNyL2xvY2FsL3Bnc3Fs L2RhdGENCiAgICAgdGhlIGFkbWluaXN0cmF0b3Igc3BlY2lmeSBhIHNoZWxsIGNvbW1hbmQg dG8gYmUgZXhlY3V0ZWQgdG8gY29weSBhDQogICAgIGNvbXBsZXRlZCBzZWdtZW50IGZpbGUg dG8gd2hlcmV2ZXIgaXQgbmVlZHMgdG8gZ28uICBUaGUgY29tbWFuZCBjb3VsZCBiZQ0KICAg ICBhcyBzaW1wbGUgYXMgYSA8bGl0ZXJhbD5jcDwvPiwgb3IgaXQgY291bGQgaW52b2tlIGEg Y29tcGxleCBzaGVsbA0KLSAgICBzY3JpcHQgJm1kYXNoOyBpdCdzIGFsbCB1cCB0byB5b3Uu DQorICAgIHNjcmlwdCAmbWRhc2g7IGl0J3MgYWxsIHVwIHRvIHlvdS4gVGhlcmUgaG93ZXZl ciBhcmUgc29tZSB0aGluZ3MgdG8gY29uc2lkZXINCisgICAgd2hlbiBjcmVhdGluZyBzdWNo IGEgY29tbWFuZCwgbW9zdCBvZiB3aGljaCBhcmUgY292ZXJlZCBiZWxvdy4NCiAgICA8L3Bh cmE+DQogDQogICAgPHBhcmE+DQpAQCAtNjM2LDcgKzYzNywxMSBAQCB0ZXN0ICEgLWYgL21u dC9zZXJ2ZXIvYXJjaGl2ZWRpci8wMDAwMDAwMTAwMDAwMEE5MDAwMDAwNjUgJmFtcDsmYW1w OyBjcCBwZ193YWwvMA0KICAgICA8cHJvZHVjdG5hbWU+UG9zdGdyZVNRTDwvPiB3aWxsIGFz c3VtZSB0aGF0IHRoZSBmaWxlIGhhcyBiZWVuDQogICAgIHN1Y2Nlc3NmdWxseSBhcmNoaXZl ZCwgYW5kIHdpbGwgcmVtb3ZlIG9yIHJlY3ljbGUgaXQuICBIb3dldmVyLCBhIG5vbnplcm8N CiAgICAgc3RhdHVzIHRlbGxzIDxwcm9kdWN0bmFtZT5Qb3N0Z3JlU1FMPC8+IHRoYXQgdGhl IGZpbGUgd2FzIG5vdCBhcmNoaXZlZDsNCi0gICAgaXQgd2lsbCB0cnkgYWdhaW4gcGVyaW9k aWNhbGx5IHVudGlsIGl0IHN1Y2NlZWRzLg0KKyAgICBpdCB3aWxsIHRyeSBhZ2FpbiBwZXJp b2RpY2FsbHkgdW50aWwgaXQgc3VjY2VlZHMuIE5vdGUgdGhhdCBhbiBleGl0DQorICAgIHN0 YXR1cyBvZiAxMjggb3IgaGlnaGVyIHdpbGwgY2F1c2UgdGhlIGFyY2hpdmVyIHRvIGV4aXQg KGFuZCByZXN0YXJ0KSwgDQorICAgIHJlc3VsdGluZyBpbiBhIChwb3RlbnRpYWxseSBtaXNs ZWFkaW5nKSBGQVRBTCBlcnJvciBpbiB0aGUgc2VydmVyIGxvZy4gDQorICAgIEUuZy4sIDxj b21tYW5kPnJzeW5jPC8+IHRlbmRzIHRvIHJldHVybiBleGl0IHN0YXR1c2VzIG9mIDI1NSBv biBuZXR3b3JrDQorICAgIGlzc3Vlcy4NCiAgICA8L3BhcmE+DQogDQogICAgPHBhcmE+DQpA QCAtNjk2LDYgKzcwMSwxNCBAQCB0ZXN0ICEgLWYgL21udC9zZXJ2ZXIvYXJjaGl2ZWRpci8w MDAwMDAwMTAwMDAwMEE5MDAwMDAwNjUgJmFtcDsmYW1wOyBjcCBwZ193YWwvMA0KICAgICBw cmVzZXJ2ZSB0aGUgZmlsZSBuYW1lICg8bGl0ZXJhbD4lZjwvPikuDQogICAgPC9wYXJhPg0K IA0KKyAgPHBhcmE+DQorICAgIERlcGVuZGluZyBvbiB5b3VyIHNwZWNpZmljIHJlcXVpcmVt ZW50cyAoYW5kIGRhdGFjZW50ZXIgbGF5b3V0KSwgeW91IG1heQ0KKyAgICB3YW50IHRvIG1h a2Ugc3VyZSB0aGF0IHRoZSBhcmNoaXZlZCBXQUwgc2VnbWVudHMgaGF2ZSBiZWVuIHdyaXR0 ZW4gb3V0IHRvDQorICAgIHBlcnNpc3RlbnQgc3RvcmFnZSBiZWZvcmUgdGhlIDxjb21tYW5k PmFyY2hpdmVfY29tbWFuZDwvPiByZXR1cm5zLg0KKyAgICBPdGhlcndpc2UsIGEgV0FMIHNl Z21lbnQgdGhhdCBpcyBhc3N1bWVkIHRvIGJlIGFyY2hpdmVkIGNvdWxkIGJlIHJlY3ljbGVk DQorICAgIHByZW1hdHVyZWx5LCByZW5kZXJpbmcgeW91ciBhcmNoaXZlIGluY29tcGxldGUg aW4gY2FzZSBvZiBhIHBvd2VyIG91dGFnZS4NCisgIDwvcGFyYT4NCisNCiAgICA8cGFyYT4N CiAgICAgTm90ZSB0aGF0IGFsdGhvdWdoIFdBTCBhcmNoaXZpbmcgd2lsbCBhbGxvdyB5b3Ug dG8gcmVzdG9yZSBhbnkNCiAgICAgbW9kaWZpY2F0aW9ucyBtYWRlIHRvIHRoZSBkYXRhIGlu IHlvdXIgPHByb2R1Y3RuYW1lPlBvc3RncmVTUUw8Lz4gZGF0YWJhc2UsDQoNCg== --------------D792AB92A0614EA4E38CE59F 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----- --------------D792AB92A0614EA4E38CE59F-- --L7VK2RwxwedgfOegfiwDksCmgvi7GHhH8-- --HAtfVjOP89GJLE9WXsgOP7GipbtXidMff 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 iQEcBAEBCAAGBQJYNd7pAAoJEBAQrmsyiTOMy18H/3EqOetDPP/2GG5voEpmodER LXrLiNeeJFG7wqFQA5C681Du4ABjQdkt5Z7oV+huoAZlmEpjAaiydaQYLgkPoMtT nhO8G1CRRR0Y7hokWgLSddC7EHzw7jZ84Mh7eFHi6XITmq8a/nVaAdc5ngjWZbCb Zx07sW2ueg/t/fmHcZOTIy2pJgyDbDa/lcs9cBhoUaFT+s/vsmxM2RT2QolwwVDQ fX0Aq0lHpg4iM2fguy7DHrBfsVdMS5CWY5Zu4wNlF4Vc/NcQwpg+gGnoxoi5FPl+ np0/zaes/jaW5GY7crkUBEMz+7bI93/g35PRYUSzedm7UhJmcsTS8DSIU5qaTK8= =gVQo -----END PGP SIGNATURE----- --HAtfVjOP89GJLE9WXsgOP7GipbtXidMff--