Re: pg_dump vs pg_basebackup

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.

G

On 25 Mar 2014, at 16:29, Ilya Kosmodemiansky <ilya(dot)kosmodemiansky(at)postgresql-consulting(dot)com> wrote:

> Joshua,
>
> On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> The advantage is that you can create backups that don't
>> have to be restored, just started. You can also use the differential
>> portions of rsync to do it multiple times a day without much issue.
>
> Are you sure, that it is a nice idea on a database with heavy write workload?
>
> And also Im not sure, that differential backups using rsync will be
> recoverable, if you have actually meant that.
>
>>
>>
>> JD
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
>> PostgreSQL Support, Training, Professional Services and Development
>> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> Political Correctness is for cowards.
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik(at)postgresql-consulting(dot)com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance