Setting Up PostgreSQL Backup via WAL-G

WAL-G PostgreSQL backup: what breaks

The nightly full dump completes with exit code 0, reports are green, but after data corruption the cluster comes up from a copy that is a day old. Operations from the past day — orders, payments, reference data edits — have to be collected manually from external systems.

An error in the dump is only revealed at the moment of an incident, when there is no time left to sort it out.

The root cause is the gap between copies. A logical dump captures the state at the moment it is taken, and everything that happened afterward is not included.

Database recovery is only possible to the moment of the last snapshot: there is no way to choose a point between dumps, and the dump itself takes hours on a large cluster.

<cite>WAL-G</cite> closes this gap: the base backup is taken rarely, while the transaction log archive is continuously replenished. The recovery point shifts from a day to a specific second — you can roll back to the state before an erroneous update.

Configuring WAL-G in this case includes a retention policy, encryption, and upload to object storage.

A separate part of the work is regular verification: the archive is restored on a separate staging environment, integrity is checked, and recovery time is measured. Without this, there is an archive, but you cannot bring up the cluster from it.

Below we analyze where such a scheme breaks most often and what PostgreSQL backup looks like that survives an incident.

What a continuous transaction log archive provides

A daily full dump answers the question 'how to recover by morning' but does not cover the last hours of work.

A failure or unsuccessful migration in the middle of the day rolls the database back to the nightly copy, and everything that happened after it has to be collected manually from application logs.

Continuous backup with WAL-G removes this hole: transaction log segments are sent to object storage as they fill, not on a schedule.

The recovery point shrinks from a day to minutes — effectively to the segment upload interval. Recovery proceeds in two steps: the nearest base backup is restored, and the log is replayed on top of it up to the desired label.

This allows you to stop a second before an unsuccessful migration or erroneous deletion, rather than 'at the last night'.

Database recovery time becomes predictable: base backups are taken less frequently and in the background, without a nightly window when the server is busy with the dump.

Data protection stops depending on a single schedule — the archive is verified by a trial restore on a separate staging environment, so a corrupt segment is found before an incident, not during it.

As a result, an entire class of failures — 'loss of the last hours of work' — disappears, and returning to a checkpoint becomes a routine operation.

WAL-G PostgreSQL archiving modes: comparison of approaches

WAL-G performs two independent actions: it continuously uploads write-ahead log segments to object storage and periodically takes a full database snapshot.

Recovery always relies on the snapshot, and the log only replays changes to the desired point — therefore, the choice of archiving mode is primarily a question of the acceptable data loss window and predictability of recovery time.

In practice, four schemes are compared. The difference manifests not at setup time, but at the moment of an incident, when the database must be brought up within an allotted window.

The hybrid backup scheme covers most tasks: a scheduled snapshot provides a reliable foundation, a continuous archive provides a point within the interval, and a read replica offloads part of the load and reduces downtime during a failure.

Let us emphasize separately: a replica is not a backup. An erroneous deletion or data corruption is instantly repeated on it, so an independent copy in storage with versioning and separate permissions remains mandatory.

Which archiving mode you need depends on the required recovery point; we will discuss this when we discuss your task.

Implementation: from cluster audit to handover of procedures

The work order is structured so that by the time archiving is enabled in production, the team already knows that recovery works. We start with an audit: cluster size, write intensity, replicas, current logging mode.

We design the storage scheme before making changes to the production configuration — otherwise the very first run hits an overfull storage or deleted logs.

1. Cluster audit. Version, replica topology, log generation rate, existing scripts and their schedule. The output is a list of gaps that currently prevent continuous archiving.

2. Storage scheme. Object storage, layout of base backups and logs, retention and deletion rules — the registry does not grow uncontrollably, and rollback to the desired point remains possible.

3. Archiving setup. Logging parameters, access, encryption, rotation. WAL-G handles delivery of logs and base backups to storage, and it also verifies archive integrity.

4. Verification on a separate staging environment. We restore a copy from the archive, measure full rollback time and point-in-time rollback, and verify checksums.

5. Monitoring automation. Daily test restore, alerts about a broken log chain and outdated copy, report on archive freshness.

6. Training the on-call shift. We practice scenarios: full rollback, point-in-time recovery, restoring an individual table, behavior when storage is unavailable.

7. Handover of procedures. Scheme, configurations, step-by-step instructions, checklists, responsibility matrix — the on-call engineer restores the cluster without calling a developer.

The set of configurations, schemes, and procedures at the output

After implementation, the customer is left not with a set of disparate commands, but with a reproducible package: using it, backups can be brought up again on a new node, verified by the on-call shift, and handed over to another team without sorting through chat logs. Below is the composition that we document in writing.

• Configuration files for log and base backup archiving — with comments for each parameter: retention depth, schedule, rotation, handling of write failures to the repository.
• Infrastructure scheme: where databases reside, where copies are written, which nodes and failure zones are involved, where encryption takes place and how keys are stored.
• Access policies: roles for reading and writing the repository, who is allowed to start recovery, separation of duties, and the procedure for granting and revoking rights.
• Backup procedure: copy types, frequency, retention period, recovery points to a given point in time, and actions when space runs out.
• Recovery verification log: date, volume, time to database operability, checksum verification result, and a list of discrepancies found.
• Incident response procedure: step-by-step scenario with decision points, criteria for switching to a copy, and a rollback plan.
• Instructions for the on-call shift and alerts: which archiving metrics are monitored, at what thresholds the signal triggers, and to whom it is addressed.

The package is updated with every configuration change — otherwise the backup documentation diverges from reality within a couple of months. Describe your current environment, and we will come back with an estimate and work plan.

Case study: recovery after disk array damage

At night, the disk array controller failed, and the database went into read-only mode. The last logical dump was taken almost a day before the incident, so an estimated 20 hours of operations were lost: orders, payment statuses, catalog edits.

The service was down for nine hours while the copy was being restored from the dump, and discrepancies with external systems were sorted out manually for several more days.

We switched to continuous upload of write-ahead logs to object storage: a daily base snapshot, on top of it — log segments every few minutes, separate retention policies, and integrity control by checksums.

WAL-G encrypts the archive on the database side, continues the chain after a channel break, and allows restoring the state to an arbitrary point in time rather than to the snapshot moment.

Old copies do not accumulate: a lifecycle rule keeps the required number of generations and cleans up the rest, so the archive does not grow uncontrollably.

The recovery point shrank from a day to minutes, and database recovery — from nine hours to 25 minutes on a separate environment.

Once a quarter we conduct drills: we restore a copy from the archive, compare the number of records and key aggregates, and measure the time to service readiness.

We catch corrupt segments and discrepancies during drills, not at the moment of an incident — data integrity is confirmed by checks, not assumptions.

How does a log archive differ from logical replication?

A replica is often confused with a backup, although their horizon is different.

A replica constantly applies changes from the primary node and keeps the state at the current moment: a few seconds after an erroneous change without a condition, it will have the same corrupted data set. It does not store history — it cannot be rolled back, because the past simply is not in it.

A log archive is structured the opposite way: it is a continuous chain of changes accumulated over time.

Point-in-time recovery restores a base backup and replays changes up to the desired label — the state one minute before a failure, before an unsuccessful schema change, or before data corruption by an upload.

The rollback depth is set by the archive retention period, not by the moment of the last synchronization, so any points in the past within this window are available.

Logical replication solves a third task — it transmits changes as parsed operations and allows maintaining a read replica. Reports and heavy queries leave the primary node without interfering with writes, and failover takes seconds.

But such a replica covers only the current state and spreads a logical error across all nodes at the same speed.

Therefore, the mechanisms do not replace but complement each other: the replica covers availability and read load, the archive covers preservation and rollback to any point in the past.

Log collection is built on top of object storage, and WAL-G adds incremental copies and integrity checks before restore — recovery stops depending on whether the node survived.

Technical doubts: integrity, compatibility, verification

Before switching to continuous archiving, engineers ask the same set of questions: what about compatibility, how to verify a copy, who can see the archive, and how long it lives. Below are direct answers: they also become acceptance criteria.

How can we be sure the copy will actually restore?

An unverified copy is a hope, not a guarantee. We restore the archive on a separate staging environment on a schedule, verify data integrity, and restore the state to an arbitrary point in time.

We record the result of the latest verification in a log — it is visible when the copy was actually brought up.

Is the archive encrypted and who has access to it?

Encryption is enabled before sending to storage, the key lives outside the cloud bucket: without it, the archive contents cannot be read even with access to the objects. We rotate keys and store them separately from the copies themselves.

How long do copies live and what happens if the connection to storage breaks?

The period is set by the retention policy: we keep fresh copies densely, thin out old ones, then objects are deleted automatically. If the channel breaks, the archive is not lost — logs accumulate locally and go to the bucket when the connection is restored, only the lag grows.

Let's discuss your backup task and come back with a plan

The first step is to understand what you already have. Send the server version, approximate database size, current backup procedure, and requirements for the data loss window: this is enough to estimate the effort and propose a working scheme. If copies are not taken at all or are done manually, that is also a normal starting point.

The estimate does not require access to the production environment: first, analysis based on the description and logs. If we need to look at actual configurations and backup history, we will agree on a window and minimal permissions so as not to touch anything on the live system.

What is included in the work:

• backup audit: we check that copies are actually taken, read, and restored, not just taking up space;
• set up backup on WAL-G: continuous log writing plus base backups to object storage;
• recovery plan: step-by-step scenario to an arbitrary point in time with verification on a copy of the production database;
• retention policy and schedule: how many full copies and logs we keep, when and what we delete;
• monitoring with alerts about failures, log lag, and checksum discrepancies;
• documentation and handover to the on-call shift: recovery commands, control points, escalation procedure;
• database support: regular scheduled checks, quarterly test restore, incident response.

Describe the task and current state — we will come back with an estimate, scheme, and stage-by-stage work plan.