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= database_name

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


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

  • I think DROP DATABASE is included only when you use both -c and -C options on pg_restore. As far as I can tell, pg_dump never includes DROP DATABASE. – Mike Sherrill 'Cat Recall' Jul 12 '13 at 22:21

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= database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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