Database Migration & Upgrade Playbooks

This guide delivers production-tested project plans and operational runbooks for executing database version upgrades and platform migrations to Okustera Managed Databases (DBaaS).

It provides detailed execution procedures for two primary enterprise migration scenarios:

1. Example 1: Migrating an on-premises / external VM-hosted PostgreSQL 9 cluster to Cloud DBaaS running PostgreSQL 16.
2. Example 2: Performing an in-platform major version upgrade from DBaaS PostgreSQL 12 to PostgreSQL 16 with near-zero downtime.

---

1. Migration Strategy Matrix

Metric / Dimension	Example 1: 2x VMs (PostgreSQL 9) → DBaaS (PG 16)	Example 2: DBaaS (PostgreSQL 12) → DBaaS (PG 16)
Source Platform	Self-managed Linux VMs (On-prem / External Cloud)	Cloud DBaaS (CloudNativePG on OpenCloud K8s)
Source Engine Version	PostgreSQL 9.x (EOL Nov 2021)	PostgreSQL 12.x (EOL Nov 2024)
Target Engine Version	CloudNativePG PostgreSQL 16.x	CloudNativePG PostgreSQL 16.x
High Availability Target	3-instance synchronized cluster across AZs	3-instance synchronized cluster across AZs
Native Logical Replication	❌ Unsupported (Introduced in PostgreSQL 10)	Supported (CREATE PUBLICATION / CREATE SUBSCRIPTION)
Orchestration Method	Fast Parallel Logical Dump/Restore or External CDC	Blue-Green Live Sync via Native PostgreSQL Logical Replication
Write Cutover Window	Scheduled maintenance window (or near-zero with CDC)	Sub-30 seconds (Brief connection drain & switchover)
Storage Architecture	Direct attached disk / NFS	High-throughput Ceph NVMe RBD (ceph-nvme)
Data Protection	Source VM snapshots	Continuous S3 WAL streaming with Point-in-Time Recovery (Barman)

---

2. Example 1: Migrating 2x VMs (PostgreSQL 9) to Cloud DBaaS (PostgreSQL 16)

2.1 Architectural Assessment & Breaking Changes

PostgreSQL 9 to 16 represents an evolutionary leap spanning seven major versions. Before running migration commands, audit your application for breaking changes:

1. Topology Clarification:
   • If the 2 VMs are configured as an Active/Standby pair (e.g., using repmgr, corosync, or physical streaming replication), only the Primary node needs to be exported. The target CloudNativePG cluster automatically orchestrates 3-way HA across failure domains.
   • If the 2 VMs host two distinct databases, provision two distinct DBaaS clusters (or two logical databases inside one cluster).
2. Authentication Evolution:
   • PostgreSQL 9 defaults to md5 password hashing.
   • PostgreSQL 16 enforces scram-sha-256. Client database drivers must be verified for SCRAM support, and user passwords will need rotation or SCRAM-based reset.
3. Deprecated System Views and Functions:
   • pg_stat_activity: procpid was renamed to pid; current_query was renamed to query.
   • pg_xlog was renamed to pg_wal.
   • Native gen_random_uuid() is built-in in PostgreSQL 16 (no longer requiring the legacy uuid-ossp or pgcrypto extension for standard UUIDs).
4. Parameter Name Changes:
   • checkpoint_segments is replaced by max_wal_size and min_wal_size.
   • wal_keep_segments is replaced by wal_keep_size.

---

2.2 Phase-by-Phase Execution Plan

Step 1: Provision the PostgreSQL 16 DBaaS Cluster

Provision the target cluster in your tenant namespace using Terraform:

resource "okustera_database_postgresql" "production_pg16" {
  name            = "production-core-pg16"
  instances       = 3
  storage_size_gb = 250
  storage_class   = "ceph-nvme"
  database_name   = "core_app"
  enable_backups  = true

  parameters = {
    "max_connections"      = "300"
    "shared_buffers"       = "4GB"
    "effective_cache_size" = "12GB"
    "maintenance_work_mem" = "1GB"
    "password_encryption"  = "scram-sha-256"
  }
}

