Backup dan Pemulihan Database PostgreSQL: Panduan Lengkap untuk Pengguna AlexHost

Mengapa Strategi Backup PostgreSQL Lebih Penting Daripada yang Anda Pikirkan

Kehilangan data bukan risiko hipotetis — ini adalah kepastian operasional yang akan dihadapi setiap administrator database di beberapa titik. Kegagalan hardware, penghapusan yang tidak disengaja, transaksi yang rusak, dan serangan ransomware semuanya dapat menghancurkan lingkungan produksi dalam hitungan detik. Bagi pengguna PostgreSQL, memiliki strategi backup yang kuat, teruji, dan otomatis adalah perbedaan antara insiden kecil dan kegagalan bisnis yang katastrofal.

AlexHost Dedicated Servers menyediakan fondasi ideal untuk hosting dan melindungi database PostgreSQL. Dengan penyimpanan NVMe SSD tingkat enterprise yang memberikan throughput I/O luar biasa, akses root penuh untuk kontrol konfigurasi lengkap, dan perlindungan DDoS bawaan, AlexHost memberikan performa infrastruktur dan postur keamanan yang diminta oleh beban kerja database serius.

Baik Anda menjalankan platform e-commerce dengan lalu lintas tinggi, aplikasi SaaS, instalasi WordPress yang didukung oleh database relasional, atau sistem enterprise kustom, panduan ini memandu Anda melalui setiap metode backup dan recovery PostgreSQL utama — dari SQL dumps sederhana hingga Point-in-Time Recovery (PITR) canggih — semuanya dioptimalkan untuk lingkungan produksi.

Daftar Isi

1. Memahami Opsi Backup PostgreSQL
2. Prasyarat dan Persyaratan Privilege
3. Metode 1 — SQL Dump dengan pg_dump
4. Metode 2 — Backup Semua Database dengan pg_dumpall
5. Metode 3 — Custom Format Backups untuk Database Besar
6. Restore dari SQL Dumps
7. Restore dari Custom Format Dumps
8. Metode 4 — Continuous Archiving dan Point-in-Time Recovery (PITR)
9. Mengotomatisasi Backups dengan Cron
10. Mengamankan dan Menyimpan Backups Offsite
11. Ringkasan Best Practices

1. Memahami Opsi Backup PostgreSQL

PostgreSQL dilengkapi dengan beberapa mekanisme backup yang matang dan terdokumentasi dengan baik. Memilih yang tepat tergantung pada ukuran database Anda, recovery time objectives (RTO), recovery point objectives (RPO), dan toleransi kompleksitas operasional.

Memahami trade-off ini sebelum Anda mulai sangat penting. Sebagian besar lingkungan produksi mendapat manfaat dari menggabungkan setidaknya dua pendekatan — misalnya, custom format dumps malam hari bersama dengan continuous WAL archiving untuk kemampuan recovery yang granular.

2. Prasyarat dan Persyaratan Privilege

Sebelum menjalankan operasi backup apa pun, konfirmasi prasyarat berikut sudah ada:

User Privileges:

• Anda harus menjadi superuser PostgreSQL atau pemilik database target untuk melakukan backup penuh.
• Untuk pg_dumpall, privilege superuser adalah wajib.

Verifikasi versi PostgreSQL Anda:

psql --version

Periksa ruang disk yang tersedia sebelum backup:

df -h /var/lib/postgresql/

Pastikan tujuan backup Anda memiliki ruang bebas yang cukup — minimum 1,5× ukuran database yang sedang di-backup untuk memperhitungkan file sementara dan overhead kompresi.

Hubungkan ke server Anda melalui SSH:

ssh root@your-server-ip

Jika Anda menggunakan paket VPS Hosting, Anda akan memiliki akses SSH penuh dan kemampuan untuk menginstal, mengonfigurasi, dan mengelola PostgreSQL tanpa batasan.

3. Metode 1 — SQL Dump dengan pg_dump

Utilitas pg_dump adalah alat backup PostgreSQL yang paling umum digunakan. Ini menghasilkan snapshot konsisten dari satu database, bahkan saat database sedang digunakan secara aktif. Output adalah skrip SQL plain-text yang dapat ditinjau, diedit, dan diputar ulang pada instalasi PostgreSQL yang kompatibel.

Langkah 1: Buka Terminal dan Akses Server Anda

ssh root@your-alexhost-server-ip

Langkah 2: Jalankan Perintah pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Penjelasan parameter:

Contoh praktis:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Pro Tip: Menambahkan timestamp menggunakan $(date +%Y%m%d_%H%M%S) ke nama file backup Anda memastikan Anda tidak pernah secara tidak sengaja menimpa backup sebelumnya dan membuat arsip kronologis alami.

Langkah 3: Verifikasi File Backup

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

File harus dimulai dengan komentar header PostgreSQL dan pernyataan SET, mengkonfirmasi dump yang valid telah dibuat.

4. Metode 2 — Backup Semua Database dengan pg_dumpall

Ketika Anda perlu backup setiap database dalam instance PostgreSQL — termasuk objek global seperti roles dan tablespaces — pg_dumpall adalah alat yang tepat.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Perintah ini mengekspor:

• Semua database
• Semua roles (users dan groups)
• Semua tablespaces
• Semua konfigurasi global

Penting: File output dari pg_dumpall dapat sangat besar di server yang sibuk. Pastikan partisi backup Anda memiliki ruang yang cukup dan pertimbangkan untuk mengompresi output segera:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Metode 3 — Custom Format Backups untuk Database Besar

