Amazon RDS PostgreSQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the PostgreSQL database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database.

Below are the steps through which the PostgreSQL data migration happens using the export and import mechanisms.

Importing from an Amazon EC2 Instance

When there is a PostgreSQL server on an Amazon EC2 instance and it needs to be moved to a RDS – PostgreSQL DB instance, we use the below steps to do that.

Export The Data

Create a file using pg_dump that contains the data to be loaded. A dump file containing data and all the meta data of the database is created using the pg_dump utility. The following command in the psql utility cerates the dump file from the database named mydbname.

pg_dump dbname=mydbname -f mydbnamedump.sql 

Create Target DB Instance

Next, we create the target DB instance and restore the data into it using the pg_restore command.

createdb [new database name]
pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump 

Create Target Database

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

psql \
   -f mydbnamedump.sql \
   --host awsdbpginstance.d34f4mnfggv0.us-west-2.rds.amazonaws.com \
   --port 8199 \
   --username awsdbuser \
   --password awsdbpassword \
   --dbname mynewdb