Recovering PostgreSQL data after pg_control loss

To ensure fault tolerance, PostgreSQL DBMS, like many databases, uses a special logbook in which it keeps a history of data changes. Before writing data to the database files, the PostgreSQL server accumulates changes in RAM and writes them to a sequential log file so as not to lose them due to an unexpected power outage.

The data is written to the log before the database user receives a message about the successful application of changes. This log is called the proactive write log (Write-Ahead Log or simply WAL), and the log files are stored in the pg_xlog directory. Also, periodically PostgreSQL flushes changed accumulated data from RAM to disk. This data reconciliation process is called a checkpoint. The checkpoint is also executed every time PostgreSQL is shut down.

Information about which internal values ​​the checkpoint has completed is stored in the global / pg_control file and therefore the database must be accessible before the time of data recovery. If PostgreSQL shuts down abnormally, changes from the log files (pg_xlog) are applied to the database files, starting from the position of the last checkpoint. This process is called data recovery.

The pg_control file contains information:

• version of the format of the control file,
• checksum of data recorded in this file
• version of the database file format,
• unique identifier of the database instance
• current state: running / stopped,
• position in the log corresponding to the running and previous control points,
• current timeline (timeline)
• Maximum visible transaction number (xid)
• maximum number of internal object counter (oid),
• time of creation,
• and much more.

You can view the contents of pg_control using the pg_controldata utility:

$ pg_controldata /var/lib/pgsql/9.5/data pg_control version number: 942 Catalog version number: 201510051 Database system identifier: 6242923005164171508 Database cluster state: in production pg_control last modified: Fri Apr 29 01:00:00 2016 Latest checkpoint location: EEAF/BAA5520 Prior checkpoint location: EEAF/BAA5440 ... Latest checkpoint's NextXID: 7/876524573 Latest checkpoint's NextOID: 264355612 Latest checkpoint's NextMultiXactId: 134512401 Latest checkpoint's NextMultiOffset: 547842659 ... 

If the contents of pg_control were lost, PostgreSQL will not be able to start the recovery procedure. If it happened that the database files suddenly disappeared, which can happen during an emergency shutdown with the fsync=off parameter, then the correct recovery method is switching to a backup. This article can be useful in those cases when you need to restore the database to the minimum time, but you cannot switch to the backup and you can donate some of the data.

The pg_control file is not protected from failures, and it can only be restored using the pg_resetxlog utility or a hex editor. Using pg_resetxlog, you can lose some data. You discard all current transaction logs and consider that PostgreSQL has completed its work normally: all data is written to files, as if the checkpoint had just completed. You will also have to select the maximum visible number of the transaction counter. If you select a transaction number that is too large, then the data files will not contain information that the DBMS has not yet flushed to the disk from RAM, as the process of creating a checkpoint would have done. If you select a transaction number that is too small, then the data recorded later will not be visible.

It is logical to choose the time of the control point, but where can you get this value? The standard utility pg_xlogdump, which can view the contents of WAL files, comes to the rescue. You need to select the most recent file containing the checkpoint record with the XLOG record type:

 $ pg_xlogdump -r XLOG pg_xlog/$FILE ... rmgr: XLOG len (rec/tot): 80/ 106, tx: 0, lsn: EEAF/0BAA5B40, prev EEAF/0BAA5B08, desc: CHECKPOINT_ONLINE redo EEAF/BAA5B08; tli 2; prev tli 2; fpw true; xid 7/876524573; oid 264355612; multi 134512401; offset 547842659; oldest xid 686019718 in DB 16400; oldest multi 128391103 in DB 16400; oldest/newest commit timestamp xid: 0/0; oldest running xid 876524573; online 

In this case, you can select the following parameters for pg_resetxlog:

 $ pg_resetxlog -x 876524573 -o 264355612 -m 134512401,128391103 -n /var/lib/pgsql/9.5/data 

In order for the specified command to apply values, you need to run it without the -n option and with the -f option key. The command will clear the contents of the pg_xlog directory and write the new values ​​to the pg_control file. After that, you can run PostgreSQL without data recovery.

If you selected a checkpoint for recovery, then in order not to get into a situation where the preempted data from the buffer cache was written to the database files, it is recommended to set the value of the autovacuum=off parameter before starting the instance and remove the logical backup using the pg_dump utility. If errors occur during the backup, use the zero_damaged_pages=on parameter. After removing a logical backup, it must be restored on a new PostgreSQL instance.

All successful operation of PostgreSQL and backup at your fingertips!