PostgreSQL Backup Strategy: When to Use pg_dump, Physical Backups, and WAL Archiving?

The most valuable component of an enterprise application running on PostgreSQL is its data; the server can be reinstalled and the code pulled from the repository, but lost data cannot be recovered. In this article, we cover backup methods for PostgreSQL, which one fits which need, and practical practices that turn the phrase "we have a backup" into "we can recover."

Two fundamental methods

Logical backup: pg_dump / pg_dumpall

Exports the database contents in SQL or a custom archive format. It is cross-version portable, makes restoring a single table easy, and is sufficient for small to medium-sized databases. The disadvantage is that changes between the backup moment and the incident moment are lost, and it can take a long time on large databases.

# Özel formatta, sıkıştırılmış, tek veritabanı
pg_dump -Fc -Z 6 -d app -f /backups/app_$(date +%F_%H%M).dump

# Küme genelindeki roller ve ayarlar (ayrıca alınmalı)
pg_dumpall --globals-only -f /backups/globals_$(date +%F).sql

Physical backup: pg_basebackup + WAL archiving

Takes a consistent copy of the data directory; when combined with a WAL (write-ahead log) archive, point-in-time recovery (PITR) to a desired point becomes possible. This is the only way for the scenario: "recover the table accidentally deleted at 14:32 back to 14:31." It is the standard approach for large databases and low data loss tolerance.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

# Temel yedek
pg_basebackup -D /backups/base_$(date +%F) -Ft -z -Xs -P

In enterprise environments, tools like pgBackRest or Barman manage this process, offering full/incremental backups, retention policies, validation, and off-site repository support.

Which method for which situation?

NeedRecommended Small database, daily loss acceptableDaily pg_dump + off-site copy Minute-level loss tolerancePhysical backup + WAL archive (PITR) Need to restore a single table / schemapg_dump archive format (selective restore) Before upgrading versionsBoth methods together

3-2-1 and immutability

Three copies, two different environments, one copy in another location. Object storage (S3-compatible services or your own MinIO installation) is a common choice for off-site copies; features like "object lock" ensuring backups cannot be deleted for a certain period are critical in ransomware scenarios. The off-site copy must be protected with separate permissions so it cannot be deleted using the production server's credentials.

Encryption

Backup files contain personal and financial data; encryption in transit and at rest is mandatory. The key must not be kept in the same place as the backup; otherwise, encryption is merely superficial. Loss of the key means loss of the backup; key management is part of the plan.

A backup without a restore drill is just a hope. The drill should not be performed on the day the backup is actually needed for the first time.

Restore drills: Measure RTO and RPO

Regularly restore the backup to a separate server (or temporary container), connect the application to it, run basic operations, and record the duration. If the measured duration exceeds your recovery time objective (RTO), the method must change. In Docker-based environments, drills can be automated:

docker run --rm -d --name pg_restore_test -e POSTGRES_PASSWORD=test postgres:16
docker cp /backups/app_latest.dump pg_restore_test:/tmp/app.dump
docker exec pg_restore_test pg_restore -U postgres -d postgres --create /tmp/app.dump
docker exec pg_restore_test psql -U postgres -d app -c "select max(created_at) from orders;"

Monitoring: Knowing when a backup fails

You must monitor not just whether the backup job ran, but whether it completed successfully and the file is of the expected size. The rule "last successful backup older than 24 hours" should trigger an alert. Silently failing backups are the most common disaster scenario.

Supabase self-hosting note

If you run Supabase on your own server, a PostgreSQL backup alone is not enough: storage objects, configuration, and secret keys must also be backed up; otherwise, the database returns, but files and authentication configuration do not.

Minimum plan proposal

1. Nightly pg_dump + continuous WAL archive
2. Weekly physical base backup
3. Encrypted off-site copy, with separate permissions, protected against deletion
4. Quarterly restore drills with durations recorded
5. Alerting for backup failures

Point-in-time recovery (PITR) step by step

If you have a physical backup and WAL archive, it is possible to return to a specific point in time. The conceptual flow is as follows:

# 1. Temel yedeği yeni veri dizinine açın
mkdir /var/lib/postgresql/restore && tar -xzf /backups/base_2026-10-01/base.tar.gz -C /var/lib/postgresql/restore

# 2. Kurtarma hedefini ve WAL kaynağını belirtin (postgresql.conf veya postgresql.auto.conf)
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-10-03 14:31:00+03'
recovery_target_action = 'promote'

# 3. recovery.signal dosyasını oluşturun ve sunucuyu bu dizinle başlatın
touch /var/lib/postgresql/restore/recovery.signal

The server applies WAL records up to the target time and then resumes normal operation. This process should be tested in a drill environment, not for the first time during a real incident; the most common point of failure for PITR is a missing file in the WAL archive, and only a drill will reveal this.

Retention policy: How far back?

Keeping daily backups for a month, weekly backups for three months, and monthly backups for a year is a common starting point; regulations (retention period for accounting records) and personal data retention rules shape this policy. Note: when personal data needs to be deleted, there must also be a plan for copies remaining in backups; most organizations solve this with the rule "backups are deleted when their retention period expires" and ensuring this period complies with the personal data policy.

Practical notes on large databases

• Running pg_dump in parallel (directory format and -j option) shortens the duration.
• Separate backup frequencies can be defined for large tables (logs, event records); it is not necessary to back up all history every night.
• Backups should not be taken during the production server's peak hours, but when the load is low; this impact is lower in physical backups.
• Backup compression and encryption consume CPU; this must be factored into server resource planning.

Replication is not a backup

Hot standby (streaming replication) provides high availability; however, an accidentally deleted table is reflected on the replica within seconds. Replication and backups complement each other, they do not replace one another. Similarly, disk snapshots provide fast recovery, but they must be coordinated with the database for consistency and copied to a separate location.

Backing up in a Docker environment

In PostgreSQL running with Docker Compose, backups should be taken via a separate backup container or a cron job on the host, not from inside the container; as long as the data volume is attached, pg_dump works over the container network. Container image version and backup format compatibility (pg_dump version must not be older than the database version) must be monitored.

# Örnek: Compose ortamında zamanlanmış pg_dump (ana makine cron'u)
0 2 * * * docker exec app_db pg_dump -U app -Fc app | gpg --encrypt -r backup@ornek > /backups/app_$(date +%F).dump.gpg

Documentation

The backup plan must be written down: what, how often, where, by whom, and how it is restored. Recovery steps must be written clearly enough for someone unfamiliar with the system to follow; the person who knows the system best may not be reachable during a disaster.

In Cy Logic's server and cloud infrastructure services, a backup strategy is a defined component of the setup, not an optional add-on.