pg_dump

The pg_dump utility dumps the contents of the database in a file. Used without any options, it will use the default settings and dump the default database. In the following example, we will use the –C option. This option will generate the output with the command CREATE DATABASE so that we know which database was dumped:

# pg_dump -C > /tmp/a.sql
# grep "CREATE DATABASE" /tmp/a.sql | cut -f1-3 -d" "
CREATE DATABASE postgres

We will do the same for another database; use the –d option to specify the database and inspect the output. We can see that the database dumped was test:

pg_dump -C -d  test > /tmp/b.sql

The output file contains a command to set different values (such as statement_timeout and lock_timeout) followed by the CREATE DATABASE command because we used the –C option followed by SQL to create various database objects and then COPY commands to populate the tables with data. The file also contains commands to reset the sequences so that we don't have to worry about this...