Обсуждение: Unexpected block ID found when reading data
Hi, I have a db dump (.backup) that i want to restore. But it comes up with error: "found unexpected block ID when reading data" Is there any chance i can save my backup? The problem is I don't have the database anymore. I used psql 12.5 when dumping the database approx a year ago.
On 8/2/21 6:42 PM, Gilar Ginanjar wrote: > Hi, > > I have a db dump (.backup) that i want to restore. But it comes up with error: > > "found unexpected block ID when reading data" > > Is there any chance i can save my backup? The problem is I don't have the database anymore. > > I used psql 12.5 when dumping the database approx a year ago. > What command and version of pg_dump did you use to dump it? What command and program(and version) did you use to restore it? What version of Postgres where you restoring to? -- Adrian Klaver adrian.klaver@aklaver.com
Hi, Adrian Thanks in advance. pdgump command: pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6. pgrestore command: pg_restore -U myuser -j8 -d mydb dbdump.backup I’ve tried to restore to postgre 9.6, 12.1 and 12.5 > On 3 Aug 2021, at 09.11, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 8/2/21 6:42 PM, Gilar Ginanjar wrote: >> Hi, >> I have a db dump (.backup) that i want to restore. But it comes up with error: >> "found unexpected block ID when reading data" >> Is there any chance i can save my backup? The problem is I don't have the database anymore. >> I used psql 12.5 when dumping the database approx a year ago. > > What command and version of pg_dump did you use to dump it? > > What command and program(and version) did you use to restore it? > > What version of Postgres where you restoring to? > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
Hi, Adrian Thanks in advance. pdgump command: pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6. pgrestore command: pg_restore -U myuser -j8 -d mydb dbdump.backup I’ve tried to restore to postgre 9.6, 12.1 and 12.5 > On 3 Aug 2021, at 09.11, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 8/2/21 6:42 PM, Gilar Ginanjar wrote: >> Hi, >> I have a db dump (.backup) that i want to restore. But it comes up with error: >> "found unexpected block ID when reading data" >> Is there any chance i can save my backup? The problem is I don't have the database anymore. >> I used psql 12.5 when dumping the database approx a year ago. > > What command and version of pg_dump did you use to dump it? > > What command and program(and version) did you use to restore it? > > What version of Postgres where you restoring to? > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
On Tue, Aug 3, 2021 at 4:49 AM Gilar Ginanjar <gilar@innovation-project.com> wrote: > pgrestore command: > pg_restore -U myuser -j8 -d mydb dbdump.backup > As a desparate approach I would try to use options -L and -l to extract objects from the backup and restore a small subset of them, in order to find out where the error is. I suspect the compressed archived is damaged. Luca
Hi, Luca Thanks for the response. I’ve tried with option -L and -l before. The schema is fine, but sadly when i try to restore only data on the tables i need,it comes up with the error. Is there any small chance I can view/save/restore my data? I desperately need it. Many thanks. > On 3 Aug 2021, at 17.06, Luca Ferrari <fluca1978@gmail.com> wrote: > > On Tue, Aug 3, 2021 at 4:49 AM Gilar Ginanjar > <gilar@innovation-project.com> wrote: >> pgrestore command: >> pg_restore -U myuser -j8 -d mydb dbdump.backup >> > > As a desparate approach I would try to use options -L and -l to > extract objects from the backup and restore a small subset of them, in > order to find out where the error is. > I suspect the compressed archived is damaged. > > Luca
On Tue, Aug 3, 2021 at 1:16 PM Gilar Ginanjar <gilar@innovation-project.com> wrote: > Is there any small chance I can view/save/restore my data? I desperately need it. > I guess there is not much left to do. I will try doing a pg_restore -t one table at a time to limit the damage, assuming that --section=data is not working at all. Luca
On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar <gilar@innovation-project.com> wrote:
Hi, Adrian
Thanks in advance.
pdgump command:
pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup
I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6.
pgrestore command:
pg_restore -U myuser -j8 -d mydb dbdump.backup
I’ve tried to restore to postgre 9.6, 12.1 and 12.5
do you remember which patch version 9.6.x did you take the dump from ?
9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating, maybe your restoration to the latest minor version is failing.
can you run a pg_restore -f <filename> ... so it creates a sql dump file and check if that is clean ?
and then check the version in the dump file, and compile that version from source (or git) and try if the restore worked fine there.
just saying, in case you are out of options, else ignore.
Vijaykumar Jain <vijaykumarjain.github@gmail.com> writes:
> On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar <gilar@innovation-project.com>
> wrote:
>> I'm not sure which pg_dump version did i use before, but I used psql 12.5
>> to dump and the db version is postgresql 9.6.
>>
>> pgrestore command:
>> pg_restore -U myuser -j8 -d mydb dbdump.backup
>>
>> I’ve tried to restore to postgre 9.6, 12.1 and 12.5
> 9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
> maybe your restoration to the latest minor version is failing.
This error is internal to pg_restore, so the target server version isn't
going to make any difference. Either the dump file is corrupt, or more
likely you're dealing with a pg_restore bug or version discrepancy.
(pg_restore *should* complain if the archive file is too new, but there
were some bugs in that code until recently :-(.)
Anyway, people have asked for the pg_restore version several times,
and I hope this explains why it's critical information. *PLEASE*
show us the output of "pg_restore --version". It would also be
useful to see the first dozen or two lines of output from
"pg_restore -l -v dbdump.backup", which should include the dump
file's version as well as the source pg_dump's version.
regards, tom lane
I’m not sure which patch version i used to dump, but i was using postgre 12.5 for pg_dump back then.
I’m running pg_restore -f dbdump.backup right now, I think it will take some times because it has a large size (around 9 GB). There are no issues yet.
Thanks for advice.
On 3 Aug 2021, at 20.15, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar <gilar@innovation-project.com> wrote:Hi, Adrian
Thanks in advance.
pdgump command:
pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup
I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6.
pgrestore command:
pg_restore -U myuser -j8 -d mydb dbdump.backup
I’ve tried to restore to postgre 9.6, 12.1 and 12.5
do you remember which patch version 9.6.x did you take the dump from ?9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating, maybe your restoration to the latest minor version is failing.can you run a pg_restore -f <filename> ... so it creates a sql dump file and check if that is clean ?and then check the version in the dump file, and compile that version from source (or git) and try if the restore worked fine there.just saying, in case you are out of options, else ignore.
Yes, i’ve found at some forums (DBeaver) that it is a bug on pg_restore. Hopefully so, so that I have hope to restore my data later.
This is the output for "pg_restore —version”:
pg_restore (PostgreSQL) 12.7
And this are some "pg_restore -l -v dbdump.backup” output:
; dbname: mydb
; TOC Entries: 6487
; Compression: 3
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.6.21
; Dumped by pg_dump version: 12.5
On 3 Aug 2021, at 20.59, Tom Lane <tgl@sss.pgh.pa.us> wrote:Vijaykumar Jain <vijaykumarjain.github@gmail.com> writes:On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar <gilar@innovation-project.com>
wrote:I'm not sure which pg_dump version did i use before, but I used psql 12.5
to dump and the db version is postgresql 9.6.
pgrestore command:
pg_restore -U myuser -j8 -d mydb dbdump.backup
I’ve tried to restore to postgre 9.6, 12.1 and 12.59.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
maybe your restoration to the latest minor version is failing.
This error is internal to pg_restore, so the target server version isn't
going to make any difference. Either the dump file is corrupt, or more
likely you're dealing with a pg_restore bug or version discrepancy.
(pg_restore *should* complain if the archive file is too new, but there
were some bugs in that code until recently :-(.)
Anyway, people have asked for the pg_restore version several times,
and I hope this explains why it's critical information. *PLEASE*
show us the output of "pg_restore --version". It would also be
useful to see the first dozen or two lines of output from
"pg_restore -l -v dbdump.backup", which should include the dump
file's version as well as the source pg_dump's version.
regards, tom lane
On 8/3/21 8:20 AM, Gilar Ginanjar wrote: > Yes, i’ve found at some forums (DBeaver) that it is a bug on pg_restore. The specific bug being? > Hopefully so, so that I have hope to restore my data later. > > > This is the output for "pg_restore —version”: > > pg_restore (PostgreSQL) 12.7 > > > And this are some "pg_restore -l -v dbdump.backup” output: > > ; dbname: mydb > ; TOC Entries: 6487 > ; Compression: 3 > ; Dump Version: 1.14-0 > ; Format: CUSTOM > ; Integer: 4 bytes > ; Offset: 8 bytes > ; Dumped from database version: 9.6.21 > ; Dumped by pg_dump version: 12.5 > > > Once again many thanks for the help. Really appreciate it. -- Adrian Klaver adrian.klaver@aklaver.com
Gilar Ginanjar <gilar@innovation-project.com> writes:
> This is the output for "pg_restore —version”:
> pg_restore (PostgreSQL) 12.7
Hmph. That's current, so it has the bug fixes that I thought might
be related. Either you've hit a previously-unknown bug, or the file
actually is corrupt :-(.
Have you tried running the restore without the -j option?
regards, tom lane
On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar <gilar@innovation-project.com> wrote:
I’m not sure which patch version i used to dump, but i was using postgre 12.5 for pg_dump back then.I’m running pg_restore -f dbdump.backup right now, I think it will take some times because it has a large size (around 9 GB). There are no issues yet.
Did this complete without issues ? or did it throw the same errors ?
Hi, sorry it's been a long time to reply.
It throw the same errors. I can't find any solution. It frustrated me and i've been on a break since. ;)
On 5 Aug 2021, at 00.50, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar <gilar@innovation-project.com> wrote:I’m not sure which patch version i used to dump, but i was using postgre 12.5 for pg_dump back then.I’m running pg_restore -f dbdump.backup right now, I think it will take some times because it has a large size (around 9 GB). There are no issues yet.Did this complete without issues ? or did it throw the same errors ?
On 8/27/21 5:31 PM, Gilar Ginanjar wrote: > Hi, sorry it's been a long time to reply. > > It throw the same errors. I can't find any solution. It frustrated me > and i've been on a break since. ;) Per upstream advice do something like: pg_restore -U myuser -s -d mydb dbdump.backup to get the schema definitions into the database. Then: pg_restore -U myuser -a -t <table_name> -d mydb dbdump.backup to restore the data for a table at a time. You will mostly likely hit an error on one or more tables, but you will get the rest in and know where your problem is. > > >> On 5 Aug 2021, at 00.50, Vijaykumar Jain >> <vijaykumarjain.github@gmail.com >> <mailto:vijaykumarjain.github@gmail.com>> wrote: >> >> On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar >> <gilar@innovation-project.com <mailto:gilar@innovation-project.com>> >> wrote: >> >> I’m not sure which patch version i used to dump, but i was using >> postgre 12.5 for pg_dump back then. >> >> I’m running pg_restore -f dbdump.backup right now, I think it will >> take some times because it has a large size (around 9 GB). There >> are no issues yet. >> >> >> Did this complete without issues ? or did it throw the same errors ? > -- Adrian Klaver adrian.klaver@aklaver.com