DEV Community

Oliver Samuel
Oliver Samuel

Posted on

PostgreSQL Installation and Setup on a Linux Server(with Azure VM Provisioning)

Introduction

PostgreSQL (often referred to as Postgres) is an advanced, open-source relational database management system (RDBMS) known for its stability, extensibility, and standards compliance. It is widely used in both small applications and large-scale enterprise systems.

This guide walks through installing and configuring PostgreSQL on a Linux server, focusing on:

  • Provisioning a Linux server on Microsoft Azure
  • Installing PostgreSQL from official repositories
  • Creating roles and databases
  • Securing the installation
  • Setting up remote access (optional)

Prerequisites

  • A running Linux server (Ubuntu 20.04+, Debian 10+, CentOS/RHEL 8+)
  • A user with sudo privileges
  • Internet connectivity to install packages

Step 0: Provisioning a Linux Virtual Machine on Microsoft Azure

0.1 Sign in to Azure Portal

Go to https://portal.azure.com.

Create an account if you don't have one. A free-tier VM is available.

0.2 Create a Linux Virtual Machine

  1. Go to Virtual Machines > Create > Azure Virtual Machine
  2. Fill in the Basics:
    • Subscription: Select your Azure subscription
    • Resource Group: Create or choose one
    • VM Name: pg-server
    • Region: Nearest to your users
    • Image: Ubuntu 22.04 LTS
    • Size: B1s (1 vCPU, 1 GB RAM) for testing
    • Authentication: Use SSH public key (recommended)
    • Username: azureuser
  3. On Disks, select Standard SSD
  4. On Networking, ensure SSH (port 22) is open
  5. Click Review + Create, then Create

0.3 Connect to Your VM

Once deployed:

  • Copy the public IP address of the VM
  • Open a terminal or SSH tool and connect: 
ssh azureuser@<VM_IP_ADDRESS>
Enter fullscreen mode Exit fullscreen mode

Installing PostgreSQL

For Ubuntu/Debian-based Distributions:

Step 1: Update system packages

sudo apt update && sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

Step 2: Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode

This installs both the PostgreSQL server and extra utilities like pgadmin, pg_basebackup, etc.

For CentOS/RHEL-based Distributions:

Step 1: Enable the PostgreSQL repository

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Enter fullscreen mode Exit fullscreen mode

Step 2: Disable built-in PostgreSQL

sudo dnf -qy module disable postgresql
Enter fullscreen mode Exit fullscreen mode

Step 3: Install PostgreSQL (e.g., version 15)

sudo dnf install -y postgresql15-server postgresql15
Enter fullscreen mode Exit fullscreen mode

Step 4: Initialize and start PostgreSQL

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable --now postgresql-15
Enter fullscreen mode Exit fullscreen mode

Creating Roles and Databases

PostgreSQL uses a role-based authentication system.

Switch to the postgres user:

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

Create a new database user:

createuser --interactive
Enter fullscreen mode Exit fullscreen mode

Create a database:

createdb my_database
Enter fullscreen mode Exit fullscreen mode

Connect to the PostgreSQL prompt:

psql
Enter fullscreen mode Exit fullscreen mode

Inside the psql shell, you can run:

\l       -- List all databases  
\du      -- List all roles  
\q       -- Quit
Enter fullscreen mode Exit fullscreen mode

Securing PostgreSQL

Change password for postgres: 

psql -c "ALTER USER postgres PASSWORD 'StrongPasswordHere';"
Enter fullscreen mode Exit fullscreen mode

Configure pg_hba.conf (host-based access):

Ubuntu:

sudo nano /etc/postgresql/*/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

CentOS/RHEL:

sudo nano /var/lib/pgsql/15/data/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change:

local   all             postgres                                peer
Enter fullscreen mode Exit fullscreen mode

To:

local   all             postgres                                md5
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL: 

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Allowing Remote Connections (Optional)

Step 1: Modify postgresql.conf

Ubuntu:

sudo nano /etc/postgresql/*/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

CentOS/RHEL:

sudo nano /var/lib/pgsql/15/data/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Set:

listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

Step 2: Update pg_hba.conf

Add:

host    all             all             0.0.0.0/0               md5
Enter fullscreen mode Exit fullscreen mode

Be cautious with 0.0.0.0/0. Restrict this to your IP range in production environments.

Step 3: Restart PostgreSQL and open firewall 

sudo ufw allow 5432/tcp
sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Testing the Installation

Local access:

psql -U postgres
Enter fullscreen mode Exit fullscreen mode

Remote access:

psql -h <your_server_ip> -U <your_user> -d <your_database>
Enter fullscreen mode Exit fullscreen mode

Conclusion

PostgreSQL is a robust, feature-rich database that integrates well with modern applications. By following these steps, you have:

  • Provisioned a Linux server on Azure
  • Installed PostgreSQL
  • Set up secure users and databases
  • Configured the database for local and remote access

References

  1. Azure VM Quickstart – Microsoft Docs
  2. PostgreSQL Official Documentation
  3. Ubuntu PostgreSQL Guide
  4. pgAdmin Documentation
  5. Cybertec PostgreSQL Security Best Practices

Top comments (0)