Re: pg_upgrade and rsync

Jim,

* Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)com) wrote:
> On 1/23/15 12:40 PM, Stephen Frost wrote:
> >>>That said, the whole timestamp race condition in rsync gives me the heebie-jeebies. For normal workloads maybe it's not that big a deal, but when dealing with fixed-size data (ie: Postgres blocks)? Eww.
> >The race condition is a problem for pg_start/stop_backup and friends.
> >In this instance, everything will be shut down when the rsync is
> >running, so there isn't a timestamp race condition to worry about.
>
> Yeah, I'm more concerned about people that use rsync to take base backups. Do we need to explicitly advise against that? Is there a way to work around this with a sleep after pg_start_backup to make sure all timestamps must be different? (Admittedly I haven't fully wrapped my head around this yet.)

I've thought about it a fair bit actually and I agree that there is some
risk to using rsync for *incremental* base backups. That is, you have
a setup where you loop with:

pg_start_backup
rsync -> dest
pg_stop_backup

without using -I, changing what 'dest' is, or making sure it's empty
every time. The problem is the 1s-level granularity used on the
timestamp. A possible set of operations, all within 1s, is:

file changed
rsync starts copying the file
file changed again (somewhere prior to where rsync is at)
rsync finishes the file copy

Now, this isn't actually a problem for the first time that file is
backed up- the issue is if that file isn't changed again. rsync won't
re-copy it, but that change that rsync missed won't be in the WAL
history for the *second* backup that's done (only the first), leading to
a case where that file would end up corrupted.

This is a pretty darn narrow situation and one that I doubt many people
will hit, but I do think it's possible.

A way to address this would be to grab all timestamps for all files
at the start of the backup and re-copy any files whose times are changed
after that point (or which were being changed at the time the check was
done, or perhaps simply any file which has a timestamp after the
starting timestamp of the backup).

> >>>How horribly difficult would it be to allow pg_upgrade to operate on multiple servers? Could we have it create a shell script instead of directly modifying things itself? Or perhaps some custom "command file" that could then be replayed by pg_upgrade on another server? Of course, that's assuming that replicas are compatible enough with masters for that to work...
> >Yeah, I had suggested that to Bruce also, but it's not clear why that
> >would be any different from an rsync --size-only in the end, presuming
> >everything went according to plan.
>
> Yeah, if everything is shut down maybe we're OK.

Regarding this, yes, I think it 'should' work, but it would definitely
be good to test it quite a bit before relying on it..

Thanks,

Stephen