pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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> (raw)
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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



view thread (19+ messages)  latest in thread

Message-ID: <4859022.GjYQ53Lp8r@skynet.simkin.ca>
Permalink:  ../4859022.GjYQ53Lp8r@skynet.simkin.ca/
Also on:    postgresql.org/message-id/4859022.GjYQ53Lp8r@skynet.simkin.ca

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ahodgson@simkin.ca
  Subject: Re: pg_dump vs pg_basebackup
  In-Reply-To: <4859022.GjYQ53Lp8r@skynet.simkin.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox