"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Wells Oliver <wellsoliver@gmail.com> wrote:
>> I admit to being scared as crap of rsync'ing a live database to
>> another server. Like chills are running down my spine even typing
>> it. Is this an approved, safe thing?
> It is fine, as long as you're doing it between the pg_start_backup()
> and pg_stop_backup() calls. We do it on 100 production databases
> every week. It is approved, documented, and safe.
Right. It is true that the rsync alone will not give you a consistent
copy if the database is actively being modified. The secret sauce is
in having a copy of all the WAL that was generated while the rsync
happened. Replaying that against the copied data will apply (or
reapply) any changes that occurred meanwhile, thus fixing the
inconsistencies.
(I find that people are much more likely to believe that this works
once they grasp the principle.)
regards, tom lane