# Importing a PostgreSQL database from an Amazon EC2 instance
<a name="PostgreSQL.Procedural.Importing.EC2"></a>

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

1. Create the target DB instance

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

1. 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](#PostgreSQL.Procedural.Importing.EC2.Option2).

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
<a name="PostgreSQL.Procedural.Importing.EC2.Step1"></a>

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
<a name="PostgreSQL.Procedural.Importing.EC2.Step2"></a>

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
<a name="PostgreSQL.Procedural.Importing.EC2.Step3"></a>

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
<a name="PostgreSQL.Procedural.Importing.EC2.Step4"></a>

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](USER_CreateSnapshot.md).

## Loading the data into a database with a different name
<a name="PostgreSQL.Procedural.Importing.EC2.Option2"></a>

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.