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.