PostgreSQL connection problems fall roughly into four categories:

Don't change configuration yet. First run this on the VPS itself:

sudo systemctl status postgresql --no-pager
sudo pg_isready

If it isn't ready locally, troubleshoot service startup first; if it works locally but not remotely, then troubleshoot the listen address, firewall, and pg_hba.conf.

PostgreSQL installed from packages on Ubuntu / Debian is usually managed by systemd:

sudo systemctl status postgresql --no-pager

Check recent logs:

sudo journalctl -u postgresql -n 100 --no-pager

Depending on the version, you can also check a specific instance:

pg_lsclusters

In the output, focus on:

• Whether Status is online;
• Whether Port is 5432 or your custom port;
• Whether Data directory is correct;
• Where the log file path is.

If the service fails to start, common causes include:

• Incorrect permissions on the data directory;
• The port is already occupied by another PostgreSQL instance;
• Disk full or inodes exhausted;
• Syntax errors in the configuration file;
• Shared memory, connection count, or kernel parameters set too high.

The official PostgreSQL documentation also explicitly states that on startup failure you should check the logs first; for example, when the port is occupied you'll see something like Address already in use, and when the connection fails the client will see Connection refused.

The service showing running doesn't mean remote connections will necessarily work. Continue by checking the listening port:

sudo ss -lntup | grep 5432

There are two common outputs:

127.0.0.1:5432
0.0.0.0:5432

If it only listens on 127.0.0.1:5432, PostgreSQL only accepts local connections. When a remote client accesses the VPS public IP, it will naturally fail.

You can also test locally:

psql -h 127.0.0.1 -U postgres -d postgres

If local connections work, the problem is more likely in remote listening, the firewall, the security group, or pg_hba.conf.

PostgreSQL by default usually only listens locally. You can find the configuration file location like this:

sudo -u postgres psql -c "SHOW config_file;"

Back up before editing the configuration:

sudo cp /etc/postgresql/*/main/postgresql.conf /etc/postgresql/postgresql.conf.bak.$(date +%F-%H%M) 2>/dev/null || true

Check the current value:

sudo -u postgres psql -c "SHOW listen_addresses;"

If you really need remote access, it's better to bind to an internal network address or a specific address rather than writing * directly. For example:

listen_addresses = '127.0.0.1,10.0.0.5'

If your VPS has no internal network and you can only allow temporary public access, you must also pair it with a strict pg_hba.conf and security group allowlist. Don't expose the database to the entire internet.

Restart after modifying:

sudo systemctl restart postgresql

Check again:

sudo ss -lntup | grep 5432

listen_addresses determines whether PostgreSQL listens on a given address, while pg_hba.conf determines which clients can authenticate and how.

Find the file location:

sudo -u postgres psql -c "SHOW hba_file;"

Back up before editing:

sudo cp /etc/postgresql/*/main/pg_hba.conf /etc/postgresql/pg_hba.conf.bak.$(date +%F-%H%M) 2>/dev/null || true

A safer remote access rule should restrict the source IP, for example allowing only your office public IP:

host    mydb    myuser    203.0.113.10/32    scram-sha-256

This is not recommended:

host    all     all       0.0.0.0/0          md5

It not only covers too broad a range but may also use an older authentication method. The PostgreSQL documentation also emphasizes that pg_hba.conf is matched in order, and the first matching rule takes effect; if no rule matches, the connection is rejected.

After modifying, you can reload:

sudo systemctl reload postgresql

Or:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

If you suspect a syntax problem in the rules, check with:

sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;"

Remote connections don't just need the port to be reachable; the user also needs permission to connect to the corresponding database.

Enter PostgreSQL:

sudo -u postgres psql

View roles:

\du

Example of creating a business user:

CREATE USER appuser WITH PASSWORD '替换为强密码';
CREATE DATABASE appdb OWNER appuser;
GRANT CONNECT ON DATABASE appdb TO appuser;

Test the connection:

psql -h 127.0.0.1 -U appuser -d appdb

If it connects locally but not remotely, continue by checking the firewall and security group.

Local UFW:

sudo ufw status verbose

If you only want to allow a specific IP:

sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp

Don't do this directly:

sudo ufw allow 5432/tcp

Unless you're very sure the cloud security group already restricts the source IP.

Also check the security group or firewall in the cloud provider's console. Many VPS users only change UFW inside the system but forget that the console security group still blocks 5432.

Test the port from the client:

nc -vz 你的VPS公网IP 5432

If the port is unreachable, troubleshoot the network layer first; if the port is reachable but authentication fails, go back to the PostgreSQL configuration layer.

If PostgreSQL runs in Docker, also check whether the container port is mapped to the host:

docker ps

You need to see something like:

0.0.0.0:5432->5432/tcp

If it's only 5432/tcp inside the container but there's no host mapping, it can't be accessed from outside.

Docker Compose example:

services:
  postgres:
    image: postgres:16
    ports:
      - "127.0.0.1:5432:5432"
    environment:
      POSTGRES_DB: appdb
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: "替换为强密码"

In production, it's more recommended to bind to 127.0.0.1 so the application accesses it on the same machine, or access it via an internal network/VPN/bastion host, rather than exposing the database directly to the public internet.

It's recommended to follow this order:

1. systemctl status postgresql to check the service;
2. journalctl -u postgresql -n 100 to check startup errors;
3. pg_lsclusters to check the version, port, and data directory;
4. ss -lntup | grep 5432 to check the listen address;
5. SHOW listen_addresses; to check whether remote listening is allowed;
6. SHOW hba_file; to check pg_hba.conf;
7. Use pg_hba_file_rules to check for rule errors;
8. Check users, databases, and CONNECT permissions;
9. Check UFW and the cloud security group;
10. Finally, check Docker port mapping or the application connection string.

The benefit of troubleshooting this way is that each step narrows the scope, avoiding mixing listening, authentication, firewall, and password problems together.

Even if you've fixed the connection problem, that doesn't mean you should expose 5432 long-term. Safer approaches are:

• Application and database on the same VPS: listen on 127.0.0.1;
• Application and database on the same cloud provider's internal network: listen only on the internal IP;
• Remote management: use an SSH tunnel;
• Access from multiple servers: use WireGuard / Tailscale / ZeroTier;
• Temporary troubleshooting: allow only your fixed IP in the security group, and close it when done.

You can refer to this database security article:

• Don't Expose VPS Databases Directly to the Public Internet (2026): How to Do MySQL, PostgreSQL, and Redis Remote Access Safely

When PostgreSQL on a VPS won't connect, the three most easily confused things are: whether the port is listening, whether pg_hba.conf allows it, and whether the network layer permits it. Connection refused is often a service or listening problem; no pg_hba.conf entry is an authentication rule problem; connection timeouts are more like a firewall, security group, or network path problem.

Start troubleshooting locally first, then work layer by layer toward remote access. Don't open up listen_addresses='*' and 0.0.0.0/0 right away. Database connectivity problems can be fixed, but the risk of exposing a database to the public internet is often more troublesome than the connection failure itself.