Re: Need Help Restoring Old Backup

On 12/15/2013 11:00 AM, Steve Knott wrote:
> Hi,
>
> I have a really old database (5+ years old) that was backed up stored
> away for a while. Now I want some data out of it.

Not sure what you mean here? Do you mean you stored the database data
directory or did a dump and stored that?

>
> I originally thought that if I made a backup and kept around the
> original database install.exe file, I could reinstall the db & restore
> the data. It looks like that is not happening. The old database was
> somewhere in 8.2.x before it was shut down.
>
> Whenever I try to restore, I get:
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 1490: 1259 26787
> TABLE users client
> > ...
> > pg_restore: [custom archiver] found unexpected block ID (1257691431)
> when reading data -- expected 2049

So here you are using an old dump file produced with -Fc switch?

What version of pg_restore are you using?

A dump/restore should work across architectures, but for troubleshooting
purposes have you changed machines?

>
> I tried restoring on an 8.2, 8.4 and 9.0, but each fail with similar
> error messages.

How similar, the same table, the same unexpected block id message, or other?

>
> Is there anyway to repair this file or break it up some I can get most
> of the data out of it? It appears to be stopping half way through. If
> I could get it past this point, I could at least reclaim the tables
> after this issue.

Well you could use the below to selectively dump items.

http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html

-l
--list

List the contents of the archive. The output of this operation can
be used as input to the -L option. Note that if filtering switches such
as -n or -t are used with -l, they will restrict the items listed.

-L list-file
--use-list=list-file

Restore only those archive elements that are listed in list-file,
and restore them in the order they appear in the file. Note that if
filtering switches such as -n or -t are used with -L, they will further
restrict the items restored.

list-file is normally created by editing the output of a previous
-l operation. Lines can be moved or removed, and can also be commented
out by placing a semicolon (;) at the start of the line. See below for
examples.

>
> Thanks for any help,
> Steve

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com