Install & Hardening PostgreSQL di Linux (Production–Enterprise Guide)

Dokumen ini merangkum cara install PostgreSQL di Linux (Debian/Ubuntu & RHEL/Rocky/Alma), daftar versi dan lifecycle/EOL, arsitektur HA (high availability) dari level production hingga enterprise, serta security best practices. Fokus pada operational playbook agar bisa langsung dipakai.

Audience: Sysadmin/DevOps/DBA • OS: Debian/Ubuntu, RHEL/Rocky/AlmaLinux • PostgreSQL: 13–17 (dan 18 saat rilis GA)

1) Ringkas: Versi & Lifecycle

PostgreSQL rilis mayor setahun sekali (sekitar September) dan disupport 5 tahun sejak rilis awal. Artinya, tiap versi punya tanggal End of Life (EOL). Rekomendasi umum: gunakan minor terbaru pada major yang masih didukung.

Major	Rilis	Perkiraan EOL	Catatan
17	2024-09	2029-11	Stable saat ini, fitur logical repl. & kinerja makin matang
16	2023-09	2028-11	Umum dipakai di LTS distro/cloud
15	2022-11	2027-11	Stabil luas
14	2021-09	2026-11	Stabil, mendekati EOL
13	2020-09	2025-11-13	Segera rencana upgrade
12	2019-10	2024-11-14	EOL (hindari di production)

2) Cara Install di Linux

2.1 Debian/Ubuntu (PGDG APT Repo)

sudo apt update
sudo apt install -y curl ca-certificates lsb-release

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail   https://www.postgresql.org/media/keys/ACCC4CF8.asc

echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17 postgresql-contrib-17

psql --version
sudo systemctl status postgresql

2.2 RHEL/Rocky/Alma (PGDG YUM/DNF Repo)

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17-server postgresql17-contrib

sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17

/usr/pgsql-17/bin/psql --version
sudo systemctl status postgresql-17

3) Konfigurasi Dasar (pasca install)

3.1 Lokasi berkas penting

Debian/Ubuntu: /etc/postgresql/<major>/main/, /var/lib/postgresql/<major>/main
RHEL family: /var/lib/pgsql/<major>/data

3.2 postgresql.conf

listen_addresses = '0.0.0.0'
port = 5432
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
max_wal_senders = 10
max_replication_slots = 10
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_min_duration_statement = 1000
password_encryption = 'scram-sha-256'
ssl = on

3.3 pg_hba.conf

hostssl  all     all      10.10.0.0/16   scram-sha-256
hostnossl all    all      0.0.0.0/0      reject

3.4 User & DB awal

\password postgres
CREATE ROLE app_user LOGIN PASSWORD 'kuat';
CREATE DATABASE appdb OWNER app_user;
GRANT CONNECT ON DATABASE appdb TO app_user;

4) High Availability (HA)

4.1 Pola umum

Proxy: HAProxy/Envoy
Pooling: PgBouncer
Replikasi: streaming (sync/async), logical
Failover: Patroni atau repmgr
Backup: pgBackRest/Barman

4.2 Streaming Replication

Primary postgresql.conf:

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
synchronous_standby_names = 'ANY 1 (standby1, standby2)'

User repl:

CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'superkuat';

Standby bootstrap:

pg_basebackup -h <PRIMARY_IP> -D <DATA_DIR> -U repl -X stream -C -S slot_standby1 --progress

4.3 Patroni contoh

scope: pg-ha
namespace: /service/
name: node1
restapi:
  listen: 0.0.0.0:8008
  connect_address: node1:8008
etcd:
  host: etcd1:2379,etcd2:2379,etcd3:2379
bootstrap:
  dcs:
    synchronous_mode: true
    postgresql:
      parameters:
        wal_level: replica
postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1:5432
  data_dir: /var/lib/postgresql/17/main

5) Backup & PITR

5.1 pgBackRest

sudo -u postgres pgbackrest --stanza=demo stanza-create
sudo -u postgres pgbackrest --stanza=demo backup
sudo -u postgres pgbackrest --stanza=demo restore --delta --type=time "--target=2025-09-01 10:00:00"

5.2 Barman

Server terpisah, pull/push backup, katalog multi-server, PITR

6) Security Best Practices

TLS wajib (ssl=on)
scram-sha-256 untuk password
Batasi akses network
Minimalkan SUPERUSER
Role terpisah (app_user, ro_user)
File config mode 600
Gunakan pgaudit
OS hardening: firewall, SELinux/AppArmor
Jangan expose langsung ke internet

7) Monitoring & Tuning

postgres_exporter + Prometheus + Grafana
Ekstensi: pg_stat_statements, auto_explain
Pantau: replication lag, bloat, deadlocks, slow queries

8) Upgrade Strategy

Minor upgrade: apt/dnf upgrade + restart
Major upgrade: pg_upgrade (downtime singkat) atau Logical Replication (hampir zero-downtime)

9) Checklist Go‑Live

[ ] Versi supported & minor terbaru
[ ] TLS aktif
[ ] scram-sha-256 aktif
[ ] Tidak ada trust di pg_hba.conf
[ ] Role minimal privilege
[ ] Backup diuji restore
[ ] Monitoring aktif
[ ] HA failover diuji

Lampiran A — pg_hba.conf

hostnossl all all 0.0.0.0/0 reject
hostssl all app_user 10.50.0.0/16 scram-sha-256

Lampiran B — HAProxy

frontend psql
  bind *:6432
  mode tcp
  default_backend pg_rw

backend pg_rw
  mode tcp
  option tcp-check
  server pg1 10.10.1.11:5432 check
  server pg2 10.10.1.12:5432 check backup

Lampiran C — PgBouncer

[databases]
appdb = host=127.0.0.1 port=6432 dbname=appdb auth_user=pgbouncer

[pgbouncer]
listen_port = 6433
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 100
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Referensi

PostgreSQL Versioning Policy & EOL
PGDG APT/YUM repo
Patroni, repmgr, CloudNativePG
pgBackRest, Barman
CIS PostgreSQL Benchmark