Install PostgreSQL on Debian 11

As a GIS specialist, ArcGIS is my main tool to create maps, doing some spatial analysts and so on. ArcGIS supports various databases including PostgreSQL (PostGIS), SQL Server, Oracle and some more. In this article, I am going to build a database system to support my work. I will build a PostgreSQL on Debian 11. This system is running on Linode. So basically it’s a cloud system. Linode offers a low-cost cloud VM that is great for a starter.

Steps to Install PostgreSQL on Debian 11

Step 1. Update System

First, we need to update our system prior to the PostgreSQL installation. Connect to the host via SSH. I would recommend Termius to connect to the remote/cloud system. Execute this command to update Debian 11.

sudo apt update && sudo apt upgrade

Step 2. Install PostgreSQL

Now we are ready to install PostgreSQL. Use this command.

sudo apt install postgresql postgresql-contrib

Wait until the installation completes

Step 3. Start PostgreSQL

Now we can start the PostgreSQL service and see the status with this command

sudo systemctl start postgresql
sudo systemctl status postgresql

Output

Step 4. Create a New Database

First, we need to connect to the PostgreSQL database locally.

sudo -i -u postgres

And then, run the psql command

psql

Output

Now let’s create a new PostgreSQL user called ‘dhani’ with password ‘12345’.

CREATE USER dhani WITH PASSWORD '12345';

Now we are going to create a new database called gis_database.

CREATE DATABASE gis_database;

Now we have a new database ready. Now we can grant the new user to the new database we created.

GRANT ALL PRIVILEGES ON DATABASE gis_database TO dhani;

Step 5. Enable Remote Access

By default, PostgreSQL only be accessible from the localhost. With a few clicks, we can enable remote access so our applications can connect to the database remotely. As root or sudo users, we need to edit the postgresql.conf file.

nano /etc/postgresql/13/main/postgresql.conf  

Scroll down and find the following line

listen_addresses = 'localhost'

Change it into:

listen_addresses = '*'

Next, we need to edit the pg_hba.conf.

nano /etc/postgresql/13/main/pg_hba.conf

Scroll down until you see this line

host    replication     all             127.0.0.1/32            md5

Change it to:

host    replication     all             0.0.0.0/0            md5

Close and save the file and then restart PostgreSQL service.

sudo systemctl restart postgresql

In case you have firewall active, we need to allow port 5432 through the ufw.

sudo ufw allow 5432