UPDATE September 2023: This feature is now called pg_resetwal, see the PostgreSQL documentation about it here. The blog below will be preserved for reference. See this blog by Laurenz Albe about corrupting databases to read more aboutpg_resetwal.

PostgreSQL has proven to be one of the most reliable Open Source databases around. In the past we have dealt with customers who have achieved superior uptimes and who are really happy with PostgreSQL. But even if PostgreSQL does its job day after day after day – there are some components, which can just fail and cause trouble. In short: Hardware may crash, filesystem can fail, and so on. In 99% of all cases there is a way around total disaster. Either you have a replica, which protected you from downtime or you simply have a reasonably good backup to help out in case of disaster. But what if there is really no option left anymore? You got to make this database work again – no matter what (even if it is just a partial or an unreliable restore). Maybe pg_resetxlog is your last helping hand in this case.

Killing an instance

As you might know the purpose of the transaction log (= xlog or WAL) is to restore order and consistency in case of a crash. If the xlog is broken, you might face disaster. This is exactly what we want 😉

So, to cause some disaster I sent a “kill -9” to a database system under load to stop it. In case of a restart this would lead to a recovery process and PostgreSQL would fix things on startup by replaying the transaction log. Let us disturb the replay process a little …

[hs@jacqueline pg_xlog]$ dd if=/dev/zero of=00000001000000050000007C bs=1024 count=16384
16384+0 records in
16384+0 records out
16777216 bytes (17 MB) copied, 0.0280045 s, 599 MB/s

[hs@jacqueline pg_xlog]$ dd if=/dev/zero of=00000001000000050000007D bs=1024 count=16384
16384+0 records in
16384+0 records out
16777216 bytes (17 MB) copied, 0.0304789 s, 550 MB/s

[hs@jacqueline pg_xlog]$ dd if=/dev/zero of=00000001000000050000007F bs=1024 count=16384
16384+0 records in
16384+0 records out
16777216 bytes (17 MB) copied, 0.028223 s, 594 MB/s

What I have just done is really nasty. I have overwritten some transaction log files found in the pg_xlog directory with zeros. This should instantly make sure that the recovery process ends up in hell. Let us check if my attempts to really kill things has succeeded:

[hs@jacqueline test_db]$ pg_ctl -D /data start
server starting
LOG:  database system was shut down at 2014-08-08 12:17:00 CEST
LOG:  invalid magic number 0000 in log segment 00000001000000050000007B, offset 0
LOG:  invalid primary checkpoint record
LOG:  invalid magic number 0000 in log segment 00000001000000050000007B, offset 0
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 18807) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure

Wow, this is exactly what I have intended to do. Nothing works anymore so it is time to fix things and demonstrate the power of pg_resetxlog.

pg_resetxlog at work

pg_resetxlog is a C program shipped with PostgreSQL. So, there is no need to install it on top of PostgreSQL. It is simply there and ready for action in case it is needed. Actually it is very powerful as the following help page shows:

[hs@jacqueline ~]$ 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

It is possible to nicely configure pg_resetxlog to make it do precisely what is needed (maybe by just setting xlog back a little). However, in this example we don’t care too much – we just want to reset the xlog completely:

[hs@jacqueline test_db]$ pg_resetxlog /data

Transaction log reset

One line is enough to reset the xlog of PostgreSQL.

Let us see if this has worked:

[hs@jacqueline test_db]$ pg_ctl -D . start
server starting
LOG:  database system was shut down at 2014-08-08 12:18:51 CEST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Voila, the database was able to start up again. The autovacuum daemon has been launched nicely and the system is able to accept connection. A simple test reveals that all databases seem to be around:

[hs@jacqueline test_db]$ psql -l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | hs    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | hs    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/hs            +
           |       |          |             |             | hs=CTc/hs
 template1 | hs    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/hs            +
           |       |          |             |             | hs=CTc/hs
 test      | hs    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

So, where is the problem?

Be cautious

pg_resetxlog is really the last line of defence. If you have to call it, you should expect some data to be lost. Maybe you will face slightly broken tables or corrupted indexes. You should not really trust this database anymore – however, you will have the chance to extract some data, restore at least something.

Under any circumstances: If you are forced to use pg_resetxlog I would really advise to not continue with the database instance in doubt, take a backup, PROPERLY check the data and start over with a new database instance.