Database Administration - PostgreSQL: Up and Running - page 39

To create a compressed backup of tables whose names start with “pay” in any schema:
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay .backup mydb
To create a compressed backup of all objects in the hr and payroll schemas:
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr .back-
up mydb
To create a compressed backup of all objects in all schemas, excluding the public
schema:
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_ex
cept_pub .backup mydb
To create a plain-text SQL backup of select tables, useful for porting structure and data
to lower versions of PostgreSQL or non-PostgreSQL databases (plain text generates a
SQL script that you can run on any system that speaks SQL):
pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts -f se
lect_tables .backup mydb
If your file paths contain spaces or other characters that could con‐
fuse the command-line shell, wrap the file path in double quotes: " /
path with spaces/mydb.backup " . As a general rule, you can al‐
ways use double quotes if you aren't sure.
The directory format option was introduced in version 9.1. This option backs up each
table as a separate file in a folder and gets around potential limitations of file size in your
filesystem. This option is the only pg_dump backup format option that generates multiple
files, as shown in Example 2-8 . It creates a new directory and populates it with a gzipped
file for each table, together with a file that lists all the included structures. The command
will exit with an error if the directory already exists.
Example 2-8. Directory format backup
pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb
A parallel backup option was introduced in version 9.3 with the --jobs ( -j ) option.
Setting this to --jobs=3 runs three backups in parallel. The parallel backup option
makes sense only with the directory format option, because each parallel write must
write to a separate file. Example 2-9 demonstrates its use.
Example 2-9. Directory format parallel backup
pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb