[nycbug-talk] postgres incremental backups?

[Peter Wright]

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

yea, i wasn't too sure exactly how much trouble shooting he put into
getting his code to play nice with 8.x.  i'm sure he was vacuuming/etc as
we talked about that - so for lack of further evidence i'm guessing his
code was doing something nasty (at the time of the bug report it was
causing a deadlock for example).

for this data warehousing project i think i'm going to recommend using 8.x
(despite the fact that we generally try to shy away from non-vendor built
applications for business critical tasks...sigh redhat...but that's
another sad story).  the code that was causing problems before has a much
differnent load profile than the code we will be implementing on this new
cluster - so i feel a bit safer in my decision.

thanks for your input!

-pete



-- 
~~oO00Oo~~
Peter Wright
pete at nomadlogic.org
www.nomadlogic.org/~pete
310.869.9459