Message-ID: <4E794FB5.80400@2ndQuadrant.com>
Date: Tue, 20 Sep 2011 22:45:09 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-docs@postgresql.org
Subject: Re: somewhat wrong archive_command example
References: <1316524671.9044.12.camel@fsopti579.F-Secure.com>
In-Reply-To: <1316524671.9044.12.camel@fsopti579.F-Secure.com>
Content-Type: multipart/mixed; boundary="------------090300060905000101080106"

This is a multi-part message in MIME format.
--------------090300060905000101080106
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 09/20/2011 09:17 AM, Peter Eisentraut wrote:
> At
> http://www.postgresql.org/docs/current/static/continuous-archiving.html
> we say
>
> """
> Many people choose to use scripts to define their archive_command, so
> that their postgresql.conf entry looks very simple:
>
> archive_command = 'local_backup_script.sh'
> """
>
> It seems to me, however, that even a simple archive_command like that
> ought to contain at least %p, right?
>    

Yes, you have to pass %p, and even though you can derive it from there 
it's easier for most if they get %f too.

That whole section is really problematic.  It says "Any messages written 
to stderr from the script will appear in the database server log", but 
on some platforms things printed to stdout appear there too.  And 
without an example, it really doesn't make a good case for why a 
separate script can be useful in all cases.  I've lost count of how many 
people I've seen hang themselves with impossible to giant single line 
archive_command setups, following the examples that are given; those 
blow up quite badly when things go wrong.

Attached is a working local_backup_script.sh that does the same basic 
thing as the "Standalone Hot Backups" example.  It includes lots of 
error checking, useful messages when it doesn't work like this:

Archive directory does not exist
LOG:  archive command failed with exit code 1
DETAIL:  The failed archive command was: ./local_backup_script.sh 
pg_xlog/00000001000000010000007D 00000001000000010000007D

And it takes advantage of the fact that scripts will be executed in 
$PGDATA, by putting all the paths it uses relative to it.

If I could get some general agreement this is a good direction to wander 
in, I'd be happy to wrap this into a full doc patch and submit it.  I've 
been meaning to do this for a while and just never got around to it.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--------------090300060905000101080106
Content-Type: application/x-sh;
 name="local_backup_script.sh"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="local_backup_script.sh"

#!/bin/bash

# archive_command script for Standalone Hot Backup
#
# Assumes that your backup_in_progress trigger file
# is located at a subdirectory below the database
# directory, along with an archive/ subdirectory
# to hold WAL files.

PATH="$1"
FILE="$2"
ARCHIVE="../archive"

if [ ! -f ../backup_in_progress ]; then
  exit 0
fi

if [ -f ${ARCHIVE}/${FILE} ] ; then
  echo Archive file ${FILE} already exists in archive, skipping >&2
  exit 0
fi

if [ ! -d ${ARCHIVE} ] ; then
  echo Archive directory does not exist >&2
  exit 1
fi

/bin/cp ${PATH} ${ARCHIVE}/${FILE}
if [ $? -ne 0 ] ; then
  echo $0 Archive copy of ${FILE} failed with error $? >&2
  exit 1
fi

exit 0

--------------090300060905000101080106--