Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > As I understand it, the log space accumulates for the oldest transaction
> > which is still running, and all transactions which started after it.
>
> No, pg_xlog can be truncated as soon as a checkpoint occurs. If Jeremy
> wasn't using archive_command then the only possible explanation for
> bloated pg_xlog is that checkpoints were failing. Which is not unlikely
> if the *data* partition runs out of space. Were there gripes in the log
> before the system crash? The scenario we've seen in the past is
>
> * data partition out of space, so writes fail
> * each time Postgres attempts a checkpoint, writes fail, so the
> checkpoint fails. No data loss at this point, the dirty buffers
> just stay in memory.
> * pg_xlog bloats because we can't truncate away old segments
> * eventually pg_xlog runs out of space, at which point we PANIC
> and can't continue running the database
>
> Once you free some space on the data partition and restart, you should
> be good to go --- there will be no loss of committed transactions, since
> all the operations are in pg_xlog. Might take a little while to replay
> all that log though :-(
Amazing that all works. What I did not see is confirmation from the
user that the data directory filled up _before_ pg_xlog filled up.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +