How to get a pg_dump -s to include the CREATE DATABASE command?
backup, postgresql, postgresql-9.2
Solution
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
Problem
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.