Re: Use of rsync for data directory copying

Rsync is popular with Postgres users, but I don't understand how they
are using the default check mode (file size, modification time) to
synchronize shut-down data directories? It seems they would have to use
--checksum because it is too easy for files to change in the same
second, and for a backend to write into the middle of a file.

Is everyone who is using rsync with Postgres also using --checksum mode?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Jul 9, 2012, at 7:48 PM, Bruce Momjian wrote:

> Rsync is popular with Postgres users, but I don't understand how they
> are using the default check mode (file size, modification time) to
> synchronize shut-down data directories? It seems they would have to use
> --checksum because it is too easy for files to change in the same
> second, and for a backend to write into the middle of a file.
>
> Is everyone who is using rsync with Postgres also using --checksum mode?

I must be missing something, if they're shut down you can't write to them. =)

I do use rsync though for resyncing my mirror's, it's been working great so far. I assume
that the WAL fixes anything that gets goofed up in the copy. (hopefully I've been assuming correctly.)

---
#!/bin/ksh

SOURCE=masterdbserver

. /etc/sysconfig/pgsql/postgresql-9.1

export PGDATA
export PGPORT

rm -f $PGDATA/failover

pg_ctl stop -D $PGDATA -m immediate
psql -h $SOURCE -p $PGPORT <<EOD
checkpoint;
select pg_start_backup('mirror');
EOD
rsync -avv --delete-delay \
--exclude postgresql.conf \
--exclude pg_hba.conf \
--exclude server.conf \
--exclude archive.conf \
--exclude recovery.conf \
--exclude recovery.done \
--exclude pg_ident.conf \
--exclude failover \
--exclude pg_xlog \
--exclude postmaster.pid \
$SOURCE:$PGDATA/ $PGDATA
psql -h $SOURCE -p $PGPORT -c "select pg_stop_backup()"
cp $PGDATA/recovery.done $PGDATA/recovery.conf
pg_ctl start -D $PGDATA

On Mon, Jul 9, 2012 at 08:00:48PM -0700, David Kerr wrote:
>
> On Jul 9, 2012, at 7:48 PM, Bruce Momjian wrote:
>
> > Rsync is popular with Postgres users, but I don't understand how they
> > are using the default check mode (file size, modification time) to
> > synchronize shut-down data directories? It seems they would have to use
> > --checksum because it is too easy for files to change in the same
> > second, and for a backend to write into the middle of a file.
> >
> > Is everyone who is using rsync with Postgres also using --checksum mode?
>
>
> I must be missing something, if they're shut down you can't write to them. =)
>
> I do use rsync though for resyncing my mirror's, it's been working great so far. I assume
> that the WAL fixes anything that gets goofed up in the copy. (hopefully I've been assuming correctly.)

If two writes happens in the middle of a file in the same second, it
seems one might be missed. Yes, I suppose the WAL does fix that during
replay, though if both servers were shut down cleanly, WAL would not be
replayed.

If you using it for a hot backup, and WAL would clean that up.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> If two writes happens in the middle of a file in the same second, it
> seems one might be missed. Yes, I suppose the WAL does fix that during
> replay, though if both servers were shut down cleanly, WAL would not be
> replayed.
>
> If you using it for a hot backup, and WAL would clean that up.

Right... If it's hot backup, then WAL will fix it; if it's done after a
clean shut-down, nothing should be writing to those files (much less
multiple writes in the same second), so checksum shouldn't be
necessary...

If you're doing rsync w/o doing pg_start_backup/pg_stop_backup, that's
not likely to work even *with* --checksum..

So, can you explain which case you're specifically worried about?

Thanks,

Stephen

On Sat, Jul 14, 2012 at 09:17:22PM -0400, Stephen Frost wrote:
> Bruce,
>
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > If two writes happens in the middle of a file in the same second, it
> > seems one might be missed. Yes, I suppose the WAL does fix that during
> > replay, though if both servers were shut down cleanly, WAL would not be
> > replayed.
> >
> > If you using it for a hot backup, and WAL would clean that up.
>
> Right... If it's hot backup, then WAL will fix it; if it's done after a
> clean shut-down, nothing should be writing to those files (much less
> multiple writes in the same second), so checksum shouldn't be
> necessary...
>
> If you're doing rsync w/o doing pg_start_backup/pg_stop_backup, that's
> not likely to work even *with* --checksum..
>
> So, can you explain which case you're specifically worried about?

OK. The basic problem is that I previously was not clear about how
reliant our use of rsync (without --checksum) was on the presence of WAL
replay.

Here is an example from our documentation that doesn't have WAL replay:

http://www.postgresql.org/docs/9.2/static/backup-file.html

Another option is to use rsync to perform a file system backup. This is
done by first running rsync while the database server is running, then
shutting down the database server just long enough to do a second rsync.
The second rsync will be much quicker than the first, because it has
relatively little data to transfer, and the end result will be
consistent because the server was down. This method allows a file system
backup to be performed with minimal downtime.

Now, if a write happens in both the first and second half of a second,
and only the first write is seen by the first rsync, I don't think the
second rsync will see the write, and hence the backup will be
inconsistent.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> On Sat, Jul 14, 2012 at 09:17:22PM -0400, Stephen Frost wrote:
> > So, can you explain which case you're specifically worried about?
>
> OK. The basic problem is that I previously was not clear about how
> reliant our use of rsync (without --checksum) was on the presence of WAL
> replay.

We should only be relying on WAL replay for hot backups which used
pg_start/pg_stop_backup.

> Here is an example from our documentation that doesn't have WAL replay:
>
> http://www.postgresql.org/docs/9.2/static/backup-file.html
>
> Another option is to use rsync to perform a file system backup. This is
> done by first running rsync while the database server is running, then
> shutting down the database server just long enough to do a second rsync.
> The second rsync will be much quicker than the first, because it has
> relatively little data to transfer, and the end result will be
> consistent because the server was down. This method allows a file system
> backup to be performed with minimal downtime.

To be honest, this looks like a recommendation that might have been made
all the way back to before we had hot backups. Technically speaking, it
should work fine to use the above method where the start/stop backup is
only done for the second rsync, if there's a reason to implement such a
system (perhaps the WALs grow too fast or too numerous for a full backup
with rsync between the start_backup and stop_backup?).

> Now, if a write happens in both the first and second half of a second,
> and only the first write is seen by the first rsync, I don't think the
> second rsync will see the write, and hence the backup will be
> inconsistent.

To be more specific, rsync relies on the combination of mtime and size
to tell if the file has been changed or not. In contrast, cp --update
looks like it might only depend on mtime (from reading the cp man page
on a Debian system).

It seems like there could be an issue where PG is writing to a file, an
rsync comes along and copies the file, and then PG writes to that same
file again, after the rsync is done, but within the same second. If the
file size isn't changed by that write, a later rsync might feel that it
isn't necessary to check if the file contents changed. I have to say
that I don't believe I'v ever seen that happen though.

Thanks,

Stephen

Le dimanche 15 juillet 2012 07:02:01, Stephen Frost a écrit :
> Bruce,
>
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > On Sat, Jul 14, 2012 at 09:17:22PM -0400, Stephen Frost wrote:
> > > So, can you explain which case you're specifically worried about?
> >
> > OK. The basic problem is that I previously was not clear about how
> > reliant our use of rsync (without --checksum) was on the presence of WAL
> > replay.
>
> We should only be relying on WAL replay for hot backups which used
> pg_start/pg_stop_backup.
>
> > Here is an example from our documentation that doesn't have WAL replay:
> > http://www.postgresql.org/docs/9.2/static/backup-file.html
> >
> > Another option is to use rsync to perform a file system backup. This is
> > done by first running rsync while the database server is running, then
> > shutting down the database server just long enough to do a second rsync.
> > The second rsync will be much quicker than the first, because it has
> > relatively little data to transfer, and the end result will be
> > consistent because the server was down. This method allows a file system
> > backup to be performed with minimal downtime.
>
> To be honest, this looks like a recommendation that might have been made
> all the way back to before we had hot backups. Technically speaking, it
> should work fine to use the above method where the start/stop backup is
> only done for the second rsync, if there's a reason to implement such a
> system (perhaps the WALs grow too fast or too numerous for a full backup
> with rsync between the start_backup and stop_backup?).
>
> > Now, if a write happens in both the first and second half of a second,
> > and only the first write is seen by the first rsync, I don't think the
> > second rsync will see the write, and hence the backup will be
> > inconsistent.
>
> To be more specific, rsync relies on the combination of mtime and size
> to tell if the file has been changed or not. In contrast, cp --update
> looks like it might only depend on mtime (from reading the cp man page
> on a Debian system).
>
> It seems like there could be an issue where PG is writing to a file, an
> rsync comes along and copies the file, and then PG writes to that same
> file again, after the rsync is done, but within the same second. If the
> file size isn't changed by that write, a later rsync might feel that it
> isn't necessary to check if the file contents changed. I have to say
> that I don't believe I'v ever seen that happen though.

+1
And rsync use utimes() whenever possible (if µs modtime)...but this ignore a
ntp update in the meantime...

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> if a write happens in both the first and second half of a second,

While I'm not sure whether I believe that granularity is really to
the nanosecond, a stat of a table in a production database on xfs
shows this:

Modify: 2012-07-24 10:15:44.096415501 -0500

So presumably both writes would need to happen within a much smaller
time frame than a second.

-Kevin