Re: Why so long between archive calls?

Tom,

I am able to tell how far behind the archiving is running since every minute, I copy the current archive_logs to a backup directory.  This is allowing me to be able to do an up to the minute pitr if required.  The archive_command removes the file from this backup dir when the file is archived by PostgreSQL.

Here my script for that:
#!/bin/bash

## archive the current log file every minute to make sure we can recover within 1 minute

###set -vx

source ~postgres/etc/postgres.profile

date >~/log
if [ -z $1 ]
then

  echo "archive_current_pg_xlog.sh <port>"
  exit

fi

if [ -f /tmp/archive_log ]
then

  echo "archive is already running - exiting"
  echo "`/bin/hostname` - archive is running long" | mail -s "`/bin/hostname` problem" $DBAPAGER $DBAMAIL
  exit
fi

touch /tmp/archive_log

serverPort=$1
serverName=`/bin/hostname`

currentArchiveDir=`echo ${archiveBaseDir}/${serverName}/${serverPort}/current_log`

echo "currentArchiveDir: "$currentArchiveDir >> ~/log
mkdir -p $currentArchiveDir

find $DB_PATH/50001/pg_xlog/ -cmin 1 -type f | grep -v "archive_status" > ~/cp_list
ls $DB_PATH/50001/pg_xlog/archive_status/*.done | while read FILE
do
  FILE2=`echo $FILE|sed 's/\.done//'`
  cat ~/cp_list | grep -v $FILE2 > ~/cp_list2
  mv ~/cp_list2 ~/cp_list
  rm $currentArchiveDir/$FILE2 2>/dev/null
done

cat ~/cp_list | while read FILE
do

  echo "archiving $FILE" >> ~/log
  cp $FILE ${currentArchiveDir}/.

done

find ${archiveBaseDir}/${serverName}/${serverPort}/pg_xlog_arch/ -type f -mmin -5 |
  sed 's/.*pg_xlog_arch\///' |
  while read FILE
    do
      rm -v ${currentArchiveDir}/$FILE >> log 2>/dev/null
    done

rm /tmp/archive_log

date >> ~/log

--------------------

Here is my postgresql.conf

listen_addresses = '*'
port = 50001
max_connections = 256
shared_buffers = 32768 
max_prepared_transactions = 256        
work_mem = 4096                
maintenance_work_mem = 1048576         
max_fsm_pages = 8000000                
max_fsm_relations = 10000              
vacuum_cost_delay = 1000                       
vacuum_cost_page_hit = 1               
vacuum_cost_page_miss = 10             
vacuum_cost_page_dirty = 20            
vacuum_cost_limit = 8000                       
wal_buffers = 64                       
checkpoint_segments = 256              
checkpoint_timeout = 1800              
archive_command = '/home/postgres/bin/archive_pg

Chris