Re: Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

På onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian <bruce(at)momjian(dot)us
<mailto:bruce(at)momjian(dot)us>>:
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
> På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
> bruce(at)momjian(dot)us>:
>
>     On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
>     >     Improvements?
>     >
>     > Thanks, that certainly improves things.
>     > But; I still find the rsync-command in f) confusing;
>     > 1. Why --size-only? From rsync manual: "skip files that match in
size",
>     is this
>     > safe??
>
>
>     > 2. Why is old_pgdata in the rsync-command, why is it needed to sync
it?
>
>     If the file exists under the same name, it doesn't need to be checked at
>     all --- it is the same.  We don't want to check the file modification
>     time because it will probably be different because of replay delay or
>     clock drift.  We could use checksums, but there is no need since there
is
>     no way the file contents could be different.
>
>  
>  
> So you're saying that if the file exists (has the same name) on the standby
(in
> old_pgdata), and has the same size, then you're safe that it contains the
same
> data, hence --size-only?
> Does this apply when not using --link mode for pg_upgrade?

Well, it is really true in every case.  For link mode, we have to use an
rsync command that lists both the old and new clusters on the command
line (since we need rsync to see those hard links to reproduce them). If
we don't use --size-only, we are going to checksum check the _old_ data
cluster.  The new cluster will be empty so we will copy all of that (no
need for a checksum there since there are no files).  I think you need
size-only even without link since that old cluster is going to be listed
for rsync.

Now, what you could do, if you are _not_ using link mode, is to rsync
only the new cluster, but the instructions we give work the same for
link and non-link mode and produce the same results in the same time
even if we had a non-link-mode example, so it seems we might as well
just give one set of instructions.

>     > There are many ways to do/configure things it seems, resulting in many
>     ifs and
>     > buts which makes section 10 rather confusing. I really think a
complete
>     > example, with absolute paths, would be clarifying.
>
>     You mean a full rsync command, e.g.:
>
>       rsync --archive --delete --hard-links --size-only \
>           /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL
>
>     Does that help?
>
>  
>  
> It seems some non-obvious assumptions (to me at least) are made here.
> This example seems only valid when using pg_upgrade --link, correct? If so
it
> would be clearer to the reader if explicitly stated.

Well, as I stated above, --hard-links is only going to recreate hard
links on the standby that exist on the primary, and if you didn't use
pg_upgrade's --link mode, there will be none, so it is harmless if
pg_upgrade --link mode was not used.

> 1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND
/opt/PostgreSQL/9.6,
> wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgrade
--link"
> AND "rsync --hard-links" and therefore it somewhat needs to transfer less
data?

As I stated above, rsync has to see _both_ hard links on the primary to
recreate them on the standby.  I thought the doc patch was clear on
that, but obviously not.  :-(  Suggestions?  (Yes, I admit that using
rsync in this way is super-crafty, and I would _love_ to take credit for
the idea, but I think the award goes to Stephen Frost.)

> 2. What would the rsync command look like if pg_upgrade wasn't issued with
> --link?

It would look like:

  rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6

but effectively there isn't anything _in_ standby:/opt/PostgreSQL/9.6,
so you are really just using rsync as cp, and frankly I have found 'cp'
is faster than rsync when nothing exists on the other side so it really
becomes "just copy the cluster when the server is down", but I don't
think people even need instructions for that.

Maybe we should recommend rsync only for pg_upgrade --link mode?

> 3. What if the directory-layout isn't the same on primary and standby, ie.
> tablespaces are located differently?

The way we reconfigured the location of tablespaces in PG 9.0 is that
each major version of Postgres places its tablespace in a subdirectory
of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.  If
your tbldir is different on the primary and standby, rsync will  still
work.  Everything _under_ the standby dir must be laid out the same, but
the directories above it can be different.
 
 
(I know this isn't exactly -hackers food, but it seems natural to end this
thread here)
 
Ok, thanks.
It is clearer what happens now that you've explained that there's a clever
"rsync-trick" involving 2 directories and making rsync preserving
hard-links that way on the destination-server. Maybe it's because I'm not a
native English speaker but it wasn't obvious to me...
 
I have my tablespaces laid out like this:
/storage/fast_ssd/9.6/tablespaces/<customer>
which you correctly say that in practice means that 9.6 files are (I see now
that I don't need the pg-version in my directory-structure):
/storage/fast_ssd/9.6/tablespaces/<customer>/PG_9.6_201608131
 
I understand, I hope, that without link-mode rsyncing tablespaces would be
like this:
rsync --archive /path/to/tablespace_basedir standby:/path/to/tablespace_basedir
 
What would the equivalent be in link-mode, for transferring most efficiently?
The reason I ask is that it's not immediately obvious to me what "old_datadir"
and "new_datadir" when rsync'ing tablespaces and pg_wal dirs outside the
"pg-dirs".
 
Speaking of pg_wal, how should this be rsynced now that it's changed its name
(from pg_xlog), just rsync pg_xlog and rename it?
 
I know I'm being a little nitty-gritty here, but if it helps me understand it
might help others.
 
Thanks.

--
Andreas Joseph Krogh