Re: Improve documentation for pg_upgrade, standbys and rsync

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