Re: Experiencing error during restore - found unexpected block ID (0)

It's typically recommended that you use the pg_dump of the same version as
the pg_restore.  In your case, the v12 pg_dump *will* successfully read from
a 10.1 database.

On 12/19/19 9:24 AM, Sar wrote:
> for pg_dump it is: PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit
> for pg_restore it is the same machine as well as newest version of
> Postgresql 12, so I assume the error is in pg_dump.
> selects work no problem, I even dumped one table with COPY and it doesnt
> raise any issue. The other table however is probably too big to ever do this.
> schema for one table:
> CREATE TABLE table1 (
>     foreign_id integer,
>     action character varying(10),
>     numarr smallint[]
> );
>
> and other table:
> CREATE TABLE log (
>     id integer NOT NULL,
>     place character varying(50),
>     place_no integer,
>     "time" character varying(80),
>     action character varying(100),
>     "user" character varying(50),
>     place_definition character varying(10),
>     place_definition2 character varying(50),
>     place_definition3 character varying(30)
> );
>
> ---------- Původní e-mail ----------
> Od: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Komu: Sar <zengu(at)seznam(dot)cz>, pgsql-general(at)postgresql(dot)org
> Datum: 17. 12. 2019 7:39:39
> Předmět: Re: Experiencing error during restore - found unexpected block ID
> (0)
>
>
> On 12/16/19 9:46 AM, Sar wrote:
> > Hi,
> > I have database on Windows Server, running postgresql 10 and I've run
> > pg_dump with options: -Fc  --verbose -d db_name --file "E:\backup.sql"
> > -T some_table.
> > Now when I run pg_restore, also on windows server with postgresql
> 12, or
> > on the same original server with postgresql 10, using options
> > --data-only --verbose -j 8,
> > or without jobs, but selecting some specific few tables with -t
> option I
> > get follwing error:
>
> Which Postgres version of pg_dump/pg_restore are you using to do above?
>
> > error: pg_restore: error: found unexpected block ID (0) when reading
> > data -- expected 3142
> > This error will only happen on very few tables, most run fine.
>
> What happens if you SELECT from these tables in the Postgres 10 instance?
>
> Can you show the schema definition for one or more of the problem tables?
>
> > Currently, I'm running without jobs and without tables selected with
> -t,
> > however I haven't gotten to the specific table that causes the trouble
> > (the restore will take days), however I already fear when it comes to
> > that table the same error will trigger.
> > I guess this could serve as a possible bug report as well, but any
> > advice as to what I can do is appreciated.
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Angular momentum makes the world go 'round.