I have a Bacula script that does an automatic backup of a Postgres Database. The script makes two backups using (pg_dump) of the data base :

The schema only and the data only.

/usr/bin/pg_dump --format=c -s $dbname --file=$DUMPDIR/$dbname.schema.dump
/usr/bin/pg_dump --format=c -a $dbname --file=$DUMPDIR/$dbname.data.dump

The problem is that I can't figure out how to restore it with pg_restore.

Do I need to create the database and the users before then restore the schema and finally the data.

I did the following :

pg_restore  --format=c -s -C -d template1 xxx.schema.dump
pg_restore  --format=c -a -d xxx xxx.data.dump

This first restore creates the database with emtpy tables but the second gives many error like this one :

pg_restore: [archiver (db)] COPY failed: ERROR:  insert or update on table "Table1" violates foreign key constraint "fkf6977a478dd41734"
DETAIL:  Key (contentid)=(1474566) is not present in table "Table23".

Any ideas?

1 Answer 1


You need --disable-triggers when performing data-only restores with pg_restore. Normally, the schema+data restore creates the referential integrity triggers after the data is added. If you just restore a schema, then the triggers are created with no data in the table, and the database expects any data added after that to comply with the triggers.

Disabling triggers requires that you use a postgres superuser account to restore the data.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .