Point-in-time Postgres recovery on Kubernetes: CloudNativePG backups to object storage

It was 14:18 UTC on a Thursday when our main Slack alert channel lit up. A rogue autonomous agent run, deployment code-named “Project Oracle”, had entered an infinite retry loop. Armed with a poorly scoped SQL statement and a high-concurrency celery worker pool, it executed a cascade of unconstrained DELETE operations. Within 90 seconds, the agent had wiped 4.2 million rows of high-value system prompts, conversation histories, and vector metadata from our production PostgreSQL instance.

Our core service was down. We are an AI agent platform; without those prompts and memory contexts, our models were hallucinating or outright failing.

Our last scheduled physical backup had run at 02:00 UTC—more than twelve hours prior. Restoring from that backup meant losing half a day of customer interactions, fine-tuning feedback loops, and expensive vector embeddings that had taken hours of GPU compute to generate.

To resolve this, we needed Point-in-Time Recovery (PITR). We needed to reconstruct the state of our database precisely at 14:14:00 UTC, exactly 60 seconds before the rogue agent initiated its purge.

This post details how we architected, configured, and successfully executed a PITR using CloudNativePG (CNPG) on Kubernetes, backing up directly to AWS S3.

The Architecture: Why standard snapshots fail AI workloads

For high-throughput AI workloads, nightly snapshots are an operational relic. Vector databases and agentic state tables are highly dynamic. If you rely solely on daily physical disk snapshots or simple pg_dump cron jobs, you are accepting a Recovery Point Objective (RPO) of up to 24 hours. In our case, that was unacceptable.

To achieve an RPO of minutes—or even seconds—we must continuously capture changes as they occur. Postgres achieves this via the Write-Ahead Log (WAL). Every transaction is written to the WAL before it is applied to the data pages.

By continuously shipping these WAL files to secure object storage (a process called wal archiving) and combining them with a periodic baseline physical backup, we can reconstruct the database state at any arbitrary microsecond in the past.

Here is how the data flows within our Kubernetes cluster:

flowchart LR
 PrimaryCluster["Primary Postgres Pod"]
 WALArchiver["CloudNativePG WAL Archiver"]
 S3Bucket["AWS S3 Object Storage"]
 RecoveryCluster["Recovery Postgres Pod"]
 PrimaryCluster -->|"Write-Ahead Logs"| WALArchiver
 WALArchiver -->|"wal archiving"| S3Bucket
 PrimaryCluster -->|"Base Backups"| S3Bucket
 S3Bucket -->|"Restore Base + Play WAL"| RecoveryCluster

CloudNativePG manages this loop natively using an operator pattern. It injects a sidecar manager into the Postgres pods that continuously streams WAL segments to our S3 bucket without relying on external tools like pgBackRest or Barman daemon processes inside the container.

The Setup: Configuring CloudNativePG for S3 backups

To get this working, we first need to configure our Kubernetes namespace with the necessary AWS IAM credentials and define a CloudNativePG Cluster that knows how to write to our bucket.

1. Kubernetes secrets for AWS S3 access

We use an IAM user with fine-grained access to our backup bucket, prod-pg-backups-uswest2. We define these credentials inside a Kubernetes secret in the same namespace as our database.

2. The production Cluster manifest

Next, we define our Cluster resource. The critical block is the backup configuration, which details our barmanObjectStore endpoint, destination bucket, credentials, and compression choices.

Apply these files to write your initial configuration:

Once applied, CloudNativePG boots the cluster, elects a primary, starts replicating to two hot standbys, and immediately initiates WAL archiving.

We can verify that WAL files are streaming to our S3 bucket using the AWS CLI:

The output should show regular 16MB WAL segments compressed as .gz files:

Executing a Point-in-Time Recovery

Back to our incident. The catastrophic data deletion occurred exactly at 2024-10-24 14:15:22 UTC. We need to restore the database to 2024-10-24 14:14:00 UTC.

We will perform this recovery by spinning up a completely separate database cluster (pg-ai-recovered). This isolated environment allows us to verify the integrity of our recovered data before swapping production traffic over, eliminating the risk of overwriting remaining data on the damaged live cluster.

1. Step 1: Create the Recovery Cluster Manifest

To instruct CNPG to perform a PITR, we use the bootstrap.recovery block in our new cluster definition. We point it to our existing backup path in S3 and specify our recovery target time in UTC.

Save this to cluster-recovery.yaml and apply it:

Under the Hood: Monitoring the restore loop

When you apply this manifest, the CloudNativePG operator takes the following actions:

1. It spins up a temporary bootstrap pod for the recovery cluster.
2. It downloads the closest physical base backup taken before our target time (2024-10-24 02:00:00 UTC) directly from S3.
3. It configures Postgres in recovery mode by auto-generating a recovery.signal file and setting configuration parameters like recovery_target_time.
4. It streams and replays the sequence of archived WAL logs starting from the snapshot time up to exactly 2024-10-24 14:14:00 UTC.
5. Once it hits the target timestamp, Postgres exits recovery mode, promotes itself to a writable primary database, and spins up the replica instances.

Let’s watch the logs of our recovery pod to verify this process:

You should see log output detailing the restoration steps:

To get a structured overview of the status, run:

Output:

Verifying the Restored Data

With our cluster healthy, we need to verify that our lost AI prompt templates and agent sessions are safely intact. Let’s connect directly to the recovered instance and count the records.

Once inside the interactive terminal, run:

Expected Output:

The records are there. No transaction logged after 14:14:00 exists. The rogue execution loop was deleted from history, and we successfully recovered our system prompt and memory records with minimal disruption.

Lessons from the Trenches

Through our experience setting up and executing point-in-time recoveries under pressure, we learned several critical operational lessons:

1. Keep WAL transmission latency low: By default, Postgres waits until a 16MB WAL segment is full before archiving it. For low-traffic clusters, this can delay updates to your remote storage. We recommend setting archive_timeout = 60 in your Postgres parameters to force segment switching every minute, ensuring you never risk losing more than 60 seconds of data.
2. Watch your S3 Rate Limits: Replaying hundreds of gigabytes of WAL files triggers massive burst read activity on your object storage. Ensure your AWS account limits can handle thousands of concurrent GET requests, or use a localized S3 gateway endpoint inside your VPC to avoid latency and throttling issues.
3. Automate Recovery Drills: Backups are only as reliable as your ability to restore them. We now run a weekly automated test suite that restores a clone of our production database to a sandbox environment using PITR, validates our table schemas, and deletes the temporary cluster.