CloudTada | Infrastructure & DevOps Insights

Installing PostgreSQL

Learning Objectives

After this lesson, you will:

Install PostgreSQL from package repository
Understand how to install PostgreSQL from source (optional)
Configure basic postgresql.conf for HA
Understand pg_hba.conf and authentication
Prepare PostgreSQL on 3 nodes for Patroni cluster

1. Installing PostgreSQL from Package Repository

1.1. Preparation

Before installing PostgreSQL, we need to setup the official PostgreSQL package repository (PGDG - PostgreSQL Global Development Group).

Advantages of PGDG repository:

✅ Latest PostgreSQL versions
✅ Fast security updates
✅ Many extensions available
✅ Support for multiple distros

1.2. Installation on Ubuntu/Debian

Step 1: Add PGDG repository

TEXT

# Import repository signing key
sudo apt install -y wget gnupg2

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update package list
sudo apt update

Step 2: Install PostgreSQL

TEXT

# Install PostgreSQL 18 (recommended for production)
sudo apt install -y postgresql-18 postgresql-contrib-15 postgresql-server-dev-15

# Check version
psql --version
# Output: psql (PostgreSQL) 15.5

Step 3: Check service

TEXT

# Check status
sudo systemctl status postgresql

# Output:
# ● postgresql.service - PostgreSQL RDBMS
#      Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
#      Active: active (exited) since ...

Step 4: Stop and disable PostgreSQL default cluster

TEXT

# Patroni will manage PostgreSQL, so we disable the default service
sudo systemctl stop postgresql
sudo systemctl disable postgresql

# Remove default cluster (Patroni will create a new cluster)
sudo pg_dropcluster 15 main --stop

# Check
pg_lsclusters
# Output: (empty - no clusters)

1.3. Installation on CentOS/RHEL/Rocky Linux

Step 1: Add PGDG repository

TEXT

# Install EPEL (Extra Packages for Enterprise Linux)
sudo dnf install -y epel-release

# Add PostgreSQL 18 repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module
sudo dnf -qy module disable postgresql

Step 2: Install PostgreSQL

TEXT

# Install PostgreSQL 18
sudo dnf install -y postgresql15-server postgresql15-contrib postgresql15-devel

# Check
/usr/pgsql-15/bin/postgres --version
# Output: postgres (PostgreSQL) 15.5

Step 3: Create symlink (optional but convenient)

TEXT

# Create symlinks for binaries to PATH
sudo alternatives --install /usr/bin/psql psql /usr/pgsql-15/bin/psql 1
sudo alternatives --install /usr/bin/pg_config pg_config /usr/pgsql-15/bin/pg_config 1
sudo alternatives --install /usr/bin/pg_basebackup pg_basebackup /usr/pgsql-15/bin/pg_basebackup 1

Step 4: Do not initialize database (Patroni will do it)

TEXT

# DO NOT run:
# sudo /usr/pgsql-15/bin/postgresql-18-setup initdb

# DO NOT enable service:
# sudo systemctl enable postgresql-18

2. Installing PostgreSQL from Source (Optional - Advanced)

Installing from source allows custom compile options, but is more complex and harder to maintain.

2.1. When to install from source?

🔧 Need custom features not available in binary packages
🔧 Testing with development version
🔧 Optimization for specific hardware
🔧 Apply custom patches

2.2. Process for installing from source

Step 1: Install dependencies

TEXT

# Ubuntu/Debian
sudo apt install -y build-essential libreadline-dev zlib1g-dev \
  flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils \
  xsltproc libkrb5-dev libldap2-dev libpam0g-dev libperl-dev \
  python3-dev tcl-dev libsystemd-dev

# CentOS/RHEL
sudo dnf install -y gcc make readline-devel zlib-devel openssl-devel \
  libxml2-devel libxslt-devel systemd-devel perl-ExtUtils-Embed \
  python3-devel

Step 2: Download source

TEXT

cd /usr/local/src
sudo wget https://ftp.postgresql.org/pub/source/v15.5/postgresql-18.5.tar.gz
sudo tar -xzf postgresql-18.5.tar.gz
cd postgresql-18.5

Step 3: Configure and compile

TEXT

# Configure with options
sudo ./configure \
  --prefix=/usr/local/pgsql-15 \
  --with-openssl \
  --with-libxml \
  --with-systemd \
  --with-readline \
  --enable-nls

# Compile (using multiple cores)
sudo make -j$(nproc)

# Run tests (optional)
sudo make check

# Install
sudo make install

# Install contrib modules
cd contrib
sudo make install

Step 4: Setup environment

TEXT

# Add to ~/.bashrc
export PATH=/usr/local/pgsql-15/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql-15/lib:$LD_LIBRARY_PATH

source ~/.bashrc

Note: Installing from source does not automatically include systemd service, needs to be created manually.

3. Basic postgresql.conf Configuration

Patroni will manage most PostgreSQL configurations through DCS. However, it's important to understand the key parameters.

3.1. postgresql.conf file structure

TEXT

# /etc/postgresql/18/main/postgresql.conf
# or: /var/lib/pgsql/15/data/postgresql.conf

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
data_directory = '/var/lib/postgresql/18/main'
hba_file = '/etc/postgresql/18/main/pg_hba.conf'
ident_file = '/etc/postgresql/18/main/pg_ident.conf'

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'          # Listen on all interfaces
port = 5432
max_connections = 100           # Maximum number of connections

3.2. Important parameters for HA

Replication Settings

TEXT

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG (WAL)
#------------------------------------------------------------------------------
wal_level = replica              # Level of information in WAL
                                 # minimal, replica, or logical

fsync = on                       # Ensure WAL is flushed to disk
synchronous_commit = on          # Wait for WAL write confirmation

wal_log_hints = on              # Required for pg_rewind

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 10            # Maximum number of standby servers
max_replication_slots = 10      # Number of replication slots

# WAL keep settings
wal_keep_size = 1GB             # Keep at least 1GB WAL files
                                # (PG 13+, replaces wal_keep_segments)

# Archive settings (for PITR)
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/18/archive/%f'
archive_timeout = 300           # Archive every 5 minutes

Memory Settings

TEXT

#------------------------------------------------------------------------------
# RESOURCE USAGE (MEMORY)
#------------------------------------------------------------------------------
shared_buffers = 2GB            # RAM dành cho PostgreSQL cache
                                # Khuyến nghị: 25% của RAM

effective_cache_size = 6GB      # Ước tính tổng cache (OS + PG)
                                # Khuyến nghị: 50-75% của RAM

work_mem = 16MB                 # RAM cho mỗi query operation
                                # Tổng có thể dùng: work_mem × max_connections

maintenance_work_mem = 512MB    # RAM cho maintenance operations
                                # (VACUUM, CREATE INDEX, etc.)

Checkpoint Settings

TEXT

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG (Checkpoints)
#------------------------------------------------------------------------------
checkpoint_timeout = 10min      # Tần suất checkpoint tối đa
max_wal_size = 2GB             # WAL size trigger checkpoint
min_wal_size = 1GB             # Giữ ít nhất 1GB WAL

checkpoint_completion_target = 0.9  # Spread checkpoint I/O
                                    # (90% của checkpoint_timeout)

Logging Settings

TEXT

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'
logging_collector = on

log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'UTC'

# Log slow queries
log_min_duration_statement = 1000  # Log queries > 1 second

# Log connections/disconnections
log_connections = on
log_disconnections = on

# Log checkpoints (useful for tuning)
log_checkpoints = on

3.3. Patroni will override these settings

Patroni manages the following parameters through DCS, DO NOT set in postgresql.conf:

TEXT

# ❌ DO NOT set in postgresql.conf when using Patroni
# hot_standby = on
# primary_conninfo = '...'
# restore_command = '...'
# recovery_target_timeline = 'latest'

Patroni will automatically set them in postgresql.auto.conf.

4. Understanding pg_hba.conf

pg_hba.conf (Host-Based Authentication) controls client authentication.

4.1. Structure of pg_hba.conf

TEXT

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

# IPv4 connections from anywhere (for replication)
host    all             all             0.0.0.0/0               scram-sha-256

# Replication connections
host    replication     replicator      10.0.1.0/24             scram-sha-256

4.2. The columns

TYPE:
- local: Unix socket connections
- host: TCP/IP (clear text or SSL)
- hostssl: TCP/IP with SSL only
- hostnossl: TCP/IP without SSL
DATABASE:
- Database name
- all: all databases
- replication: replication connections
USER:
- Username
- all: all users
ADDRESS:
- IP/netmask: 10.0.1.0/24
- Hostname
- 0.0.0.0/0: anywhere (not recommended)
METHOD:
- trust: No password required (only for local dev)
- md5: MD5 hashed password (deprecated)
- scram-sha-256: Modern, secure (recommended)
- peer: Unix username = PostgreSQL username
- cert: SSL certificate authentication

4.3. pg_hba.conf cho Patroni Cluster

TEXT

# /etc/postgresql/18/main/pg_hba.conf

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# Localhost
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# Application connections
host    all             app_user        10.0.1.0/24             scram-sha-256

