PostgreSQL 9.3 – How to Reset XLOG

postgresqlpostgresql-9.3

I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously).

For non-technical reasons, the server needed to be immediately brought back up, with literally minutes to work out a solution, I found and used pg_resetxlog – just restart the WAL archives. My reasoning was that the server was not in active use – it being a weekend, so there was nothing much that could be lost.

According to the documentation (http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html ) pg_resetxlog should really not be used, and when it is used, a DB dump should be taken and restored immediately after.

My question is, is this a concern for any transactions that occurred in the last seconds – or is it a concern that could ultimately hurt the server as a whole? Is it possible that on an inactive server (with a few routine logs written via cron the most that could have possibly been lost), there was no harm done?

The database will be in heavy use for the next day or so, can I do the dump and restore after that if it is necessary?

Best Answer

Don't use pg_resetxlog except as a last resort. (I think I'll submit a patch to add an --i-have-read-the-manual option to pg_resetxlog, I see it abused too often.)

You should dump the server and restore the dump.

The transactions logs are crucial for data integrity. Without them, you might have incompletely written transactions, transactions that were committed but never actually made it to the main database, transactions out of order, and more.

So it's not as simple as losing a few recent transactions. You lose the write-ordering that preserves data integrity.

Index blocks might not match the data in the heap. Visibility maps etc may be invalid. Queries could produce incorrect results, disk blocks could be split (resulting in errors on read), and more.

It's really a very good idea to dump, re-initdb, and restore. Keep a copy of the old damaged database around just in case. Even then, your database is damaged - the dump won't magically fix it if data is inconsistent and wrong, and it's possible you may have to repair the dump to get foreign keys to restore correctly, etc.

Related Question