Setting Up Backup and Recovery

Creating backups with pg_dump

The pg_dump utility is a command-line utility that creates a logical backup of a PostgreSQL database. To create a backup using it, we need to open a terminal and run the following command:

In the command above, database_name is the name of the database we want to back up, and backup_file.sql is the name of the file containing the backup.

An example of how to use this command to create a backup in tar format is as follows:

The command above uses the following options:

• -U postgres: This specifies the PostgreSQL user account to use for the backup process.

• -F t: This specifies the format of the backup file, in this case, the tar format.

• -f backupfile.tar: This specifies the name of the backup file to create.

• database_name: This is the name of the database to be backed up.

The pg_dump command is typically run from a shell prompt, not from within the SQL console or a psql session. The output is usually redirected to a file, as mentioned above. But for demonstration purposes, we can use the \! meta-command to execute a shell command from within psql, as shown below: ...