1

Firstly, please forgive my poor English.

The key word, "pg_clog" is not mentioned in the chapter "Continuous Archiving and Point-in-Time Recovery (PITR)" of postgresql 9.x documents. But as I learned from the book, Database System Implementation, commit logs are necessites for a database to recover from a failure or from a backup, because they help the database to be restored to consistency.

Is it because the commit records are also written in other files? For example, WALs?

Thank you.

xuange
  • 57
  • 1
  • 10

1 Answers1

3

The commit log contains the information whether a particular transaction was committed or rolled back.

When rows are written to a table, it is not yet clear if the transaction will succeed or not. Rather that storing that information in each row version (“tuple”) when the transaction is committed or restored, PostgreSQL only marks the state of the transaction in the commit log.

So it is necessary to have the commit log to determine if a certain tuple is visible or not; without it the database is unusable.

The commit log is not specifically mentioned in the recovery documentation, because it is part of the data directory and is written to disk at checkpoint time.

For recovery, it is not necessary to have commit log information beyond the checkpoint from which you recover: since COMMIT and ROLLBACK are recorded in the transaction log (“WAL”), all the information is there and will be written to the commit log during recovery.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Interesting. If the same information is available in the transaction log, then why is the commit log needed? Is that a performance optimization because searching the WAL archives would be less efficient? –  Jan 29 '18 at 11:22
  • Of course it would be very inefficient to extract this information from the WAL. But it is also impossible, since the WAL files are not available to the server any more once they have been archived and recycled. – Laurenz Albe Jan 29 '18 at 11:26