Database Recovery from Corrupted Dump or Raw database table file.
Hi all,
I have a raid catastrophe which has effectively blitzed a cluster data
directory. I have several pg_dump backups but these will not restore
cleanly. I assume the disk has been failing for some time and the
backups are of the corrupted database.
Using a selective pg_restore on the dumps, I have restored 2/3rds of the
data but some tables I cannot recover directly, so I would like to see
if it is possible to examine the dumps (they are in compressed format)
to see if there are any rows which are recoverable. I do not know how or
if it is even possible to pick out rows from an individual table, when
in all likelyhood the file itself is corrupted.
I also have some parts of the data directory, so the tables may be
accessible if I know the ID of the corrupt datatables. Is the ID listed
in the pg_dump --list file? And can the data be extracted from the raw
data files without running a cluster. I am unsure if there is enough
data to actually start a cluster.
Thanks,
Howard.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Howard News wrote:
I have a raid catastrophe which has effectively blitzed a cluster data
directory. I have several pg_dump backups but these will not restore
cleanly. I assume the disk has been failing for some time and the
backups are of the corrupted database.Using a selective pg_restore on the dumps, I have restored 2/3rds of the
data but some tables I cannot recover directly, so I would like to see
if it is possible to examine the dumps (they are in compressed format)
to see if there are any rows which are recoverable. I do not know how or
if it is even possible to pick out rows from an individual table, when
in all likelyhood the file itself is corrupted.I also have some parts of the data directory, so the tables may be
accessible if I know the ID of the corrupt datatables. Is the ID listed
in the pg_dump --list file? And can the data be extracted from the raw
data files without running a cluster. I am unsure if there is enough
data to actually start a cluster.
A backup created with pg_dump consists of SQL statements to recreate the
objects.
You can extract the SQL statements as text with
pg_restore -f sqlfile backupfilename
That should help you with restoring the data.
What exactly do you mean by "do not restore cleanly"?
Do you get error messages or is the content not ok?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/11/2016 13:12, Albe Laurenz wrote:
Howard News wrote:
I have a raid catastrophe which has effectively blitzed a cluster data
directory. I have several pg_dump backups but these will not restore
cleanly. I assume the disk has been failing for some time and the
backups are of the corrupted database.Using a selective pg_restore on the dumps, I have restored 2/3rds of the
data but some tables I cannot recover directly, so I would like to see
if it is possible to examine the dumps (they are in compressed format)
to see if there are any rows which are recoverable. I do not know how or
if it is even possible to pick out rows from an individual table, when
in all likelyhood the file itself is corrupted.I also have some parts of the data directory, so the tables may be
accessible if I know the ID of the corrupt datatables. Is the ID listed
in the pg_dump --list file? And can the data be extracted from the raw
data files without running a cluster. I am unsure if there is enough
data to actually start a cluster.A backup created with pg_dump consists of SQL statements to recreate the
objects.You can extract the SQL statements as text with
pg_restore -f sqlfile backupfilename
That should help you with restoring the data.
What exactly do you mean by "do not restore cleanly"?
Do you get error messages or is the content not ok?Yours,
Laurenz Albe
Hi Laurenz,
Yes I get errors when trying to restore. When I try to restore to an
uncompressed file as suggested, I get the same errors - or the
pg_restore crashes.
e.g. in the console I get the following...
...
pg_restore: executing SEQUENCE SET example_seq
pg_restore: processing data for table example_table
pg_restore: [compress_io]
** crash **
If I selectively restore I have about 10 tables that do not recover, and
all of the Blobs.
Howard.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Nov 7, 2016 at 8:23 AM, Howard News <howardnews@selestial.com> wrote:
pg_restore: executing SEQUENCE SET example_seq
pg_restore: processing data for table example_table
pg_restore: [compress_io]** crash **
What crashes? the pg_restore process or the backend server?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/11/2016 13:44, Vick Khera wrote:
On Mon, Nov 7, 2016 at 8:23 AM, Howard News <howardnews@selestial.com> wrote:
pg_restore: executing SEQUENCE SET example_seq
pg_restore: processing data for table example_table
pg_restore: [compress_io]** crash **
What crashes? the pg_restore process or the backend server?
The pg_restore crashes.
For example if I run the "pg_restore -f example.sql backup" then it will
fall over when it tries to restore one of the corrupted tables.
Presumably the backup itself is corrupted.
The "pg_restore --list" works fine, so I can run a selective restore by
commenting out the dodgy tables using the "pg_restore -L" option.
Regarding the postgres ids, these appear to be listed in the pg_restore
-L so I can find out which data file the tables refer to, but I have
not found out how to restore a single one of these files to a usable or
readable state yet.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general