Enable remote access to PostgreSQL

Photo by Sean Lim on Unsplash

After installed PostgreSQL, by default connection to the database using TCP/IP is not allowed that why you cannot remote database via PGAdmin or PSQL.

psql: could not connect to server: Connection refused
Is the server running on host “xxx.xxx.xxx.xxx” and accepting
TCP/IP connections on port 5432?

Step 1: Remote to server

You can remote to the PostgreSQL server via SSH or Putty.

$ ssh root@192.168.100.1

if you are logged in server already skip this step

Step 2: Change Listen Address in postgresql.conf

Add the following line in the end of /var/lib/pgsql/data/postgresql.conffile or /etc/postgresql/10/main/postgresql.conf

listen_addresses = '*'

Step 3: Configuring pg_hba.conf

Add the following line in the end of /var/lib/pgsql/data/pg_hba.conf file or /etc/postgresql/10/main/pg_hba.conf:

host    all      all              0.0.0.0/0                    md5
host    all      all              ::/0                         md5

Note: Any IP address connection and required password.

Step 4: Restart Service

Restart PostgreSQL server to apply the changes:

$ systemctl restart postgresql.service

Step 5: Finally Testing remote

Let’s start testing via terminal using PSQL.

$ psql -h 192.168.100.1 -U postgres
 Password for user postgres:
 psql (9.4.1, server 9.4.5)
 Type "help" for help.
postgres=# \l

Clap! Clap! :)👏🏼 for mark as you done.

don’t forget follow me & give me Clap! Clap! :)👏🏼