Question 183900 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Connection to postgres on instance fails

I’m running postgres (14) database that I installed on an Ubuntu compute instance to which I fail to connect remotely:

    ~$ psql -U postgres -h remote.server.address -p 5432 datadb
    psql: error: connection to server at remote.server.address, port 5432 
    failed: 
    Connection timed out
    Is the server running on that host and accepting TCP/IP connections?

I’m able to query the database from the instance and the service was accessible remotely just a few days ago.
Checking postgres status on the instance shows that it is active:

$ sudo systemctl status 'postgresql*'
● [email protected] - PostgreSQL Cluster 14-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Wed 2023-06-07 07:11:15 UTC; 14s ago
    Process: 3614 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 14-main start (code=exited, status=0/SUCCESS)
   Main PID: 3619 (postgres)
      Tasks: 7 (limit: 4694)
     Memory: 18.1M
        CPU: 164ms

And listening on port 5432:

$ sudo lsof -i -n -P | grep -E 'postgres.*LISTEN.*' | less
postgres  2775        postgres    5u  IPv4  30639      0t0  TCP *:5432 (LISTEN)
postgres  2775        postgres    6u  IPv6  30640      0t0  TCP *:5432 (LISTEN)

The settings postgresql.conf are:

listen_addresses = '*'                  # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

And on pg_hba.conf:

# IPv4 remote connections for the tutorial:
host    all             all             0.0.0.0/0               md5

An advice I read elsewhere says that ‘if you see that all of the config is correct and it’s running there. The problem must be elsewhere. Maybe a bad connection string or a gateway is rejecting the connection along the way.’

How can I further debug this issue to see the factor that is preventing me from connecting to the database?

Update:
In reply to suggestions below to check firewall rules, I did that:

$ sudo ufw allow 5432/tcp
Skipping adding existing rule
Skipping adding existing rule (v6)

But the issue persists

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The connection was restored after updating the Source IPv4 ranges to my laptop’s public IP in the instance’s firewall rules details. I did that by going to VPC Networks than clicked Firewall in the side menu. In the main pane, titled VPC firewall rules, I scrolled to list of instances to locate and click on <instance_name>-postgres which leading to the Firewall rule details pane, where I clicked Edit to replace the IP that was written there (I have no idea as to how or why) with the public IP of my laptop (curl https://ipinfo.io/ip) and BOOM! connection restored! Many thanks for the advice given.


  2. 0

    If you are using IAP tunneling, Check whether you configured firewalls rules properly.

    Check the below steps, which may help to resolve your issue :

    1. Check 5432 port connection allowed & firewall rules setup allow access to port 22.

    2. Check Daemon services are running.

    3. Check Sshd service is running and the port is open.

    4. Not only port 22, also JDBC connection and incoming traffic may be blocked.

    5. Check kernel error or OOM (out of memory) which might cause the OS to have no resource to handle the connection request.

    6. Check the OS firewall rules, since Ubuntu is used, run the command (sudo ufw status) with sudoer permissions and check the output of UFW status and list of rules active.

    7. Check the status of daemon services based on your OS built in firewall and version and Running the connectivity test, It’s very simple, by clicking "Compute engine"->"VM instances"->"postgresql", find the "Network interfaces" through tab "Details", then click "View details" under "Network details". Find "Network analysis", click "Enable Network Management API". There will be a connectivity window for this instance which can check which part blocked the request.

    8. Even if your VM Instance is listening in all the ports, if there are any firewall rules on the firewall of the instance, the connections will be denied. Log in to the serial console and check the output of the commands(ip addr show & ip route show table all) to know the current routing setup and how that works so that all routing decisions are specified properly.

    9. Either you configured your router wrong, or the ISP itself is blocking the connection regardless of what the router tries to do. Check the ip address you must use in the postgres configuration files is the ip address of your ROUTER.

    10. Refer to Helena Alexander’s Devart blog on How to Configure PostgreSQL for Remote Connections & Michael Okoh’s LogRocket blog on Setting up a remote Postgres database server on Ubuntu 18.04 for more information.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search