current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you very much for the reply, you did a good job at convincing me I did the wrong thing. Is there a way for me to check if integrity was somehow preserved? The DB is already back up and running and a bunch of data has been inputted since then, any ideas as to what the next step should be? Thanks. –  greenejob May 4 at 6:18
    
Take a backup (pg_dump). Then if possible VACUUM FULL all tables to force a table and index rebuild. Otherwise rebuild all indexes (REINDEX) and force a scan to read all tables by setting enable_indexonlyscan = off and SELECT * FROM the_table for each table. Also verify all foreign key relationships - you'll need to write a script to do this. Run a database-wide ANALYZE. –  Craig Ringer May 4 at 6:23
    
Thank you so much for this, I already did the REINDEX and ANALYZE - I will do the rest and update this if there is anything notable for anyone else looking into this (of course if there is, please read the first few bold letters of Craigs reply. –  greenejob May 4 at 6:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.