From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Khusro Jaleel <mailing-lists@kerneljack.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Backing up a replication set every 30 mins
Date: Wed, 15 Feb 2012 09:39:43 -0600
Message-ID: <4F3B7D5F0200002500045603@gw.wicourts.gov> (raw)
In-Reply-To: <4F3BCCA2.1030102@kerneljack.com>
References: <4F3BA826.9000708@kerneljack.com>
<CANjgnBvJU-0C=eDkKf32YV2UrC+bVfsM8X=542T-Yn92p=vVEA@mail.gmail.com>
<4F3BBBA6.1040707@kerneljack.com>
<4F3B715102000025000455FB@gw.wicourts.gov>
<4F3BCCA2.1030102@kerneljack.com>
Khusro Jaleel <mailing-lists@kerneljack.com> wrote:
> 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 :-)
That sounds like it's probably overkill. Once you have your base
backup, you can just accumulate WAL files. We do a base backup once
per week and keep the last two base backups plus all WAL files from
the start of the first one. We can restore to any particular point
in time after that earlier base backup. I've heard of people
happily going months between base backups, and just counting on WAL
file replay, although I'm slightly too paranoid to want to go that
far.
>> 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?
Not exactly. I was saying that if you have a very unusual situation
where the database is very small but has very high volumes of
updates (or inserts and deletes) such that it stays very small while
generating a lot of WAL, it is within the realm of possibility that
a pg_dump every 30 minutes could be your best option. I haven't
seen such a database yet, but I was conceding the possibility that
such could exist.
-Kevin
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, mailing-lists@kerneljack.com
Subject: Re: Backing up a replication set every 30 mins
In-Reply-To: <4F3B7D5F0200002500045603@gw.wicourts.gov>
References: <4F3BA826.9000708@kerneljack.com> <CANjgnBvJU-0C=eDkKf32YV2UrC+bVfsM8X=542T-Yn92p=vVEA@mail.gmail.com> <4F3BBBA6.1040707@kerneljack.com> <4F3B715102000025000455FB@gw.wicourts.gov> <4F3BCCA2.1030102@kerneljack.com> <4F3B7D5F0200002500045603@gw.wicourts.gov>
* 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