Creating Basic Backups with pg_dump

pg_dump is a command-line tool provided by PostgreSQL that lets you export a database into a backup file. This file can include just the database structure, just the data, or both.

The main advantage of pg_dump is that it lets you move data between servers, restore your database after a crash, or just make an archive for long-term storage. Just a heads up: this tool is more for logical backups (using SQL queries) than for physically copying database files.

Basic pg_dump Command Syntax

To make a backup with pg_dump, just run this command in your terminal:

pg_dump -U username -d database_name -f backup_file.sql

Let’s break down the command:

• -U username — sets the PostgreSQL username used to connect to the database.
• -d database_name — specifies the name of the database you want to back up.
• -f backup_file.sql — tells pg_dump where to save the backup file. This can be a full path or a relative one.

Example: let’s say you have a database called university and you want to back it up to a file called university_backup.sql. The command would look like this:

pg_dump -U postgres -d university -f university_backup.sql

PostgreSQL will ask for the password for the postgres user to allow access to the data.

Tip: If you’re working with a local server and don’t want to type your password every time, set up a .pgpass file. Check out the official PostgreSQL docs for more info.

Options and How to Use Them

The pg_dump utility is super flexible and can be tweaked for different backup setups:

1. Backing Up Just the Database Structure

If you only want to save the database structure (like to spin it up on a new server), use the --schema-only flag. Example:

pg_dump -U postgres -d university --schema-only -f university_schema.sql

This will make a SQL script with just CREATE TABLE, CREATE INDEX, etc., but no data.

2. Backing Up Just the Data

Sometimes you might want to save just the data, not the structure. In that case, use the --data-only flag:

pg_dump -U postgres -d university --data-only -f university_data.sql

The resulting file will have INSERT INTO commands to restore your data.

3. Compressing the Backup

The backup file can get pretty big, especially if your database has a lot of tables and data. To shrink the file size, use the --compress option:

pg_dump -U postgres -d university -f university_backup.sql --compress=6

The compression level goes from 0 (no compression) to 9 (max compression). Keep in mind, higher compression takes more time to make the backup.

4. Exporting in Binary Format

If you want a faster way to restore, you can save the backup in binary format instead of a text SQL script:

pg_dump -U postgres -d university -Fc -f university_backup.dump

The -Fc flag sets the Custom format, which you can restore with the pg_restore tool.

Backup Examples

Full Database Backup

Let’s say you have a database called company and you want to make a full backup, including both structure and data:

pg_dump -U postgres -d company -f company_backup.sql

Backing Up a Single Table

If you only need to save one table from the database, use the -t option:

pg_dump -U postgres -d company -t employees -f employees_backup.sql

This file will have the structure and data just from the employees table.

Backing Up Multiple Tables

To back up several tables, add multiple -t options:

pg_dump -U postgres -d company -t employees -t departments -f selected_tables_backup.sql

Compressed Backup

To save space, use the --compress flag or just make the file in compressed format right away:

pg_dump -U postgres -d company -Fc -f company_backup_compressed.dump

Handy Tips

Check your backup file size. If the backup is super small, something might have gone wrong. Use the --verbose flag to get more details about the backup process.

Automate the process. Use cron or pg_cron to set up regular backups. Don’t forget to rotate old backups so you don’t run out of disk space.

Test your backups. Try restoring your backups on a test server to make sure they actually work.

Common Errors You Might Run Into

Authentication error. If you didn’t set the right user or forgot the password, pg_dump will throw an error. Make sure you set up your .pgpass file for automatic login.

Connection error. If the PostgreSQL server isn’t running or you typed the database name wrong, the tool won’t be able to make a backup. Double-check the database name and server status.

Permission issues. The user you run pg_dump as needs to have read access to the database structure and data. Use an admin role if you can.