From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Michael Monnerie <michael.monnerie@is.it-management.at>
To: pgsql-admin@postgresql.org
Subject: Re: Hard link backup strategy
Date: Mon, 30 Mar 2009 12:20:43 -0500
Message-ID: <49D0B91B.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <200903270843.45051@zmi.at>
References: <49CB9D8302000025000257DF@gwmta.wicourts.gov>
<49CBA19F02000025000257EB@gwmta.wicourts.gov>
<49CBA19F.EE98.0025.0@wicourts.gov>
<200903270843.45051@zmi.at>
Michael Monnerie <michael.monnerie@is.it-management.at> wrote:
> On Donnerstag 26 März 2009 Kevin Grittner wrote:
>> (1) Our archive script copies WAL files to a directory on the
>> database server, using cp to a one directory followed by mv to
>> another (to prevent partial files from being processed).
>
> What "partial files" do you speak about? I'd like to know, as we
> soon start doing WAL copies.
I don't want anything to attempt to process a WAL file while it is in
the process of being copied. By copying to a separate directory on
the same mount point and moving it, once complete, to the location
where other software is looking for WAL files, we avoid that problem.
> You mean you make the hard link both on client and server, and
> afterwards transfer with rsync? That only works for postgresql
> <8.3, right? As I understand, 8.3 will reuse the space from updated
> tuples and therefore lots of changes in-between will be done.
PostgreSQL version has nothing to do with it. PostgreSQL segments a
table into 1GB files. Our largest tables are are either "insert only"
or rarely have updates or deletes, so many of these table segment
files are unchanged from one base backup to the next. What we're
trying to accomplish is to use Linux hard links to create directory
entries from multiple base backups which point to the same file body.
Do a web search for "linux hard links" if this doesn't make sense to
you.
The benefit of rsync is that, when you use a daemon as we do, it won't
send unchanged portions of a file over the wire. In preliminary tests
our largest county only would only send about 10% of the full set of
data if we used rsync on a snapshot of the original data directory,
without using cpio or gzip first. I'm hoping that if we use the warm
standby image as the base, rather than the previous week's backup, it
will be a fraction of that.
Basically, the hard links will be used to conserve disk space; rsync
will be used to conserve network bandwidth.
-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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, michael.monnerie@is.it-management.at
Subject: Re: Hard link backup strategy
In-Reply-To: <49D0B91B.EE98.0025.0@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