Postgresql remote access

Last updated 1 week, 6 days

Author: sghaier

How to enable remote access to PostgreSql server

In order to connect to a PostgreSQL (installation) instance remotly there are some configurations that needs to be done in order to tell PostgreSQL to listen to external IPs.

Step 1 - Connect to the remote server

Open your terminal and connect to the server using SSH 

ssh user@server-ip

Step 2 - Update the postgresql.conf file

Next you need to update the postgresql.conf file to tell PostgreSQL to listen to external IP adresses 

for that we can use nano text editor 

So from the terminal let's open the postgresql.conf file located in /etc/postgresql/main/10/ using the sudo privilege.

sudo nano /etc/postgresql/10/main/postgresql.conf

After that you need to search for a the line that contains

  • listen_addresses = 'localhost' 

update it to

  • listen_addresses = '*'

and remove the # to uncomment it.

Step 3 - Update pg_hba.conf file

After these changes PostgreSQL is now supposed to listen to external requests, but we are not done yet

we need to update another file which is located in /etc/postgresql/10/main/pg_hba.conf, and to do so we will use nano 

sudo nano /etc/postgresql/10/main/pg_hba.conf

we need to add this line in the bottom of the file 

host    all        all        all       md5

Finally we need to restart postgresql service in order to apply all the changes

sudo service postgresql restart

Step 4 - Check you connection to postgresql

From you local machin terminal 

psql -h server-ip -p 5432 -U database-user database-name

  • change the server-ip to your server IP address
  • change the database-user to your database user
  • change the database-name to the database you want to connect to

Quick actions

Filter posts based on the following filters.

  • Latest Posts
  • Announcements
  • Calendars
  • etc

Seach posts by tags

Click the tag name to filter the posts