pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Grega Bremec <gregab@p0f.net>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: FW: Setting up of PITR system.
Date: Sat, 08 Apr 2006 14:39:49 +0200
Message-ID: <4437AF15.3020707@p0f.net> (raw)
In-Reply-To: <a97c77030604031129m6e014adcu52df9eee26510ea1@mail.gmail.com>
References: <!&!AAAAAAAAAAAuAAAAAAAAALfqleqaijxJlxu+E5RYF+YBAJaQ0jfg6zBFp7poaER6UCkAAAGy3PcAABAAAAB0heSWPqq+QLrrk5Lik5sLAQAAAAA=@mailnetwork.co.uk>
	<a97c77030603290445o29f96099xd3cc5f15cd4fb364@mail.gmail.com>
	<442B93DD.4020305@p0f.net>
	<a97c77030603312023k7bdc705eif22af429dc3c943d@mail.gmail.com>
	<442E1945.4030006@p0f.net>
	<a97c77030604010258j657622fcj6a42a10c8c38f344@mail.gmail.com>
	<442FDC7F.9010001@p0f.net>
	<a97c77030604020914n38b3e3bco2138b9673f25b176@mail.gmail.com>
	<4430BC59.7040006@p0f.net>
	<a97c77030604031129m6e014adcu52df9eee26510ea1@mail.gmail.com>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Rajesh Kumar Mallah wrote:
|>| Do you see any problem in the current approach ?
|>|  i have seen it working fine till now.
|>
|>I do, to be honest. The WAL location counter accounts for 4294967295
|>positions and while I'm certain that's WAY more than the average number
|>of transactions that go into a WAL, quite a number of small ones can
|>certainly happen before a WAL is rolled over, and until then, you're
|>dealing with the same log file.
|>
|>If two backups happen in that period of time for whatever reason, you're
|>going to have a false positive by looking into ${WAL_ARCHIVE} and
|>searching just for the WAL name, so including the location in the search
|>of a WAL fragment is certainly necessary. Infact, going purely by
|>chance, the probability of hitting the same location in two different
|>log files in two subsequent backups is much lower than hitting the same
|>WAL twice.
|
| The current wal log is not being removed from the wal archive area
| in any case. The files less than the current ones are being rm'ed.
|
| I am sorry i am not able to get your apprehension. But i shall
| surely try harder to understand your point.

Hi Rajesh, list.

I'm sorry I didn't get back to you earlier, I was at an IBM business
conference for a couple of days; not to say it rendered me incapable of
communicating via e-mail, but it did bring along certain social
responsibilities which caused me to both stay up and sleep late, if you
know what I mean. :)

Let me explain the above predicament in more practical terms.

Let us say you started a backup very soon after a WAL had been rolled
over. Current WAL at that time was called, for example,
${PGDATA}/pg_xlog/000000010000000E0000000A. The location at that time
was 000F594A (iow, early in the WAL cycle). [disclaimer: all events in
this story are entirely fictional, any similarity to actual persons and
events is purely coincidental :) ]

pg_start_backup() will create a WAL backup:
~  ${PGDATA}/pg_xlog/000000010000000E0000000A.000F594A.backup

which will be archived to ${WAL_ARCHIVE} under the same name, or
possibly given a different extension, depending on archive_method. Let
us assume for the purpose of this explanation, that archive_method
consists only of cp -i </dev/null, although the problem would have been
identical if one used gzip -c, for example.

Now, this backup fails for whatever reason (rsync trouble, etc.). You
abort it and leave WAL archive as it was. You diagnose the problem that
caused the backup to fail and repeat the procedure. And since your
diagnostic skills are so good it took you almost no time to fix it, the
database engine is now at location 002D94AF in that _same_ WAL.

Once you restart the backup script, pg_start_backup() is called and
${PGDATA}/pg_xlog/000000010000000E0000000A.002D94AF.backup is created
and archived to ${WAL_ARCHIVE} under that same name.

Your method of discovering logs to delete will now match _two_ "current"
log file archives instead of one, because they both come from the same
WAL, fail to actually delete the stale one (the one from position
000F594A) and thus clutter your backup with irrelevant WAL fragments.

The second part of the second paragraph was only to expose that,
following the same logic as outlined above, if you take WAL locations as
the criterion of removing stale WAL fragments instead of WAL names, it
is far less likely to hit a false positive, because you would have to
pg_start_backup() _exactly_ 4294967296 locations after the first one.

Of course, you want to be unambiguous in your search of the perfect WAL
archive, so you want to use _both_ WAL name and location as the criterion.

| the old log files without the base backup are not useful. since
| rsync is being used to optimise the copying by overwriting the
| base backup everytime, i dont thing preserving the old files
| makes sense. Had it been and non overwritng backup the files
| would have made sense.

I see. I was assuming you used rsync to copy the database cluster
somewhere then tar it there, while it was lying still ("Fell, Destroyed"
of Fugazi comes to mind :) ).

I will get back to you with the review of your script later. A quick
scan reveals there is not much left to be improved, though.

Kind regards,
- --
~    Grega Bremec
~    gregab at p0f dot net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFEN68Vfu4IwuB3+XoRAxbjAJ49Hg6d9J0RZLywEZLr3WeD1sHevQCfSOm8
Pr3jEYb/+viTp/OAoJ6a5/M=
=9e0i
-----END PGP SIGNATURE-----



view thread (36+ messages)  latest in thread

Message-ID: <4437AF15.3020707@p0f.net>
Permalink:  ../4437AF15.3020707@p0f.net/
Also on:    postgresql.org/message-id/4437AF15.3020707@p0f.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gregab@p0f.net, mallah.rajesh@gmail.com
  Subject: Re: FW: Setting up of PITR system.
  In-Reply-To: <4437AF15.3020707@p0f.net>
  References: <!&!AAAAAAAAAAAuAAAAAAAAALfqleqaijxJlxu+E5RYF+YBAJaQ0jfg6zBFp7poaER6UCkAAAGy3PcAABAAAAB0heSWPqq+QLrrk5Lik5sLAQAAAAA=@mailnetwork.co.uk> <a97c77030603290445o29f96099xd3cc5f15cd4fb364@mail.gmail.com> <442B93DD.4020305@p0f.net> <a97c77030603312023k7bdc705eif22af429dc3c943d@mail.gmail.com> <442E1945.4030006@p0f.net> <a97c77030604010258j657622fcj6a42a10c8c38f344@mail.gmail.com> <442FDC7F.9010001@p0f.net> <a97c77030604020914n38b3e3bco2138b9673f25b176@mail.gmail.com> <4430BC59.7040006@p0f.net> <a97c77030604031129m6e014adcu52df9eee26510ea1@mail.gmail.com> <4437AF15.3020707@p0f.net>

* 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