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
4
5
6
7
8
|
# 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
|
1
2
3
|
mkdir -p /pg-data/archive /var/log/pg
chown -R postgres: /pg-data /var/log/pg
chmod 700 /pg-data
|
Install extensions
#
Install extensions if you need.
1
2
|
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
dnf install postgresql16-contrib pg_wait_sampling_16 pg_stat_kcache_16
|
1
|
systemctl edit postgresql-16
|
1
2
|
[Service]
Environment=PGDATA=/pg-data
|
- /var/lib/pgsql/.pgsql_profile
1
2
3
|
export PGDATA=/pg-data
export PG_HOME=/usr/pgsql-16
export PATH=$PATH:$PG_HOME/bin
|
init postgresql
#
1
2
|
su - postgres
initdb -D /pg-data
|
結果如下
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
|
[postgres@vm152-db-pg16 ~]$ initdb -D /pg-data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pg-data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Taipei
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pg-data -l logfile start
|
編輯 postgresql.conf
#
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
|
Start Datraase and Enabled when Power on
#
1
2
|
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16
|
設定 pg_profile
#
實做方法請參考PostgreSQL 效能分析利器：pg_profile 佈署 SOP (v4.11)
防火牆 - 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
#
