Skip site navigation (1) Skip section navigation (2)

Re: Backing up a replication set every 30 mins

From: Khusro Jaleel <mailing-lists(at)kerneljack(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Backing up a replication set every 30 mins
Date: 2012-02-15 15:17:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
On 02/15/2012 02:48 PM, Kevin Grittner wrote:
> Khusro Jaleel<mailing-lists(at)kerneljack(dot)com>  wrote:
> The PITR style backup you describe doesn't cause bloat or block DDL,
> and if you archive the WAL files you can restore to any point in
> time following the pg_stop_backup.  pg_dump just gives you a
> snapshot as of the start of the dump, so if you use that you would
> need to start a complete dump every 30 minutes.

Sounds like my pg_start/rsync/pg_stop script solution every 30 mins 
might be better then, as long as the jobs don't overlap :-)

With PITR backups
> and WAL archiving you could set your archvie_timeout to force timely
> archiving (or use streaming replication if you are on 9.0 or later)
> and effectively dump incremental database *activity* to stay
> up-to-date.

Well, I am already using streaming replication to a slave, and I also 
have archive_timeout set to 30 minutes, but it seems that writes occur 
more often, probably every minute or so. I'm not sure why that is, is it 
because of the replication, or is it because the Java app using the DB 
is perhaps changing something slightly in the DB every minute or so? 
Nobody is actually using this DB, I just brought it up, so there is no 
load, just two front-end Java app servers connected, doing nothing (I 
hope, but maybe they are).

> Now, if 30 minutes of activity is more than the size of the
> database, pg_dump could, as Vladimir says, still be a good
> alternative.

I'm not sure I understand what you said there. I think you are saying 
that if the DB doubles or more in size in 30 minutes due to the 
activity, then pg_dump is still a good alternative?

In response to


pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-02-15 15:39:43
Subject: Re: Backing up a replication set every 30 mins
Previous:From: Kevin GrittnerDate: 2012-02-15 14:48:17
Subject: Re: Backing up a replication set every 30 mins

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group