Connect to Postgresql database from another EC2 instance

Issue Summary

I am not super skilled with docker.

Using older versions of Redash (such as 0.12.0+b2449 ), I was able to connect to the Redash metadata database in Postgresql from another EC2 instance. We have a need to sometimes query the Redash database to pull data out of the queries table.

I’m now running Redash Version: 8.0.0+b32245 and from another EC2 instance I’m receiving the “psql: could not connect to server: Connection refused” message:

# psql -h [redash-ec2-instance-private-ip-address-here]
psql: could not connect to server: Connection refused
Is the server running on host "[redash-ec2-instance-private-ip-address-here]" and accepting
TCP/IP connections on port 5432?

What I am trying to figure out how to do is connect to the Postgresql service running within docker from another EC2 instance. I think this has to do more with docker than Redash, and just understanding how to configure/expose the Postgresql service/port to other EC2 instances.

$ docker ps
ba01fa910376        postgres:9.6-alpine          "docker-entrypoint.s…"   3 weeks ago         Up 3 weeks          5432/tcp                      redash_postgres_1

Another option would be to host the Redash database on RDS, but this is a QA environment so I’m looking to avoid the additional compute cost.

Any ideas? I really appreciate any help.

Technical details:

  • Redash Version: Version: 8.0.0+b32245
  • Browser/OS: Ubuntu 18.04.3 LTS (GNU/Linux 4.15.0-1054-aws x86_64)
  • How did you install Redash: Used your us-east-1 AMI “redash-8.0.0-b32245-1-us-east-1 (ami-0d915a031cabac0e0)”

I think my issue might be similar to Solved: Postgres connection: a very basic (probably) stupid problem posted by @dmudro and referenced by @arikfr but im not 100% certain…

Ok, I’m learning more. What I believe I need to do is port forward 5432 of the host instance to the container port 5432 so that

5432/tcp                      redash_postgres_1

is changed to

0.0.0.0:5432->5432/tcp        redash_postgres_1

It seems I need to recreate the container in order to port forward, such as:

$ docker stop redash_postgres_1
$ docker commit redash_postgres_1 postgres:9.6-alpine
$ docker rm redash_postgres_1
$ docker run -d -P 5432:5432 --name redash_postgres_1 postgres:9.6-alpine

But prior to doing this, do a pg_dump so that I do not lose my database and can restore data to Postgresql running un the new container.

Does this sound about right? Any help is appreciated. Thank you!

Referenced:

Open to suggestions, but for now worked around this by moving the redash database to rds.