X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EC83B9FA4C8 for ; Thu, 7 Sep 2006 17:03:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 97983-06 for ; Thu, 7 Sep 2006 17:02:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.226]) by postgresql.org (Postfix) with ESMTP id 5DF249FA217 for ; Thu, 7 Sep 2006 17:02:36 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so362197wxc for ; Thu, 07 Sep 2006 13:02:35 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Ir+f8vCONhaQq2xiJThFgEpa9QXG+Dz6xVizBvU3/p9pMOBqCgk+iXZ2fH/vE0TlWrz3VhJ9RRaSjZ9wK6Ot94J3H2z9wNkWJ2sc1FGNFaP/B92urDFIVSEnGGqdKBMe3FBGIpfWbBEwOi0cEH/kOrYFgOfPWUYkjgo1gWZS3kI= Received: by 10.70.70.14 with SMTP id s14mr1299119wxa; Thu, 07 Sep 2006 13:02:34 -0700 (PDT) Received: by 10.70.73.10 with HTTP; Thu, 7 Sep 2006 13:02:34 -0700 (PDT) Message-ID: <1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com> Date: Thu, 7 Sep 2006 16:02:34 -0400 From: "Chris Hoover" To: "Tom Lane" Subject: Re: Why so long between archive calls? Cc: "Alvaro Herrera" , pgsql-admin@postgresql.org In-Reply-To: <24338.1157576070@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_207654_28920016.1157659354442" References: <1d219a6f0608290835u6647a8e1g437dfd4cedb81658@mail.gmail.com> <24247.1157059093@sss.pgh.pa.us> <1d219a6f0609011230t2407cba5u53a5e536ad4fbc20@mail.gmail.com> <25848.1157153334@sss.pgh.pa.us> <20060902014010.GA14288@alvh.no-ip.org> <26777.1157161705@sss.pgh.pa.us> <1d219a6f0609051333t70e1207eife3d0ad7a55a2e07@mail.gmail.com> <23808.1157572848@sss.pgh.pa.us> <1d219a6f0609061313q5f94ab54l9e84211e8b43c8fd@mail.gmail.com> <24338.1157576070@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.775 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/69 X-Sequence-Number: 22983 ------=_Part_207654_28920016.1157659354442 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 " 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 wrote: > > "Chris Hoover" 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 > ------=_Part_207654_28920016.1157659354442 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@sss.pgh.pa.us> wrote:
"Chris Hoover" <revoohc@gmail.com> 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

------=_Part_207654_28920016.1157659354442--