-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?

Luca Guarro
  • 1,085
  • 1
  • 11
  • 25
  • 1
    I’m voting to close this question because it belongs on another site in the Stack Exchange network: http://DBA.StackExchange.com – Basil Bourque Dec 14 '20 at 20:44
  • [How do I specify a password to 'psql' non-interactively?](https://stackoverflow.com/q/6405127/7635569) – JGH Dec 14 '20 at 20:47
  • 1
    Is your RDS instance setup as "Publicly Accessible"? If not, it won't be available from outside the VPC. If you search for how to connect to an RDS instance from outside a VPC you will find tons of help, including many duplicate questions on this website. – Mark B Dec 14 '20 at 20:56
  • That seemed to be the issue. I was also running into issues with the database name thinking that it was the name of the database instance. So I had to use the default postgres database – Luca Guarro Dec 14 '20 at 21:14

1 Answers1

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

Dennis Traub
  • 50,557
  • 7
  • 93
  • 108