Trying to connect to postgresql on droplet programmatically via python and pg8000/psycopg2

Question

My database is hosted on my droplet. I am trying to connect to the database and insert some records programmatically from python, from my dev machine (Windows).

Here is my connect attempt via code. I am running this from my dev machine on Windows.

prod_conn = pg8000.connect(
    host='xxxx',
    port=5432,
    database='xxxx',
    user='xxxx',
    password='xx'
)

Telnet to the ip, psql to the ip, pg8000.connect , psycopg2.connect , all give the same error.

connection to server at "xxxx", port 5432 failed: Connection timed out (0x0000274C/10060)
Is the server running on that host and accepting TCP/IP connection?

my ip address is allowed from firewall. pg_hba.conf has my dev ip mentioned as host all all ip_address/32 md5 postgresql.conf has listen_addresses = ‘*’ so what can I do to connect via a program?

I am able to connect to the database via ssh tunnel from my dev machine. I also checked the firewall of the droplet. There are no rules set there. Do I have to explicitly set it to allow connection to 5432? Any advice is appreciated.

Bobby · Answer

Hey! If SSH tunneling works but direct connection times out, it’s most likely a network block, not a Postgres config issue. First, run this on your Droplet: sudo netstat -plant | grep 5432 You should see something like: tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1234/postgres That means Postgres is listening on all interfaces. If you only see 127.0.0.1:5432, then it’s only listening on localhost. So you need to update your postgresql.conf to set listen_addresses = '*' and restart the service to allow external connections. Also, just in case check if a Cloud Firewall is attached to your Droplet. If so, make sure there’s a rule allowing inbound traffic on port 5432 from your dev machine’s IP. If you’re using UFW on the Droplet, allow the connection with: sudo ufw allow from <your-ip> to any port 5432 proto tcp As a workaround, you can keep using the SSH tunnel from Python like this: import psycopg2 conn = psycopg2.connect( host='localhost', port=5432, database='your_db', user='your_user', password='your_pass' ) Just make sure the tunnel is open when running the code. Alternatively, if you want to avoid dealing with firewall rules, SSH tunnels, and manual setup, you can consider using DigitalOcean’s Managed PostgreSQL . Let me know how it goes! - Bobby

alexdo · Answer

Heya, @36d56192d3114513857618b22686be You can make sure your PostgreSQL server isn’t binding only to localhost. In postgresql.conf, check that: listen_addresses = '*' And in pg_hba.conf, try changing your line to: host all all 0.0.0.0/0 md5 If none of this helps, you can use a reverse SSH tunnel or deploy a lightweight VPN (like Tailscale) to connect securely from your dev machine to the DB without opening any ports publicly. Regards

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Trying to connect to postgresql on droplet programmatically via python and pg8000/psycopg2

Related questions

Should I use a Managed Database or run PostgreSQL on a Droplet?

Should I use Managed PostgreSQL or just run Postgres on my Droplet?

App Platform - update DB pool without detach

Upgrading to postgres 18 with postgis installed

Postgres 18 availability?

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

Postgres - Database extension pg_stat_statement is activ but table doesn't exists

How do I automatically back up a DigitalOcean Managed PostgreSQL database to Spaces?

Further PostgreSQL Extension Support Roadmap

installing pgadminj4 web version ubuntu nginx

How do you create vector extension for Postgresql

Report this

Trying to connect to postgresql on droplet programmatically via python and pg8000/psycopg2

Related questions

Should I use a Managed Database or run PostgreSQL on a Droplet?

Should I use Managed PostgreSQL or just run Postgres on my Droplet?

App Platform - update DB pool without detach

Upgrading to postgres 18 with postgis installed

Postgres 18 availability?

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

Postgres - Database extension pg_stat_statement is activ but table doesn't exists

How do I automatically back up a DigitalOcean Managed PostgreSQL database to Spaces?

Further PostgreSQL Extension Support Roadmap

installing pgadminj4 web version ubuntu nginx

How do you create vector extension for Postgresql