PostgreSQL Installation and Setup on Linux Server: A Complete Guide by Nicholus Gathirwa.

PostgreSQL Installation and Setup on Linux Server: A Complete Guide

Prerequisites

Before we begin, you'll need:

• An Azure account (free tier works fine)
• A terminal application (Git Bash, PowerShell for Windows, or Terminal for Mac)
• Basic knowledge of Linux command line

Step 1: Creating an Azure Virtual Machine

1.1 Creating Your Azure Account

1. Visit Azure Portal at portal.azure.com
2. Sign up for a free account - Azure provides $200 in free credits for new users
3. Student Account Benefits: If you have a student email, use Azure for Students to get:
   • $100 in free Azure credits (no credit card required)
   • Access to free services for 12 months
   • Additional student-specific resources

1.2 Setting Up Your VM

1. Log into Azure Portal and navigate to Virtual Machines
2. Click "Create" to start the VM creation process
3. Choose your region - Select a region close to your users (e.g., West US 2)
4. Select VM specifications:
   • Choose an appropriate image (Ubuntu Server recommended)
   • Select VM size based on your needs
   • Set up authentication

VM Configuration

# Example VM settings
Username: luxStudent
Password: developer@123
Region: West US 2

1. Review and Create your virtual machine
2. Go to Resource once deployment is complete
3. Copy the Public IP address for SSH access

Step 2: Connecting to Your Linux Server

2.1 SSH Connection

SSH (Secure Shell) operates on port 22 by default and provides secure remote access to your server.

# Basic SSH connection syntax
ssh username@ip_address

# Example connection
ssh luxStudent@102.37.147.102

2.2 First Login

1. Enter your password when prompted
2. Test your connection by creating a directory:

# Create a test directory
mkdir mercy

# List contents to verify
ls

2.3 Basic File Operations

# Create a file
touch script.sql

# Edit the file using vim
vim script.sql

# Exit vim without saving
:q!

Step 3: Installing PostgreSQL

3.1 System Preparation

Before installing PostgreSQL, update your system packages:

# Update package lists
sudo apt update

3.2 PostgreSQL Installation

Install PostgreSQL along with additional contrib packages:

# Install PostgreSQL and additional tools
sudo apt install postgresql postgresql-contrib

The postgresql-contrib package includes useful additional utilities and extensions.

3.3 Verify Installation

Check if PostgreSQL service is running:

# Check PostgreSQL service status
sudo systemctl status postgresql

You should see the service as "active" if installation was successful.

Step 4: PostgreSQL Initial Configuration

4.1 Accessing PostgreSQL

Switch to the postgres user and access the PostgreSQL prompt:

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL command line
psql

4.2 Database Operations

Once in the PostgreSQL prompt, you can perform various operations:

-- List all databases
\l

-- Create a new database
CREATE DATABASE luxStudent;

-- Verify database creation
\l

4.3 User Management

Create a new user and assign privileges:

-- Create a new user with password
CREATE USER "username" WITH PASSWORD "password";

-- Grant superuser privileges
ALTER USER "username" WITH SUPERUSER;

-- Exit PostgreSQL prompt
\q

Then exit from postgres user:

exit

Step 5: Configuring PostgreSQL for Remote Access

5.1 PostgreSQL Configuration Files

Navigate to the PostgreSQL configuration directory:

# Go to PostgreSQL configuration directory
cd /etc/postgresql

# List PostgreSQL versions
ls

# Navigate to version directory (e.g., 16)
cd 16

# Go to main configuration directory
cd main

# List configuration files
ls

5.2 Editing postgresql.conf

Edit the main PostgreSQL configuration file:

# Edit PostgreSQL configuration
sudo vim postgresql.conf

Find and modify the listen_addresses setting:

# Change from:
# listen_addresses = 'localhost'

# To:
listen_addresses = '*'

Save and exit vim (:wq).

5.3 Configuring Client Authentication

Edit the pg_hba.conf file to allow remote connections:

# Edit authentication configuration
sudo vim pg_hba.conf

Add the following line to allow connections from any IP address:

# Allow all connections (use with caution in production)
host all all 0.0.0.0/0 md5

Save and exit vim.

5.4 Restart PostgreSQL Service

Apply the configuration changes:

# Restart PostgreSQL service
sudo systemctl restart postgresql

# Verify service status
sudo systemctl status postgresql

Step 6: Azure Network Configuration

6.1 Configure Inbound Port Rules

1. Go to Azure Portal → Your VM → Networking
2. Click "Add inbound port rule"
3. Configure the rule:
   • Destination port ranges: 5432
   • Protocol: TCP
   • Action: Allow
   • Name: PostgreSQL
4. Click "Add"

Step 7: Testing Remote Connection

7.1 Using DBeaver

To test your PostgreSQL setup with a database client like DBeaver:

1. Host: Your VM's public IP address eg 192.168.90.1
2. Port: 5432
3. Database: The database name you created
4. Username: The username you created
5. Password: The corresponding password

7.2 Connection Troubleshooting

During installation and setup, one might face a couple of issues. Follow the steps below, Generally applicable to most situations, which may include but not limited to potential issues faced during installation and setup:

1. Verify PostgreSQL is running:

sudo systemctl status postgresql

1. Check if port 5432 is listening:

sudo netstat -tlnp | grep 5432

1. Verify Azure firewall rules are properly configured
2. Check authentication settings in pg_hba.conf

Keep in mind

Important Security Notes:

• Never use weak passwords in production environments
• Restrict IP access instead of allowing 0.0.0.0/0 in production
• Use SSL/TLS encryption for remote connections
• Regularly update your PostgreSQL installation
• Implement proper backup strategies

Production-Ready Security:

# Example of restricting access to specific IP ranges
host all all 192.168.1.0/24 md5
host all all 10.0.0.0/8 md5

Additional Resources

• Official PostgreSQL Documentation
• PostgreSQL Performance Tuning Guide
• Azure Virtual Machines Documentation

---

This guide provides a comprehensive walkthrough for setting up PostgreSQL on a Linux server deployed on microsoft azure, connecting to the server locally using dbeaver and other configurations that may be required. Remember to always follow security best practices and regularly update your systems. Have a good one!!!

Comments

[Jeremiah Kiarie]

Nice