0

I have a postgres db that I have been able to dump and restore to other postgres installs without issue using:

pg_dump the_db_name > the_backup.sql

Then copy the backup to your development server, restore with:

psql the_new_dev_db < the_backup.sql

h/t to this link for that gem.

I've also been able to setup and configure ec2, s3 and eb (elastic beanstalk) installs for wordpress, ruby & node js needs. However, I'm staring into the abyss trying to import a postgres db into an amazon rds instance. I suspect this is not a psql problem but a security one. Any suggestions on what to try I'd greatly appreciate it.

I connect to aws using a .pem file.

I can configure pgAdmin to use an ssh tunnel to connect to my rds instance.

I cannot connect to my postgres db on AWS using psql nor can I configure pgAdmin to connect to my db in the way AWS recommends.

In both of these cases I get the error:

psql: could not connect to server: Connection timed out
    Is the server running on host "xxxxxxx.us-west-2.rds.amazonaws.com" (xx.xx.xx.xx) and accepting
    TCP/IP connections on port 5432?

In the rds dashboard I see my instance is part of two security groups. When I click on one it says: 

Your account does not support the EC2-Classic Platform in this region. DB Security Groups are only needed when the EC2-Classic Platform is supported. Instead, use VPC Security Groups to control access to your DB Instances. Go to the EC2 Console to view and manage your VPC Security Groups. For more information, see AWS Documentation on Supported Platforms and Using RDS in VPC

When I click on the other Security Group and go to its Inbound tab I see there are two references that show Postgresql is receiving TCP on port 5432

Also in the RDS dashboard I see I have a VPC ID, but after reading this article, I'm not sure which way to go because there is no field for "Supported Platforms."

If I try to do a restore from the working config in pgAdmin I get a similar message as the connection one:

pg_restore: [archiver (db)] connection to database "ebdb" failed: could not connect to server: Connection timed out
    Is the server running on host "XXXX.us-west-2.rds.amazonaws.com" (54.148.0.217) and accepting
    TCP/IP connections on port 5432?

Finally, I've tried to bring up the PSQL console in the db in pgAdmin. It comes up and I can run psql commands like:

ebdb=> \conninfo
You are connected to database "ebdb" as user "awsuser" on host "127.0.0.1" at port "34883".
SSL connection (protocol: TLSv1.2, cipher: XXXX, bits: 256, compression: off)

However, if I try to run pg_restore here using this command: 

ebdb=> \! /usr/bin/pg_restore --dbname "ebdb" --section pre-data --section data --section post-data --create --clean --verbose "/home/awsuser/040517.backup"

it does not create any tables or push in any data. The console lites up and flashes all the SQL commands and the data as though it worked, but when I go back into pgAdmin and look it is still empty.

Community
  • 1
  • 1
edswartz
  • 491
  • 3
  • 13

1 Answers1

0

I was able to get the db imported with the above pg_restore command by adding the security group of the elastic beanstalk server as a source for my postgres inbound traffic.

EC2 console\security groups\inbound I had PostgresQL and port 5432 but needed to add the security group sg-xxxx of the eb server into the source field.

On to new errors!

edswartz
  • 491
  • 3
  • 13