0

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?

0

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
  1. Then take the data only dump.

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

  1. 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

  • How can I precisely identify the offending table(s) to actually correct this problem in the source? It's not a single table backup, it's almost a full DB as only two schemas are being excluded. I'll try the workaround and report back.Thanks! – Gonzalo Vasquez Apr 17 '18 at 11:47
  • Tried to get all the statements for disabling triggers, but that SQL is only showing tables in postgresql related tables, not our actual data tables. Output at gist.github.com/gvasquez-waypoint/… – Gonzalo Vasquez Apr 17 '18 at 12:18
  • 1
    Before this, have you restored the schema dump? – Bhuvanesh Apr 17 '18 at 12:41
  • yes @SQLadmin, all the schemas have been restored (FYI: into AWS' RDS) – Gonzalo Vasquez Apr 17 '18 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.