On Wed, 2021-05-19 at 10:31 -0400, Stephen Frost wrote: > * Laurenz Albe (laurenz.a...@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 . Yours, Laurenz Albe : https://www.postgr.es/m/1a5a1b6e-7bb6-47eb-8443-40222b769...@iris.washington.edu
From 47b685b700548af06ab08673187bdd1df7236464 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Fri, 16 Jul 2021 07:45:22 +0200 Subject: [PATCH] Improve doc for pg_upgrade and standby servers Recommend using the --relative option of rsync for clarity and adapt the code samples accordingly. Using relative paths makes clearer what is meant by "current directory" and "remote_dir". Add a reminder that "standby.signal" needs to be created. --- doc/src/sgml/ref/pgupgrade.sgml | 23 ++++++++++++----------- 1 file changed, 12 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index a83c63cd98..7aff00833a 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -528,26 +528,26 @@ pg_upgrade.exe <para> When using link mode, standby servers can be quickly upgraded using - <application>rsync</application>. To accomplish this, from a directory on + <application>rsync</application>. To accomplish this, change into a directory on the primary server that is above the old and new database cluster - directories, run this on the <emphasis>primary</emphasis> for each standby + directories and run this on the <emphasis>primary</emphasis> for each standby server: <programlisting> -rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir +rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative old_cluster new_cluster remote_dir </programlisting> where <option>old_cluster</option> and <option>new_cluster</option> are relative to the current directory on the primary, and <option>remote_dir</option> - is <emphasis>above</emphasis> the old and new cluster directories - on the standby. The directory structure under the specified - directories on the primary and standbys must match. Consult the + is the directory on the standby that corresponds to your current directory + on the primary. The directory structure under the specified + directories on the primary and standbys must be the same. Consult the <application>rsync</application> manual page for details on specifying the remote directory, e.g., <programlisting> -rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.5 \ - /opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL +rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative 9.6 13 \ + standby.example.com:/var/lib/postgresql </programlisting> You can verify what the command will do using @@ -576,8 +576,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/Postgr <application>rsync</application> command for each tablespace directory, e.g.: <programlisting> -rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \ - /vol1/pg_tblsp/PG_9.6_201608131 standby.example.com:/vol1/pg_tblsp +rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative \ + PG_9.6_201608131 PG_13_202007201 standby.example.com:/vol1/tblsp </programlisting> If you have relocated <filename>pg_wal</filename> outside the data @@ -593,7 +593,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tb Configure the servers for log shipping. (You do not need to run <function>pg_start_backup()</function> and <function>pg_stop_backup()</function> or take a file system backup as the standbys are still synchronized - with the primary.) + with the primary.) Don't forget to create <filename>standby.signal</filename> + on the standby server. </para> </step> -- 2.26.3