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: Sun, 02 Apr 2006 16:15:27 +0200
Message-ID: <442FDC7F.9010001@p0f.net> (raw)
In-Reply-To: <a97c77030604010258j657622fcj6a42a10c8c38f344@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>

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

Rajesh Kumar Mallah wrote:
|
| Instead of taking a round about method i am using the content of the
| file, (This was also suggested by Andy at somepoint)

After reading the docs again, that's what I would do as well, indeed. :)

| lately i feel that we should not be discussing the topic over here
| becoz it has less to do with postgresql and more of bash.

I've been considering that seriously in the very first post I wrote, but
since there seems to be a lot of people interested in a working,
flexible WAL archiving script, I decided to keep it on the list. It is
an administration issue, afterall. I will stand corrected if someone
feels we're clogging their mailboxes.

| ##############################################################
| #START WAL LOCATION: E/A9145E4 (file 000000010000000E0000000A)
| #CHECKPOINT LOCATION: E/A92939C
| #START TIME: 2006-04-01 14:36:48 IST
| #LABEL: base_backup_01-04-2006-14-36-45
| ###############################################################
|
| BACKUP_LABEL=$DATADIR/backup_label
| # get the like containing line START WAL LOCATION
|
| START_LINE=`grep -i  "START WAL LOCATION"  $BACKUP_LABEL`
| # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begin.
| START_LINE=${START_LINE/#START*file /}
| # strip ')' from end.
| START_LINE=${START_LINE/%)/}
| # REF_FILE_NUM is something like 000000010000000A00000068
| REF_FILE_NUM=$START_LINE

Why not go for the entire filename? Record offset is never going to be
more than eight characters, as include/access/xlogdefs.h states:

~  typedef struct XLogRecPtr
~  {
~      uint32    xlogid;  /* log file #, 0 based */
~      uint32    xrecoff; /* byte offset of location in log file */
~  } XLogRecPtr;

A 32 bit unsigned integer can always be represented in eight hexadecimal
digits or less.

~  REF_FILE="`grep 'START WAL LOCATION' ${BACKUP_LABEL} | \
~    awk '{
~            sub(/)/, "", $6);
~            sub(/[0-9A-F]\//, "", $4);
~            printf("%s.%08s.backup", $6, $4);
~         }'`"

This will remove the trailing paren from WAL filename (field 6), the
leading xlogid and the slash from WAL location (field 4) and compose
them into the full filename, zero-padding WAL location to eight
characters and giving back something like this:

~    000000010000000E0000000A.0A9145E4.backup

What you need to do now is just appendd a glob (if your archive_method
consists of gzip/bzip2/...) and prepend ${WAL_ARCHIVE}:

~  REF_FILE="${WAL_ARCHIVE}/${REF_FILE}*"

| ~  RM_LIST=""
| ~  find ${WAL_ARCHIVE} -type f | sort -g | while read archive; do
| ~    if [ ! "${archive}" = "${REF_FILE}" ]; then
| ~      RM_LIST="${RM_LIST:+${RM_LIST} }${archive}"
| ~    else
| ~      break
| ~    fi
| ~  done
| ~  rm -f ${RM_LIST}
| i think you meant < instead of '=' in above [comparison].

Absolutely not. :) What we're doing here is we're looking at all files
in ${WAL_ARCHIVE} (find), sorting them according to their general
numeric value (sort, lowest first) and adding them one-by-one to the
list of WALs to remove (RM_LIST assignment) until we find REF_FILE (the
"equals not" comparison). As soon as we find REF_FILE, we escape the
while loop (break) and remove all the old log files (rm -f).

Since WALs are numbered in a sequence, and location identifiers in a WAL
which are also a part of the filename are sequential too, sorting will
always produce a list of WAL segments in chronological order, oldest
first, newest last.

What is critical to the above piece of code is that BOTH ${archive} and
${REF_FILE} are either absolute filenames or relative ones, of course,
otherwise they'll never match.

| regarding
|   $ env LC_ALL="C" backup_script.sh

If you do it inside the script, you shoud definitely export it to
subshells since all the backtick commands execute in a subshell. Using
"sort -g" to sort the listing according to general numeric value is the
safest option though, and it is also the least disruptive one as it
doesn't require any changes to the environment.

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

iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY
4xAxFb3Ncd8RHWkBbgyag7U=
=7MXQ
-----END PGP SIGNATURE-----



view thread (36+ messages)  latest in thread

Message-ID: <442FDC7F.9010001@p0f.net>
Permalink:  ../442FDC7F.9010001@p0f.net/
Also on:    postgresql.org/message-id/442FDC7F.9010001@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: <442FDC7F.9010001@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>

* 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