xDev Asia

Install PostgreSQL from package repository or source, configure postgresql.conf and pg_hba.conf on all 3 nodes in the cluster.

PostgreSQL High Availability with Patroni & etcd

Part 2: Installation & Configuration

xdev.asia

Aim_

After this lesson, you will:

Install PostgreSQL from package repository_
Understand how to install PostgreSQL from source (optional)
Configuration postgresql.conf Basic for HA
Understand about pg_hba.conf and authentication
Preparing PostgreSQL on 3 nodes for Patroni cluster_

1. Install PostgreSQL from Package Repository

1.1. Preparation

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

Advantages of PGDG repository:

✅ Latest PostgreSQL version
✅ Quick security updates_
✅ Many extensions available available
✅ Support many distros

1.2. Install on Ubuntu/Debian

Step 1: Add PGDG repository

# 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

# Cài PostgreSQL 18 (khuyến nghị cho production) sudo apt install -y postgresql-18 postgresql-contrib-15 postgresql-server-dev-15 Kiểm tra version psql --version Output: psql (PostgreSQL) 15.5

Step 3: Test service

# Kiểm tra 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

# Patroni sẽ quản lý PostgreSQL, nên ta disable service mặc định sudo systemctl stop postgresql sudo systemctl disable postgresql Xóa cluster mặc định (Patroni sẽ tạo cluster mới) sudo pg_dropcluster 15 main --stop Kiểm tra pg_lsclusters Output: (empty - no clusters)

1.3. Install on CentOS/RHEL/Rocky Linux

Step 1: Add PGDG repository

# Cài đặt EPEL (Extra Packages for Enterprise Linux) sudo dnf install -y epel-release Thêm 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_

# Cài PostgreSQL 18 sudo dnf install -y postgresql15-server postgresql15-contrib postgresql15-devel Kiểm tra /usr/pgsql-15/bin/postgres --version Output: postgres (PostgreSQL) 15.5

Step 3: Create symlink (optional but convenient)

# Tạo symlink cho các binary vào 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 the database (Patroni will do it)

# KHÔNG chạy: sudo /usr/pgsql-15/bin/postgresql-18-setup initdb KHÔNG enable service: sudo systemctl enable postgresql-18

2. Installing PostgreSQL from Source (Optional - Advanced)

Installing from source allows custom compile options, but is more complicated and difficult to maintain.

2.1. When do you need to install from source?

🔧 Need custom features that are not in the binary package
🔧 Testing with development version
🔧 Optimized for hardware can
🔧 Apply custom patches

2.2. Installation process from source

Step 1: Install dependencies

# 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

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

# Configure với options sudo ./configure --prefix=/usr/local/pgsql-15 --with-openssl --with-libxml --with-systemd --with-readline --enable-nls Compile (sử dụng nhiều cores) sudo make -j$(nproc) Chạy tests (optional) sudo make check Install sudo make install Install contrib modules

cd contrib sudo make install

Step 4: Setup environment

# Thêm vào ~/.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 have a systemd service, it needs to be created manually.

3. Basic postgresql.conf configuration

Patroni will manage most of the PostgreSQL configuration through DCS. However, it is important to understand the important parameters.

3.1. File structure postgresql.conf

# /etc/postgresql/18/main/postgresql.conf hoặc: /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 = '*' # Lắng nghe trên tất cả interfaces port = 5432 max_connections = 100 # Số connections tối đa

3.2. Important Parameters for HA

Replication Settings_

#------------------------------------------------------------------------------ WRITE-AHEAD LOG (WAL) #------------------------------------------------------------------------------ wal_level = replica # Mức độ thông tin trong WAL # minimal, replica, hoặc logical fsync = on # Đảm bảo WAL được flush to disk synchronous_commit = on # Wait cho WAL write confirmation wal_log_hints = on # Cần cho pg_rewind #------------------------------------------------------------------------------ REPLICATION #------------------------------------------------------------------------------ max_wal_senders = 10 # Số standby servers tối đa max_replication_slots = 10 # Số replication slots WAL keep settings wal_keep_size = 1GB # Giữ ít nhất 1GB WAL files # (PG 13+, thay thế wal_keep_segments) Archive settings (để PITR)

archive_mode = on archive_command = 'cp %p /var/lib/postgresql/18/archive/%f' archive_timeout = 300 # Archive mỗi 5 phút

Memory Settings

#------------------------------------------------------------------------------ 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_

#------------------------------------------------------------------------------ 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

#------------------------------------------------------------------------------
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 the settings

Patroni manages the following parameters through DCS, NO should be set in postgresql.conf:

# ❌ KHÔNG set trong postgresql.conf khi dùng Patroni
hot_standby = on
primary_conninfo = '...'
restore_command = '...'
recovery_target_timeline = 'latest'

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

4. Understand pg_hba.conf

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

4.1. Structure of pg_hba.conf_

# 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. 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___HTMLTAG_232__ :
Username
all: all users
ADDRESS:
IP/netmask:&nbsp ;10.0.1.0/24
Hostname
0.0.0.0/0: anywhere (not recommended)
METHOD:
trust: Not needed password (local dev only)
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 for Patroni Cluster