Step 2: Schema Extraction & Compatibility Dry-Run

[!IMPORTANT] Always execute pg_dump using PostgreSQL 16 client tools connecting to the remote PostgreSQL 9 server. Never use legacy PostgreSQL 9 binaries to generate dumps intended for PostgreSQL 16.

1. Export Global Roles:

   pg_dumpall -h 192.0.2.10 -p 5432 -U postgres --globals-only -f globals_pg9.sql

2. Export Schema Structure Only:

   pg_dump -h 192.0.2.10 -p 5432 -U postgres -d core_app \
     --schema-only --no-owner --no-privileges -f schema_pg9.sql

3. Inspect Schema: Review schema_pg9.sql for obsolete data types (abstime, reltime) or legacy procedural language syntax.
4. Apply Schema to Target DBaaS:

   psql -h production-core-pg16-rw.tenant-ns.svc.cluster.local -p 5432 \
     -U postgres -d core_app -f schema_pg9.sql

Step 3: Data Migration Execution

Option A: Parallel Dump & Direct Restore (Fast Maintenance Window)

For databases under 500 GB, a scheduled parallel dump and restore maximizes throughput:

1. Place source application into Maintenance / Read-Only Mode.
2. Run multi-threaded directory dump from source VM:

   pg_dump -h 192.0.2.10 -p 5432 -U postgres -d core_app \
     -Fd -j 8 -Z 1 --data-only -f /mnt/fast_nvme/pg9_data_dump

3. Disable triggers and foreign key constraint checks on the target cluster during data load:

   SET session_replication_role = 'replica';

4. Restore in parallel across 8 worker threads:

   pg_restore -h production-core-pg16-rw.tenant-ns.svc.cluster.local -p 5432 \
     -U postgres -d core_app -j 8 --data-only --disable-triggers \
     /mnt/fast_nvme/pg9_data_dump

5. Re-enable constraint triggers:

   SET session_replication_role = 'DEFAULT';

Option B: Asynchronous CDC (For Continuous 24/7 Availability)

If application downtime must not exceed 2 minutes:

• Deploy Bucardo (trigger-based asynchronous replication that bridges PG 9 → PG 16).
• Or deploy a Debezium CDC connector reading from PostgreSQL 9 logical decoding (wal2json or test_decoding) streaming changes to the PG 16 target until cutover.

Step 4: Sequence Synchronization & Integrity Check

Run the automated synchronization script to ensure all auto-increment sequence counters exceed the current maximum primary key values:

DO $$
DECLARE
    r RECORD;
    max_val BIGINT;
BEGIN
    FOR r IN SELECT sequence_schema, sequence_name FROM information_schema.sequences LOOP
        EXECUTE format('SELECT COALESCE(MAX(id), 1) FROM %I', replace(r.sequence_name, '_id_seq', '')) INTO max_val;
        EXECUTE format('SELECT setval(''%I.%I'', %s)', r.sequence_schema, r.sequence_name, max_val + 1);
    END LOOP;
END $$;

Step 5: Application Cutover & Optimization

1. Update application database credentials and endpoint to the DBaaS read-write service:

   postgresql://app_user:<PASSWORD>@production-core-pg16-rw.tenant-ns.svc.cluster.local:5432/core_app?sslmode=require

2. Run database statistics compilation:

   VACUUM ANALYZE;

3. Verify that continuous Barman S3 backups are active. Retain source VMs in read-only state for 7 days.

---

3. Example 2: DBaaS Major Version Upgrade (PostgreSQL 12 → PostgreSQL 16)

3.1 Why Blue-Green Logical Replication?

In cloud-native Kubernetes environments:

