7.3 PostgreSQL Replication

This page covers bidirectional logical replication between two CloudNativePG (CNPG) PostgreSQL clusters using the CNPG Declarative Pub/Sub CRDs (Publication and Subscription custom resources). This enables active-active replication where both clusters can accept writes independently.

---

Key Decisions

• Topology: Active-Active (bidirectional, on-prem ↔ on-prem)
• Replication Tool: CNPG Declarative Pub/Sub CRDs (logical replication)
• Database: failed_message_offset
• Authentication: scram-sha-256 via dedicated repl_user
• Networking: LoadBalancer services with static IPs (port 5432)

Architecture

  Cluster A                                 Cluster B
 ┌──────────────────────────────┐          ┌──────────────────────────────┐
 │  Namespace: rciis            │          │  Namespace: rciis            │
 │                              │          │                              │
 │  Cluster: esb-postgres       │          │  Cluster: esb-postgres       │
 │  DB: failed_message_offset   │          │  DB: failed_message_offset   │
 │                              │          │                              │
 │  pub_cluster_a            ───┼─── ► ────┼─ sub_from_cluster_a          │
 │                              │          │                              │
 │  sub_from_cluster_b       ───┼─── ◄ ────┼─ pub_cluster_b               │
 │                              │          │                              │
 └──────────────────────────────┘          └──────────────────────────────┘

Each cluster publishes its local changes and subscribes to the other cluster's publication. The origin: "none" parameter on each subscription ensures only locally-originated changes are forwarded, preventing infinite replication loops.

Why CNPG Declarative Pub/Sub?

• Lifecycle management — CNPG recreates publications/subscriptions on the new primary after failover
• GitOps-friendly — Publication and Subscription are Kubernetes CRDs tracked in Git
• No manual SQL — No need to kubectl exec into pods to run CREATE PUBLICATION / CREATE SUBSCRIPTION

---

Prerequisites

• CNPG operator >= 1.25 installed on both Kubernetes clusters — see 5.3.2 Data Services for installation
• PostgreSQL >= 16 (for origin = none loop prevention)
• Cross-cluster network connectivity see 5.1.1 Networking for setting up the Cilium Cluster Mesh
• Identical schema on both clusters — logical replication does NOT replicate DDL
• Tools: kubectl, sops, age, kustomize with KSOPS plugin — see 2.2 Install & Configure Tooling for base tools and 2.1.2 Credential Management for SOPS/Age setup

---

File Structure

apps/rciis/
├── nucleus/
│   ├── Cluster_A/
│   │   ├── kustomization.yaml
│   │   └── extra/
│   │       ├── pg-imagecatalog.yaml
│   │       ├── pg-instance.yaml
│   │       ├── publication.yaml
│   │       ├── subscription.yaml
│   │       └── pg-podmonitor.yaml
│   └── Cluster_B/
│       ├── kustomization.yaml
│       └── extra/
│           ├── pg-imagecatalog.yaml
│           ├── pg-instance.yaml
│           ├── publication.yaml
│           └── subscription.yaml
└── secrets/
    ├── Cluster_A/esb/
    │   ├── cnpg-esb-owner.yaml
    │   ├── cnpg-esb-superuser.yaml
    │   └── cnpg-repl-user.yaml
    └── Cluster_B/esb/
        ├── cnpg-esb-owner.yaml
        ├── cnpg-esb-superuser.yaml
        └── cnpg-repl-user.yaml

Config Files Per Environment

#	File	Purpose
1	cnpg-esb-owner.yaml	Database owner credentials (Secret)
2	cnpg-esb-superuser.yaml	Superuser credentials (Secret)
3	cnpg-repl-user.yaml	Replication user credentials (Secret)
4	pg-imagecatalog.yaml	PostgreSQL image version (ImageCatalog)
5	pg-instance.yaml	Cluster definition with externalClusters (Cluster)
6	publication.yaml	Local publication (Publication)
7	subscription.yaml	Subscription to remote publication (Subscription)
8	pg-podmonitor.yaml	Prometheus metrics scraping (PodMonitor, optional)

