Skip site navigation (1) Skip section navigation (2)

Re: Cron DB Bounce causes index problems && Help rotating logs

From: Richard_D_Levine(at)raytheon(dot)com
To: "Chris Hoover" <revoohc(at)sermonaudio(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-admin-owner(at)postgresql(dot)org
Subject: Re: Cron DB Bounce causes index problems && Help rotating logs
Date: 2005-01-14 16:22:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Hi Chris,

In the I would replace the first sleep 150 with a call to
fuser (in a sleeping loop) to make sure there is no file access to the log
file.  I don't understand the second sleep 150.


                      "Chris Hoover"                                                                                                       
                      <revoohc(at)sermonaudio(dot)        To:       <pgsql-admin(at)postgresql(dot)org>                                                  
                      com>                         cc:                                                                                     
                      Sent by:                     Subject:  [ADMIN] Cron DB Bounce causes index problems && Help rotating logs            
                      01/14/2005 10:16 AM                                                                                                  

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.



Here is the cron line:
01 20 * * * /pgsql/tmp/bin/ 50001

Here is the script:

source ~postgres/etc/dba.profile



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 ]
  echo "POSTGRES DID NOT STOP" | mail -s "`hostname` stop fail" $DBAPAGER

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 ]
  echo "POSTGRES DID NOT START" | mail -s "`hostname` start fail" $DBAPAGER

echo "`date` - `hostname` - log rotation finished" | mail -s "`hostname`
rotation" $DBAPAGER

Here is the dba.profile file:
DBAPAGER=<my pager number>





---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

pgsql-admin by date

Next:From: Tom LaneDate: 2005-01-14 16:45:20
Subject: Re: Cron DB Bounce causes index problems && Help rotating logs
Previous:From: Goulet, DickDate: 2005-01-14 16:16:45
Subject: Re: Installing PostgreSQL as "postgress" versus "root"

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group