From: Chris Hoover <revoohc@sermonaudio.com>
To: pgsql-admin@postgresql.org
Subject: Cron DB Bounce causes index problems && Help rotating logs
Date: Fri, 14 Jan 2005 15:16:52 (GMT)
Message-ID: <MAILSERVERZ9rWjAKYV00000271@mail.astudios.com> (raw)
Hi all,
We are having an issue here trying to automate our db log rotations. We were using the Apache rotatelogs program and we thought it was working fine. However, when we had a server crash and had to recover and roll forward using the postgresql logs (we are logging all sql), we discovered that some of the sql records in the log were being over written. This has caused us to go back to just having postgresql log directly to the drives. (The problem with rotatelogs looked like this "[2005-01-14 08:00:01] [123] LOG: query: select * fr[2005-01-14 08:00:01] ....")
Anyway, we are now manually rotating the logs. We tried to rotate the logs via a simple script and cron, but we ended up causing strange index issues. The data would be in the tables and would show if a table scan was forced. But many tables would not show the data if an index scan (esp. if the primary key was used) was used. We also had issues with system allowing records to be inserted that violated the primary key.
Manually logging into the servers and running the same script from the shell prompt works fine and does not cause any index issues.
Any ideas on what is going on here? We do not have any postgresql related environmental variables set in the shell. Do we need to execute a db bounce via cron with a "special" call from cron?
Also, has anyone else seen the rotatelogs issue?
Is there a better way to rotate the logs w/o bouncing the db every night? Due to recovery requirements, we must have the sql logged to the postgresql log. These logs grow to between 2 and 5 GB every day. Running for more than 2 days without rotating the logs tends to cause a noticable slowdown to our users.
Any help would be appreciated.
Chris
P.S.
Here is the cron line:
01 20 * * * /pgsql/tmp/bin/rotate_logs.sh 50001
Here is the rotate_log.sh script:
source ~postgres/etc/dba.profile
PORT=$1
LOGPATH=${LOG_PATH}/${PORT}/pg_log
echo "`date` - `hostname` - log rotation started" | mail -s "`hostname` rotation" $DBAPAGER
## shutdown the database
${POSTGRES_BIN}/pg_ctl -D ${DB_PATH}/${PORT} -m fast stop > ~postgres/db_${PORT}_stop.log 2>&1
STOPPED=`cat ~postgres/db_${PORT}_stop.log | grep "postmaster successfully shut down" | wc -l`
if [ $STOPPED != 1 ]
then
echo "POSTGRES DID NOT STOP" | mail -s "`hostname` stop fail" $DBAPAGER
exit
fi
sleep 150
mv $LOGPATH/postgres.log $LOGPATH/postgres.log.`date +%m%d%y%H%M`
sleep 150
${POSTGRES_BIN}/pg_ctl -D ${DB_PATH}/${PORT} -l ${LOGPATH}/postgres.log start > ~postgres/db_${PORT}_start.log 2>&1
STARTED=`grep "successfully start" ~postgres/db_${PORT}_start.log | wc -l `
if [ $STARTED != 1 ]
then
echo "POSTGRES DID NOT START" | mail -s "`hostname` start fail" $DBAPAGER
fi
echo "`date` - `hostname` - log rotation finished" | mail -s "`hostname` rotation" $DBAPAGER
Here is the dba.profile file:
DBAPAGER=<my pager number>
POSTGRES=/pgsql/
POSTGRES_BIN=${POSTGRES}/bin
DB_PATH=/data
LOG_PATH=/data
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@sermonaudio.com
Subject: Re: Cron DB Bounce causes index problems && Help rotating logs
In-Reply-To: <MAILSERVERZ9rWjAKYV00000271@mail.astudios.com>
References: <MAILSERVERZ9rWjAKYV00000271@mail.astudios.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