CODEWITHSUNDEEP
postgresqlserversqlalchemypsycopg2pgadmin
Paul
Paul

Reputation: 231

Timeout connecting to remote PostgreSQL server

I am doing this task as self-education, to better understand the interaction between the client and the server.

So I have a laptop and a desktop computer at home. I will use the laptop as a client, and the desktop computer as a server. Both are connected to the network via a network cable.

Now about the client. On the laptop, I wrote a small program in Python and sqlalchemy that takes data from the database and prints it out

connect_to_db_in_another_comp.py

from sqlalchemy import create_engine
from sqlalchemy.engine import URL
from sqlalchemy.orm import sessionmaker
from models import Base

url = URL.create(
    database="Test-connect-2",
    username="postgres",
    host="192.168.XXX.XX",
    password="123",
    port=5432,
    drivername="postgresql",
)

engine = create_engine(url)
connection = engine.connect() # traceback start here

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

all_records = session.query(User).all()
for i in all_records:
    print(i.user_name)

Full text traceback:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "192.168.XXX.XX", port 5432 failed: Connection timed out (0x0000274C/10060) Is the server running on that host and accepting TCP/IP connections ?

Now about the server. On the desktop computer in pgAdmin 4 in the already existing PostgreSQL 16 server I created the Test-connect-2 database. In this database I added one table "user". The server properties are as follows:

Host name/address: 192.168.XXX.XX

Port: 5432

Maintenance database: postgres

Username: postgres

In file pg_hba.conf I added last line:

host all all 0.0.0.0/0 md5

I disabled the firewall and disabled the antivirus in both devices, but I still get the error I indicated above. Tell me what my mistake is

Upvotes: 0

Views: 3604

Answers (1)

Kartik Kumar
Kartik Kumar

Reputation: 26

Modify postgresql.conf to Allow Remote Connections PostgreSQL, by default, only listens on localhost. To enable remote connections, add the following to C:\NetIQ\idm\postgres\data\postgresql.conf

listen_addresses = '*'

If the server has multiple interfaces, you can specify one.

Update pg_hba.conf for Remote Authentication To allow remote connections, add the following to C:\NetIQ\idm\postgres\data\pg_hba.conf

host all all 0.0.0.0/0 md5

For IPv6:

host all all ::0/0 md5

Test and Restart Restart PostgreSQL and ensure firewall rules allow TCP connections on port 5432.

Additionally, make sure that if you're using a protected server, it allows TCP connections on port 5432. You can achieve this by modifying the firewall rules.

sudo ufw status

sudo ufw allow 5432/tcp

sudo ufw reload

Upvotes: 0

Related Questions