How to enable TCP connections to PostgreSQL

Posted on Edited on In , Views:

Problem statement

By default, the TCP connection to PostgreSQL is disallowed. You would see the following like error when you try to connect.

# su - postgres
-bash-4.2$ psql --host=10.10.10.243 --port=5432 --username=postgres -w -c "\l"
psql: error: connection to server at "10.10.10.243", port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

How to enable

Modify the following two configuration files to allow the TCP connections from any hosts and restart the Postgres service. You can specify the particular host to connect if needed by changing “0.0.0.0” to the host IP address.

# vim /mnt/pgdata/postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on;

# vim /mnt/pgdata/pg_hba.conf
# IPv4 local connections:
host    all             all             0.0.0.0/0           trust


# su - postgres -c "/usr/pgsql-15/bin/pg_ctl -D /mnt/pgdata -l logfile restart"

Verify the TCP connections

bash-4.2$ psql --host=10.10.10.243 --port=5432 --username=postgres -w -c "create database testdb"
CREATE DATABASE
-bash-4.2$ psql --host=10.10.10.243 --port=5432 --username=postgres -w -c "\l"
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)