Deploy Postgresql on Oracle Linux 8
#
Why Select 16 Version?
#
因為在 AWS 的長期支援板(LTS) 當前是使用 16.8，所以可以預期的是在未來的擴充套件有級大的可能是在 16.8 上進行開發。如果要採用社群維護可以參考Versioning Policy
Lab System
#
- Platfrom: Oracle Linux 8
- Architecture: x86_64
- Database Vserion: Postgresql 15
Step by Step
#
- Upgrate system
1
2
|
sudo dnf update
sudo dnf install -y wget tar
|
1
2
3
|
mkdir -p /pg-data/archive /var/log/pg
chown -R postgres: /pg-data /var/log/pg
chmod 700 /pg-data
|
1
2
3
4
5
6
7
8
9
10
11
|
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
# Install PostgreSQL:
sudo dnf install -y postgresql16-server
dnf install postgresql16-contrib pg_wait_sampling_16 pg_stat_kcache_16
dnf install https://github.com/ossc-db/pg_hint_plan/releases/download/REL16_1_6_2/pg_hint_plan16-1.6.2-1.pg16.rhel8.x86_64.rpm
|
1
|
systemctl edit postgresql-16
|
1
2
|
[Service]
Environment=PGDATA=/pg-data
|
- /var/lib/pgsql/.pgsql_profile
1
2
|
export PG_HOME=/usr/pgsql-16
export PATH=$PATH:$PG_HOME/bin
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
listen_addresses = '*' # 設定要監聽的網卡IP
# 日誌調整
log_directory = '/var/log/pg' # 預設為: log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, redhat 預設為 postgresql-%a.log
log_line_prefix = '%m [%p] %q%u@%d-%a ' # special values 預設為: '%m [%p] '
log_checkpoints = on # special values 預設為: off
log_connections = on # 記錄連線建立，預設為: off
log_disconnections = on # 記錄連線摧毀，預設為: off
log_duration = off # 記錄SQL query的執行時間，預設為: off，核心系統再開
log_statement = 'ddl' # option: none, ddl, mod, all，預設為: none，可以紀錄執行哪個錯誤SQL,DDL 操作SQL
log_timezone = 'Asia/Taipei'
shared_buffers = 4GB # RAM 的 25% ~ 50%
work_mem = 32MB # ORDER BY, JOIN (each session)
maintenance_work_mem = 1GB # RAM 的 1/16, 維護性任務，如 VACUUM、CREATE INDEX、ALTER TABLE
max_parallel_workers = 8 # 與 CPU 核心數一致
max_parallel_workers_per_gather # 單一查詢建議使用核心數的一半
shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache, pg_wait_sampling, pg_hint_plan'
# ARCHIVELOG
# 必須重啟才會生效
wal_level = replica
archive_mode = on
# 當 WAL 填滿 (16MB) 時執行的指令，%p 是來源路徑，%f 是檔名
# 'test ! -f ...' 是為了確保不覆蓋已存在的檔案，增加安全性
archive_command = 'test ! -f /pg-data/archive/%f && cp %p /pg-data/archive/%f'
# 強制每 10 分鐘（600秒）歸檔一次，避免交易量小時歸檔間隔太長
archive_timeout = 600
|
- /db/postgresql/pg_hba.conf
1
|
host all all 172.18.17.0/24 scram-sha-256
|
AWR ? pg_profile
#
實做方法請參考PostgreSQL 效能分析利器：pg_profile 佈署 SOP (v4.11)
Start Datraase and Enabled when Power on
#
1
2
|
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
|
pg_archivecleanup
防火牆 - Firewall
#
1
|
firewall-cmd --permanent --add-service=postgresql
|
不重啟的情況下修改部分參數
#
1
2
3
4
5
|
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET max_parallel_workers = 8;
-- 執行後需載入設定
SELECT pg_reload_conf();
|
檢查 Archive 運作狀況
#
1
|
SELECT * FROM pg_stat_archiver;
|
1
2
3
4
5
6
7
|
postgres=# SELECT * FROM pg_stat_archiver;
archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_reset
----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+---------------------------
----
16 | 000000010000000000000010 | 2026-05-03 15:52:20.072353+08 | 0 | | | 2026-04-30 23:38:25.612094
+08
|
How to resolve ERROR
#
Unable start DB: invalid value for parameter “shared_buffers”: “4G”
#
使用手動方式啟用檢查訊息
1
|
sudo -u postgres /usr/pgsql-16/bin/postgres -D /pg-data/
|
錯誤訊息如下
1
2
3
4
5
6
|
[root@vm125-db-pg16 pg-data]# sudo -u postgres /usr/pgsql-16/bin/postgres -D /pg-data/
2026-04-30 16:11:24.150 GMT [51805] LOG: invalid value for parameter "shared_buffers": "4G"
2026-04-30 16:11:24.150 GMT [51805] HINT: Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".
2026-04-30 16:11:24.150 GMT [51805] LOG: invalid value for parameter "maintenance_work_mem": "1G"
2026-04-30 16:11:24.150 GMT [51805] HINT: Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".
2026-05-01 00:11:24.151 CST [51805] FATAL: configuration file "/pg-data/postgresql.conf" contains errors
|
Referance
#
