PostgreSQL adalah database relasional paling powerful yang open source. Banyak startup dan perusahaan besar menggunakannya karena reliability dan fitur-fiturnya yang lengkap.
Instalasi PostgreSQL 17
Kita akan install PostgreSQL 17 versi terbaru di Ubuntu:
sudo apt update
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-17
Konfigurasi untuk Production
Edit postgresql.conf:
# Memory - sesuaikan dengan RAM server
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 4MB
maintenance_work_mem = 128MB
# WAL
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
# Query Planning
random_page_cost = 1.1
effective_io_concurrency = 200
# Logging
log_min_duration_statement = 1000
log_checkpoints = on
Buat Database dan User
CREATE USER myapp WITH PASSWORD 'secure_password_here';
CREATE DATABASE mydb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;
Backup Otomatis
Buat script backup harian:
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U myapp mydb | gzip > /backups/mydb_$DATE.sql.gz
find /backups -mtime +7 -delete
Tambahkan ke crontab:
0 2 * * * /root/backup.sh
Monitoring
Gunakan pg_stat_statements untuk monitoring query performance:
CREATE EXTENSION pg_stat_statements;
Dengan setup ini, PostgreSQL kamu siap untuk menangani traffic production.