From: Alan Hodgson <ahodgson@simkin.ca>
To: pgsql-performance@postgresql.org
Subject: Re: pg_dump vs pg_basebackup
Date: Tue, 25 Mar 2014 09:12:46 -0700
Message-ID: <4859022.GjYQ53Lp8r@skynet.simkin.ca>
User-Agent: KMail/4.11.5 (Linux/3.12.13-gentoo; KDE/4.11.5; x86_64; ; )
In-Reply-To: <110A59E2-9EFF-43D9-9690-CDD5B3D92697@skogoglandskap.no>
References: <1395722709.28433.YahooMailNeo@web161601.mail.bf1.yahoo.com>
 <CAG95seWXZo9O_AJs_cNyiyw8obN7guaeRutzDCN1oaiGTReP6w@mail.gmail.com>
 <110A59E2-9EFF-43D9-9690-CDD5B3D92697@skogoglandskap.no>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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).


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance