Muhammad Ali Mughal

Posted on Oct 31

SaaS Backup & Disaster Recovery: RPO, RTO and PITR

#devops #postgres #mysql #backend

A nightly pg_dump to the same VPS is not a backup strategy. It is a file that disappears with the disk, the compromised root account or the provider incident that took the server down in the first place, and even when it survives it can only take you back to 2 AM. For a SaaS product, the real questions are how much data you can afford to lose (RPO), how long you can afford to be down (RTO), and whether anyone has actually restored from your backups recently.

At Loop Origin we run Laravel and Node SaaS platforms on VPS infrastructure for clients, and backup design is part of every server setup and maintenance engagement. This is the setup I deploy by default, with the configuration, restore commands and drill schedule.

What is a good backup and disaster recovery setup for a SaaS on a VPS?

Direct answer: Combine continuous database archiving for point-in-time recovery (PostgreSQL WAL with pgBackRest or WAL-G, MySQL binary logs) with periodic full backups pushed to versioned, write-protected object storage in a separate account and region. Define RPO and RTO per tier, encrypt everything, and run a timed restore drill every month.

RPO and RTO tiers: pick one per system, not per company

RPO (recovery point objective) is how much recent data you can lose. RTO (recovery time objective) is how long recovery may take. They drive cost, so set them per system rather than declaring "zero data loss" for everything.

Tier	Typical RPO	Typical RTO	Mechanism	Fits
Tier 0	Seconds	Minutes	Streaming replica plus WAL/binlog archiving, scripted failover	Payments, orders, core tenant data
Tier 1	Minutes	1-4 hours	Continuous WAL/binlog archive plus daily full backup, restore to new VPS	Most B2B SaaS primary databases
Tier 2	24 hours	1 day	Nightly full backup to object storage	Analytics copies, internal tools
Tier 3	Rebuildable	Days	Infrastructure as code, no data backup	Caches, queues, search indexes rebuilt from source

Most clients I work with land on Tier 1 for the primary database and Tier 2 or 3 for everything else. Redis queues and Elasticsearch-style indexes are usually Tier 3: rebuild them from the database instead of backing them up. My guide on running Laravel queues in production covers why failed jobs should live in the database rather than only in Redis.

Why dumps alone are not enough

A logical dump (pg_dump, mysqldump) is a consistent snapshot at one moment. If a bad migration or an accidental DELETE without a WHERE runs at 4:40 PM, a nightly dump takes you back to the previous night and you lose the day. Point-in-time recovery (PITR) closes that gap: restore the last full physical backup, then replay the database's change log up to the second before the mistake.

Dumps are still useful for moving individual tenants, seeding staging and long-term archives. They are just not the recovery mechanism for the primary.

PostgreSQL PITR with pgBackRest

postgresql.conf changes (restart required for archive_mode and wal_level):

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60          # force a WAL segment switch at least every 60s

/etc/pgbackrest/pgbackrest.conf:

[global]
repo1-type=s3
repo1-s3-bucket=acme-db-backups
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com
repo1-s3-region=eu-central-1
repo1-path=/pgbackrest
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<from secrets manager>
compress-type=zst
start-fast=y

[main]
pg1-path=/var/lib/postgresql/16/main

Initialize, verify and schedule:

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check          # confirms WAL archiving works
sudo -u postgres pgbackrest --stanza=main --type=full backup   # weekly via cron/systemd timer
sudo -u postgres pgbackrest --stanza=main --type=diff backup   # daily
sudo -u postgres pgbackrest --stanza=main info

archive_timeout = 60 is what makes the RPO roughly one minute on a quiet database: without it, a low-traffic system may not fill and ship a 16 MB WAL segment for a long time.

The restore you will run when someone drops a table at 16:40:

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
  --type=time --target="2026-10-31 16:39:00+05" --target-action=promote restore
sudo systemctl start postgresql

In practice I restore to a new server first, verify the data, then cut over or copy the missing rows back. Restoring over the only production copy removes your ability to try again.

WAL-G is the main alternative: archive_command = 'wal-g wal-push %p', wal-g backup-push $PGDATA for base backups, and wal-g backup-fetch $PGDATA LATEST plus restore_command = 'wal-g wal-fetch %f %p' and recovery_target_time for PITR. I prefer pgBackRest for its built-in check, retention and parallel restore, and WAL-G where a team already uses it.

MySQL and MariaDB PITR with binary logs

# my.cnf
[mysqld]
server_id = 1
log_bin = /var/lib/mysql/binlog
binlog_format = ROW
binlog_expire_logs_seconds = 1209600   # 14 days, longer than your full-backup interval
sync_binlog = 1

