Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jul 13 '13 at 10:58

This question came from our site for professional and enthusiast programmers.

    
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

1 Answer 1

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
share|improve this answer
    
I'll test that. –  Patrick Maciel Jul 12 '13 at 20:38

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.