Re: FW: Setting up of PITR system.

-----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-----