Runbook — PostgreSQL Backup & Restore

Server Reference

Variable	Nilai saat ini
$SERVER_USER	enalfarid
$SERVER_HOST	ptikn3-vm.cluster-vps.dalang.io
$SERVER_PORT	2262
Repo path	/home/$SERVER_USER/kesles_merchant
Backup dir	/var/backups/kesles-db

Ganti $SERVER_USER dan path terkait jika server user berubah. Cukup update tabel ini sebagai satu-satunya referensi.

Scope: all Kesles databases on the production server (192.168.58.234:5432) — db_backup.sh dumps every database in $DATABASES: db_kesles_merchant, db_kesles_merchant_auth, db_kesles_merchant_content, db_kesles_merchant_inventory, db_kesles_merchant_marketing, db_kesles_merchant_notification, db_kesles_merchant_order, db_kesles_merchant_partner, db_kesles_merchant_payment, db_kesles_merchant_planning, db_kesles_merchant_poslite, and db_reference.

Tooling:

• scripts/db_backup.sh — nightly dump + retention + optional MinIO mirror.
• scripts/db_restore.sh — interactive restore helper with integrity check + sanity summary.

1. Initial setup (one-time)

1.1 Install prerequisites on the server

sudo apt update
sudo apt install -y postgresql-client coreutils

postgresql-client provides pg_dump and pg_restore. coreutils is already default on Ubuntu, including sha256sum and find.

Optional (for mirroring to MinIO):

curl -fsSL https://dl.min.io/client/mc/release/linux-amd64/mc \
  -o /usr/local/bin/mc
sudo chmod +x /usr/local/bin/mc
mc alias set keslesmerchant http://192.168.58.234:9000 kesles '<MINIO_SECRET>'
mc mb --ignore-existing keslesmerchant/db-backups

1.2 Prepare credentials without plaintext in the shell

Store the password in ~/.pgpass (user $SERVER_USER), mode 600:

cat > ~/.pgpass <<'EOF'
192.168.58.234:5432:*:kesles:<PASSWORD>
EOF
chmod 600 ~/.pgpass

pg_dump / pg_restore / psql automatically use this file without needing the PGPASSWORD env.

1.3 Prepare the local backup directory

sudo mkdir -p /var/backups/kesles-db
sudo chown $SERVER_USER:$SERVER_USER /var/backups/kesles-db
sudo chmod 750 /var/backups/kesles-db

1.4 Clone / sync repo to the server

cd /home/$SERVER_USER/kesles_merchant
git pull  # ensure scripts/ is up to date
chmod +x scripts/db_backup.sh scripts/db_restore.sh

---

2. Run a manual backup (verify setup)

cd /home/$SERVER_USER/kesles_merchant
./scripts/db_backup.sh

Expected output:

[2026-04-23T19:00:00Z] Dump db_kesles_merchant -> /var/backups/kesles-db/db_kesles_merchant-20260423-190000.dump
[2026-04-23T19:00:03Z]   ok, size=12M sha256=abc123...
[2026-04-23T19:00:03Z] Dump db_reference -> /var/backups/kesles-db/db_reference-20260423-190003.dump
[2026-04-23T19:00:06Z]   ok, size=4.2M sha256=def456...
[2026-04-23T19:00:06Z] Prune local backups older than 30 hari
[2026-04-23T19:00:06Z] Backup sukses: db_kesles_merchant db_reference @ 20260423-190000

Quick verification:

ls -lh /var/backups/kesles-db/
# Make sure each DB has a matching .dump + .dump.sha256 pair.

# Dry-run read header — confirms the file is not corrupt:
pg_restore --list /var/backups/kesles-db/db_kesles_merchant-*.dump | head

---

3. Set up the nightly cron

File: /etc/cron.d/kesles-db-backup

# Kesles — nightly PostgreSQL backup to /var/backups/kesles-db/
# Runs at 02:00 WIB (UTC+07) = 19:00 UTC the previous day.
0 2 * * * $SERVER_USER cd /home/$SERVER_USER/kesles_merchant && \
    MINIO_ALIAS=keslesmerchant \
    MINIO_BUCKET_PATH=keslesmerchant/db-backups \
    ./scripts/db_backup.sh >> /var/log/kesles-backup.log 2>&1

MINIO_ALIAS is set if you have completed the optional step 1.1. If not, drop the two MINIO_* lines and the script still runs — local backup only.

Verify the cron is scheduled:

sudo systemctl reload cron
sudo grep -i cron /var/log/syslog | tail -5
cat /var/log/kesles-backup.log   # the next morning

---

4. Restore for verification (monthly restore test)

Goal: confirm the backup can really be restored, not just garbage files.

Pattern: restore the latest backup to a temporary database (*_restore_test), run smoke queries, then drop it.

cd /home/$SERVER_USER/kesles_merchant

# 4.1 Pick the latest backup
latest=$(ls -t /var/backups/kesles-db/db_kesles_merchant-*.dump | head -1)
echo "Will restore: $latest"

# 4.2 Restore to a temporary DB
./scripts/db_restore.sh \
    "$latest" \
    db_kesles_merchant_restore_test \
    --yes-i-understand

