Database replication and backups — protect your data

Data loss is not a hypothetical problem—it’s a routine operational risk. Hardware fails, cloud volumes get corrupted, humans run the wrong command, applications have bugs, and attackers deploy ransomware. Database replication and backups are two complementary techniques that protect your data, but they solve different problems. Beginners often assume replication is a backup; it isn’t. This article explains why backups matter, how full vs. incremental backups work, what replication does (and doesn’t) do, and how to build automated, testable backup routines using practical tools like `pg_dump` and `restic`.

Why backups matter (even if you have replication)

Backups exist to let you recover data from the past. They give you a way to return to a known-good state—yesterday, an hour ago, or just before a bad deployment.

Common scenarios where backups save you:

• Human error: `DELETE FROM users;` without a `WHERE` clause, or dropping the wrong table.
• Application bugs: A migration or script corrupts data or overwrites rows.
• Security incidents: Ransomware encrypts your data, or an attacker deletes records.
• Infrastructure failures: Disk/controller failure, corrupted filesystem, failed cloud snapshot.
• Compliance needs: You may need to retain data for months/years.

Replication helps with availability (keeping the service running) and sometimes performance. But if bad data is written to the primary database, replication will happily copy that bad data to replicas. That’s why you need backups: they provide point-in-time recovery to a time before the damage happened.

A useful mental model:

• Replication: “Keep serving requests if a node dies.”
• Backups: “Recover the correct data after something went wrong.”

Backup types: full vs incremental (and where each fits)

A backup is a copy of data that you can restore later. There are several strategies, but two foundational concepts are full and incremental backups.

Full backups

A full backup captures all the data needed to restore your database at that moment.

Pros

• Simple restore procedure (restore one backup).
• Easy to reason about.

Cons

• Can be large.
• Takes longer to create and transfer.
• Higher storage costs if done frequently.

Example use case

• Weekly full backups stored offsite.

Incremental backups

Incremental backups store only the changes since the last backup (full or incremental). In database ecosystems, incremental often means binary logs / WAL (Write-Ahead Log) or filesystem-level incremental backups.

Pros

• Smaller and faster to run frequently.
• Better suited for tight recovery point objectives (RPO), such as “lose at most 5 minutes of data.”

Cons

• Restores are more complex: you usually restore a full backup, then apply incrementals in order.
• More moving parts, more need for testing.

Example use case

• Nightly full backups + WAL archiving every few minutes (or continuous).

Practical takeaway for beginners

If you’re new and your database is small-to-medium, start with:

• Daily full logical backups (e.g., `pg_dump`), kept for 7–30 days, stored offsite.

Then evolve to:

• Periodic full + incrementals (e.g., WAL archiving for PostgreSQL) as your uptime and RPO needs tighten.

Replication: master–slave (primary–replica) basics

Replication means maintaining one or more copies of a database on separate servers. In a common “master–slave” (now often called “primary–replica”) setup:

• Primary (master): accepts writes (INSERT/UPDATE/DELETE).
• Replica (slave): receives changes from the primary and is typically read-only.

Reasons teams use replication:

• High availability: if the primary fails, promote a replica.
• Read scaling: serve read queries from replicas.
• Maintenance flexibility: patch or reboot a node with less downtime.

What replication does not protect you from

Replication is not a time machine. It usually does not protect you from:

• Accidental deletes/updates (they replicate).
• Data corruption due to a buggy app (it replicates).
• Malicious changes made with valid credentials (it replicates).
• Schema mistakes (bad migration replicates).

Replication reduces downtime, but it does not replace backups.

Replication lag and split-brain risks (beginner awareness)

Two common operational issues:

• Replication lag: the replica is behind the primary. If you fail over, you might lose recent transactions.
• Split-brain: two primaries accidentally accept writes, causing divergence. Proper failover tooling and fencing are needed to avoid this.

You don’t need to master these on day one, but you should know they exist—and you should still have backups.

Automated backup strategies (a practical baseline)

A good backup strategy is not just “run a backup.” It’s a plan that answers:

• RPO (Recovery Point Objective): How much data can you afford to lose? (e.g., 24 hours, 1 hour, 5 minutes)
• RTO (Recovery Time Objective): How quickly must you restore service? (e.g., 1 hour)
• Retention: How long to keep backups? (e.g., 30 days daily, 12 months monthly)
• Storage: Where are backups stored (offsite)? Are they encrypted?
• Access control: Who can read/restore backups?
• Verification: How do you know backups can be restored?

The 3-2-1 rule (simple and effective)

A classic guideline:

• Keep 3 copies of your data
• On 2 different media/storage types
• With 1 copy stored offsite

For many small deployments, “offsite” can mean object storage (S3-compatible) in a different region/account, with encryption enabled.

Scheduling backups with cron (example)

A common entry-level automation path is cron:

• Daily database dump
• Followed by pushing to backup storage
• With rotation/retention

Example cron entry (runs daily at 02:15):

```cron 15 2 * /usr/local/bin/backup-postgres.sh >> /var/log/backup-postgres.log 2>&1 ```

Your `backup-postgres.sh` should: 1. Create a dump. 2. Verify it’s non-empty and the command succeeded. 3. Store it offsite (preferably encrypted). 4. Apply retention (delete old backups).

Tooling: `pg_dump` for logical backups

For PostgreSQL beginners, `pg_dump` is a standard tool for creating logical backups (SQL or custom format). Logical backups capture schema and data in a portable way.

Example: dump one database (custom format)

```bash export PGHOST=127.0.0.1 export PGPORT=5432 export PGUSER=backup_user export PGPASSWORD='strong-password'

BACKUP_DIR="/var/backups/postgres" mkdir -p "$BACKUP_DIR"

FILE="$BACKUP_DIR/appdb_$(date +%F).dump"

pg_dump -Fc -d appdb -f "$FILE" ```

Notes:

• `-Fc` uses PostgreSQL’s custom format, which is compressed and supports flexible restore with `pg_restore`.
• Use a dedicated `backup_user` with appropriate privileges.
• Prefer `PGPASSFILE` or a `.pgpass` file over putting passwords in scripts.

Example: restore with `pg_restore`

```bash createdb appdb_restore pg_restore -d appdb_restore /var/backups/postgres/appdb_2026-09-26.dump ```

For large databases, restores can take time; measure it so you understand your RTO.

Tooling: `restic` for encrypted, incremental repository backups

`restic` is a backup tool that can store encrypted backups in many backends (S3, SFTP, local disk, etc.). It deduplicates data and keeps snapshots over time. A common pattern is:

• Use `pg_dump` to create a dump file
• Use `restic` to back up that file to offsite storage with retention rules

Example workflow: dump then restic backup

1) Initialize a restic repository (one-time):

```bash export RESTIC_REPOSITORY="s3:s3.amazonaws.com/your-bucket/restic-postgres" export RESTIC_PASSWORD="a-long-random-passphrase" export AWS_ACCESS_KEY_ID="..." export AWS_SECRET_ACCESS_KEY="..."

restic init ```

2) Backup the dump directory:

```bash restic backup /var/backups/postgres ```

3) Apply retention policy (example: keep 7 daily, 4 weekly, 12 monthly):

```bash restic forget --keep-daily 7 --keep-weekly 4 --keep-monthly 12 --prune ```

This gives you encrypted offsite backups with manageable storage growth.

Testing restores: the step most people skip (and regret)

A backup you haven’t restored is not a backup—it’s a file you hope works.

What to test

At minimum, test:

• Can you retrieve the backup from storage? (permissions, credentials, network)
• Can you decrypt it? (keys/passwords available and correct)
• Can you restore it to a clean environment?
• Does the application work with the restored database? (basic smoke test)
• How long does restore take? (measure RTO)

A simple restore test plan (monthly)

1. Provision a temporary server/container. 2. Fetch the latest backup (e.g., `restic restore` or download the dump). 3. Restore into a fresh PostgreSQL instance. 4. Run sanity checks: - Count key tables - Spot-check recent records - Run application migrations (if appropriate) and start the app against it 5. Document the steps and timings.

Example: restore a file from restic:

```bash mkdir -p /tmp/restore restic restore latest --target /tmp/restore pg_restore -d appdb_restore /tmp/restore/var/backups/postgres/appdb_2026-09-26.dump ```

Putting it together: a beginner-friendly “good enough” setup

If you’re hosting a typical web app on a single PostgreSQL server and want a solid baseline:

1. Replication (optional early on): - Add one replica if uptime is critical and you can manage failover procedures. 2. Daily full logical backups: - `pg_dump -Fc` nightly. 3. Offsite, encrypted storage: - Use `restic` to store dumps in an S3 bucket (or another remote backend). 4. Retention policy: - Keep at least 7–30 days of backups. 5. Restore testing: - Monthly restore drill; record the RTO. 6. Access and security: - Separate credentials for backups. - Restrict who can delete backups. - Consider immutable buckets/object lock where available.

Replication helps you stay online during failures; backups help you recover correct data after mistakes and incidents. Use both, automate them, and—most importantly—practice restores until they’re routine.