Обсуждение: 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@momjian.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 \ --excludepg_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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce, * Bruce Momjian (bruce@momjian.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@momjian.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.htmlAnother option is to use rsync to perform a file system backup.This isdone by first running rsync while the database server is running, thenshutting down the database server justlong enough to do a second rsync.The second rsync will be much quicker than the first, because it hasrelatively littledata to transfer, and the end result will beconsistent because the server was down. This method allows a file systembackupto 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce, * Bruce Momjian (bruce@momjian.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@momjian.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@momjian.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