Why PITR Matters in CNPG

PostgreSQL can restore a cluster to a chosen moment rather than only to the end of the last backup. That matters most when the damage is human: a dropped table or database, a wrong update, or an accidental delete. CloudNativePG (CNPG) exposes this capability declaratively, using a Barman Cloud plugin as the backup and WAL archive. The walkthrough below covers a full cycle: recording a safe recovery point, dropping a table on purpose, and bootstrapping a new cluster that stops just short of the mistake.

Setting Up the Failure

Start from an existing CNPG cluster instance:

postgres=# create database pitr_test;
CREATE DATABASE

postgres=# \c pitr_test
You are now connected to database "pitr_test" as user "postgres".

Create a table and populate it, then change a few rows so the restored data is easy to verify:

CREATE TABLE test_data (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO test_data (name, age, created_at)
SELECT
    'User_' || gs,
    (random() * 60 + 18)::INT,
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 10000) gs;

1

2

3

UPDATE test_data

SET age = 1

WHERE id <= 15;

Before breaking anything, capture the current WAL position and timestamp. These become the recovery target:

SELECT pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/85BDBB0

SELECT now();
             now              
------------------------------
 2025-09-19 13:39:18.89106+00

Then drop the table to simulate the disaster:

DROP TABLE test_data;

With the table gone, PITR is the only way back to the chosen timestamp.

WAL Archiving Requirements

A backup and WAL archive must already be configured, since WAL segments hold every transaction committed after the last base backup. Switching and the resulting archive file name can be confirmed with:

SELECT pg_switch_wal();
 pg_switch_wal 
---------------
 0/85C0B00

SELECT pg_walfile_name(pg_current_wal_lsn());
 pg_walfile_name      
--------------------------
 000000010000000000000009

Defining the Recovery Cluster

Recovery is driven by a new cluster manifest that uses bootstrap.recovery and targets a point just before the DROP TABLE:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-backup-pitr
spec:
  instances: 3
  storage:
    storageClass: csi-hostpath-sc
    size: 3Gi
  resources:
    requests:
      memory: "32Mi"
      cpu: "50m"
    limits:
      memory: "512Mi"
      cpu: "100m"
  bootstrap:
    recovery:    
      source: cybertec-pitr
      recoveryTarget:
        targetTime: "2025-09-19 13:39:18.89106+00"
  externalClusters:
    - name: cybertec-pitr
      plugin:
        name: barman-cloud.cloudnative-pg.io
        parameters:
          barmanObjectName: minio-store
          serverName: cluster-example-backup

Three fields carry most of the meaning here. recovery.source names the external cluster defined under cluster.spec. serverName identifies the cluster being recovered; CNPG looks for a main directory with that name in the barman object store. Inside recovery.recoveryTarget, several keys are accepted — backupID and targetTLI among them — but targetTime is the practical choice. The recorded LSN could be supplied instead, yet a timestamp is easier to reason about.

Apply it:

kubectl apply -f cluster-pitr.yaml

CNPG then pulls the base backup and WAL archives out of barman-cloud and replays transactions up to the given timestamp, halting before the DROP TABLE.

Verifying the Restored Cluster

Wait for the cluster to come up:

kubectl get pods

Connect to it:

kubectl exec -it cluster-example-backup-pitr-1 -- psql -U postgres -d pitr_test

And inspect the table:

\d
              List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | test_data   | table    | postgres

SELECT count(*) FROM test_data;
 count 
-------
 10000

select count(*) from test_data where age = 1;
 count 
-------
    15

All rows are present again, confirming recovery to the intended point in time.

Takeaway

PITR rewinds PostgreSQL to an exact moment, which is what makes recovery from dropped tables and bad writes feasible. On Kubernetes, CNPG plus Barman Cloud turns that into a declarative operation: adjust the cluster manifest and the restore lands on the required LSN, timeline, or timestamp.