Partial database exports (Simple)

Backups are not limited to the whole running instance. Each database can be dumped individually with the pg_dump utility.

Getting ready

Please refer to the Getting a basic export (Simple) recipe on preparing a sample database.

How to do it...

This time, we will need to execute the following commands on the command line:

1. First, type the following command to obtain global objects such as users, groups, and passwords:

   $> pg_dumpall -g -f globals.sql
   

2. Next, this command will create a sample database:

   $> pg_dump -f sample_backup.sql sample
   

How it works...

This took a bit more effort, but not much. Because the pg_dump utility can only back up one database at a time, we don't get global objects such as users and groups. Thus we must also use pg_dumpall if we want to restore with the same users and groups.

But what about the SQL dump itself? Just like pg_dumpall, pg_dump uses the -f switch to send output to a named file. The last parameter is a positional parameter. Most PostgreSQL tools are set up to assume the last parameter without a flag is actually a database name. In this case, our sample database is what we are exporting to SQL.

There's more...

Why do we even need pg_dump if it can only back up one database at a time? It seems silly at first, but by doing so, we unlock several additional capabilities, not the least of which is the ability to restore a database independently of its original name. There are also significant improvements and several new command-line options.

Compressed exports

Unlike pg_dumpall, which could not compress backup output, pg_dump makes it quite simple by using the following command:

$> pg_dump -Fc -f sample_backup.pgr sample

The -F switch changes the output format. In this case, we chose c for custom output. The PostgreSQL custom output format is a proprietary compressed export that you will not be able to read, but requires much less space than the default SQL output. The restore tool actually prefers this format, and requires it for advanced options such as parallel database restore, which we will be discussing later.

Table-only exports

Not only can we restrict a backup to a single database, but pg_dump also provides an option to back up one or more tables. Our sample database contains a pgbench_accounts table. Let's export this table by itself with the following command:

$> pg_dump -t pgbench_accounts -f accounts_backup.sql sample

Exporting individual tables means they can also be restored in other databases or archived for later. We can also use the -t switch as often as we like, keeping several related tables together. However, keep in mind that getting a complete list of related tables is often difficult. Views, triggers, stored procedures, and other related objects may also be necessary to retain full functionality of these objects upon restore. When you use this option, you only get the objects you requested, and nothing else.

Schema-only exports

As with tables, schemas themselves (collections of related objects) can be exported. Our sample database only has the public schema, which we can export with the -n switch, as shown in the following command:

$> pg_dump -n public -f public_namespace_backup.sql sample

Larger instances sometimes have schemas for each application or client. With the option to export these separately, they can be moved between databases, backed up or restored independently of the entire database, or archived.

Data and schema-only exports

Tables, views, and other objects contained in the schema can also be exported with or without the data. Perhaps we want to track schema changes, for example, as shown in the following command:

$> pg_dump -s -f schema_backup.sql sample

The opposite is also true. We may not need or want the schema definitions. The -a flag gives us only table data using the following command:

$> pg_dump -a -f data_backup.sql sample

Again, remember that performing an export of a single object may lose a lot of dependent elements (for example, views). Don't use the single object export options if you need this information together.

Either of these options can be combined with table or schema exports. Let's grab only the data for the pgbench_branches table.

$> pg_dump -a -t pgbench_branches -f branch_data.sql sample