What is the pg_dump command for backing up a PostgreSQL database?

1 What is pg_dump?

Pg_dump is a command-line utility that allows you to export a PostgreSQL database to a file or a standard output stream. You can use pg_dump to create a plain text, custom, or directory format backup of your database. A plain text backup is a SQL script that can be executed to restore the database. A custom backup is a compressed binary file that can be manipulated by other PostgreSQL tools. A directory backup is a collection of files that can be restored in parallel.

3 How to use pg_dump for a full backup?

A full backup is a backup that contains all the objects and data of a database. To create a full backup with pg_dump, you can use the following syntax: pg_dump -U username -F format -f filename database The -U option specifies the username to connect to the server. The -F option specifies the output format, which can be p (plain text), c (custom), or d (directory). The -f option specifies the output file name, or - for standard output. The database argument is the name of the database to back up.

For example, to create a full backup of the sales database in custom format and save it to sales.bak, you can run: pg_dump -U postgres -F c -f sales.bak sales

4 How to use pg_dump for a partial backup?

A partial backup is a backup that contains only a subset of objects or data of a database. To create a partial backup with pg_dump, you can use various options to filter what you want to include or exclude from the backup. Some of the most useful options are:

- -t table or --table=table: to back up only a specific table or a list of tables separated by commas

- -T table or --exclude-table=table: to exclude a specific table or a list of tables from the backup

- -n schema or --schema=schema: to back up only a specific schema or a list of schemas separated by commas

- -N schema or --exclude-schema=schema: to exclude a specific schema or a list of schemas from the backup

- -a or --data-only: to back up only the data, not the schema

- -s or --schema-only: to back up only the schema, not the data

For example, to create a partial backup of the sales database in plain text format and save it to sales.sql, but exclude the customers and orders tables, you can run: pg_dump -U postgres -F p -f sales.sql -T customers,orders sales

5 How to restore a backup created by pg_dump?

To restore a backup created by pg_dump, you can use different tools depending on the output format of the backup. If the backup is in plain text format, you can use the psql command to execute the SQL script. If the backup is in custom or directory format, you can use the pg_restore command to restore the backup to a database or a file. Both tools have various options to control the restoration process, such as:

- -d database or --dbname=database: to specify the name of the database to restore to

- -f file or --file=file: to specify the name of the file to restore from or to

- -l or --list: to list the contents of the backup

- -t table or --table=table: to restore only a specific table or a list of tables separated by commas

- -T table or --exclude-table=table: to exclude a specific table or a list of tables from the restoration

- -a or --data-only: to restore only the data, not the schema

- -s or --schema-only: to restore only the schema, not the data

For example, to restore a full backup of the sales database in custom format from sales.bak, you can run: pg_restore -U postgres -d sales sales.bak

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?