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.
Table of Contents
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.
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 ...
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[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:
|
1 2 3 4 5 6 7 8 9 10 |
[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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[hs@jacqueline ~]$ pg_resetxlog --help pg_resetwal resets the PostgreSQL write-ahead log. Usage: pg_resetwal [OPTION]... DATADIR Options: [-D, --pgdata=]DATADIR data directory -f, --force force update to be done even after unclean shutdown or if pg_control values had to be guessed -n, --dry-run no update, just show what would be done -V, --version output version information, then exit -?, --help show this help, then exit Options to override control file values: -c, --commit-timestamp-ids=XID,XID set oldest and newest transactions bearing commit timestamp (zero means no change) -e, --epoch=XIDEPOCH set next transaction ID epoch -l, --next-wal-file=WALFILE set minimum starting location for new WAL -m, --multixact-ids=MXID,MXID set next and oldest multitransaction ID -o, --next-oid=OID set next OID -O, --multixact-offset=OFFSET set next multitransaction offset -u, --oldest-transaction-id=XID set oldest transaction ID -x, --next-transaction-id=XID set next transaction ID --wal-segsize=SIZE size of WAL segments, in megabytes |
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:
|
1 2 3 |
[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:
|
1 2 3 4 5 |
[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:
|
1 2 3 4 5 6 7 8 9 10 11 |
[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?
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.
Leave a Reply