Message-ID: <c176f23cdde4d4ff0010b5e7a517c1b371500279.camel@cybertec.at>
Subject: Re: Improve documentation for pg_upgrade, standbys and rsync
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-docs@lists.postgresql.org
Date: Fri, 16 Jul 2021 07:46:31 +0200
In-Reply-To: <20210519143135.GI20766@tamriel.snowman.net>
References: <22f129004bb66cd91e1dfd3345a9787f5039f3ae.camel@cybertec.at>
	 <20210519143135.GI20766@tamriel.snowman.net>
Content-Type: multipart/mixed; boundary="=-YWhHHaMPRhfocsecUZJS"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c176f23cdde4d4ff0010b5e7a517c1b371500279.camel%40cybertec.at>
Precedence: bulk


--=-YWhHHaMPRhfocsecUZJS
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

On Wed, 2021-05-19 at 10:31 -0400, Stephen Frost wrote:
> * Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> > I revently tried to upgrade a standby following the documentation,
> > but I found it hard to understand, and it took me several tries to
> > get it right.  This is of course owing to my lack of expertise with
> > rsync, but I think the documentation and examples could be clearer.
> > 
> > I think it would be a good idea to recommend the --relative option
> > of rsync.
> 
> An additional thing that we should really be mentioning is to tell
> people to go in and TRUNCATE all of their UNLOGGED tables before going
> through this process, otherwise the rsync will end up spending a bunch
> of time copying the files for UNLOGGED relations which you really don't
> want.

I have thought about that some more, and I am not certain that we should
unconditionally recommend that.  Perhaps the pain of rebuilding the
unlogged table on the primary would be worse than rsyncing it to the
standby.

The documentation already mentions

  "Unfortunately, rsync needlessly copies files associated with temporary
   and unlogged tables because these files don't normally exist on standby
   servers."

I'd say that is good enough, and people can draw their conclusions from
that.

Attached is a new patch with an added reminder to create "standby.signal",
as mentioned in [1].

Yours,
Laurenz Albe

 [1]: https://www.postgr.es/m/1A5A1B6E-7BB6-47EB-8443-40222B769404@iris.washington.edu

--=-YWhHHaMPRhfocsecUZJS
Content-Disposition: attachment;
	filename="0001-Improve-doc-for-pg_upgrade-and-standby-servers.V2.patch"
Content-Transfer-Encoding: base64
Content-Type: text/x-patch;
	name="0001-Improve-doc-for-pg_upgrade-and-standby-servers.V2.patch";
	charset="UTF-8"

