X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F9F73A1A56 for ; Fri, 14 Jan 2005 15:16:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77895-02 for ; Fri, 14 Jan 2005 15:16:44 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 4A0513A18A7 for ; Fri, 14 Jan 2005 15:16:45 +0000 (GMT) thread-index: AcT6TCyZedzgwkakSROzn0hVQcqp/g== Received: from webserver ([10.0.0.10]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Fri, 14 Jan 2005 10:17:34 -0500 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Content-Class: urn:content-classes:message Importance: normal From: "Chris Hoover" To: Date: Fri, 14 Jan 2005 15:16:52 (GMT) X-MSMail-Priority: Normal X-Mailer: AspMail 4.0 4.03 (SMT492C4AF) Subject: Cron DB Bounce causes index problems && Help rotating logs MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message-ID: X-OriginalArrivalTime: 14 Jan 2005 15:17:34.0328 (UTC) FILETIME=[2C710780:01C4FA4C] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.235 tagged_above=0 required=5 tests=AWL, INVALID_DATE X-Spam-Level: X-Archive-Number: 200501/145 X-Sequence-Number: 16004 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=3D$1 LOGPATH=3D${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=3D`cat ~postgres/db_${PORT}_stop.log | grep "postmaster = successfully shut down" | wc -l` if [ $STOPPED !=3D 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=3D`grep "successfully start" ~postgres/db_${PORT}_start.log | wc = -l ` if [ $STARTED !=3D 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=3D POSTGRES=3D/pgsql/ POSTGRES_BIN=3D${POSTGRES}/bin DB_PATH=3D/data LOG_PATH=3D/data