Message-ID: <512F3562.9060609@scorpionsoftware.ro>
Date: Thu, 28 Feb 2013 12:45:54 +0200
From: Daniel Paval <dani@scorpionsoftware.ro>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Data corruption / recover
Content-Type: multipart/alternative;
	boundary="------------000703020001050103000606"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------000703020001050103000606
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

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!

--------------000703020001050103000606
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi,<br>
    <br>
    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.<br>
    <br>
    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 <b>FULL</b> 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:<br>
    <br>
    <i>WARNING:&nbsp; some databases have not been vacuumed in over 2 billion
      transactions</i><i><br>
    </i><i>DETAIL:&nbsp; You may have already suffered transaction-wraparound
      data loss.</i><i><br>
    </i><br>
    A full vacuum <u>without</u> 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..<br>
    <br>
    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.<br>
    <br>
    Thank you!<br>
  </body>
</html>

--------------000703020001050103000606--