Best Practices for Using pg_dump

Description

pg_dump is a native backup tool of PostgreSQL. The backup file generated by pg_dump can be a SQL script file or an archive file. For details, see pg_dump.

• SQL script file: a plain-text file that contains SQL commands for rebuilding the database to the state when it was backed up.
• Archive file: It must be used with pg_restore to rebuild the 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. To restore data, you need to create the desired database and user in the destination instance in advance.

• --format=custom: The backup is in binary format, which can be restored only using pg_restore. The table to be restored can be specified.
• --format=plain: The backup is in text format, which can be restored by directly connecting to the corresponding database.

Constraints

When using pg_dump and pg_restore to back up and restore data, ensure that the versions of the source and destination databases are the same to avoid compatibility issues. If the versions are different, data may be lost or cannot be restored correctly.

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:

• Exporting 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 other than 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 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 the data exported using a plain-text SQL script file, run the psql command. For example:

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

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

# Restore a 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 destination database.

To restore the data exported in other 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 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 backup file to the destination RDS for PostgreSQL database?

   Solution:

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