After going through the basic guidelines to hunt down the most common issues that you will face in your database, the upcoming sections will discuss some of the most common error scenarios that occur in the PostgreSQL world.
Facing clog corruption
PostgreSQL has a thing called the commit log (now called pg_xact; it was formally known as pg_clog). It tracks the state of every transaction on the system and helps PostgreSQL determine whether a row can be seen. In general, a transaction can be in four states:
#define TRANSACTION_STATUS_IN_PROGRESS 0x00
#define TRANSACTION_STATUS_COMMITTED 0x01
#define TRANSACTION_STATUS_ABORTED 0x02
#define TRANSACTION_STATUS_SUB_COMMITTED 0x03
The clog has a separate directory in the PostgreSQL database instance (pg_xact).
In the past, people have reported something called clog corruption, which can be caused by faulty disks or bugs in PostgreSQL that have been fixed over the years. A corrupted commit...