Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.