Thanks for looking at this!
On Fri, 2021-07-16 at 09:17 -0400, Stephen Frost wrote:
> > > 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.
>
> I disagree entirely. The reason to even consider using this approach is
> to minimize the time required to get things back online and there's no
> question that having the unlogged tables get rsync'd across would
> increase the time required.
I am not totally convinced that minimal down time is always more important
than keeping your unlogged tables, but I have adapted the patch accordingly.
> > 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.
>
> I disagree. Instead, we should have explicit steps included which
> detail how to find and truncate unlogged tables and what to do to remove
> or exclude temporary files once the server is shut down.
Ok, done.
> > 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".
>
> I'm not really convinced that this is actually a positive change, though
> I don't know that it's really a negative one either. In general, I
> prefer fully qualified paths to try and make things very clear about
> what's happening, but this is also a bit of an odd case due to hard
> links, etc.
I normally prefer absolute paths as well.
But that is the only way I got it to run, and I think that in this
case it adds clarity to have the data directories relative to your
current working directory.
> > Add a reminder that "standby.signal" needs to be created.
>
> This makes sense to include, certainly, but it should be an explicit
> step, not just a "don't forget" note at the end. I'm not really sure
> why we talk about "log shipping" either..? Wouldn't it make more sense
> to have something like:
>
> g. Configure standby servers
>
> Review the prior configuration of the standby servers and set up the
> same configuration in the newly rsync'd directory.
>
> 1. touch /path/to/replica/standby.signal
> 2. Configure restore_command to pull from WAL archive
> 3. For streaming replicas, configure primary_conninfo
Ok, I have modified the final step like this. That is better than
talking about log shipping.
Patch V3 attached.
Yours,
Laurenz Albe
From 43453dc7379f87ca6638c80c9ec6bf528f8e2e28 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Thu, 22 Jul 2021 15:33:59 +0200
Subject: [PATCH] Improve doc for pg_upgrade and standby servers
Recommend truncating or removing unlogged and temporary
tables to speed up "rsync". Since this is best done in
the step "Prepare for standby server upgrades", move that
step to precede "Stop both 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".
Rewrite the final substep to not mention "log shipping".
Rather, provide a list of the necessary configuration steps.
---
doc/src/sgml/ref/pgupgrade.sgml | 96 +++++++++++++++++++++------------
1 file changed, 63 insertions(+), 33 deletions(-)
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index a83c63cd98..3ccb311ff7 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -324,6 +324,35 @@ make prefix=/usr/local/pgsql.new install
</para>
</step>
+ <step id="prepare-standby-upgrade">
+ <title>Prepare for standby server upgrades</title>
+
+ <para>
+ If you are upgrading standby servers using methods outlined in section <xref
+ linkend="pgupgrade-step-replicas"/>, you should consider dropping temporary
+ tables and truncating unlogged tables on the primary, since that will speed up
+ <application>rsync</application> and keep the down time short.
+ You could run the following <application>psql</application> commands
+ in all databases:
+
+<programlisting>
+SELECT format('DROP TABLE %s', oid::regclass) FROM pg_class WHERE relpersistence = 't' \gexec
+SELECT format('TRUNCATE %s', oid::regclass) FROM pg_class WHERE relpersistence = 'u' \gexec
+</programlisting>
+
+ After stopping the primary servers as described in the following step, verify that
+ the old standby servers have caught up by running
+ <application>pg_controldata</application> against the old primary and
+ standby clusters. Verify that the <quote>Latest checkpoint location</quote>
+ values match in all clusters.
+ (There will be a mismatch if old standby servers were shut down
+ before the old primary or if the old standby servers are still running.)
+ Also, make sure <varname>wal_level</varname> is not set to
+ <literal>minimal</literal> in the <filename>postgresql.conf</filename> file on the
+ new primary cluster.
+ </para>
+ </step>
+
<step>
<title>Stop both servers</title>
@@ -349,23 +378,6 @@ NET STOP postgresql-&majorversion;
</para>
</step>
- <step>
- <title>Prepare for standby server upgrades</title>
-
- <para>
- If you are upgrading standby servers using methods outlined in section <xref
- linkend="pgupgrade-step-replicas"/>, verify that the old standby
- servers are caught up by running <application>pg_controldata</application>
- against the old primary and standby clusters. Verify that the
- <quote>Latest checkpoint location</quote> values match in all clusters.
- (There will be a mismatch if old standby servers were shut down
- before the old primary or if the old standby servers are still running.)
- Also, make sure <varname>wal_level</varname> is not set to
- <literal>minimal</literal> in the <filename>postgresql.conf</filename> file on the
- new primary cluster.
- </para>
- </step>
-
<step>
<title>Run <application>pg_upgrade</application></title>
@@ -528,26 +540,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
@@ -568,7 +580,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/Postgr
small.) This provides rapid standby upgrades. Unfortunately,
<application>rsync</application> needlessly copies files associated with
temporary and unlogged tables because these files don't normally
- exist on standby servers.
+ exist on standby servers, so you should have truncated or dropped
+ such tables as described in <xref linkend="prepare-standby-upgrade"/>.
</para>
<para>
@@ -576,8 +589,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
@@ -587,13 +600,30 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tb
</step>
<step>
- <title>Configure streaming replication and log-shipping standby servers</title>
+ <title>Configure standby servers</title>
<para>
- 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.)
+ Review the prior configuration of the standby servers and set up the
+ same configuration in the newly <application>rsync</application>'ed
+ directory.
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ touch <filename>/path/to/replica/standby.signal</filename>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ configure <varname>restore_command</varname> to pull from WAL archive
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ for streaming replicas, configure <varname>primary_conninfo</varname>
+ </para>
+ </listitem>
+ </itemizedlist>
</para>
</step>
--
2.26.3
