Re: pg_dump vs pg_basebackup

On Tuesday, March 25, 2014 03:48:07 PM Graeme B. Bell wrote:
> Postgresql rsync backups require the DB to be shutdown during the 'second'
> rsync.
>
> 1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99%
> consistent DB on the backup filesystem) 2. shut down the DB
> 3. rsync the shut down DB onto the backup filesystem (synchronises the
> last few files to make the DB consistent, and is usually very fast) 4.
> start the DB up again
>
> Is there any way to notify postgres to pause transactions (and note that
> they should be restarted), and flush out write buffers etc, instead of
> doing a full shutdown? e.g. so that the second rsync call would bring the
> backup filesystem's representation of the DB into a recoverable state
> without needing to shutdown the production DB completely.
>

You use pg_start_backup() before rsync, and pg_stop_backup() after. And keep
all your WAL log files. No need to pause transactions; whatever happens during
the rsync just gets replayed during recovery (as I understand it). You do need
to do a PITR restore to make use of this rsync copy.

That's basically what pg_basebackup does, I believe (I haven't used it, I only
do rsyncs).