What are the steps to migrate a local postgres database to a postgres instance on aws rds?

-1

I have a 4GB local postgres database that I want to migrate to an AWS RDS database instance.

Here is what I have done:

  1. On the aws rds console, I created a database instance called "testdatabase"

  2. I added my IP address to a security group to allow for inbounding traffic.

  3. I dumped my database to a file using the following command:

pg_dump -U postgres --dbname=TestDatabase -f C:\Users\lucag\Documents\Work\Data\DatabaseDumps\Descriptive_Dumps\rds\rds_initial.sql

  1. I then try to use psql to push this dump to the aws rds database instance I have created

psql -f C:\Users\lucag\Documents\Work\Data\DatabaseDumps\Descriptive_Dumps\rds\rds_initial.sql --host testdatabase.xxxxxxxxxxx.us-east-2.rds.amazonaws.com --port 5432 --username postgres --password secretpassword --dbname testdatabase

When I try to run this last command, it first tells me that "extra command line arguments: '--dbname' and 'testdatabase'" will be ignored. Then it spits out this error:

psql: error: could not connect to server: could not connect to server: Connection timed out (0x0000274C/10060)
        Is the server running on host "testdatabase.xxxxxxxxxxx.us-east-2.rds.amazonaws.com" (172.31.2.110) and accepting
        TCP/IP connections on port 5432?

I do not understand this error because I would have thought that my step 2 would resolve it. In fact the security group is set up to allow all types of traffic, all protocols, and all ports from my specific IP address.

What am I missing?

postgresql
amazon-web-services
amazon-rds
asked on Stack Overflow Dec 14, 2020 by Luca Guarro • edited Dec 14, 2020 by Luca Guarro

1 Answer

0

In order to migrate to RDS you can use the AWS Database Migration Service (DMS) which takes care of a lot of the heavy lifting for you.

This article walks you through the steps for migrating an on-premises PostgreSQL database to Amazon RDS using AWS DMS: Migrate an on-premises PostgreSQL database to Amazon RDS for PostgreSQL

answered on Stack Overflow Dec 14, 2020 by Dennis Traub

User contributions licensed under CC BY-SA 3.0