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
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
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.
Scroll down and find the following line
listen_addresses = 'localhost'
Change it into:
listen_addresses = '*'
Next, we need to edit the 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