Resetting the transaction log

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