2

I am trying to create a Telegram bot using Heroku and Postgres but I got stuck at connecting to the Heroku Postgres from my local. Here's what I have done so far:

1) Created a Heroku project with the Postgres add-on.

2) I read the Heroku Postgres documentation (https://devcenter.heroku.com/articles/heroku-postgresql) and set up the Heroku CLI and PostgreSQL 11.

3) I followed this tutorial (https://www.youtube.com/watch?v=f76zBj_M5JY) about connecting to Heroku using pgAdmin 4.

The error I received was "could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "ec2-107-22-253-158.compute-1.amazonaws.com" (107.22.253.158) and accepting TCP/IP connections on port 5432?"

4) I googled the error. https://devcenter.heroku.com/articles/heroku-postgresql#external-connections-ingress had the most likely solution. I typed in the commands in Windows Powershell, and I realized the credentials I obtained through this method is exactly the same as the ones I obtained from https://data.heroku.com/ under project > settings > credentials. I have already done this in step 3.

5) https://dba.stackexchange.com/questions/21869/connecting-pgadmin3-to-postgres-on-heroku mentions that the Maintenance Database value has to be the name of the database I want to connect to. I have already done that in step 3.

6) pgAdmin 4 Query Tool not connecting to database did not have a solution to my case, although his/her problem seems to be similar.

At this point, most of the solutions available by googling seem to be the same and I have already tried all these. I have also tried the psql CLI and got the same error as the one in pgAdmin. As I am new to both Heroku and Postgres, any help would be much appreciated!

Nelson Lim
  • 21
  • 2
  • did you add your local system ip in the pg_hba.conf file, try pinging your server from local machine. Also check your vpc if your local instance has access to the Ec2 server. – Lohit Gupta Jan 29 '20 at 17:49
  • a quick and dirty trick I use sometimes to establish if a postgres db is alive (and at least ready to authenticate me) is to `telnet hostname 5432` if postgres is alive will answer something like: `Trying ip_address... Connected to hostname.`: if it answers then you should investigate pg_hba and authentication, if it doesn't then its a network problem. This will not sort out your problem entirely but should point you in the right direction Good Luck – Edoardo Jan 29 '20 at 18:08
  • Thanks for the quick replies! I added my ip address to pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 host all all (my ip address) md5 I also checked postgresql.conf and ensured that listen_addresses = '*'. When I tried connecting to my local, I get this error: Unable to connect to server: timeout expired. I even set the connection timeout to 9999 seconds but I still get this. Also, I'm using Windows Home if that might affect my issue. – Nelson Lim Jan 30 '20 at 06:13
  • Just curious, where you trying by any change to connect through a VPN? The error was driving me crazy and disconnecting from the VPN fixed it for me – gmm Nov 10 '22 at 14:58

0 Answers0