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 DBB6A3A1AFE for ; Fri, 14 Jan 2005 16:45:32 +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 87805-09 for ; Fri, 14 Jan 2005 16:45:22 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 18A133A1AF9 for ; Fri, 14 Jan 2005 16:45:21 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0EGjKdo024252; Fri, 14 Jan 2005 11:45:20 -0500 (EST) To: "Chris Hoover" Cc: pgsql-admin@postgresql.org Subject: Re: Cron DB Bounce causes index problems && Help rotating logs In-reply-to: References: Comments: In-reply-to "Chris Hoover" message dated "Fri, 14 Jan 2005 15:16:52" Date: Fri, 14 Jan 2005 11:45:20 -0500 Message-ID: <24251.1105721120@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/148 X-Sequence-Number: 16007 "Chris Hoover" writes: > We are having an issue here trying to automate our db log rotations. What PG version is this? On what platform? > 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] ....") Are you sure it was overwritten, and not just that pieces of different messages were interspersed? Even that seems a bit unlikely unless the messages exceeded 4K apiece, which your example certainly doesn't. > 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. That sounds like a corrupted index --- time to do a REINDEX. I strongly doubt that this has anything to do with the stderr log though. > Is there a better way to rotate the logs w/o bouncing the db every night? Piping into rotatelogs is really the best bet. (In 8.0 there is a built in equivalent of this method.) I would recommend trying to find out why that's not behaving right for you, rather than abandoning it at the first sign of trouble. In general, though, I haven't got a lot of confidence in the idea of using log_statement as a backup technique. There's no guarantee that the log entries will appear in exactly the order in which they effectively executed, and even without that issue, pulling apart interspersed separate transactions seems a bit painful. regards, tom lane