Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8030A9FB688 for ; Wed, 11 Apr 2007 18:02:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09656-01 for ; Wed, 11 Apr 2007 18:02:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.251]) by postgresql.org (Postfix) with ESMTP id 8E5F79FA5F7 for ; Wed, 11 Apr 2007 18:02:13 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so365675ana for ; Wed, 11 Apr 2007 14:02:12 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=PBBjGKTN7zikDMSdsqfS7q4G1B6cgdPA5Th1+UDNm6C7qnVlptFS+FVLSzAonAPgl+UzJN4lie2lcTm2Wq8EwIXZXnmK7FyNMBXxEN5oMbn65IEI3vlRHq1gDyjbu/nDZX6igltY9zRwekRCGfnTNOqNjmYr6yI3Hn8CuD27Jws= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=TmdTV4DJGaTpy06xyKqGXqQ9cbmDasnTMVxwH1mVSQCrmu7ZjWubI0jf3cHQdn6h3Hs8FL/O+2mok8NGzOGuSRx/1CBDCjp+Ba3X3uB1EKReLQnerJkpq1/IjR/v0Ookfoqvdno4rGMR5XfCOVZumE86h2HxCVAzlQdS59DcyoE= Received: by 10.100.143.1 with SMTP id q1mr810402and.1176325332230; Wed, 11 Apr 2007 14:02:12 -0700 (PDT) Received: by 10.100.165.19 with HTTP; Wed, 11 Apr 2007 14:02:12 -0700 (PDT) Message-ID: <1d219a6f0704111402x5c1efa92j109d1d155986acb2@mail.gmail.com> Date: Wed, 11 Apr 2007 17:02:12 -0400 From: "Chris Hoover" To: "Sean Murphy" Subject: Re: Best compressed archive_command on Linux? Cc: pgsql-admin@postgresql.org In-Reply-To: <461D0C11.4030400@equipoint.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11959_7188067.1176325332171" References: <461D0C11.4030400@equipoint.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/103 X-Sequence-Number: 25167 ------=_Part_11959_7188067.1176325332171 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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//pg_xlog_arch/archive///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 On 4/11/07, 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 > ------=_Part_11959_7188067.1176325332171 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

On 4/11/07, Sean Murphy < Sean.Murphy@equipoint.com> 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

------=_Part_11959_7188067.1176325332171--