Thread: Best compressed archive_command on Linux?

I'm in the process of moving an 8.2 server from Win32 to Ubuntu Linux.
On Win32, I implemented WAL archiving with the archive_command
"rar a -ep -m5 -s -t -dh w:/%f.rar %p"

Anyway, I'm going to use WAL archiving on the new server, and need the
files to be compressed. My first inclination is to use
"tar -czf /walarchive/%f.tar.gz %p > /dev/null"

Before I go with that, though, I was wondering if anybody had any
specific experience / advice with a better command, reasons why this
command might fail zero or succeed nonzero, or additional/different
options for tar that would yield better results.

Thanks,
Sean

I am running my system with the logs going into a dated directory.  Then each night, I have a cron job that tars up the previous days directory.

Here is my script for the archive_command:
#!/bin/bash
echo "start" >> ~/archive_log_timings
date >> ~/archive_log_timings

source /home/postgres/etc/postgres.profile

## setup variables
archiveFile=$1                  ## full path with filename to be archived
archiveFileName=$2              ## the file name to be archived
serverPort=$3                   ## port the server is running on
serverName=`/bin/hostname`      ## the name of this machine
returnValue=0                   ## Set the return code to success ( 1 for failur
e)
DATE=`/bin/date +%Y%m%d`
fullArchiveDir=`echo ${archiveBaseDir}/${serverName}/${serverPort}/pg_xlog_arch/
${DATE}`

## make sure the mount point is available
if [ ! -d $archiveBaseDir ]
then
  returnValue=1
  echo "`/bin/hostname` missing directory $archiveBaseDir" | mail -s "URGENT DB
ISSUE" $DBAPAGER
  sleep 300
else
  ## make sure the proper directory tree exists.  If not build it.
  mkdir -p $fullArchiveDir

  ## get an md5sum of the original file
  originalMD5=`md5sum $archiveFile| awk '{print $1}'`
 
  ## copy of file to the new location
  cp -i ${archiveFile} ${fullArchiveDir}/.  < /dev/null

  ## get an md5sum of the copied file
  copiedMD5=`md5sum ${fullArchiveDir}/$archiveFileName | awk '{print $1}'`

  ## see if the sums match
  if [ "$originalMD5" = "$copiedMD5" ]
  then

    ## remove the file from the current archive save directory
    currentArchiveDir=`echo ${archiveBaseDir}/${serverName}/${serverPort}/curren
t_log`
    rm ${currentArchiveDir}/$archiveFileName ##2>/dev/null

    ###bzip2 ${fullArchiveDir}/$archiveFileName
    returnValue=0
  else
    returnValue=1
    echo "`/bin/hostname` - LOG ARCHIVE FAILED - `date`" | mail -s "URGENT DB IS
SUE" $DBAPAGER
    sleep 300
  fi
fi
echo "done" >> ~/archive_log_timings
date >> ~/archive_log_timings
exit $returnValue
----

Here is my tar command in cron:
cd /postgresql/<port>/pg_xlog_arch/archive/<hostname>/<port>/pg_xlog_arch/
/usr/bin/find . -type d -mtime +1 |
        /bin/sort |
        /bin/sed 's/\.\///' |
        while read DIR
                do
                        /bin/tar -cvzf ${DIR}.tar.gz $DIR
                        /bin/rm -r $DIR
        done

I think the tar script could use some further refinement, but for now it works.

Chris

sometimes tar  (gnu tar) exited with nonzero status for no reason on my
system... no error msg or anything. I recommend something called star.

On Wednesday 11 April 2007, Sean Murphy wrote:
> I'm in the process of moving an 8.2 server from Win32 to Ubuntu Linux.
> On Win32, I implemented WAL archiving with the archive_command
> "rar a -ep -m5 -s -t -dh w:/%f.rar %p"
>
> Anyway, I'm going to use WAL archiving on the new server, and need the
> files to be compressed. My first inclination is to use
> "tar -czf /walarchive/%f.tar.gz %p > /dev/null"
>
> Before I go with that, though, I was wondering if anybody had any
> specific experience / advice with a better command, reasons why this
> command might fail zero or succeed nonzero, or additional/different
> options for tar that would yield better results.
>
> Thanks,
> Sean
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq



--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." -- Linus
Torvalds

I experience similar on my nightly backups - tar exits with status '2' but
for the life of me I cannot work out WHAT exit 2 means. It still works fine,
but the non-zero return it just annoying.

If you're just compressing (ie, not creating a tar-type archive, just either
GZip (gzip) or BZip2 (bzip2) the WAL files.

BZip2 has incredible compression, but at the expense of speed of course.

Cheers,
~p

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Eduardo J. Ortega
Sent: Thursday, 12 April 2007 02:50
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Best compressed archive_command on Linux?

sometimes tar  (gnu tar) exited with nonzero status for no reason on my
system... no error msg or anything. I recommend something called star.

On Wednesday 11 April 2007, Sean Murphy wrote:
> I'm in the process of moving an 8.2 server from Win32 to Ubuntu Linux.
> On Win32, I implemented WAL archiving with the archive_command
> "rar a -ep -m5 -s -t -dh w:/%f.rar %p"
>
> Anyway, I'm going to use WAL archiving on the new server, and need the
> files to be compressed. My first inclination is to use
> "tar -czf /walarchive/%f.tar.gz %p > /dev/null"
>
> Before I go with that, though, I was wondering if anybody had any
> specific experience / advice with a better command, reasons why this
> command might fail zero or succeed nonzero, or additional/different
> options for tar that would yield better results.
>
> Thanks,
> Sean
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq



--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." --
Linus
Torvalds

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments