3

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?

2

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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