Best Practices for Using pg_dump

Description

pg_dump is a native tool for backing up a PostgreSQL database. The file created by pg_dump can be a SQL script file or an archive file. For details, see pg_dump.

• SQL script file: It is a plain-text file that contains the SQL commands required to rebuild a database to the state when it was backed up.
• Archive file: It must be used with pg_restore to rebuild a database. This format allows pg_restore to select the data to be restored.

Precautions

pg_dump dumps a single database, schemas, or tables. Only tables, data, and functions can be exported. Before restoration, you need to create a database and account in the target instance in advance.

• --format=custom: The dump file is in binary format, which can be used only by pg_restore. You can restore specific tables from a dump file.
• --format=plain: The dump file is in plain-text format. To restore from such a plain-text file, connect to the database and execute the file.

Constraints

Before using pg_dump and pg_restore, ensure that the versions of the source and target databases are the same to avoid compatibility issues. Incompatible versions may cause data loss or restoration errors.

Preparing Test Data

# Create a database.
create database dump_database;

# Log in to the database.
\c dump_database

# Create table 1 and insert data into the table.
create table dump_table(id int primary key, content char(50));
insert into dump_table values(1,'aa');
insert into dump_table values(2,'bb');

# Create table 2 and insert data into the table.
create table dump_table2(id int primary key, content char(50));
insert into dump_table2 values(1,'aaaa');
insert into dump_table2 values(2,'bbbb');

Using pg_dump to Export a Database to a SQL File

Syntax

pg_dump --username=<DB_USER> --host=<DB_IPADDRESS> --port=<DB_PORT> --format=plain --file=<BACKUP_FILE><DB_NAME>

• DB_USER indicates the database username.
• DB_IPADDRESS indicates the database address.
• DB_PORT indicates the database port.
• BACKUP_FILE indicates the name of the file to be exported.
• DB_NAME indicates the name of the database to be exported.
• --format indicates the format of the exported file. plain (default) indicates a plain-text file that contains SQL scripts. For details about other options, see pg_dump.

Examples

• Export a database to a SQL file (INSERT statements).

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --inserts --file=backup.sql dump_database
    Password for user root:

• Export all table schemas from a database to a SQL file.

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --schema-only --file=backup.sql dump_database
    Password for user root:

• Export all table data from a database to a SQL file.

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --data-only --file=backup.sql dump_database
    Password for user root:

After the commands in any of the above examples are executed, a backup.sql file will be generated as follows:

[rds@localhost ~]$ ll backup.sql
-rw-r----- 1 rds rds 5657 May 24 09:21 backup.sql

Using pg_dump to Export Specified Tables from a Database to a SQL File

Syntax

pg_dump --username=<DB_USER> --host=<DB_ADDRESS> --port=<DB_PORT> --format=plain --file=<BACKUP_FILE> <DB_NAME> --table=<TABLE_NAME>

• DB_USER indicates the database username.
• DB_ADDRESS indicates the database address.
• DB_PORT indicates the database port.
• BACKUP_FILE indicates the name of the file to be exported.
• DB_NAME indicates the name of the database to be migrated.
• TABLE_NAME indicates the name of the specified table in the database to be migrated.
• --format indicates the format of the exported file. plain (default) indicates a plain-text file that contains SQL scripts. For details about other options, see pg_dump.

Examples

• Export a single table from a database to a SQL file.

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=dump_table
    Password for user root

• Export multiple tables from a database to a SQL file.

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=dump_table --table=dump_table2
    Password for user root:

• Export all tables starting with ts_ from a database to a SQL file.

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=ts_*
    Password for user root:

• Export all tables excluding those starting with ts_ from a database to a SQL file.

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database -T=ts_*
    Password for user root:

After the commands in any of the above examples are executed, a backup.sql file will be generated as follows:

[rds@localhost ~]$ ll backup.sql
-rw-r----- 1 rds rds 5657 May 24 09:21 backup.sql

Using pg_dump to Export Data of a Specific Schema

Syntax

pg_dump --username=<DB_USER> --host=<DB_IPADDRESS> --port=<DB_PORT> --format=plain --schema=<SCHEMA> <DB_NAME> --table=<BACKUP_FILE>

• DB_USER indicates the database username.
• DB_IPADDRESS indicates the database address.
• DB_PORT indicates the database port.
• BACKUP_FILE indicates the name of the file to be exported.
• DB_NAME indicates the name of the database to be exported.
• SCHEMA indicates the name of the schema to be exported.
• --format indicates the format of the exported file. plain (default) indicates a plain-text file that contains SQL scripts. For details about other options, see pg_dump.

Examples

• Export all data of the public schema from a database.

  pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --schema=public  dump_database  > backup.sql

• Export all data except the public schema from a database in a customized compression format.

  pg_dump --username=root --host=192.168.61.143 --port=5432 --format=custom -b -v -N public dump_database > all_sch_except_pub.backup

Restoring Data

To restore from a plain-text SQL script file, run the psql command. See the following example commands:

# Restore a specific database.
psql --username=root --host=192.168.61.143 --port=5432  backup_database  < backup.sql

# Restore a specific table.
psql --username=root --host=192.168.61.143 --port=5432  backup_database --table=dump_table  < backup.sql

# Restore a specific schema.
psql --username=root --host=192.168.61.143 --port=5432  backup_database --schema=public  < backup.sql

Before the restoration, create a database named backup_database in the target database.

To restore from other file formats, use pg_restore. pg_restore is used to restore a PostgreSQL database in any non-plain-text format dumped by pg_dump.

pg_restore --username=root --host=192.168.61.143 --port=5432 --dbname=backup_database --format=custom all_sch_except_pub.backup --verbose

FAQ

1. What should I do if an error about insufficient permissions is reported for pg_dump?

   Solution:

   Check whether the root user is used to export data. If any other user account is used, an error about insufficient permissions will be reported. If the root user is used and an error is still reported, check the database version. You can run pg_dump commands as the root user only when the kernel version support root privilege escalation. For details about the kernel versions that support root privilege escalation, see Privileges of the root User.

2. Why an error was reported for functions such as control_extension after I imported a dump file to the target RDS for PostgreSQL database?

   Solution:

   That's because the target database contains these functions. This error can be ignored.