pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Hoover <revoohc@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Why so long between archive calls?
Date: Thu, 7 Sep 2006 16:02:34 -0400
Message-ID: <1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com> (raw)
In-Reply-To: <24338.1157576070@sss.pgh.pa.us>
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>

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
>

view thread (16+ messages)  latest in thread

Message-ID: <1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com>
Permalink:  ../1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com/
Also on:    postgresql.org/message-id/1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: revoohc@gmail.com, tgl@sss.pgh.pa.us, alvherre@commandprompt.com
  Subject: Re: Why so long between archive calls?
  In-Reply-To: <1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com>
  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> <1d219a6f0609071302yabbebd0rcc018cbff3d2858e@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox