Running a remote PostGIS database (Linux)

This document uses Ubuntu 18.10 in conjunction with PostgreSQL 10 with PostGIS. Similar concepts should apply to other installations. It assumes PostgreSQL has been installed with the PostGIS extensions already.

Initializing the database to run on server startup

Either open the terminal in your Linux server or open an SSH session to your server and log in using an appropriate username and run the following command:

sudo update-rc.d postgresql enable

Enabling remote connections to the PostgreSQL cluster

Using the Nano text editor, make changes to the postgresql.conf file by typing:

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

Scroll down to the Connection Settings part of the file using your keyboard and add:

listen_addresses = ‘*’

Use CNTR-X to exit, save when prompted.

Next, use nano to change the pg_hba.conf file. This file allows access to the database cluster for users with encrypted passwords.

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

Change or add all the red text below

# TYPE DATABASE USER ADDRESS METHOD

 

# “local” is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host all all 0.0.0.0/0
md5

# IPv6 local connections:

host all all ::0/0 md5

# All IPs

host all all all md5

 

Again, exit and save.

Restart the PostgreSQL database cluster:

sudo systemctl restart postgresql

Configuring for an internal network

In most business situations where all employees are under one roof or use a VPN to connect into a business network it is possible to connect directly to the GIS server using its local IP address.

By default, PostgreSQL listens to requests on port 5432. All that is required to connect to a PostGIS database on a network then is the following:

  • IP address of the physical or virtual server
  • Name of the Database
  • Login credentials

Configuring for an externally facing server

Servers that need to be accessible over the internet for remote workers or workers not able to connect in via a VPN connection need to be routed accordingly by your network hardware. This means that all requests to port 5432 are routed to the PostGIS server. If more than one server is on the local network, it will be necessary to specify a different port than default for PostgreSQL.

After that is completed, it will be necessary to also ensure that your network has a static IP address or you are using a DNS forwarding service. This is beyond the scope of this document.

Posted in Instructional Information.