• Modifying the container image directly in a live PostgreSQL 12 StatefulSet causes crash loops because on-disk catalog pages differ between major releases.
• Because PostgreSQL 12 and PostgreSQL 16 both support Native Logical Replication (pgoutput), we can build a zero-data-loss, live-synchronized blue-green replica.
• Live application reads can continue uninterrupted on PostgreSQL 12 while data is synchronizing. Write cutover requires only seconds to finalize sequence counters.

---

3.2 Step-by-Step Blue-Green Execution Plan

Step 1: Pre-Upgrade Verification on Source (PostgreSQL 12)

Ensure all tables have primary keys or explicit replica identities:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i 
    WHERE i.indrelid = c.oid AND (i.indisprimary OR i.indisreplident)
  );

(Any table lacking a primary key must run ALTER TABLE <table_name> REPLICA IDENTITY FULL; to replicate updates and deletes.)

Step 2: Provision Target PostgreSQL 16 Cluster

Create the target cluster manifest:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: billing-db-v16
  namespace: tenant-production
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:16.2
  storage:
    size: 500Gi
    storageClass: ceph-nvme
  bootstrap:
    initdb:
      database: billing
      owner: billing_admin
  monitoring:
    enablePodMonitor: true

Step 3: Schema DDL Transfer

pg_dump -h billing-db-v12-rw.tenant-production.svc.cluster.local \
  -U billing_admin -d billing --schema-only -f /tmp/schema_v12.sql

psql -h billing-db-v16-rw.tenant-production.svc.cluster.local \
  -U billing_admin -d billing -f /tmp/schema_v12.sql

Step 4: Configure Publication & Subscription

1. On Source Cluster (PG 12):

   CREATE PUBLICATION omc_upgrade_pub FOR ALL TABLES;

2. On Target Cluster (PG 16):

   CREATE SUBSCRIPTION omc_upgrade_sub
     CONNECTION 'host=billing-db-v12-rw.tenant-production.svc.cluster.local port=5432 user=billing_admin password=<PASSWORD> dbname=billing'
     PUBLICATION omc_upgrade_pub
     WITH (copy_data = true, create_slot = true);

Step 5: Replication Verification

Monitor synchronization until replication lag is 0 bytes:

-- Run on PG 12:
SELECT client_addr, application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Step 6: Atomic Cutover Sequence

1. Momentarily quiesce application writes.
2. Confirm source and target LSN match:

   SELECT pg_current_wal_lsn();

3. Copy exact sequence values from PG 12 to PG 16 using pg_dump or the sequence sync utility.
4. Tear down subscription on target:

   DROP SUBSCRIPTION omc_upgrade_sub;

5. Repoint the internal service or connection secret:

   kubectl patch service billing-db-rw -n tenant-production \
     -p '{"spec":{"selector":{"cnpg.io/cluster":"billing-db-v16","role":"primary"}}}'

6. Re-enable application writes. Total cutover duration: under 30 seconds.

Step 7: Post-Upgrade Optimization & Decommission

1. Rebuild optimizer statistics:

   ANALYZE VERBOSE;

2. Delete the legacy publication on PG 12:

   DROP PUBLICATION omc_upgrade_pub;

3. Retain the PG 12 cluster in read-only mode for 72 hours before decommissioning.

---

4. Rollback & Disaster Recovery Procedures

Scenario	Rollback Trigger	Mitigation Action	Target RTO / RPO
Example 1: Ingestion failure or corrupt data dump	DDL syntax errors or timeout during restore	Abort restore on PG 16. Source VMs were unmodified and continue serving traffic without impact.	RTO: < 1 min RPO: 0
Example 1: Post-migration application regressions	Latency anomalies or driver query syntax incompatibilities	Revert DNS CNAME or connection string back to VM 1 IP. Replay delta WALs if available.	RTO: < 5 mins RPO: Minimal
Example 2: Replication lag or network saturation	Target unable to keep pace with PG 12 WAL generation	Drop subscription (DROP SUBSCRIPTION). Source PG 12 remains active; scale network bandwidth or storage IOPS before retry.	RTO: 0 RPO: 0
Example 2: Post-cutover defect in PostgreSQL 16	Unforeseen application defect detected within 24 hours of cutover	Repoint Service selector back to billing-db-v12. (If reverse logical replication is configured, zero data loss is achieved).	RTO: < 30s RPO: 0

