From: Jakov Sosic <jakov.sosic@srce.hr>
To: pgsql-admin@postgresql.org
Subject: Re: Backing up postgresql database
Date: Wed, 04 Mar 2009 01:51:24 +0100
Message-ID: <49ADD08C.3000604@srce.hr> (raw)
In-Reply-To: <dcc563d10903031622o781a1834s5ba9f2e123101279@mail.gmail.com>
References: <20090303204005.3607451e@pc-jsosic.srce.hr>
<49AD3C83.EE98.0025.0@wicourts.gov>
<49ADC204.3050200@srce.hr>
<dcc563d10903031622o781a1834s5ba9f2e123101279@mail.gmail.com>
Scott Marlowe wrote:
> That might not be much smaller if you're doing a lot of updates /
> inserts / deletes. Also, SQL ain't the same as what was committed. If
> you insert now() it won't be the same when you run it in two days.
Well I think I miscalculated a little bit... It's more like a 120 GB per
day, not 5TB... I'll post correct results tommorow.
> The key here is it seems like you have a very high churn rate, and if
> you're backup solution involves saving all those changes and applying
> them later, it's gonna incur a rather large storage use.
>
> I.e. PITR, as you have it implemented, might not be your best solution.
> You might be better off with something like Slony, Burcado (sp?) or
> another of other live replication setups that don't have the same
> issues you're seeing here.
Replication involves another machine, and more load on current one, and
that's not what I want.
> It's probably better to keep pg_dump type backups for those types of
> things. pitr is a poor challenger to the easily compressed output of
> pg_dump for offsite backups. Especially over a wire.
But how long would pg_dump execute on DB with this volume of traffic?
And would it kill the machine in that period? I can try that as well
tommorow...
> If I update a single row in a database 1,000,000,000 times, how man
> WAL files will you need?
> If you update every row in a 1,000,000,000 row database, how many WAL
> files will you need?
Obviously application is not just appending new data, as I thought
before... Because appends (aka INSERT) wouldn't cause this volume of
WAL's or would it?
> I'm wondering what you're objectives are in your backup strategy, it
> might be you're coming at it one way when we'd all approach it from
> another way. Or not.
You are correct on this one. DB I'm talking about is not that important
to have rigorous backup schedule and recovery policies. I rather have
hardware limitations and I have to find the best solution to not
overutilize the hardware and in the same time have as frequent backups
as possible.
--
| Jakov Sosic | ICQ: 28410271 | PGP: 0x965CAE2D |
=================================================================
| start fighting cancer -> http://www.worldcommunitygrid.org/ |
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-admin@postgresql.org
Cc: jakov.sosic@srce.hr
Subject: Re: Backing up postgresql database
In-Reply-To: <49ADD08C.3000604@srce.hr>
* 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