# /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: Do not use 0.0.0.0/0 otherwise need

✅ Use scram-sha-256: Modern authentication method

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

✅ Document: Comment for each rule

✅ Restrict replication: Allow only replication user from IP of Patroni nodes

❌ Avoid trust method: Even in dev environment

5. Create necessary users and databases

5.1. Create replication user

# Sau khi Patroni bootstrap cluster, connect đến primary:
sudo -u postgres psql -h localhost -p 5432
Trong psql:
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'your_strong_password';
Kiểm tra
\du replicator

5.2. Create Patroni monitoring user

-- User cho 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

-- Tạo database
CREATE DATABASE myapp;
-- Tạo 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: Install PostgreSQL on 3 nodes

6.1. Lab Environment

node1 (pg-node1): 10.0.1.11  - Primary (sau khi 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

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL 18

# Thêm 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
Cài đặt
sudo apt install -y postgresql-18 postgresql-contrib-15 postgresql-server-dev-15

Step 3: Stop and disable default cluster

sudo systemctl stop postgresql
sudo systemctl disable postgresql
sudo pg_dropcluster 15 main --stop
Verify
pg_lsclusters
Output: (should be empty)

Step 4: Create directory for PostgreSQL data

# Patroni sẽ quản lý data directory, nhưng ta tạo 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: Test PostgreSQL binary

# Kiểm tra version
postgres --version
Output: postgres (PostgreSQL) 15.5
Kiểm tra các tools
which psql pg_basebackup pg_rewind

6.3. Verify on each node_

# 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 on data directory

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

_Issue 2: PostgreSQL service still running

# Stop forcefully
sudo systemctl stop postgresql@15-main
sudo systemctl disable postgresql@15-main
Kill processes nếu cần
sudo pkill -9 postgres

_Issue 3: Port 5432 is already in use

# Kiểm tra process sử dụng port
sudo lsof -i :5432
Hoặc
sudo netstat -tlnp | grep 5432

7. Summary

Key Takeaways

✅ Package repository__HTMLTAG_355___: Install PostgreSQL from PGDG repo to get the new version most

✅ Disable default service: Patroni will manage PostgreSQL, do not use the default systemd service define

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

✅ pg_hba.conf: Configure authentication for connections and replication

✅ Not initialized cluster: Patroni will automatically bootstrap the cluster

Checklist after Lab

PostgreSQL 18 has been installed on all 3 nodes
Default cluster deleted
PostgreSQL service disabled
Data directories created with permissions correct
Binary paths already exist in $PATH

Preparing for Lesson 6

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

Lesson 5: Install PostgreSQL

Aim_

1. Install PostgreSQL from Package Repository

1.1. Preparation

1.2. Install on Ubuntu/Debian

Step 1: Add PGDG repository

Update package list

Step 2: Install PostgreSQL

Kiểm tra version

Output: psql (PostgreSQL) 15.5

Step 3: Test service

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

Xóa cluster mặc định (Patroni sẽ tạo cluster mới)

Kiểm tra

Output: (empty - no clusters)

1.3. Install on CentOS/RHEL/Rocky Linux

Step 1: Add PGDG repository

Thêm PostgreSQL 18 repository

Disable built-in PostgreSQL module

Step 2: Install PostgreSQL_

Kiểm tra

Output: postgres (PostgreSQL) 15.5

Step 3: Create symlink (optional but convenient)

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

sudo /usr/pgsql-15/bin/postgresql-18-setup initdb

KHÔNG enable service:

sudo systemctl enable postgresql-18

2. Installing PostgreSQL from Source (Optional - Advanced)

2.1. When do you need to install from source?

2.2. Installation process from source

Step 1: Install dependencies

CentOS/RHEL

Step 2: Download source

Step 3: Configure and compile

Compile (sử dụng nhiều cores)

Chạy tests (optional)

Install

Install contrib modules

Step 4: Setup environment

3. Basic postgresql.conf configuration

3.1. File structure postgresql.conf

hoặc: /var/lib/pgsql/15/data/postgresql.conf

FILE LOCATIONS

CONNECTIONS AND AUTHENTICATION

3.2. Important Parameters for HA

Replication Settings_

WRITE-AHEAD LOG (WAL)

REPLICATION

WAL keep settings

Archive settings (để PITR)

Memory Settings

RESOURCE USAGE (MEMORY)

Checkpoint Settings_

WRITE-AHEAD LOG (Checkpoints)

Logging Settings

REPORTING AND LOGGING

Log slow queries

Log connections/disconnections

Log checkpoints (useful for tuning)

3.3. Patroni will override the settings

hot_standby = on

primary_conninfo = '...'

restore_command = '...'

recovery_target_timeline = 'latest'

4. Understand pg_hba.conf

4.1. Structure of pg_hba.conf_

"local" is for Unix domain socket connections only

IPv4 local connections:

IPv4 connections from anywhere (for replication)

Replication connections

4.2. Columns_

4.3. pg_hba.conf for Patroni Cluster

Local connections

Localhost

Application connections

Patroni REST API health checks (optional database connection)