Re: Why so long between archive calls?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
_xlog.sh %p %f 50001'  
effective_cache_size = 383490          
random_page_cost = 2                   
default_statistics_target = 100        
constraint_exclusion = on
redirect_stderr = on 
log_directory = 'pg_log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1048576
log_min_messages = debug2
log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%d,%p,%u,%m,%c,%l,%s,%x,%i,'
log_statement = 'all'             
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = on
autovacuum = on                
autovacuum_naptime = 60        
autovacuum_vacuum_threshold = 1000     
autovacuum_analyze_threshold = 500     
autovacuum_vacuum_scale_factor = 0.001 
autovacuum_analyze_scale_factor = 0.0005       
autovacuum_vacuum_cost_delay = -1      
autovacuum_vacuum_cost_limit = -1      
statement_timeout = 0
lc_messages = 'C'     
lc_monetary = 'C'           
lc_numeric = 'C' 
lc_time = 'C'       
add_missing_from = on


Chris

On 9/6/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Chris Hoover" <revoohc@xxxxxxxxx> writes:
> Currenty, there are no .ready files in the pg_xlog/archive_status file.

Well, that explains why the archiver thinks it has nothing to do.

> Like I mentioned, the db server was stoped and restarted on 9/5 am, but the
> oldest unarchived log file is:
> -rw-------  1 postgres postgres 16M Sep  1 04:04 0000000100000197000000F1

Are you sure that's an unarchived file, and not a file waiting to be
used later?  What's the current WAL endpoint address?  (pg_controldata
would tell you ... but your trace showed 000000010000019700000078 being
archived, which would suggest that this one is just waiting to be reused.)
Have you got a particularly large checkpoint_segments setting?

                        regards, tom lane


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux