PostgreSQL VPS: Triển Khai Database Server

Cài đặt PostgreSQL từ repository chính thức:

apt install -y curl gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor > /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt update
apt install -y postgresql-16
systemctl enable postgresql
systemctl start postgresql
psql --version

Đăng nhập vào PostgreSQL và tạo database user:

sudo -u postgres psql

CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE myapp_db OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;
q

Cấu hình pg_hba.conf để giới hạn truy cập:

nano /etc/postgresql/16/main/pg_hba.conf

Giới hạn chỉ cho kết nối local và từ app server:

# Chỉ cho local connections
local   all             postgres                                peer
local   all             all                                     md5
# Cho phép app server (thay YOUR_APP_IP)
host    myapp_db        myapp           127.0.0.1/32            md5

Trong postgresql.conf, chỉ listen trên localhost:

listen_addresses = 'localhost'
port = 5432

Nếu cần remote access, dùng SSH tunnel thay vì mở port PostgreSQL ra internet:

ssh -L 5432:localhost:5432 root@VPS_IP -N

Điều chỉnh postgresql.conf theo RAM của VPS (ví dụ 4GB RAM):

nano /etc/postgresql/16/main/postgresql.conf

# Memory settings
shared_buffers = 1GB                # 25% RAM
effective_cache_size = 3GB          # 75% RAM
work_mem = 64MB                     # RAM / max_connections
maintenance_work_mem = 256MB

# Checkpoint settings
checkpoint_completion_target = 0.9
wal_buffers = 64MB

# Query planner
random_page_cost = 1.1              # SSD (dùng 4.0 cho HDD)
effective_io_concurrency = 200      # SSD

# Connections
max_connections = 100

systemctl restart postgresql

Cài đặt PgBouncer để connection pooling:

apt install -y pgbouncer

Script backup tự động với pg_dump:

nano /usr/local/bin/pg_backup.sh

#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp_db"

mkdir -p $BACKUP_DIR
sudo -u postgres pg_dump $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Giữ backup trong 30 ngày
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

chmod +x /usr/local/bin/pg_backup.sh

Thêm vào cron để chạy mỗi ngày lúc 3 giờ sáng:

crontab -e

0 3 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1