ติดตั้งและตั้งค่า PostgreSQL 11 บน CentOS/RHEL 7

PostgreSQL เป็น Object-Relational Database ที่เข้มงวดกับ SQL Standards, มี MVCC ให้ประสิทธิภาพสูง, รองรับ JSON, Replication, และ Scalability ดี

ติดตั้ง PostgreSQL 11 จาก PGDG Repository

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11-server -y
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11

Firewall และ SELinux

firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload
# หรือ iptables
iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
service iptables save
# SELinux
setsebool -P httpd_can_network_connect_db 1

จัดการฐานข้อมูลด้วย PSQL

sudo -u postgres psql
# เปลี่ยนรหัสผ่าน postgres
ALTER ROLE postgres WITH PASSWORD 'StrongPass';

# สร้าง DB และ User
CREATE DATABASE myapp;
CREATE USER myuser WITH PASSWORD 'StrongPass';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

# คำสั่ง PSQL พื้นฐาน
\l          # List Databases
\c dbname   # Connect to DB
\dt         # List Tables
\du         # List Users
\q          # Quit

# Admin Commands
SELECT * FROM pg_stat_activity WHERE datname='myapp';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='myapp';

Web GUI: ใช้ pgAdmin4 (Python/JS) คล้าย phpMyAdmin

ไฟล์ตั้งค่าสำคัญ (/var/lib/pgsql/11/data/)

• postgresql.conf – Main Config
• pg_hba.conf – Host-Based Authentication (Access Control)
• pg_ident.conf – Ident Mapping

pg_hba.conf – บังคับ Password Authentication:

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

postgresql.conf – พารามิเตอร์สำคัญ:

Backup และ Restore

# Backup (SQL Dump)
sudo -u postgres pg_dump myapp > /backup/myapp_$(date +%F).sql

# Backup (Custom Format + Compress)
sudo -u postgres pg_dump -Fc myapp > /backup/myapp.dump

# Restore (SQL)
sudo -u postgres psql myapp < /backup/myapp.sql

# Restore (Custom Format)
sudo -u postgres pg_restore -d myapp /backup/myapp.dump

Performance Tuning ด้วย PGTune (Online Tool)

เข้า pgtune.leopard.in.ua ใส่: DB Version, OS, DB Type (Web/OLTP/DW), RAM, CPU, Disk Type (SSD/HDD), Max Connections → ได้ postgresql.conf ที่ปรับให้เหมาะสม

ตัวอย่างสำหรับ VPS 4GB RAM, 4 vCPU, SSD, Web App:

max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

เครื่องมืออื่น: Cybertec PostgreSQL Configurator, PostgreSQL Configuration Tool

ตั้งค่า NGINX + PHP-FPM High Performance Web Server (LEMP Stack) บน CentOS 7

ตั้งค่า Date, Time, Timezone และ NTP Sync บน CentOS 7/8