---

5. Automated Data Parity Audit Script

To verify data integrity before initiating traffic cutover, use this Python script to audit table row counts and synchronize sequence counters:

#!/usr/bin/env python3
"""
PostgreSQL Migration Parity & Sequence Sync Utility
Audits table row counts across source and target databases, then syncs sequence states.
"""

import argparse
import sys
import psycopg2
from psycopg2.extras import RealDictCursor

def compare_row_counts(source_conn, target_conn):
    print("\n--- Auditing Table Row Counts ---")
    query = """
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
      AND table_type = 'BASE TABLE'
    ORDER BY table_schema, table_name;
    """
    with source_conn.cursor(cursor_factory=RealDictCursor) as s_cur:
        s_cur.execute(query)
        tables = s_cur.fetchall()

    discrepancies = 0
    with source_conn.cursor() as s_cur, target_conn.cursor() as t_cur:
        for t in tables:
            qualified = f'"{t["table_schema"]}"."{t["table_name"]}"'
            s_cur.execute(f"SELECT COUNT(*) FROM {qualified}")
            s_cnt = s_cur.fetchone()[0]
            t_cur.execute(f"SELECT COUNT(*) FROM {qualified}")
            t_cnt = t_cur.fetchone()[0]

            match = "✓ OK" if s_cnt == t_cnt else "✗ MISMATCH"
            if s_cnt != t_cnt:
                discrepancies += 1
            print(f"[{match}] {qualified:40} Source: {s_cnt:8} | Target: {t_cnt:8}")

    print(f"\nAudit complete. Tables: {len(tables)}, Discrepancies: {discrepancies}")
    return discrepancies == 0

def sync_sequences(source_conn, target_conn):
    print("\n--- Synchronizing Sequences ---")
    seq_query = """
    SELECT sequence_schema, sequence_name
    FROM information_schema.sequences
    WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema');
    """
    with source_conn.cursor(cursor_factory=RealDictCursor) as s_cur:
        s_cur.execute(seq_query)
        sequences = s_cur.fetchall()

    with source_conn.cursor() as s_cur, target_conn.cursor() as t_cur:
        for seq in sequences:
            qualified = f'"{seq["sequence_schema"]}"."{seq["sequence_name"]}"'
            s_cur.execute(f"SELECT last_value, is_called FROM {qualified}")
            row = s_cur.fetchone()
            if row:
                last_value, is_called = row
                t_cur.execute(f"SELECT setval('{qualified}', %s, %s)", (last_value, is_called))
                print(f"[SYNCED] {qualified:40} -> value: {last_value}, is_called: {is_called}")
        target_conn.commit()
    print(f"\nSuccessfully synchronized {len(sequences)} sequence(s).")

def main():
    parser = argparse.ArgumentParser(description="PostgreSQL Migration Validator")
    parser.add_argument("--source", required=True, help="Source connection URI")
    parser.add_argument("--target", required=True, help="Target connection URI")
    parser.add_argument("--sync-seq", action="store_true", help="Sync sequence values")
    args = parser.parse_args()

    s_conn = psycopg2.connect(args.source)
    t_conn = psycopg2.connect(args.target)

    if not compare_row_counts(s_conn, t_conn):
        print("Warning: Row counts do not match!", file=sys.stderr)

    if args.sync_seq:
        sync_sequences(s_conn, t_conn)

    s_conn.close()
    t_conn.close()

if __name__ == "__main__":
    main()