How to get a pg_dump -s to include the CREATE DATABASE command?

Question

I have a postgresql db server that has multiple database in it.

postgres=# \list
                               List of databases
     Name     |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
--------------+----------+-----------+---------+-------+-----------------------
 test1        | postgres | UTF8      | C       | C     | 
 test2        | postgres | SQL_ASCII | C       | C     | 
 test3        | postgres | SQL_ASCII | C       | C     | 
 test4        | postgres | SQL_ASCII | C       | C     | 
 template0    | postgres | SQL_ASCII | C       | C     | =c/postgres          +
              |          |           |         |       | postgres=CTc/postgres
 template1    | postgres | SQL_ASCII | C       | C     | postgres=CTc/postgres+
              |          |           |         |       | =c/postgres
(6 rows)

I need a way to create a dump file that contains the schema - including a CREATE DATABASE statement - for the database I'm trying to dump.

So far, I've figured out that:

pg_dump -s -U postgres -d test1 > test1_only.sql

will create the schema just for the test1 database, but it doesn't include the CREATE DATABASE command. The only way I was able to get the CREATE DATABASE command to appear was to do:

pg_dumpall -s -U postgres > /schema_alldatabases.sql 

will dump the schema for all databases. But this then ofcourse include all the schemas for all databases. On the server that I'm going to restore this file on, I already have test3 and test4 ... and I don't want to overwrite them, as the schemas are different.

Is there anyway to have the pg_dump -s command include the CREATE DATABASE command? Or, am I supposed to just use the pg_dumpall and control what I restore? if so, can you show me how I can just restore test1 from the dump file?

Thanks.

Answer 1

Use --create or -C option

pg_dump --create -s -U postgres -d test1 > test1_only.sql

Begin the output with a command to create the database itself and reconnect to the created database. (With a script of this form, it doesn't matter which database in the destination installation you connect to before running the script.) If --clean is also specified, the script drops and recreates the target database before reconnecting to it.

http://www.postgresql.org/docs/current/static/app-pgdump.html