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.

We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d table listing only shows a fraction of the tables. A \d on the existing tables shows missing columns as well.. However, all the tables (still visible or not) may be queried by selects via psql and the actual data and columns in the result sets seem just fine.

We backed up the data folder and for the last 24h tried to recover the situation to a point where pg_dump works. Unfortunately we haven't been fully successful yet although we tried tons of suggestions in previous similar posts. Reindex didn't help, but a "VACUUM FULL FREEZE [ANALYZE]" did free 70% (14GB to 4) of the used disk space. Of course, the database wasn't vacuumed regularly as it should have been so:

WARNING: some databases have not been vacuumed in over 2 billion transactions DETAIL: You may have already suffered transaction-wraparound data loss.

A full vacuum without the freeze option does make all databases, tables and users visible, but duplicated, i.e. \l then shows the database twice and a \d on the database lists all tables twice. The postgres user appears twice in pg_user, etc. pg_dump won't work if the database is not listed and won't either if the postgres user is duplicated..

Does this sound familiar to anyone? It's a pity that all the data seems to be there, but we're not able to recover and restore it to a clean DB. We'd appreciate any suggestions.

Thank you!

PS: I've waiting for feedback from the pgsql-admin mailing list as well.

share|improve this question

migrated from stackoverflow.com Feb 28 '13 at 12:00

This question came from our site for professional and enthusiast programmers.

7  
7.4? Honestly? You should really, really, really upgrade to a version from this century, once you have fixed this problem. –  a_horse_with_no_name Feb 28 '13 at 12:03
    
@a_horse_with_no_name: Century bigot. Harumph. –  Mike Sherrill 'Cat Recall' Feb 28 '13 at 16:58

3 Answers 3

up vote 4 down vote accepted

First, if you haven't done the first two that vyegorov suggests do it now. If you have already vacuumed, it is not clear what sort of damage you may have already done in this case. This error should not be possible on new versions of PostgreSQL due to a number of checks, and had you upgraded some time ago, this wouldn't have happened. I highly recommend trying to stay on supported branches in the future.

I want to take a moment and describe what likely caused the problem and what it means. The prognosis IMO is not good and recovering the data, if it is even possible, is likely to be expensive and time consuming. I sincerely hope you have a good backup from before the transaction wraparound. If not, ouch....

What Went Wrong

PostgreSQL uses something called MVCC, which means that old versions of rows are kept around until they are clearly no longer used. MVCC as practiced by PostgreSQL stamps each row with a minimum transaction and a maximum transaction, and uses these for visibility management. When a transaction rolls back those rows entered are no longer visible and those rows deleted remain visible. Transaction ID's are 32-bit integers.

Periodically you are supposed to vacuum PostgreSQL instances. This, among other things, manages MVCC so that fewer transaction id's need to be checked, manages free space in tables, and can reset the transaction id sequence.

When the transaction ID wraps around, bad things happen but they boil down to the fact that PostgreSQL can no longer be sure what rows are visible and what rows are not. Note thais applies not only to rows in your own tables but in the system catalogs as well. This is a very, very bad thing. The best approach, if you can do it, is to restore from a backup before the wraparound occurred. This is why databases and tables are not showing up and why vacuuming full duplicates them.

Why Upgrading Would have Fixed This

Modern versions of PostgreSQL come with autovacuum automatically enabled which runs vacuum processes in the background to help prevent this sort of problem. More recent versions also will refuse to start new transactions for non-superusers once wraparound is approaching. This gives you a chance to detect and correct the problem before you suffer possibly catastrophic data loss.

PostgreSQL 7.4 hasn't been supported in nearly three years. I don't know when the last time anything was vacuumed but it must have been billions of transactions ago. This is not good.

share|improve this answer
    
Thank you for your detailed explanation. Can I assume that, while transaction IDs have become corrupted, all the actual data (i.e. rows - from the oldest versions to the newest) is still intact? If so we might try what @CraigRinger suggested below, using pg_dirtyread.. –  Daniel Paval Feb 28 '13 at 14:34
    
Yes, that's a safe assumption. I don't know about compiling pg_dirtyread against PostgreSQL 7.4. Might as well try though and if you know enough C, put in the effort to making it work. –  Chris Travers Feb 28 '13 at 14:36
    
Nice explanation Chris, thanks for the detail. Bookmarking this one for next time I get a client with a 7.x DB and am having trouble convincing them that upgrading would be a really good idea ;-) . Re pg_dirtyread I'll be amazed if it builds against 7.4 without significant changes, but how significant I haven't investigated. –  Craig Ringer Feb 28 '13 at 14:46

Please, do the following:

  1. shutdown your PostgreSQL instance;
  2. perform a cold physical backup of the PostgreSQL cluster directory $PGDATA, if not done yet;
  3. describe your symptomps in the message to pgsql-admin mailing list.

Hm, looks like you've already done step 3 above. Please, explicitly mention cross posting in the future.

share|improve this answer
    
Yes, I did post it to pgsql-admin, but wasn't sure how active they are and decided to try with the SO community too. –  Daniel Paval Feb 28 '13 at 11:34
2  
@DanielPaval: .. which we'd expect to be mentioned in your question. –  Erwin Brandstetter Feb 28 '13 at 12:05

If you haven't read and acted on this wiki article do so now, then come back and keep reading.

Given that this is at least partly an xid wrap-around problem you might want to check out pg_dirtyread, which may help you recover some data. See:

It is extremely likely that you will have to do significant work to port pg_dirtyread so it works with a version as old as 7.4. If nothing else, 9.0 and below don't support the extension mechanism. I'd recommend contacting someone who does this sort of thing professionally. See the PostgreSQL professional services page (Disclaimer: I work for 2ndQuadrant, one of the listed companies).

In the mean time, restore from your most recent backup and immediately begin work on upgrading to 9.1 or 9.2. You can look at merging recovered data as you recover it. It's probably going to take you a while and be somewhat mangled, so starting with a clean backup and merging from there is likely your best course of action.

share|improve this answer
    
Thank you too, pg_dirtyread sounds promising enough given the situation, but some tables hold 10s to 100s thousand entries. We'll have to decide on professional support. –  Daniel Paval Feb 28 '13 at 14:40
    
@DanielPaval Some groups can also write custom code for recovery or backport newer tools to old versions. Professional support will cost you, though; this probably isn't the sort of job that can be knocked off in a couple of hours using off-the-shelf tools. –  Craig Ringer Feb 28 '13 at 14:43
    
(You might be better off spending the time backporting pg_dirtyread yourself if you can afford the time and have some C skills.) –  Craig Ringer Feb 28 '13 at 14:49
    
I've been asked for help on this myself so it's only recent that I found out more about what's going on there with PostgreSQL. While it makes sense from what @Chris posted, it's quite a nasty trap - this transaction wrap-around - laid by PostgreSQL's inner workings. The whole system was deployed some time ago, hence version 7.4, but, with or without updates, I too would have expected it to run just fine as long as the hard disk didn't overflow :) –  Daniel Paval Feb 28 '13 at 15:17
    
@DanielPaval Yes, I agree that txid wraparound causing corruption was a deficiency that should never have existed. Newer versions do everything they can to prevent the system reaching that point and if it does (usually due to a determined admin preventing Pg from vacuuming etc) they'll shut themselves down and refuse to start until the problem is resolved. It would've been strongly preferable for that to have been the case from the beginning. –  Craig Ringer Feb 28 '13 at 23:00

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.