RnJvbSA0N2I2ODViNzAwNTQ4YWYwNmFiMDg2NzMxODdiZGQxZGY3MjM2NDY0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h
dD4KRGF0ZTogRnJpLCAxNiBKdWwgMjAyMSAwNzo0NToyMiArMDIwMApTdWJqZWN0OiBbUEFUQ0hd
IEltcHJvdmUgZG9jIGZvciBwZ191cGdyYWRlIGFuZCBzdGFuZGJ5IHNlcnZlcnMKClJlY29tbWVu
ZCB1c2luZyB0aGUgLS1yZWxhdGl2ZSBvcHRpb24gb2YgcnN5bmMgZm9yIGNsYXJpdHkKYW5kIGFk
YXB0IHRoZSBjb2RlIHNhbXBsZXMgYWNjb3JkaW5nbHkuClVzaW5nIHJlbGF0aXZlIHBhdGhzIG1h
a2VzIGNsZWFyZXIgd2hhdCBpcyBtZWFudCBieSAiY3VycmVudApkaXJlY3RvcnkiIGFuZCAicmVt
b3RlX2RpciIuCgpBZGQgYSByZW1pbmRlciB0aGF0ICJzdGFuZGJ5LnNpZ25hbCIgbmVlZHMgdG8g
YmUgY3JlYXRlZC4KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL3BndXBncmFkZS5zZ21sIHwgMjMgKysr
KysrKysrKysrLS0tLS0tLS0tLS0KIDEgZmlsZSBjaGFuZ2VkLCAxMiBpbnNlcnRpb25zKCspLCAx
MSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvcmVmL3BndXBncmFkZS5z
Z21sIGIvZG9jL3NyYy9zZ21sL3JlZi9wZ3VwZ3JhZGUuc2dtbAppbmRleCBhODNjNjNjZDk4Li43
YWZmMDA4MzNhIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcmVmL3BndXBncmFkZS5zZ21sCisr
KyBiL2RvYy9zcmMvc2dtbC9yZWYvcGd1cGdyYWRlLnNnbWwKQEAgLTUyOCwyNiArNTI4LDI2IEBA
IHBnX3VwZ3JhZGUuZXhlCiAKICAgICAgIDxwYXJhPgogICAgICAgIFdoZW4gdXNpbmcgbGluayBt
b2RlLCBzdGFuZGJ5IHNlcnZlcnMgY2FuIGJlIHF1aWNrbHkgdXBncmFkZWQgdXNpbmcKLSAgICAg
ICA8YXBwbGljYXRpb24+cnN5bmM8L2FwcGxpY2F0aW9uPi4gIFRvIGFjY29tcGxpc2ggdGhpcywg
ZnJvbSBhIGRpcmVjdG9yeSBvbgorICAgICAgIDxhcHBsaWNhdGlvbj5yc3luYzwvYXBwbGljYXRp
b24+LiAgVG8gYWNjb21wbGlzaCB0aGlzLCBjaGFuZ2UgaW50byBhIGRpcmVjdG9yeSBvbgogICAg
ICAgIHRoZSBwcmltYXJ5IHNlcnZlciB0aGF0IGlzIGFib3ZlIHRoZSBvbGQgYW5kIG5ldyBkYXRh
YmFzZSBjbHVzdGVyCi0gICAgICAgZGlyZWN0b3JpZXMsIHJ1biB0aGlzIG9uIHRoZSA8ZW1waGFz
aXM+cHJpbWFyeTwvZW1waGFzaXM+IGZvciBlYWNoIHN0YW5kYnkKKyAgICAgICBkaXJlY3Rvcmll
cyBhbmQgcnVuIHRoaXMgb24gdGhlIDxlbXBoYXNpcz5wcmltYXJ5PC9lbXBoYXNpcz4gZm9yIGVh
Y2ggc3RhbmRieQogICAgICAgIHNlcnZlcjoKIAogPHByb2dyYW1saXN0aW5nPgotcnN5bmMgLS1h
cmNoaXZlIC0tZGVsZXRlIC0taGFyZC1saW5rcyAtLXNpemUtb25seSAtLW5vLWluYy1yZWN1cnNp
dmUgb2xkX2NsdXN0ZXIgbmV3X2NsdXN0ZXIgcmVtb3RlX2RpcgorcnN5bmMgLS1hcmNoaXZlIC0t
ZGVsZXRlIC0taGFyZC1saW5rcyAtLXNpemUtb25seSAtLW5vLWluYy1yZWN1cnNpdmUgLS1yZWxh
dGl2ZSBvbGRfY2x1c3RlciBuZXdfY2x1c3RlciByZW1vdGVfZGlyCiA8L3Byb2dyYW1saXN0aW5n
PgogCiAgICAgICAgd2hlcmUgPG9wdGlvbj5vbGRfY2x1c3Rlcjwvb3B0aW9uPiBhbmQgPG9wdGlv
bj5uZXdfY2x1c3Rlcjwvb3B0aW9uPiBhcmUgcmVsYXRpdmUKICAgICAgICB0byB0aGUgY3VycmVu
dCBkaXJlY3Rvcnkgb24gdGhlIHByaW1hcnksIGFuZCA8b3B0aW9uPnJlbW90ZV9kaXI8L29wdGlv
bj4KLSAgICAgICBpcyA8ZW1waGFzaXM+YWJvdmU8L2VtcGhhc2lzPiB0aGUgb2xkIGFuZCBuZXcg
Y2x1c3RlciBkaXJlY3RvcmllcwotICAgICAgIG9uIHRoZSBzdGFuZGJ5LiAgVGhlIGRpcmVjdG9y
eSBzdHJ1Y3R1cmUgdW5kZXIgdGhlIHNwZWNpZmllZAotICAgICAgIGRpcmVjdG9yaWVzIG9uIHRo
ZSBwcmltYXJ5IGFuZCBzdGFuZGJ5cyBtdXN0IG1hdGNoLiAgQ29uc3VsdCB0aGUKKyAgICAgICBp
cyB0aGUgZGlyZWN0b3J5IG9uIHRoZSBzdGFuZGJ5IHRoYXQgY29ycmVzcG9uZHMgdG8geW91ciBj
dXJyZW50IGRpcmVjdG9yeQorICAgICAgIG9uIHRoZSBwcmltYXJ5LiAgVGhlIGRpcmVjdG9yeSBz
dHJ1Y3R1cmUgdW5kZXIgdGhlIHNwZWNpZmllZAorICAgICAgIGRpcmVjdG9yaWVzIG9uIHRoZSBw
cmltYXJ5IGFuZCBzdGFuZGJ5cyBtdXN0IGJlIHRoZSBzYW1lLiAgQ29uc3VsdCB0aGUKICAgICAg
ICA8YXBwbGljYXRpb24+cnN5bmM8L2FwcGxpY2F0aW9uPiBtYW51YWwgcGFnZSBmb3IgZGV0YWls
cyBvbiBzcGVjaWZ5aW5nIHRoZQogICAgICAgIHJlbW90ZSBkaXJlY3RvcnksIGUuZy4sCiAKIDxw
cm9ncmFtbGlzdGluZz4KLXJzeW5jIC0tYXJjaGl2ZSAtLWRlbGV0ZSAtLWhhcmQtbGlua3MgLS1z
aXplLW9ubHkgLS1uby1pbmMtcmVjdXJzaXZlIC9vcHQvUG9zdGdyZVNRTC85LjUgXAotICAgICAg
L29wdC9Qb3N0Z3JlU1FMLzkuNiBzdGFuZGJ5LmV4YW1wbGUuY29tOi9vcHQvUG9zdGdyZVNRTAor
cnN5bmMgLS1hcmNoaXZlIC0tZGVsZXRlIC0taGFyZC1saW5rcyAtLXNpemUtb25seSAtLW5vLWlu
Yy1yZWN1cnNpdmUgLS1yZWxhdGl2ZSA5LjYgMTMgXAorICAgICAgc3RhbmRieS5leGFtcGxlLmNv
bTovdmFyL2xpYi9wb3N0Z3Jlc3FsCiA8L3Byb2dyYW1saXN0aW5nPgogCiAgICAgICAgWW91IGNh
biB2ZXJpZnkgd2hhdCB0aGUgY29tbWFuZCB3aWxsIGRvIHVzaW5nCkBAIC01NzYsOCArNTc2LDgg
QEAgcnN5bmMgLS1hcmNoaXZlIC0tZGVsZXRlIC0taGFyZC1saW5rcyAtLXNpemUtb25seSAtLW5v
LWluYy1yZWN1cnNpdmUgL29wdC9Qb3N0Z3IKICAgICAgICA8YXBwbGljYXRpb24+cnN5bmM8L2Fw
cGxpY2F0aW9uPiBjb21tYW5kIGZvciBlYWNoIHRhYmxlc3BhY2UgZGlyZWN0b3J5LCBlLmcuOgog
CiA8cHJvZ3JhbWxpc3Rpbmc+Ci1yc3luYyAtLWFyY2hpdmUgLS1kZWxldGUgLS1oYXJkLWxpbmtz
IC0tc2l6ZS1vbmx5IC0tbm8taW5jLXJlY3Vyc2l2ZSAvdm9sMS9wZ190YmxzcC9QR185LjVfMjAx
NTEwMDUxIFwKLSAgICAgIC92b2wxL3BnX3RibHNwL1BHXzkuNl8yMDE2MDgxMzEgc3RhbmRieS5l
eGFtcGxlLmNvbTovdm9sMS9wZ190YmxzcAorcnN5bmMgLS1hcmNoaXZlIC0tZGVsZXRlIC0taGFy
ZC1saW5rcyAtLXNpemUtb25seSAtLW5vLWluYy1yZWN1cnNpdmUgLS1yZWxhdGl2ZSBcCisgICAg
ICBQR185LjZfMjAxNjA4MTMxIFBHXzEzXzIwMjAwNzIwMSBzdGFuZGJ5LmV4YW1wbGUuY29tOi92
b2wxL3RibHNwCiA8L3Byb2dyYW1saXN0aW5nPgogCiAgICAgICAgSWYgeW91IGhhdmUgcmVsb2Nh
dGVkIDxmaWxlbmFtZT5wZ193YWw8L2ZpbGVuYW1lPiBvdXRzaWRlIHRoZSBkYXRhCkBAIC01OTMs
NyArNTkzLDggQEAgcnN5bmMgLS1hcmNoaXZlIC0tZGVsZXRlIC0taGFyZC1saW5rcyAtLXNpemUt
b25seSAtLW5vLWluYy1yZWN1cnNpdmUgL3ZvbDEvcGdfdGIKICAgICAgICBDb25maWd1cmUgdGhl
IHNlcnZlcnMgZm9yIGxvZyBzaGlwcGluZy4gIChZb3UgZG8gbm90IG5lZWQgdG8gcnVuCiAgICAg
ICAgPGZ1bmN0aW9uPnBnX3N0YXJ0X2JhY2t1cCgpPC9mdW5jdGlvbj4gYW5kIDxmdW5jdGlvbj5w
Z19zdG9wX2JhY2t1cCgpPC9mdW5jdGlvbj4KICAgICAgICBvciB0YWtlIGEgZmlsZSBzeXN0ZW0g
YmFja3VwIGFzIHRoZSBzdGFuZGJ5cyBhcmUgc3RpbGwgc3luY2hyb25pemVkCi0gICAgICAgd2l0
aCB0aGUgcHJpbWFyeS4pCisgICAgICAgd2l0aCB0aGUgcHJpbWFyeS4pICBEb24ndCBmb3JnZXQg
dG8gY3JlYXRlIDxmaWxlbmFtZT5zdGFuZGJ5LnNpZ25hbDwvZmlsZW5hbWU+CisgICAgICAgb24g
dGhlIHN0YW5kYnkgc2VydmVyLgogICAgICAgPC9wYXJhPgogICAgICA8L3N0ZXA+CiAKLS0gCjIu
MjYuMwoK


--=-YWhHHaMPRhfocsecUZJS--