I’m trying to stand up a Dev database for a web app. I have setup the Dev database using a snapshot from the current prod server. I’m currently trying to restore the dev server’s DB from a .dmp file I have via:
psql --host=blahblahblahblah.us-east-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname <dbname> -f backup.sql
Which gives me the error:
psql: error: connection to server at "awseb-e-gjem7uperk-stack-awsebrdsdatabase-1hzzq9r4a74c.cg7ksxih0nnz.us-east-1.rds.amazonaws.com" (3.225.21.170), port 5432 failed: Operation timed out
Is the server running on that host and accepting TCP/IP connections?
I believe I have followed all the recommended steps to debug.
-
Check that the host name used is the DB instance endpoint and that the port number used is correct – yes, they are correct. I copied directly from the RDS Connectivity & security page.
-
Make sure that the DB instance’s public accessibility is set to Yes:
-
Make sure that the user connecting to the database has CONNECT access to it – (THIS ONE I’M NOT SURE ABOUT How can I do this if I can’t connect?)
-
Check that the security group assigned to the DB instance has rules to allow access – I have access to everybody. I know it’s not best practice, but I’m trying to get it to work right now
Any ideas what is causing me to not connect?
EDIT:
You can see my VPC/Internet gateway connection here:
See subnet connected to VPC group:
I also added a new group to allow all traffic, but still not working:
See subnet routing here:
See Routes here:
2
Answers
Ok, so the problem was my subnets. This was the answer from an AWS re:Post form:
By the looks of the screenshots in your stackoverflow post, the instance has been recovered into a subnet group with 12 subnets. But the routing table only has six subnets associated with it.
I did this by going to my VPC -> the routing table -> Subnet Association Tab -> Edit Subnet Association -> add required subnets (I just added them all)
Please make sure you have Internet Gateway attached to your VPC.