In the chapter on data importing techniques, I mentioned that the built-in PostgreSQL backup utilities can also be used as data transfer tools. It does, of course, depend on the scenario we are working on, but in many cases passing data as a database backup file may be a valid and reliable solution.

pg_dump is the utility that provides the database backup functionality. In order to get some help with it, type the following in the command line:

pg_dump --help

A great thing about pg_dump is that it allows for great flexibility when backing up a database. One can dump a whole database, a single schema, multiple schemas, a single table, or multiple tables; it is even possible to exclude specified schemas or tables.

This approach is great, for example, for scenarios where the main database should remain within a private network and a set of automated tasks performs the database backup, file transfer, and database restore on a web-exposed database server.

A basic example of backing up a whole database could look like this:

pg_dump -h localhost -p 5434 -U postgres -c -F c -v -b -f mastering_postgis.backup mastering_postgis

Backing up a schema is very similar:

pg_dump -h localhost -p 5434 -U postgres -c -F c -v -b -n data_import -f data_import_schema.backup mastering_postgis

A single table is backed up like this:

pg_dump -h localhost -p 5434 -U postgres -t data_import.earthquakes_subset_with_geom -c -F c -v -b -f earthquakes_subset_with_geom.backup mastering_postgis