pg_restore for 9.4 fails with error 'could not execute query'

Question

The PostgreSQL version I am trying to restore is 9.4.10. I backed up a database from the same version. The command I am using for restore is:

/opt/PostgreSQL/9.4/bin/pg_restore -U postgres --port=5432 -v --dbname=mydb < /backups/309646/WAL/pipe_309646

The error I get is:

pg_restore: executing BLOB 71197
pg_restore: [archiver (db)] Error from TOC entry 3822; 2613 71197 BLOB 71197 user
pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key value violates unique constraint "pg_largeobject_metadata_oid_index"
DETAIL:  Key (oid)=(71197) already exists.
    Command was: SELECT pg_catalog.lo_create('71197');

This is repeated in the pg errors some 112 times. I need to restore to the same database. Here is the command I used for dumping:

/opt/PostgreSQL/9.4/bin/pg_dump" -U postgres -Fc -b  --port=5432  'mydb' -t public.users > /backups/309646/WAL/pipe_309646

Any indication as to why this is happening? How can I mitigate this error?

Answer 1

You are trying to restore a large object into a database that already contains a large object with the same oid.

Use a new database that does not contain any large objects yet as target for the restore.

Alternatively, drop the large objects first with

SELECT lo_unlink(oid) FROM pg_largeobject_metadata;