PostgreSQL uses recovery mechanisms to ensure data consistency in the event of a crash or unexpected shutdown. Let’s look at each component of recovery and crash-consistency mechanisms in PostgreSQL in detail:
## Checkpointing Checkpoints are periodic points in time where PostgreSQL synchronously writes data from memory to disk. This process helps in preventing the accumulation of a lot of changes in memory that could cause a significant delay for recovery operation and crash. Checkpoints allow engines like ‘pg_ctl‘ command to know that they are "caught up" with disk writes by ensuring that the transactions ‘commit‘ before the checkpoint start have been written to disk.
A checkpoint is performed using the ‘CHECKPOINT‘ command. PostgreSQL can also perform auto-checkpoints through its ‘checkpoint_timeout‘ and ‘checkpoint_completion_target‘ configurations.
## Redo Redo, also known as Write-ahead logging (WAL), logs changes to the database before alterations are made permanent, a process known as a ‘commit‘. The log is written to the WAL on the disk, and it is replayed on the system when the engine restarts, which enables the undo process to work even when the page has been flushed.
During database recovery, PostgreSQL replays the WAL to ensure completeness because if it comes across an uncommitted transaction that had been updated in PostgreSQL memory, it writes the values of those changes, which avoids redoing transactions that had already been completed or that crashed the database instance.
To reduce the overhead of redo operations, PostgreSQL can be configured to use background writer paces flushing into the background across group groups. You can also configure the checkpoint and WAL parameters to balance the performance and the recovery time.
## Undo The Undo process works hand-in-hand with the Redo process in PostgreSQL. It is responsible for reversing the database’s data changes by keeping track of the changes made on the system. In a situation where a database is in recovery mode, and certain transactions haven’t been committed, the Undo process is used to ensure that the database maintains consistency.
PostgreSQL’s undo mechanism takes the form of transaction IDs. Every transaction has an ID, and with this ID, PostgreSQL knows how to undo the changes made by the transaction. Transactions that didnt commit successfully and those that were started but hadnt completed are undone.
In a scenario where the transaction changes had been written to the disk, the recovery process will take the last successful checkpoint and redo all the changes completed after the checkpoint. Before redoing each item, it checks the transaction ID to verify if the changes were part of a successful transaction to avoid redoing it.
Below is an example of checkpointing implementation in Java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CheckpointExample {
public static void main(String[] args) throws SQLException {
String dbName = "mydb";
String userName = "user";
String password = "password";
String url = "jdbc:postgresql://localhost:5432/" + dbName;
Connection conn = DriverManager.getConnection(url, userName, password);
conn.createStatement().execute("CHECKPOINT");
conn.close();
}
}In this example, we create an instance of a ‘Connection‘ object to PostgreSQL, execute a ‘CHECKPOINT‘, and then close down the connection. This implementation is equivalent to calling ‘CHECKPOINT‘ command in the postgres command line or ‘pg_ctl‘.