Re: FW: Setting up of PITR system.

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

Rajesh Kumar Mallah wrote:
| Dear Grega ,
|
| Thanks for the useful tips and error spotting,
| i am incorporating some of them and testing
| the script in my server . I have concerns regarding
| some of your optimisations that makes the script less
| generic , below find my comments.
|

Hello, Rajesh,

I'm glad you found some use to the comments :).

|>|
|>| echo "Executing pg_stop_backup in server ... "
|>| $PSQL -Upostgres template1 -c "SELECT pg_stop_backup();"
|>| if [ $? -ne 0 ]
|>|         then
|>|         echo "PSQL pg_stop_backup failed"
|>|         exit 1;
|>| fi
|>| echo "pg_stop_backup done successfully
|
| I think i should improve the style here.
|

Basically, since you're not storing anything into a variable, as was the
case above, anything psql says will be written to stdout/stderr,
whichever it is that psql writes it to, so the only "nasty" thing that
could happen is that line buffering could cause the error message to
appear a line or two lower than where it actually happened. If you want
to capture psql output and manipulate it (for example, indenting it to
make it more readable using sed 's/^.*$/    &/' or similar), sure - you
can assign any output psql emits to a variable you did with
pg_start_backup and redirect stderr to stdout (2>&1). That way, you can
decide if the text is an error by checking $? and act accordingly:

~  SB="`${PSQL} ... 2>&1 | sed 's/^.*$/    &/'`"
~  if [ $? -ne 0 ]; then
~    echo "psql pg_stop_backup failed:"
~    echo "${SB}"
~    exit 1
~  fi

|>
|>(optimization) You could simplify this significantly using the test
builtin:
|>
|>~  while [ ! -e ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2 ]; do
|>~    echo "Waiting for ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2"
|>~    sleep 1
|>~  done
|>~  REF_FILE="`echo ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2`"
|
|
| I would not like to incorporate this becuase this code assumes WAL
archives
| are being externally compressed to .bz2. Can you suggest an optimized but
| generic alternative ?
|

I understand your concern. I was a bit surprised you checked for .bz2
files in your original script, but I suppose that depends on WAL archive
method in use, and I didn't want to make any wild guesses there. :)

Assuming all the WAL files you deal with will either be compressed
already or not compressed at all (that is, you are not going to catch
any of them in the middle of compression taking place), you can simply
replace ".bz2" in the above tests with a glob, "*".

Also, you can define a ${MASK} variable at one place and just reuse
that, if you find there are different archive methods on different hosts
and want to keep the script configurable.

~  WAL_MASK="${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup*"
~  ...
~  echo -n "Waiting for files masked \"${WAL_MASK}\" to appear..."
~  while [ ! -e ${WAL_MASK} ]; do
~    echo -n "."
~    sleep 1
~  done
~  echo " done."
~  REF_FILE="`echo ${WAL_MASK}`"

This does the basic trick. If there are any spaces in directory names,
make sure you store IFS into some variable, reset it to ^M (newline) and
reassign the old value after the above loop:

~  oldIFS="${IFS}"
~  IFS='
~  '
~  while ...; do
~    ...
~  done
~  IFS="${oldIFS}"
~  unset oldIFS

All this gets slightly more complicated if you drop the above
assumption, and expect some files to be caught in the middle of the
compression process, which means there will be more than one file
matching the WAL_MASK:

~  000000010000000A00000068.001D1214.backup (the original)
~  000000010000000A00000068.001D1214.backup.bz2 (partially compressed)

In this case, the test should be done using a different glob expansion
method in the while loop and using find and wc to count the number of
files found, then wait until there's only one file matching the mask left:

~  WAL_MASK="*.00${TO_SEARCH}.backup*"
~  WAL_FILE="${WAL_ARCHIVE}/${WAL_MASK}"
~  ...
~  echo -n "Waiting for files masked \"${WAL_MASK}\" to appear..."
~  # Until at least one file is found, the strings will both be equal.
~  # The echo will safely expand to more than one file, whereas the old
~  # test would complain a unary operator was expected.
~  while [ "${WAL_FILE}" = "`echo ${WAL_FILE}`" ] ||
~        [ `find ${WAL_ARCHIVE} -name "${WAL_MASK}" | wc -l` -gt 1 ]; do
~    # Are we in the middle of compression? Let user know some
~    # progress is taking place.
~    if [ `find ${WAL_ARCHIVE} -name "${WAL_MASK}" | wc -l` -gt 1 ]; then
~      echo -n "c" # for "compressing" :)
~    else
~      echo -n "."
~    fi
~    sleep 1
~  done
~  echo " done."
~  REF_FILE="`echo ${WAL_MASK}`"

The last step could be to protect yourself from waiting indefinitely
just in case something goes wrong (i.e. running out of disk space or
kernel OOM killing the compression program) by defining a maximum amount
of seconds you're willing to wait for a WAL:

~  MAX_SEC=600 # 10 minutes max
~  NSEC=0
~  while ...; do
~    ...
~    sleep 1
~    NSEC=$((NSEC + 1))
~    if [ ${NSEC} -gt ${MAX_SEC} ]; then
~      echo "error: timeout while waiting for WAL."
~      break
~    fi
~  done
~  if [ ${NSEC} -le ${MAX_SEC} ]; then
~    echo "done."
~    REF_FILE="`echo ${WAL_MASK}`"
~  else
~    echo "Bailing out (or something else appropriate)."
~    exit 1
~  fi
~  unset NSEC

Feel free to use whatever is appropriate for your environment, of
course; maybe implementing some of the above is simply more trouble than
it's worth, but I included it anyway, for the sake of completeness.

|>(optimization) Perhaps using find -newer/-anewer/-cnewer could be used
|>here to find files older than the reference file:
|>
|>~  # "-not -newer" or "\! -newer" will also return REF_FILE
|>~  # so you have to grep it out and use xargs; otherwise you
|>~  # could also use the -delete action
|>~  find ${WAL_ARCHIVE} \! -newer ${REF_FILE} -type f | \
|>~    grep -v "^${REF_FILE}$" | \
|>~    xargs rm -f
|
| Nopes , i have gone by the DOCS which tells to numerically compare the
filenames
| i do not want to assume more recently created files are numerically
more that
| later as i have not seen anything like that in the docs.

I see. I've been skipping classes, it seems. :)

| I am concerned if the comparison below used in the script
|
|>|         if [[ $FILE_NUM  < $REF_FILE_NUM ]]
|
| is correct , as it compares strings not numbers , i am assuming that
| the results will be same as numerical comparison, as all the filenames
| are padded with '0' form the left.

This is definitely true in the "C" locale, where "9" comes before "A"
and "Z" comes before "a". If you want to make sure it is so, run the
script with LC_ALL set to "C" like this:

~  $ env LC_ALL="C" backup_script.sh

However, you also have the option of using "sort -g", which compares
according to general numeric value:

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

If you use this method, make sure your bash is newer than 2.05, and make
it significantly newer just to be on the safe side, because there is a
bug in old bash2 versions that will fail to propagate variables inside
the loop back to the parent shell ("while" reading from a pipe executes
in a subshell in those versions).

Good luck with the script! :)

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

iD8DBQFELhlEfu4IwuB3+XoRA/X7AJ9N9luGEuLMlz+eUTeWHh86X0CnkwCbB0Wa
WzjVwnefz5j4a1vwWdR5UzE=
=FXy8
-----END PGP SIGNATURE-----