Обсуждение: 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.
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
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.
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?
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.