# Patroni REST API health checks (optional database connection)
host    postgres        patroni_user    10.0.1.0/24             scram-sha-256

# Replication connections (Patroni nodes)
host    replication     replicator      10.0.1.11/32            scram-sha-256
host    replication     replicator      10.0.1.12/32            scram-sha-256
host    replication     replicator      10.0.1.13/32            scram-sha-256

# Monitoring connections (Prometheus exporter)
host    postgres        exporter        10.0.1.0/24             scram-sha-256

4.4. Best practices for pg_hba.conf

✅ Specific is better: Don't use 0.0.0.0/0 if not needed

✅ Use scram-sha-256: Modern authentication method

✅ Separate users: Different users for app, replication, monitoring

✅ Document: Comment for each rule

✅ Restrict replication: Only allow replication user from Patroni nodes IP

❌ Avoid trust method: Even in dev environment

5. Create necessary users and databases

5.1. Create replication user

TEXT

# After Patroni bootstraps cluster, connect to primary:
sudo -u postgres psql -h localhost -p 5432

# In psql:
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'your_strong_password';

# Check
\du replicator

5.2. Create Patroni monitoring user

TEXT

-- User for Patroni health checks
CREATE USER patroni_user WITH ENCRYPTED PASSWORD 'patroni_password';
GRANT CONNECT ON DATABASE postgres TO patroni_user;

5.3. Create application database and user

TEXT

-- Create database
CREATE DATABASE myapp;

-- Create user
CREATE USER app_user WITH ENCRYPTED PASSWORD 'app_password';

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;

-- Connect to myapp database
\c myapp

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO app_user;

6. Lab: Installing PostgreSQL on 3 nodes

6.1. Lab Environment

TEXT

node1 (pg-node1): 10.0.1.11  - Primary (after bootstrap)
node2 (pg-node2): 10.0.1.12  - Replica
node3 (pg-node3): 10.0.1.13  - Replica

6.2. Execute on ALL 3 nodes

Step 1: Update system

TEXT

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL 18

TEXT

# Add repo
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update

# Install
sudo apt install -y postgresql-18 postgresql-contrib-15 postgresql-server-dev-15

Step 3: Stop and disable default cluster

TEXT

sudo systemctl stop postgresql
sudo systemctl disable postgresql

sudo pg_dropcluster 15 main --stop

# Verify
pg_lsclusters
# Output: (should be empty)

Step 4: Create directories for PostgreSQL data

TEXT

# Patroni will manage data directory, but we create structure
sudo mkdir -p /var/lib/postgresql/18/data
sudo mkdir -p /var/lib/postgresql/18/archive

sudo chown -R postgres:postgres /var/lib/postgresql
sudo chmod 700 /var/lib/postgresql/18/data

Step 5: Check PostgreSQL binary

TEXT

# Check version
postgres --version
# Output: postgres (PostgreSQL) 15.5

# Check tools
which psql pg_basebackup pg_rewind

6.3. Verify on each node

TEXT

# Node name
hostname

# PostgreSQL version
postgres --version

# Directories
ls -ld /var/lib/postgresql/18/data
ls -ld /var/lib/postgresql/18/archive

# PostgreSQL service
systemctl status postgresql
# Output: inactive (dead) ✓

6.4. Troubleshooting

Issue 1: Permission denied trên data directory

TEXT

# Fix ownership
sudo chown -R postgres:postgres /var/lib/postgresql
sudo chmod 700 /var/lib/postgresql/18/data

Issue 2: PostgreSQL service still running

TEXT

# Stop forcefully
sudo systemctl stop postgresql@15-main
sudo systemctl disable postgresql@15-main

# Kill processes if needed
sudo pkill -9 postgres

Issue 3: Port 5432 is already in use

TEXT

# Check process using port
sudo lsof -i :5432

# Or
sudo netstat -tlnp | grep 5432

7. Summary

Key Takeaways

✅ Package repository: Install PostgreSQL from PGDG repo to get the latest version

✅ Disable default service: Patroni will manage PostgreSQL, don't use default systemd service

✅ postgresql.conf: Understand important parameters for HA and replication

✅ pg_hba.conf: Configure authentication for connections and replication

✅ Don't initialize cluster: Patroni will bootstrap cluster automatically

Lab Checklist

PostgreSQL 18 installed on all 3 nodes
Default cluster removed
PostgreSQL service disabled
Data directories created with correct permissions
Binary paths available in $PATH

Preparation for Lesson 6

The next lesson will install and configure etcd cluster - DCS layer for Patroni.

Course

PostgreSQL High Availability A-Z

Share this article

You might also like