Postgresql – Postgres backup

postgresql

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?

Best 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.

Related Solutions

Mysql – Postgres equivalent to MySQL’s \G

I'm not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want.

It's a toggle, though, so you do it before you submit the query.

\x
select * from sometable;

Linux – Automatic postgres backup

You can use pg_dump like this:

$ pg_dump -h db_host -U user_name db_name > dump_file.sql

Please think first to set the .pgpass file, that contain passwords to be used if the connection requires a password. This file should have lines of the following format:

hostname:port:database:username:password

And each of the first four fields may be a literal value, or *, which matches anything. For example: *:*:*:postgres:pg_password.

This .pgpass file must reside in the home directory ~/ and the permissions on it must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass.

Best Answer

Related Solutions

Mysql – Postgres equivalent to MySQL’s \G

Linux – Automatic postgres backup

Related Topic