Dump PostgreSQL without DROP DATABASE

Question

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

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

Answer 2

Try below example, omitting the '-c' flag in pg_dump command:

pg_dump -C database_name | xz > backup-$(date -u +%Y-%m-%d).sql.xz

Answering comments (not sure if this is the best way to do it):

can you explain what that does exactly?

Performs a dump of the database and compress it to a file named with the current date

OP didn't have a -c or -C flag, and it was a custom format dump. I don't see how your answer contributes anything useful.

Well my answer tries to provide a solution to the post title, which is: Dump PostgreSQL without DROP DATABASE. If there are additional requirement to OP question, worth editing the post title.