Understanding noteworthy error scenarios

After a basic guideline to hunt down the most common issues, this section will discuss some of the most common error scenarios happening in the PostgreSQL world.

Facing clog corruption

PostgreSQL has a thing called commit log, also called clog. It tracks the state of every transaction on the system and helps PostgreSQL determine whether a row can be seen or not. 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.

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 log is a pretty nasty thing to have because all your data is there but PostgreSQL does not know whether things...