pg_resetxlog: renamed to pg_resetwal

08.2014 / Category: / Tags: | | |

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 ...

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:

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:

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:

One line is enough to reset the xlog of PostgreSQL.

Let us see if this has worked:

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:

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.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram