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.

We regularly encounter a situation: "The site is not opening" at 3 a.m. — and it turns out that the VPS disk is full because nginx logs haven't been rotated for six months. Or the server went down under load on the day of an advertising campaign launch because the shared hosting had a limit of 50 concurrent connections. Setting up hosting and deployment is not about "where it's cheaper" but about what happens when something goes wrong. Our team helps avoid such incidents by designing infrastructure that accounts for real load patterns.

When to choose Vercel and Netlify?

Vercel is built for Next.js — deploy in one push, preview deployments for every PR, automatic CDN, Edge Functions, ISR without configuration. For frontend projects and JAMstack, it's the optimal choice: no operational overhead, time-to-deploy measured in minutes.

Real limitations: Vercel Serverless Functions run in us-east-1 by default (latency for Europe +80–100ms), Function timeout 300 seconds on Pro, Bandwidth 1TB/month on Pro. For heavy backend, you need workers or a separate server.

Netlify is closer to static sites and Edge Functions based on Deno Deploy. Build minutes are the main limitation on the free tier.

Why is Docker the foundation of predictable deployment?

"It works on my machine" — classic. Docker solves this through environment containerization. But a bad Dockerfile creates new problems.

A typical mistake: copying everything into the image without .dockerignore, resulting in an 800MB image instead of 80MB. node_modules inside the image weighs as much. Correct approach: multi-stage build.

FROM node:20-alpine AS builder
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
RUN npm run build

FROM node:20-alpine AS runner
WORKDIR /app
COPY --from=builder /app/.next ./.next
COPY --from=builder /app/node_modules ./node_modules
COPY --from=builder /app/package.json ./package.json
EXPOSE 3000
CMD ["npm", "start"]

Final image: 180MB instead of 1.2GB. CI build time is reduced due to layer caching — if package.json hasn't changed, the layer with npm ci is taken from cache.

Docker Compose for local development and simple production scenarios: application + PostgreSQL + Redis in one configuration. For production on a single server, it's a perfectly viable option if there's no requirement for horizontal scaling.

More about containerization — Wikipedia: Docker.

How to set up Nginx as a reverse proxy?

Nginx in front of the application is standard for VPS and dedicated servers. Main functions: SSL termination, gzip, static files, rate limiting, upstream load balancing.

A configuration often done incorrectly: worker_processes auto — number of processes equals CPU count. worker_connections 1024 — that's 1024 per worker process. With 4 CPUs and 1024 connections = 4096 concurrent connections. For a high-traffic site, you need worker_connections 4096 and set keepalive_timeout 65.

For static assets with hash in the filename:

location ~* \.(js|css|woff2|png|webp)$ {
    expires 1y;
    add_header Cache-Control "public, immutable";
}

immutable tells the browser: don't revalidate this file even on hard refresh. This only works correctly with content-hashed filenames (which Vite/webpack do by default). Documentation — Wikipedia: Nginx.

AWS: flexibility and complexity

EC2 + Auto Scaling Group — classic for horizontal scaling. AMI with pre-installed application, Launch Template, ASG with min/desired/max instances, Application Load Balancer. When CPU > 70% for 3 minutes — scale out, when CPU < 30% for 15 minutes — scale in. Health check via ALB removes unhealthy instances from rotation.

ECS Fargate — containers without managing EC2. Deploy a Docker image, specify CPU/memory (512 CPU units = 0.5 vCPU, from 512MB memory), Fargate launches it. More expensive than Lambda, but no cold start and no timeout limitations. Suitable for long-running processes, WebSocket servers, heavy workers.

RDS for PostgreSQL with Multi-AZ: automatic failover in 1–2 minutes when primary fails. Read Replicas for scaling reads. RDS Proxy for connection pooling — Lambda functions cannot hold long-term connections, the proxy buffers this.

Kubernetes: when it is justified

K8s adds significant operational complexity. Justified when: multiple teams deploy independent services, fine-grained resource allocation per service is needed, canary deployments and blue/green without downtime are required.

AWS EKS, GKE, or managed k8s from Hetzner (cheaper). Helm charts for standard services. Horizontal Pod Autoscaler based on CPU and custom metrics (RPS via Prometheus).

For most startups and medium-sized projects, Kubernetes is overkill. ECS or Fly.io provide 80% of the capabilities with 20% of the operational complexity.

Monitoring and alerting

A server without monitoring is waiting for an incident. Minimal stack: Prometheus + Grafana (or Grafana Cloud for managed), alerting on disk > 80%, memory > 85%, CPU > 90% over 5 minutes, error rate > 1%. Uptime via Better Uptime or Upptime (self-hosted).

Logs: Loki + Grafana or CloudWatch Logs Insights. Structured JSON logs (winston, pino) are mandatory — otherwise, log searching becomes a pain.

What is included in hosting setup

• Audit of current infrastructure and load profiling
• Selection of target architecture (VPS, AWS, serverless, Kubernetes)
• Setting up CI/CD pipeline (GitHub Actions, GitLab CI) with automatic deployment
• IaC via Terraform or Pulumi (infrastructure as code)
• Configuration of Nginx, SSL certificates, HTTP/2, brotli
• Monitoring and alerting (Prometheus + Grafana, PagerDuty)
• Documentation of runbooks and team training

Additionally, contact us if you need migration from current hosting or integration with external services.

Work process

1. Audit of current infrastructure (2–5 days)
2. Selection of target architecture with load and budget justification (1–3 days)
3. Setting up CI/CD pipeline (GitHub Actions, GitLab CI) (2–5 days)
4. IaC via Terraform or Pulumi (3–10 days)
5. Setting up monitoring and alerting (2–5 days)
6. Documentation of runbooks and team training (1–3 days)

Our experience — 7 years on the market, over 50 projects, guarantee of operability after deployment.

Timeline

• Basic deployment on VPS with Docker + Nginx + CI/CD: 1–2 weeks.
• Setting up AWS infrastructure with Auto Scaling, RDS, CDN: 3–6 weeks.
• Migration to EKS from scratch: 6–12 weeks.
• Setting up Vercel/Netlify for JAMstack: 3–5 days.

The cost is calculated individually depending on complexity and scope of work. Get a consultation — we'll evaluate your architecture in one day.