Re: pg_dump vs pg_basebackup - Mailing list pgsql-performance

From Graeme B. Bell
Subject Re: pg_dump vs pg_basebackup
Date
Msg-id 110A59E2-9EFF-43D9-9690-CDD5B3D92697@skogoglandskap.no
Whole thread Raw
In response to Re: pg_dump vs pg_basebackup  (Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>)
Responses Re: pg_dump vs pg_basebackup  (Alan Hodgson <ahodgson@simkin.ca>)
List pgsql-performance
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
isusually 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
buffersetc, 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
statewithout needing to shutdown the production DB completely.  

G

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

> Joshua,
>
> On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake <jd@commandprompt.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@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



pgsql-performance by date:

Previous
From: Ilya Kosmodemiansky
Date:
Subject: Re: pg_dump vs pg_basebackup
Next
From: "Joshua D. Drake"
Date:
Subject: Re: pg_dump vs pg_basebackup