---

Implementation Steps

Step 1: Create Secrets

Three SOPS-encrypted secrets are required per environment. All secrets use kubernetes.io/basic-auth type.

Database Owner Secret

# apps/rciis/secrets/{env}/esb/cnpg-esb-owner.yaml
apiVersion: v1
kind: Secret
metadata:
  name: cnpg-esb-owner
  namespace: rciis-{env}
type: kubernetes.io/basic-auth
stringData:
  username: esb                           # must match managed.roles[0].name in Cluster
  password: "CHANGE-ME"                   # CUSTOMIZE: unique per environment

Superuser Secret

# apps/rciis/secrets/{env}/esb/cnpg-esb-superuser.yaml
apiVersion: v1
kind: Secret
metadata:
  name: cnpg-esb-superuser
  namespace: rciis-{env}
type: kubernetes.io/basic-auth
stringData:
  username: postgres
  password: "CHANGE-ME"                   # CUSTOMIZE: unique per environment

Replication User Secret

# apps/rciis/secrets/{env}/esb/cnpg-repl-user.yaml
apiVersion: v1
kind: Secret
metadata:
  name: cnpg-repl-user
  namespace: rciis-{env}
type: kubernetes.io/basic-auth
stringData:
  username: repl_user
  password: "CHANGE-ME"                   # IMPORTANT: must be IDENTICAL on both envs

Replication User Password Must Match

The repl_user password must be the same on both environments. Each cluster's externalClusters entry references the remote's cnpg-repl-user secret by name — but reads the local copy. Both copies must contain the same password.

Encrypt with SOPS

# Encrypt each secret file (run from repo root)
sops -e apps/rciis/secrets/{env}/esb/cnpg-esb-owner.yaml > apps/rciis/secrets/{env}/esb/cnpg-esb-owner.yaml.tmp \
  && mv apps/rciis/secrets/{env}/esb/cnpg-esb-owner.yaml.tmp apps/rciis/secrets/{env}/esb/cnpg-esb-owner.yaml

# Repeat for cnpg-esb-superuser.yaml and cnpg-repl-user.yaml in both clusters

---

Step 2: Create ImageCatalog

The ImageCatalog decouples the PostgreSQL image from the Cluster spec, allowing version updates without modifying the Cluster resource.

# apps/rciis/nucleus/{env}/extra/pg-imagecatalog.yaml
apiVersion: postgresql.cnpg.io/v1
kind: ImageCatalog
metadata:
  name: postgresql-17                     # referenced by Cluster.spec.imageCatalogRef
spec:
  images:
    - major: 17
      image: ghcr.io/cloudnative-pg/postgresql:17.2  # CUSTOMIZE: pin to desired version

Tip

This file is identical in both environments. Update the image tag to roll out PostgreSQL minor/patch upgrades.

---

Step 3: Create the Cluster

This is the core resource. The annotated template below covers every section needed to spin up a new CNPG cluster configured for bidirectional replication.

# apps/rciis/nucleus/{env}/extra/pg-instance.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: esb-postgres
  annotations:
    cnpg.io/reload-secrets: "true"        # live-reload when secrets change
