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

"Chris Hoover" <revoohc(at)sermonaudio(dot)com> 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