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
>
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