PostgreSQL VPS: Hosting Offshore Cho Database Production

PostgreSQL cung cap kha nang ma cac database thuong mai khac thuong tinh phi rieng: JSONB index, partial index, materialized view, va Logical Replication. Khi ban chay tren VPS offshore thay vi DBaaS quan ly, ban tiet kiem 60-80% chi phi cho cung kha nang xu ly va co them quyen tu do trien khai extension.

Cac doi tuong huong loi ro ret tu mo hinh nay:

• SaaS multi-tenant: Schema-based isolation kho cau hinh tren RDS nhung de dang tren PostgreSQL self-hosted.
• Analytics noi bo: Khoi luong query phuc tap khong bi tinh phi theo query unit nhu BigQuery.
• Ung dung GIS: PostGIS extension co the cai dat khong gioi han kich thuoc index.
• Bao chi dieu tra: Du lieu nguon tin trong jurisdiction Iceland duoc bao ve manh me hon nhieu so voi cloud My.

Goi VPS Pro cua AnubizHost voi 4 vCPU, 8GB RAM va 100GB NVMe phu hop cho PostgreSQL phuc vu vai chuc nghin user, voi gia chi phi co dinh khong tang dot bien theo workload.

PostgreSQL 16 mang lai cai tien dang ke ve logical replication va parallel query. Cai dat tren Debian 12:

apt update && apt install -y curl ca-certificates
install -d /usr/share/postgresql-common/pgdg
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" \
  > /etc/apt/sources.list.d/pgdg.list
apt update && apt install -y postgresql-16 postgresql-contrib-16

Sau khi cai dat, dieu chinh /etc/postgresql/16/main/postgresql.conf. Voi VPS 8GB RAM, dat shared_buffers = 2GB, effective_cache_size = 6GB, maintenance_work_mem = 512MB va wal_level = replica. Cong cu pgtune.leopard.in.ua co the sinh cau hinh tu dong dua tren tai nguyen VPS.

Bao mat ket noi bang cach dat ssl = on va sinh chung chi self-signed hoac Lets Encrypt. Trong pg_hba.conf, chi cho phep hostssl tu IP da xac thuc va dung phuong thuc scram-sha-256. Tat trust va password plaintext - day la nguyen tac bao mat khong thoa hiep.

VPS offshore co the bi mat ket noi nguon hoac phan cung trung tam du lieu tam thoi gap su co, du AnubizHost cam ket SLA 99.9%. Vi vay streaming replication la bat buoc cho moi truong production.

Tren server primary, tao replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'strong_password';
-- pg_hba.conf
hostssl replication replicator REPLICA_IP/32 scram-sha-256

Tren server standby (mua them mot VPS o vi tri khac cua AnubizHost de cach ly rui ro), chay pg_basebackup:

pg_basebackup -h PRIMARY_IP -D /var/lib/postgresql/16/main \
  -U replicator -P -R -X stream -C -S replica_slot_1

Standby se dong bo lien tuc qua WAL streaming. Khi can failover, su dung pg_promote() de bien standby thanh primary trong vai giay. Ket hop voi repmgr hoac Patroni de tu dong hoa toan bo quy trinh failover voi consensus etcd.

Backup logic du phong nen lam song song bang pg_dump hang ngay, ma hoa va day len storage offsite. Tat ca lop bao ve nay dam bao du lieu cua ban an toan ngay ca khi mot vung trung tam du lieu gap su co lon.