pg_restore: [archiver] could not find entry for ID 12

Question

I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:

pg_restore: [archiver] could not find entry for ID 12

Exactly the same happens if pg_dump is invoked using -Fc instead of -Fd.

As per this link, this seems to be a known issue, but I'm not sure of the workaround I should apply to move over it.

Could this actually be overcome in this PostgreSQL version? If so, what should I alter to achieve it?

Answer 1

You need to identify in which table its throwing this error.

• Are you taking full database dump or single table?

Anyhow Try this workaround.

1. Take schema dump alone.

   pg_dump -O -s dbname > schema.sql

2. Restore it on the target. psql -h host -U user -d < schema.sql

3. On target disable trigger on all the tables.

ALTER TABLE table_name Disable TRIGGER ALL;

Use this query to generate this statement for all the tables.

SELECT 'ALTER TABLE ' 
       || table_name 
       || ' DISABLE TRIGGER ALL;' 
FROM   information_schema.TABLES 
WHERE  table_catalog = 'dbname' -- replace with your db name
       AND table_schema IN( 'public' );  -- add all your schema

4. Then take the data only dump.

pg_dump -U user dbname -Fc --data-only > dbname.dump

5. Then restore your backup.

pg_restore -h host -U user dbname < dbname.dump

To make the restore fast, use -j

pg_restore -h host -U user -j 10 dbname < dbname.dump