Postgresql – Dump PostgreSQL without DROP DATABASE

postgresqlpostgresql-8.4

I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file).

Actually I use this command:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

But, in top lines of file, I have this:

DROP DATABASE...
CREATE DATABASE...

So, I my case, I want duplicate this database, and, if I use this script with pg_restore I drop the other database (I don't want that).

Best Answer

My understanding of pg_restore (which you'll have to use because you created a pg_dump with format=custom) is that if you supply the --dbname=... flag you will restore into that database (without dropping it).

If you supply the --clean command the db and objects will be dropped before being recreated.

If no --dbname=... flag is supplied, the creation commands will spool to stdout, which you can redirect to a file and load using psql.

To test, try:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql
Related Question