From: Daniel Paval <dani@scorpionsoftware.ro>
To: pgsql-admin@postgresql.org
Subject: Data corruption / recover
Date: Thu, 28 Feb 2013 12:45:54 +0200
Message-ID: <512F3562.9060609@scorpionsoftware.ro> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dani@scorpionsoftware.ro
Subject: Re: Data corruption / recover
In-Reply-To: <512F3562.9060609@scorpionsoftware.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox