[nycbug-talk] postgres incremental backups?

[Bob Ippolito]

On 8/24/06, Peter Wright <pete at nomadlogic.org> wrote:
>
> > On 8/24/06, Peter Wright <pete at nomadlogic.org> wrote:
> >> Hi All,
> >> I have a postgres 7.4.x database which we will be using for a data
> >> warehousing project.  We project the initial database clusters will be
> >> in
> >> the ~200-300gig range (served from a NetApp volume).  As I mentioned we
> >> are using release 7.4, as we have experienced stability problems with
> >> 8.x
> >> in some specific circumstances.  Is there a good way to do incremental
> >> backups of pgsql db's that does not require taking the database down?  I
> >> guess I am looking for something similar to PITR (point in time
> >> recovery)
> >> for 7.4.x.  What are other folks out there using?
> >
> > The only thing I can think of is Slony-I [1]. It should cover all of
> > your needs and give you a safe online way to replicate your cluster to
> > some future 8.1.x or 8.2 when the time comes.
> >
>
> great thanks bob, i'll have to check that out!
>
> > Out of curiosity, what kind of stability problems are you referring to?
> >
> i did not have first hand experience with it (and the dev. left who was
> running into the problem) but from what I understood was that we were
> having bad transaction log's fill our cluster volume pretty quickly.  he
> submitted a bug (BUG #2104: pg_xlog/ trace files not reclaimed by server).
>  to tell you the truth - i am not sure if the bug is a side effect of his
> code/app or if it is an issue with postgres....

That's odd, I've got about 6 PG 8.1 installations of a couple hundred
GB each and I haven't seen anything like that. It also sounds like he
couldn't reproduce the issue.

My only guess is that he forgot to vacuum ever.. cause if you roll
over two billion transactions or whatever without vacuuming then
you've got problems. PG 8.1 has autovacuum (and previous versions had
an autovacuum daemon in contrib) that makes that pretty much a
non-issue... but you do have to turn it on either way. I think the
FreeBSD port has a cron job for a nightly vacuum though, but who knows
what the issue was.

My suggestion would be to get Slony-I up and going, and then replicate
to an 8.1 DB and see if anything like that happens over a few months..
and if not, then switch that to the master and upgrade all of the
machines to 8.1... or just wait for 8.2 and try that.

Another option is Bizgres.. the open source bizgres lives somewhere
between the current stable release and the next release (e.g. bizgres
used to be 8.0 with partitioned tables, now it's 8.1 with bitmap
indexes and an improved sort).

The commercial bizgres is expensive but sounds like it would be good
for really big databases because it scales over a cluster (both in
storage and query execution, but the way it locks doesn't sound
suitable for all apps).

-bob