View a markdown version of this page

Importing a PostgreSQL database from an Amazon EC2 instance - Amazon Relational Database Service
Step 1: Create a file using pg_dump that contains the data to loadStep 2: Create the target DB instanceStep 3: Use psql to create the database on the DB instance and load dataStep 4: Create a DB snapshot of the DB instanceLoading the data into a database with a different name
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).

Importing a PostgreSQL database from an Amazon EC2 instance

If you have data in a PostgreSQL server on an Amazon EC2 instance and want to move it to a PostgreSQL DB instance, you can follow this process to migrate the data.

  1. Create a file using pg_dump that contains the data to be loaded

  2. Create the target DB instance

  3. Use psql to create the database on the DB instance and load the data

  4. Create a DB snapshot of the DB instance

These steps load the data into a database that has the same name as the source database. To load the data into a database with a different name, see Loading the data into a database with a different name.

The following sections provide more details on each step listed above.

Step 1: Create a file using pg_dump that contains the data to load

The pg_dump utility uses the COPY command to create a schema and data dump of a PostgreSQL database. The dump script generated by pg_dump loads data into a database with the same name and recreates the tables, indexes, and foreign keys.

Before you create the data dump, you should query the tables to be dumped to get a row count so you can confirm the count on the target DB instance.

The following command creates a dump file called mydb2dump.sql for a database called mydb2.

prompt>pg_dump dbname=mydb2 -f mydb2dump.sql

Step 2: Create the target DB instance

Create the target PostgreSQL DB instance using either the Amazon RDS console, Amazon CLI, or API. Create the instance with the backup retention setting set to 0 and disable Multi-AZ. These settings allow faster data import.

Step 3: Use psql to create the database on the DB instance and load data

You can use the same connection you used to run the pg_dump command to connect to the target DB instance and recreate the database. The database must have the same name as the source database, because the dump script from Step 1 loads the data into a database with that name. Using psql, you can use the master user name and master password to create the database on the DB instance.

First, create the database using createdb:

createdb --host [endpoint of instance] --port [port] --username [master username] mydb2

The following example uses psql and a dump file named mydb2dump.sql to create a database called mydb2 on a PostgreSQL DB instance called mypginstance:

For Linux, macOS, or Unix:

psql \
   -f mydb2dump.sql \
   --host mypginstance.555555555555.aws-region.rds.amazonaws.com \
   --port 8199 \
   --username myawsuser \
   --password password \
   --dbname mydb2

For Windows:

psql ^
   -f mydb2dump.sql ^
   --host mypginstance.555555555555.aws-region.rds.amazonaws.com ^
   --port 8199 ^
   --username myawsuser ^
   --password password ^
   --dbname mydb2
Note

Specify a password other than the prompt shown here as a security best practice.

Step 4: Create a DB snapshot of the DB instance

Once you have verified that the data was loaded into your DB instance, we recommend that you create a DB snapshot of the target PostgreSQL DB instance. DB snapshots are complete backups of your DB instance that can be used to restore your DB instance to a known state. A DB snapshot taken immediately after the load protects you from having to load the data again in case of a mishap. You can also use such a snapshot to seed new DB instances. For information about creating a DB snapshot, see Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS.

Loading the data into a database with a different name

A plain-text dump loads the data into a database with the same name as the source. To load the data into a database with a different name, create a custom-format dump and restore it with pg_restore.

In place of Step 1, create a custom-format dump. The -Fc option is required because pg_restore can't read a plain-text dump.

pg_dump -Fc -v --host [endpoint of instance] --username [master username] mydb2 > mydb2.dump

Create the target DB instance as described in Step 2.

In place of Step 3, create the renamed database and restore into it with pg_restore. The following example restores mydb2.dump into a database named mydb3.

createdb --host [endpoint of instance] --port [port] --username [master username] mydb3
pg_restore -v --host [endpoint of instance] --username [master username] --dbname mydb3 mydb2.dump

Then create a DB snapshot as described in Step 4.