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.