spec:
  enableSuperuserAccess: true
  description: "PostgreSQL for ESB"
  instances: 1                            # CUSTOMIZE: 1 for dev/test, 3 for prod

  # --- Image Reference ---
  imageCatalogRef:
    apiGroup: postgresql.cnpg.io
    kind: ImageCatalog
    name: postgresql-17                   # must match ImageCatalog metadata.name
    major: 17

  # --- PostgreSQL Configuration ---
  postgresql:
    parameters:
      # Required for logical replication
      wal_level: "logical"
      max_replication_slots: "10"
      max_wal_senders: "10"
      max_logical_replication_workers: "4"
      max_worker_processes: "12"
      # Bidirectional replication slot sync (CNPG 1.27+)
      hot_standby_feedback: 'on'
      sync_replication_slots: 'on'

    # --- pg_hba Rules ---
    pg_hba:
      - host failed_message_offset esb 0.0.0.0/0 scram-sha-256
      - host replication repl_user 0.0.0.0/0 scram-sha-256
      - host failed_message_offset repl_user 0.0.0.0/0 scram-sha-256

    roles:
    - name: esb                           # application database owner
      ensure: present
      comment: ESB User
      login: true
      superuser: false
      inRoles:
        - pg_monitor
        - pg_signal_backend
      passwordSecret:
        name: cnpg-esb-owner

    - name: repl_user                     # replication user
      ensure: present
      comment: Replication user for bidirectional logical replication
      login: true
      superuser: false
      inRoles:
        - pg_read_all_data                # grants SELECT on all tables (required for pub)
      replication: true
      passwordSecret:
        name: cnpg-repl-user

  # --- Bootstrap ---
  bootstrap:
    initdb:
      database: failed_message_offset     # CUSTOMIZE: your database name
      owner: esb
      secret:
        name: cnpg-esb-owner

  superuserSecret:
    name: cnpg-esb-superuser

  # --- Storage ---
  storage:
    storageClass: ceph-rbd-single         # CUSTOMIZE: per-cluster storage class
    size: 10Gi                            # CUSTOMIZE: per-environment

  resources:
    requests:
      memory: "1Gi"
      cpu: "500m"
    limits:
      memory: "4Gi"
      cpu: "2000m"

  # --- External Clusters (remote peer for replication) ---
  externalClusters:
    - name: cluster-b                      # CUSTOMIZE: logical name for remote cluster
      connectionParameters:
        host: <<cluster b host>>             # CUSTOMIZE: remote cluster's IP/service name
        port: "5432"
        dbname: failed_message_offset
        user: repl_user
      password:
        name: cnpg-repl-user             # local secret with remote's repl_user password
        key: password

Step 4: Create Publication

Each environment publishes its local changes. The Publication CRD wraps CREATE PUBLICATION in a declarative resource.

# apps/rciis/nucleus/{env}/extra/publication.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Publication
metadata:
  name: pub-{env}-cluster                 # CUSTOMIZE: K8s resource name
  namespace: rciis-{env}                  # CUSTOMIZE: target namespace
spec:
  cluster:
    name: esb-postgres                    # must match Cluster metadata.name
  name: pub_cluster_{a|b}                 # CUSTOMIZE: PostgreSQL publication name
  dbname: failed_message_offset           # must match bootstrap.initdb.database
  target:
    allTables: true                       # publishes all tables in the database
  parameters:
    publish: "insert,update,delete"       # DML operations to publish (no TRUNCATE)

---

Step 5: Create Subscription

Each environment subscribes to the remote cluster's publication.

# apps/rciis/nucleus/{env}/extra/subscription.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Subscription
metadata:
  name: sub-from-cluster-{a|b}             # CUSTOMIZE: K8s resource name
  namespace: rciis-{env}                   # CUSTOMIZE: local namespace
spec:
  cluster:
    name: esb-postgres                     # must match local Cluster metadata.name
  name: sub_from_cluster_{a|b}             # CUSTOMIZE: PostgreSQL subscription name
  dbname: failed_message_offset            # must match local database name
  publicationName: pub_cluster_{a|b}       # MUST match remote Publication spec.name
  externalClusterName: cluster-b                     # MUST match local externalClusters[].name
  parameters:
    copy_data: "true"                      # initial data sync (see note below)
    origin: "none"                         # CRITICAL: prevents infinite replication loops
    failover: 'true'                       # subscription survives primary failover

Key Parameters

Parameter	Value	Purpose
copy_data	"true"	Copies existing table data when subscription is first created. Set to "false" if schemas are already in sync.
origin	"none"	Loop prevention. Only replicates changes that originated locally on the publisher — ignores changes that arrived via another subscription. Without this, data bounces back and forth forever.
failover	"true"	CNPG recreates the subscription on the new primary after a failover event.