Untuk database produksi yang melebihi beberapa gigabyte, format kustom (-F c) sangat direkomendasikan daripada plain SQL dumps. Custom format backups adalah:

• Terkompresi secara default — secara signifikan mengurangi persyaratan penyimpanan
• Lebih cepat untuk restore — mendukung operasi restore paralel dengan flag -j
• Dapat dipulihkan secara selektif — memungkinkan Anda untuk restore tabel atau schema individual

Membuat Custom Format Backup

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Membuat Compressed Directory Format Backup (Mendukung Parallelism)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

Pendekatan ini secara dramatis mengurangi durasi backup pada server multi-core, menjadikannya ideal untuk lingkungan dedicated server berkinerja tinggi yang tersedia di AlexHost.

6. Restore dari SQL Dumps

Restore Database Tunggal dari Plain SQL Dump

Pertama, pastikan database target ada. Jika tidak, buatlah:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Kemudian restore:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Penjelasan parameter:

Monitor Restoration Progress

Untuk file SQL besar, Anda dapat memantau progress menggunakan pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Restore dari Custom Format Dumps

Custom format dumps memerlukan utilitas pg_restore bukan psql.

Basic Restore

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Restore dan Buat Database Secara Otomatis

Gunakan flag -C untuk menginstruksikan pg_restore untuk membuat database sebelum mengisinya:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Parallel Restore untuk Recovery Lebih Cepat

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Menggunakan -j 4 dengan directory format backup dapat mengurangi waktu restore hingga 75% pada server quad-core — keuntungan signifikan saat meminimalkan downtime selama disaster recovery.

Restore Tabel Spesifik Saja

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Kemampuan granular ini adalah salah satu keuntungan utama format kustom daripada plain SQL dumps.

8. Metode 4 — Continuous Archiving dan Point-in-Time Recovery (PITR)

PITR adalah standar emas untuk deployment PostgreSQL mission-critical. Ini memungkinkan Anda untuk restore database Anda ke momen spesifik apa pun — bukan hanya backup terakhir — dengan memutar ulang segmen Write-Ahead Log (WAL) di atas backup dasar. Ini penting untuk skenario di mana Anda perlu recover dari kesalahan logis (seperti DROP TABLE yang tidak disengaja) yang terjadi pada timestamp yang diketahui.

Langkah 1: Aktifkan WAL Archiving di postgresql.conf

Temukan dan edit file konfigurasi PostgreSQL Anda:

nano /etc/postgresql/15/main/postgresql.conf

Tambahkan atau modifikasi direktif berikut:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Penjelasan parameter:

Buat direktori archive dan atur izin yang benar:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Restart PostgreSQL untuk menerapkan perubahan:

systemctl restart postgresql

Langkah 2: Ambil Base Backup dengan pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Langkah 3: Restore ke Specific Point in Time

Untuk restore dari base backup dan WAL archives:

1. Hentikan PostgreSQL:

systemctl stop postgresql

1. Hapus direktori data yang ada:

rm -rf /var/lib/postgresql/15/main/*

1. Ekstrak base backup:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Buat recovery.conf (PostgreSQL 11 dan lebih awal) atau konfigurasi postgresql.conf dan buat file recovery.signal (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Tambahkan ke postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Atur kepemilikan yang benar dan mulai PostgreSQL:

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL akan memutar ulang segmen WAL hingga timestamp yang ditentukan dan kemudian promote ke status normal read-write.

9. Mengotomatisasi Backups dengan Cron

Backup manual tidak dapat diandalkan. Mengotomatisasi jadwal backup Anda dengan cron memastikan konsistensi dan menghilangkan faktor kesalahan manusia.

Buat Backup Script

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

Buat script dapat dieksekusi:

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

Jadwalkan dengan Cron

crontab -e

Tambahkan baris berikut untuk menjalankan backup setiap malam pada pukul 2:00 AM:

0 2 * * * /usr/local/bin/pg_backup.sh

Untuk full backups mingguan plus daily incremental WAL archiving, gabungkan ini dengan setup PITR yang dijelaskan di bagian sebelumnya.

10. Mengamankan dan Menyimpan Backups Offsite

Backup yang disimpan di server yang sama dengan database produksi Anda bukan backup nyata — ini adalah single point of failure. Implementasikan praktik keamanan dan penyimpanan offsite berikut:

Enkripsi Backups Sebelum Transfer

gpg --symmetric --cipher-algo AES256 /backups/my_production_db.dump

Transfer Backups ke Lokasi Remote dengan rsync

rsync -avz --progress /backups/postgresql/ user@remote-backup-server:/remote/backups/postgresql/

Gunakan pg_dump dengan SSH Pipe untuk Direct Remote Backup

pg_dump -U postgres my_production_db | gzip | ssh user@remote-server "cat > /backups/my_production_db_$(date +%Y%m%d).sql.gz"

Firewall Rules untuk PostgreSQL (UFW)

Batasi akses port PostgreSQL hanya ke IP terpercaya:

ufw allow from 192.168.1.0/24 to any port 5432
ufw deny 5432
ufw enable

Untuk tim yang mengelola multiple projects di berbagai hosting tiers, paket AlexHost Shared Web Hosting juga mendukung database management tools yang dapat melengkapi workflow backup Anda untuk project yang lebih kecil.

11. Ringkasan Best Practices

Mengimplementasikan backup PostgreSQL dengan benar memerlukan disiplin dan pendekatan berlapis. Ikuti best practices ini untuk memastikan data Anda selalu terlindungi: