Incremental Database Backup Setup for PostgreSQL and MySQL

Incremental Database Backup Setup

We configure incremental backups for PostgreSQL and MySQL — tailored to your stack and load. Full dumps every night are expensive in disk space and time. When a database grows to tens of gigabytes, a full dump can take hours and strain disk I/O. For example, a full dump of a 500 GB database on HDD may take 8 hours, while an incremental backup takes 15 minutes. Incremental backups save only changes since the last backup, reducing time and volume by 10–50×. Storage savings reach 80%, and recovery time improves 2–5×. We use proven tools: pgBackRest for PostgreSQL, Percona XtraBackup for MySQL, and Restic with deduplication for cloud storage. All solutions include monitoring and alerting. Additional benefit: reduced disk and CPU load. We guarantee that after setup, you'll rest easy — data is safe and recovery takes minutes.

Which Incremental Backup Type to Choose?

• Differential: saves all changes since the last full backup. Recovery: full + one differential.
• Incremental: saves only changes since the last backup of any type. Recovery: full + chain of incrementals.
• WAL-based (PostgreSQL): continuous transaction log archiving, foundation for PITR.

Incremental is more space-efficient but more complex to restore due to chain dependency. We recommend a combination: full weekly, differential daily — balance between size and recovery speed.

How to Set Up WAL Archiving in PostgreSQL?

Install pgBackRest and configure:

# /etc/pgbackrest/pgbackrest.conf [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 repo1-retention-diff=7 log-level-console=info [myapp] pg1-path=/var/lib/postgresql/14/main 

Initialize and run first full backup:

# Initialize stanza and first full backup pgbackrest --stanza=myapp stanza-create pgbackrest --stanza=myapp --type=full backup # Cron schedule: full weekly, differential daily # Full (Sunday 01:00) 0 1 * * 0 pgbackrest --stanza=myapp --type=full backup # Differential (Mon-Sat 01:00) 0 1 * * 1-6 pgbackrest --stanza=myapp --type=diff backup 

For MySQL, use Percona XtraBackup:

# Full backup xtrabackup --backup --target-dir=/var/backups/mysql/full/ # Incremental based on previous xtrabackup --backup --target-dir=/var/backups/mysql/incr1/ \ --incremental-basedir=/var/backups/mysql/full/ xtrabackup --backup --target-dir=/var/backups/mysql/incr2/ \ --incremental-basedir=/var/backups/mysql/incr1/ # Restore: prepare full + apply incrementals xtrabackup --prepare --apply-log-only --target-dir=/var/backups/mysql/full/ xtrabackup --prepare --apply-log-only \ --target-dir=/var/backups/mysql/full/ \ --incremental-dir=/var/backups/mysql/incr1/ xtrabackup --prepare \ --target-dir=/var/backups/mysql/full/ \ --incremental-dir=/var/backups/mysql/incr2/ 

Cloud Storage and Deduplication

For cloud backup storage, use Restic — it supports S3, GCS, B2 and encryption on the fly:

# Initialize repository restic -r s3:s3.amazonaws.com/my-bucket/db-backups init # Backup directory with dumps restic -r s3:s3.amazonaws.com/my-bucket/db-backups \ backup /var/backups/postgres/ \ --password-file /etc/restic-password 

Restic automatically deduplicates blocks, cutting storage by an additional 30–50% on top of compression.

How to Set Up Monitoring and Alerting?

Critical metrics: last backup size (sharp drop signals problem), execution time, success status. Use Healthchecks.io for checks:

# At end of script — check and ping if pgbackrest --stanza=myapp check; then curl -s "https://hc-ping.com/${HC_UUID}" else curl -s "https://hc-ping.com/${HC_UUID}/fail" fi 

Also configure metric collection via Prometheus and Grafana dashboards to see trends and react in time.

Typical Setup Mistakes

• No backup rotation: old backups pile up, disk fills. Set retention in pgBackRest or custom scripts.
• Forgetting monitoring: if a backup fails, you only find out when data is lost. Configure alerts.
• Skipping test restores: a backup may be corrupt. Restore regularly on a test server.
• Wrong type selection: for large databases with frequent changes, WAL archiving is preferred.

How We Test Restoration

Every configured backup is tested by restoring to a staging environment. We create a copy of the database, perform a full restore, and verify checksums. Automated verification scripts run after each backup. This ensures data can be recovered at any moment. We document the exact restore sequence in a runbook.

What's Included in Setup

• Audit of current backup scheme and load testing
• Selection of optimal strategy (full + differential or WAL)
• Tool configuration (pgBackRest, XtraBackup) for your RDBMS
• Rotation and cleanup scripts
• Monitoring and alerting integration (Healthchecks.io, Prometheus)
• Restoration documentation (runbook)
• Training your team on basic administration

We have configured incremental backups for over 50 projects — from startups to enterprise. Over 5 years, no backup has failed: all data is restored on first request. We guarantee data recovery if the maintenance schedule is followed. We assess your project in 1 day. Get a consultation on backup setup — we'll choose a strategy for your infrastructure.

Timeline

Setup of pgBackRest or XtraBackup with incremental strategy — 1–2 business days.