---

Step 6: Update kustomization.yaml

Add publication.yaml and subscription.yaml to the resources list in each environment's kustomization file.

# apps/rciis/nucleus/{env}/kustomization.yaml
resources:
    - extra/pg-imagecatalog.yaml
    - extra/pg-instance.yaml
    - extra/publication.yaml              # ADD THIS
    - extra/subscription.yaml             # ADD THIS

Warning

Both publication.yaml and subscription.yaml must be listed in the kustomization for FluxCD to deploy them. Without these entries, the CRDs exist in Git but are never applied to the cluster.

---

Deployment Order

The correct deployment sequence matters because subscriptions depend on publications existing on the remote side.

1. Secrets             (both envs)    — repl_user password must match
2. ImageCatalog        (both envs)    — idempotent, can deploy in parallel
3. Cluster             (both envs)    — wait for pods to be Running and database ready
4. Verify connectivity                — confirm each cluster can reach the other's DB
5. Publication         (both envs)    — can deploy in parallel, no cross-dependency
6. Subscription        (first env)    — deploys and performs copy_data sync
7. Subscription        (second env)   — deploys after first env's sync completes

Deploy Subscriptions Sequentially

When copy_data: "true", the subscription triggers a full table copy on creation. If both subscriptions start simultaneously, both sides try to copy data at the same time, which can cause duplicate-key conflicts. Deploy one subscription first, wait for its initial sync to complete, then deploy the second.

If both databases start empty (fresh setup), parallel deployment is safe.

---

Verification & Debugging

Check CRD Status

# Check Publication status (replace {ns} with your namespace)
kubectl get publication -n {ns}
kubectl describe publication pub-{env}-cluster -n {ns}

# Check Subscription status
kubectl get subscription -n {ns}
kubectl describe subscription sub-from-cluster-{a|b} -n {ns}

# Check Cluster status
kubectl get cluster -n {ns}
kubectl describe cluster esb-postgres -n {ns}

SQL Diagnostic Queries

Run these via kubectl exec on the primary pod:

# Get the primary pod name (replace {ns} with your namespace)
kubectl get pods -n {ns} -l cnpg.io/cluster=esb-postgres,role=primary -o name

Subscription Status

-- Check if subscriptions are enabled
SELECT subname, subenabled, subconninfo
FROM pg_subscription;

Replication Slot Status

-- Check slot activity on the publisher side
SELECT
    slot_name,
    active,
    active_pid,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS slot_lag,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS flush_lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

Replication Lag

-- Check subscriber lag
SELECT
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    latest_end_time,
    now() - latest_end_time AS replication_lag
FROM pg_stat_subscription;

WAL Accumulation

-- High values = subscriber not keeping up (risk of disk exhaustion)
SELECT
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        confirmed_flush_lsn
    )) AS pending_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

---

Limitations

1. No DDL replication — CREATE TABLE, ALTER TABLE, index changes, etc. must be applied manually to both clusters. Use a migration tool (Flyway, Atlas, Liquibase) and run migrations on both sides.

2. DML only — Only INSERT, UPDATE, DELETE are replicated. TRUNCATE is excluded by our publish parameter. Sequences and large objects are not replicated.

3. Tables must have a PRIMARY KEY — Required for UPDATE/DELETE replication identity. Tables without a PK need ALTER TABLE ... REPLICA IDENTITY FULL (slower).

4. Sequences are not synchronized — Each cluster manages sequences independently. To avoid PK collisions:

   • Use UUIDs as primary keys (gen_random_uuid())
   • Or use non-overlapping sequences (odd/even: INCREMENT 2, START 1 vs START 2)

5. No PgBouncer for replication — CNPG's built-in PgBouncer pooler does NOT support logical replication connections. Always use the direct -rw service or LoadBalancer.

6. Initial sync direction — When using copy_data: "true", deploy subscriptions one at a time. If both databases already have identical data, use copy_data: "false".

---

References

• CNPG Logical Replication Docs