pg_dump and pg_restore - Database Migration Guide
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

pg_dump and pg_restore

pg_dump and pg_restore is a native PostgreSQL client utility. You can find this utility as part of the database installation. It produces a set of SQL statements that you can run to reproduce the original database object definitions and table data.

The pg_dump and pg_restore utility is suitable for the following use cases if:

  • Your database size is less than 100 GB.

  • You plan to migrate database metadata as well as table data.

  • You have a relatively large number of tables to migrate.

The pg_dump and pg_restore utility may not be suitable for the following use cases if:

  • Your database size is greater than 100 GB.

  • You want to avoid downtime.

Example

At a high level, you can use the following steps to migrate the dms_sample database.

  1. Export data to one or more dump files.

  2. Create a target database.

  3. Import the dump file or files.

  4. (Optional) Migrate database roles and users.

Export Data

You can use the following command to create dump files for your source database.

pg_dump -h <hostname> -p 5432 -U <username> -Fc -b -v -f <dumpfilelocation.sql> -d  <database_name>

-h is the name of source server where you would like to migrate your database.
-U is the name of the user present on the source server
-Fc: Sets the output as a custom-format archive suitable for input into pg_restore.
-b: Include large objects in the dump.
-v: Specifies verbose mode
-f: Dump file path

Create a Database on Your Target Instance

First, login to your target database server.

psql -h <hostname> -p 5432 -U <username> -d <database_name>

-h is the name of target server where you would like to migrate your database.
-U is the name of the user present on the target server.
-d is the name of database name present on target already.

Then, use the following command to create a database.

create database migrated_database;

Import Dump Files

You can use the following command to import the dump file into your Amazon RDS instance.

pg_restore -v -h <hostname> -U <username> -d <database_name> -j 2 <dumpfilelocation.sql>

-h is the name of target server where you would like to migrate your database.
-U is the name of the user present on the target server.
-d is the name of database name that was created in step 2.
<dumpfilelocation.sql> is the dump file that was created to generate the script of the database using pg_dump

Migrate Database Roles and Users

To export such database objects as roles and users, you can use the pg_dumpall utility.

To generate a script for users and roles, run the following command on the source database.

pg_dumpall -U <username> -h <hostname>  -f <dumpfilelocation.sql> --no-role-passwords -g


-h is the name of source server where you would like to migrate your database.
-U is the name of the user present on the source server.
-f: Dump file path.
-g: Dump only global objects (roles and tablespaces), no databases.

To restore users and roles, run the following command on your target database.

psql -h <hostname> -U <username> -f <dumpfilelocation.sql>

-h is the name of target server where you would like to migrate your database.
-U is the name of the user present on the target server.
-f: Dump file path.

To complete the export and import operations, the pg_dump and pg_restore requires some time. This time depends on the following parameters.

  • The size of your source database.

  • The number of jobs.

  • The resources that you provision for your instance used to invoke pg_dump and pg_restore.