# 4.3 Smoke queries
psql -h 192.168.58.234 -U kesles db_kesles_merchant_restore_test <<'SQL'
SELECT count(*) AS merchants
  FROM merchant.merchant_registration_requests
 WHERE deleted_at IS NULL;

SELECT count(*) AS outlets
  FROM merchant.merchant_outlets
 WHERE deleted_at IS NULL;

SELECT max(created_at) AS last_activity
  FROM merchant.merchant_registration_requests;
SQL

# 4.4 Drop the temporary DB (don't forget!)
psql -h 192.168.58.234 -U kesles -c \
    'DROP DATABASE IF EXISTS db_kesles_merchant_restore_test' postgres

Record the date the restore test succeeded in this log (or in the team calendar). If more than 45 days pass without a restore test, our backup is PRACTICALLY unproven as restorable.

---

5. Emergency production restore

🚨 Before restoring to a production DB, confirm with the team first. Restore = overwrite data; there is a data loss window since the last backup.

5.1 Gather information

• When was the last backup? ls -t /var/backups/kesles-db/*.dump | head -1
• When did the incident happen? (→ confirm the data loss window)
• Are there append-only columns that can be merged from the application log?

5.2 Stop traffic to the backend

ssh -p $SERVER_PORT $SERVER_USER@$SERVER_HOST
sudo systemctl stop core-api merchant-dashboard-api
# If using Cloudflare: temporarily enable maintenance mode / 503 rule.

5.3 Back up the current (broken) state

Always back up the current state before overwriting — if the restore fails or it turns out the issue can be patched without a full rollback, we still have the "before" state.

cd /home/$SERVER_USER/kesles_merchant
BACKUP_DIR=/var/backups/kesles-db/pre-restore-$(date -u +%Y%m%d-%H%M%S) \
    ./scripts/db_backup.sh

5.4 Restore to the production DB

FORCE_OVERWRITE=1 ./scripts/db_restore.sh \
    /var/backups/kesles-db/db_kesles_merchant-20260423-020000.dump \
    db_kesles_merchant \
    --yes-i-understand

5.5 Start backend + verify

sudo systemctl start core-api merchant-dashboard-api
systemctl status core-api
journalctl -u core-api --since '5 minutes ago' | tail -30

# Smoke test from the laptop:
curl -sS "https://kesles.com/merchant/api/public/legal-documents?code=terms_of_service" \
    | head -c 200

5.6 Post-mortem

• Record: cause of the incident, when it was detected, when restore completed.
• Compute the data loss window (backup time → incident time).
• Create action items to prevent recurrence (e.g. add WAL streaming for sub-minute PITR granularity).

---

6. Troubleshooting

6.1 pg_dump: error: connection to server at "…" failed

• Dev VPN (10.8.0.1) vs server IP (192.168.58.234) — make sure the PGHOST value matches the environment.
• Check pg_hba.conf on the DB server allows the source IP.

6.2 mc: command not found in the cron log

Script runs without mc (skip mirror). If you need an off-site copy, install mc (optional step 1.1) then re-run a manual backup to confirm.

6.3 Backup size suddenly 10× larger

• Check psql -c "SELECT pg_size_pretty(pg_database_size('db_kesles_merchant'));". Is there a new large table? Has VACUUM FULL never been run?
• Consider pg_dump --exclude-table=merchant.bigtable if a log/audit table can be backed up separately with different retention.

6.4 Restore test fails — SHA mismatch

• Backup is corrupt. Try the previous backup (ls -t ... | head -2 | tail -1).
• If 2+ consecutive backups fail SHA, check the VM disk (dmesg, SMART).

6.5 Retention accidentally deleted the latest backup

Not possible with -mtime +30 — only deletes files older than 30 days. If in doubt, check:

find /var/backups/kesles-db -type f -mtime +30 -print

without the -delete flag → list only.

---

7. Alerting (optional, recommended)

Set up at least one of the following so you know when the backup fails:

7.1 Telegram bot

Add at the end of db_backup.sh (or the cron wrapper):

if (( FAILED > 0 )); then
    curl -sS -o /dev/null \
        -d chat_id=$TELEGRAM_CHAT_ID \
        -d text="🚨 Kesles backup gagal $FAILED db — $(hostname) @ $TS" \
        "https://api.telegram.org/bot$TELEGRAM_TOKEN/sendMessage"
fi

7.2 Email via postfix/ssmtp

Cron defaults to email MAILTO= to root. Forward root to the team email:

echo "root: team@kesles.com" | sudo tee -a /etc/aliases
sudo newaliases

Non-empty stdout/stderr from the cron job → automatic email.

---

8. Monthly checklist

• Restore test successful (step 4) — date: ……
• Backup size consistent vs last month (not suddenly dropping to 0)
• MinIO mirror populated per MINIO_RETENTION_DAYS retention
• Rotate credentials if > 90 days old
• Review merchant_docs/api_docs/internal/docs/dev/database/operations/backup-restore.md — update if the backup scheme changes

---

Changelog

• 2026-04-23: initial version, nightly pg_dump -Fc + 30-day local retention + 90-day MinIO retention.