Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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/$

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

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?

share|improve this question

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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