From: Jakov Sosic <jakov.sosic@srce.hr>
To: pgsql-admin@postgresql.org
Subject: Re: Backing up postgresql database
Date: Wed, 04 Mar 2009 00:49:24 +0100
Message-ID: <49ADC204.3050200@srce.hr> (raw)
In-Reply-To: <49AD3C83.EE98.0025.0@wicourts.gov>
References: <20090303204005.3607451e@pc-jsosic.srce.hr>
<49AD3C83.EE98.0025.0@wicourts.gov>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Kevin Grittner wrote:
> What you are copying is all the information required to restore the
> database to the state it was in after the commit of any one of these
> transactions. Out of curiosity, how much space would you have thought
> that would take?
Well I surely didn't thought it would take 4.8 TB per day on a 150GB
database! I tought it will take less than a DB itself. Wouldn't it be
better to simply store SQL commands? :)
> Then you need to choose a different backup strategy. You probably
> don't need to keep a lot of these for a long time. We keep enough to
> restore from the latest two base backups, plus the minimum set
> required to restore a "snapshot" for the first base backup of each
> month.
Yes, but if base backup is every 7 days, that means 40 TB of backup for
two weeks.
Please, don't get angry because of my attitude, but I'm new to backup
strategies and that's why I'm puzzled alot with the volume of
information... I mean, we do have LTO4 tapes, but still :)
> The WAL file volume will have nothing to do with the size of the
> database; rather it reflects all activity which modified the database
> over some period of time.
As I said, almost 500 hosts sends about 25'000 values every few
minutes... So that's a lot of insert/updates...
> It records each change made to every row in the database. Do fewer
> updates or keep the WAL files for less time?
How do you mean, do fewer updates? DB transactions can't be influenced -
cause is on the application level, not on the DB level.
And what do you mean by keeping WAL's for less time? Daily volume is
still the same... I can define that I'll keep only two week backup, and
not 90day as is policy in my company, but as I've mentioned, that's
still a lots lots of data :) I thought that WAL's will be a lot smaller
than the whole DB cause only small part of DB information is inserted on
daily basis in comparison to the volume of data gathered through months
of db running. Obviously - as you say - I was completely mislead in my
assumptions...
> It's on pgfoundry.org, but no need to go after it. What it's designed
> to help with is improving the compression of WAL files which are sent
> before they're full. It sounds like yours are full. You should
> probably be compressing the WAL files. They compress down to about 4
> MB to 8 MB each when full if you pipe them through gzip.
I've read about gziping WAL's, and I will do it offcourse, but that only
makes problem a little smaller, doesn't solve it :)
- --
| Jakov Sosic | ICQ: 28410271 | PGP: 0x965CAE2D |
=================================================================
| start fighting cancer -> http://www.worldcommunitygrid.org/ |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
iEYEARECAAYFAkmtwgQACgkQMfwi35Zcri1KqACfXDffbxvQJzzyvc9rbN7arbuf
7HwAn3lrvbE/BM+y2Uy0I48VtlG1AMUd
=v6+m
-----END PGP SIGNATURE-----
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: <49ADC204.3050200@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