Database Reference
In-Depth Information
How it works...
pg_dump
allows you to specify more than one table on the command line, so it's possible to
generate a list of tables directly from the database.
We use the named parameter in psql to create a parameterized script, which we then execute
to create a dump.
Backup of database object definitions
Sometimes it's useful to get a dump of the object definitions that make up a database.
This is useful for comparing what's in the database against the definitions in a data or
object-modeling tool. It's also useful to make sure you can recreate objects in exactly the
correct schema, tablespace, and database with the correct ownership and permissions.
How to do it...
The basic command to dump the definitions only is to use the following:
pg_dumpall --schema-only > myscriptdump.sql
Which includes all objects, including roles, tablespaces, databases, schemas, tables, indexes,
triggers, constraints, views, functions, ownership, and privileges.
If you want to dump PostgreSQL role definitions, you can use the following:
pg_dumpall --roles-only > myroles.sql
If you want to dump PostgreSQL tablespace definitions, you can use the following:
pg_dumpall --tablespaces-only > mytablespaces.sql
Or if you want to dump both roles and tablespaces, then you can use the following:
pg_dumpall --globals-only > myglobals.sql
The output is a human-readable script file that can be re-executed to re-create each
of the databases.
There's more...
In PostgreSQL, the word "schema" is also used to describe a set of related database objects
similar to a directory, also known as a "namespace". Be careful that you don't confuse what
is happening here. The
--schema-only
option makes a backup of the "database schema" -
the definitions of all objects in the database (and in all namespaces). To make a backup of the
data and definitions in just one namespace, use the -s option. Or, to make a backup of only
the definitions, in just one namespace, use both -s and --schema-only together.
