In this section, resetting the transaction log will be covered. Before we get started, I want to personally issue a warning: don't take this lightly. Resetting the transaction log is a harsh thing to do. It almost always leads to some data loss, and it does not guarantee that your data will still be fully consistent. Resetting xlog
is the last thing to consider when things go south.
The same rules as we covered before apply here: always take a snapshot of the filesystem or shut down the database, and create a binary copy of the data directory before using pg_resetxlog
. Let me stress my point. In my 15-year long career as a PostgreSQL consultant, I have had to do this only a handful of times. Usually, this can be resolved in some other way.
However, if PostgreSQL does not start up anymore because the xlog
is broken, pg_resetxlog
can come to your rescue. Here is how the syntax works:
$ pg_resetxlog --help pg_resetxlog resets the PostgreSQL transaction log. Usage: pg_resetxlog [OPTION]... DATADIR Options: -e XIDEPOCH set next transaction ID epoch -f force update to be done -l XLOGFILE force minimum WAL starting location for new transaction log -m MXID,MXID set next and oldest multitransaction ID -n no update, just show what would be done (for testing) -o OID set next OID -O OFFSET set next multitransaction offset -V, --version output version information, then exit -x XID set next transaction ID -?, --help show this help, then exit
There are a couple of settings here. The most important ones are -o
, -x
, -e
, -m
, -O
, and -l
. These settings allow you to go to a certain transaction ID
, set an OID
, and so on.
In some cases, it might happen that pg_resetxlog
complains that the pg_control
file is not valid. The control file is vital because it contains information about block sizes, checkpoints, toast sizes, and so on. To see which data is in the control, try the following snippet:
pg_controldata $PGDATA # or replace $PGDATA with # your PostgreSQL data # directory
The pg_controldata
file will write a lot of information to the screen. If pg_resetxlog
cannot find a valid control file, it is necessary to use -f
. Then it tries to fix things even if the control file is long gone or is corrupted.
Once pg_resetxlog
has been executed, it is usually possible to start the database again (unless it is really doomed). Ensure that a backup is taken instantly and that data is checked for sanity. This includes verifying that all foreign keys are okay and that data can be dumped, or restored, nicely.