Take a consistent full backup with Percona XtraBackup for large databases, or mysqldump --single-transaction --source-data=2 for smaller ones (--source-data replaced --master-data in MySQL 8.0.26). Ship binlogs off the server continuously, for example with mysqlbinlog --read-from-remote-server --raw --stop-never running on a backup host.

Recovery: restore the full backup, read the binlog file and position recorded in it, then replay up to just before the incident:

mysqlbinlog --start-position=157 --stop-datetime="2026-10-31 16:39:00" \
  binlog.000412 binlog.000413 | mysql -u root -p

--stop-datetime is interpreted in the local time zone of the machine running mysqlbinlog, a detail that has caught more than one team restoring on a UTC server.

Object storage: where backups survive the incident

The repository must survive the loss of the server and of the credentials on it:

Separate account or project for the backup bucket, with a write-only key on the server. A stolen server key should not be able to delete backups.
Versioning plus object lock (S3 Object Lock, or the equivalent on S3-compatible providers that support it) with a retention period longer than your detection window.
A different region or provider from the primary VPS.
Client-side encryption (pgBackRest's repo1-cipher-type, or restic for file backups) with the passphrase stored outside the server.
File uploads (storage/app, user uploads) backed up with restic or rclone, or written directly to object storage in the first place.

Cost drivers, compared

Component	What drives cost	How to control it
Object storage	Total stored size × retention	Compression (zstd), differential backups, retention limits
Request and egress fees	Restore downloads, frequent small uploads	Choose providers with low egress; restore drills to the same region
Standby replica (Tier 0)	A second always-on server	Use only for systems that truly need minute-level RTO
Restore drill servers	Hours of a temporary VPS per month	Script the drill, destroy the server afterwards
Engineer time	Setup, monitoring, drills	Automate checks and alerts; document the runbook

For most Tier 1 SaaS databases the storage bill is small next to the replica and engineering time. My VPS vs managed PaaS cost comparison shows where managed databases with built-in PITR change the calculation.

Failure modes I have seen

Archiving silently broken. archive_command fails, WAL piles up in pg_wal and the disk fills. Monitor pg_stat_archiver.failed_count and the age of last_archived_time.
Backups nobody restored. Encryption passphrase lost, wrong PostgreSQL major version, missing extensions. Only a drill finds these.
Same blast radius. Backups in a bucket the compromised server can delete.
Binlog retention shorter than the backup interval. A gap makes PITR impossible past the last full backup.
Forgotten tenants' files. The database restores, but uploaded documents do not.
Shared-database multi-tenancy surprises. Restoring one tenant from a shared physical backup needs a side restore and a selective copy; plan it. My multi-tenant SaaS database architecture guide covers the trade-offs.

Monthly restore drill runbook

Provision a clean VPS from infrastructure code.
Restore the latest full backup plus WAL or binlogs to a target time from yesterday.
Time it end to end; compare against your RTO.
Run integrity checks: row counts on key tables, latest order or invoice timestamp, application smoke test.
Restore a sample of uploaded files and open them.
Record duration, issues and the backup set used in the runbook.
Destroy the drill server and rotate any credentials it used.

Frequently asked questions

What is the difference between RPO and RTO?

RPO is the maximum acceptable data loss, measured as time between the last recoverable point and the incident. RTO is the maximum acceptable time to restore service. A system can have a one-minute RPO and a four-hour RTO, and the two are engineered with different tools.

Is pg_dump enough for a production SaaS database?

Not as the only mechanism. It restores to the moment the dump ran, so anything after that is lost. Use physical backups with WAL archiving for point-in-time recovery, and keep logical dumps for tenant exports, staging data and long-term archives.

pgBackRest or WAL-G?

Both provide base backups, WAL archiving to object storage, compression, encryption and PITR. pgBackRest has a built-in configuration check, retention management and parallel restore. WAL-G is lighter and common in container setups. Choose the one your team will monitor and drill.

How often should I test restoring backups?

At least monthly for production databases, and after any major version upgrade, storage change or backup configuration change. A drill should restore to a fresh server, be timed against your RTO and include an application-level check, not just a successful exit code.

Should backups be stored in the same cloud provider?

A different region at minimum, and ideally a separate account with write-only credentials from the server and object lock enabled. A different provider adds protection against account-level incidents at the cost of egress fees during restores.

Do I need to back up Redis?

Usually not, if Redis holds caches, sessions and queues that can be rebuilt or retried. Keep durable state such as failed jobs and anything business-critical in the main database. Back up Redis only if it is your primary store for data you cannot regenerate.

DEV Community