PostgreSQL – Backing up and restoring one or more tables using pg_dump and pg_restore

If you take a backup in plain format (.sql file), we can use psql to restore.

If you take a backup in a custom format (tar/dir/binary), the only option to restore is pg_restore.

pg_restore is 10x faster than psql.

When you do a backup using pg_dump, it will not apply any locks; hence, it will not cause any waits to the database traffic.

There are a few things to note before using pg_dump and planning your backup strategy:

• Backing up multiple databases is not possible using pg_dump. It can only backup one database at a time.
• pg_dump is not suitable for point-in-time recovery. To do a point-in-time recovery, WAL segments need to be replayed, but it will not be possible to replay transaction logs (WALs) after restoring backups taken using pg_dump.
• To ensure that all the data from the disk is readable and have no corrupted pages, it is important to have a periodical logical backup testing.

Backing up a single or several tables from a database is possible using pg_dump. However, backing up a table from one database and another table from another database simultaneously is not possible. pg_dump can only work on one database at a time. Furthermore, taking a backup of an entire database excluding one or more tables is also possible.

We shall use the flag -t to backup and restore a table.

Syntax for backing up a table:

pg_dump -h source_server -p source_port -U  database_name -Fformat -t tablename -f /location/dumpfile_name.bin

Syntax to restore a custom format dump:

pg_restore -h target_server -p target_port -U username -d database_name -t tablename /backup_location/dumpfile_name.bin

Syntax to restore a plain format dump:

psql -h target_server -p target_port -U username -d mypgdb -f /backup_dir/tables.sql

Example: Backing up one or more tables

1. In the example below, we are going to backup employees and salary tables.

Custom format:

pg_dump -t employees -t salary -Fc -f /postgres/backups/employees_salary.bin -v

Plain Format:

pg_dump -t employees -t salary -f /postgres/backups/employees_salary.sql -v

2. If you want to restore these tables to another database in a target server, then create the database first. You may ignore this step if the database already exists.

psql -c "CREATE DATABASE perf_review"

3. Now, we are going to restore the two tables from backup to another database called perf_review:

Custom format:

pg_restore -p5432 -U postgres -t employees -t salary -d perf_review /postgres/backups/employees_salary.bin -v

Plain Format:

psql -p5432 -U postgres -t employees -t salary -d perf_review -f /postgres/backups/employees_salary.sql

Cheers!