Database Administration and Operations Questions

Approach summary:

• Make additive, backward-compatible changes first (nullable column), backfill in controlled batches, deploy app changes that read new column when present, then enforce NOT NULL + remove fallbacks. Use feature toggles and explicit validation; be prepared to roll back at each stage.

Migration plan (steps):

1. Prep — add column nullable (no default to avoid table rewrite)

ALTER TABLE orders ADD COLUMN new_status VARCHAR(20);

2. Backfill in safe, idempotent batches (avoid long locks). Example using primary key ranges:

-- pseudo-SQL, run in a loop from app or tooling
UPDATE orders
SET new_status = computed_value -- deterministic function or lookup
WHERE id > :last_id AND id <= :last_id + :batch_size
AND new_status IS NULL;

• Batch size tuned by monitoring (e.g., 1000–10000 rows). Use transactions per batch, sleep between batches, run during low traffic.

3. Validate backfill correctness:

• Row counts:

SELECT COUNT(*) FROM orders WHERE new_status IS NULL;
SELECT COUNT(*) FROM orders WHERE new_status = old_status_equivalent; -- domain check

• Spot-check hashes:

SELECT id, md5(concat(...)) FROM orders WHERE id IN (...);

• Compare aggregate metrics (counts per status) to pre-migration baseline.

4. Read/write compatibility:

• Deploy app change A (backwards compatible):
  • App writes both legacy column and new_status (or writes new_status but also keeps legacy behavior).
  • App reads prefer new_status if not null, else fallback to legacy source.
• Use feature toggle to switch reads to new column in stages (subset of traffic).

5. Enforce NOT NULL (only after full backfill + validation)

ALTER TABLE orders ALTER COLUMN new_status SET NOT NULL;

If you must set a default at enforcement, use a safe default and then set NOT NULL in separate statements to avoid full rewrite on some DBs.

6. Rollback plan:

• If backfill causes issues: pause backfill, revert app to read legacy column (toggle off). New writes should still populate legacy column.
• If app change causes errors: flip feature toggle to serve old code path; deploy rollback.
• To remove column if needed:

ALTER TABLE orders DROP COLUMN new_status;

(only after ensuring no code depends on it)

7. Monitoring & safety:

• Metrics: backfill progress, transaction durations, replication lag, error rates, CPU/IO.
• Alerts on replication lag > threshold, >95th update latency, increased 5xx in services.
• Use read replicas to run heavy validation queries to avoid production primary load.

Edge considerations & trade-offs:

• Some DBs (Postgres older versions, MySQL) handle ALTER TABLE differently; prefer nullable then separate NOT NULL to avoid full-table rewrite.
• For huge tables consider creating a new table + streaming copy (blue/green), or use logical replication.
• Ensure idempotency so backfill can be retried safely.

This plan minimizes downtime by keeping all steps online, uses feature toggles for gradual rollout, validates correctness at multiple levels, and provides clear rollback actions.

Requirements & constraints:

• 5 TB Postgres in cloud-managed service (limited superuser), zero downtime SLA, minimal CPU/IO impact, ability to rollback, validate correctness & performance.

High-level approach:

1. Phased, online migration using shadow table + dual-writes + controlled backfill, with feature-flagged cutover and monitoring.

Steps:

• Prep: add no-blocking changes first (new nullable columns, indexes concurrently). Create a shadow table with desired schema (create table like main plus changes).
• Dual-write: deploy application change to write to both main and shadow (via library/DB proxy). If app change not possible, use logical replication (pglogical/pg_recvlogical) to replicate changes into shadow.
• Backfill: run parallelized, throttled backfill jobs that copy historical rows from main -> shadow in ID ranges using ORDER BY primary key with batched transactions. Use COPY/INSERT with pg_copy or COPY (to minimize WAL) if allowed. Rate-limit with pg_sleep or a job scheduler to control IO.
• Keep applying ongoing WAL/replicated changes until lag is zero.

Validation:

• Row-level checksums/hashes (md5 of important columns) per batch; compare counts and sample queries. Use checksum tables and reconcile diffs.
• Query performance: run representative production read queries against shadow (via traffic shadowing or read-replica) under load testing to compare latency and IO.
• Monitor: CPU, IO, WAL size, replication lag, query latency, error rates, SLOs.

Cutover:

• Switch reads to shadow using feature flag / read-route change, monitor for errors for a short canary window.
• Promote shadow to primary: in cloud-managed DB, validate approach: if rename/simple swap not possible, create a transactional swap (create new table then rename tables in a short maintenance transaction). If rename takes too long, update app routing to point to new DB endpoint (preferred).
• Stop dual-write, ensure no drift.

Rollback:

• If issues, flip reads back and resume dual-writes to main; mark shadow for investigation. Keep final cutover reversible for a short period.

Tools & techniques:

• CONCURRENTLY index creation, logical replication (pglogical), pg_repack for bloat, pg_dump/pg_restore for non-production, pt-online-schema-change-like approach for Postgres (gh-ost concepts), cloud-native features (read replicas, replica promotion).
• Use orchestration: Airflow/Argo for backfill batches, Prometheus/Grafana + alerts for metrics.

Trade-offs:

• Dual-write complexity vs replication lag risk. Shadow table + replication reduces app changes but needs robust reconcile.
• Backfill speed vs impact: prefer slower safe copy.

Key validations:

• Automated checksums per batch, query-level SLA comparison, end-to-end functional tests, run chaos tests for partial failures. Document rollback steps and keep stakeholders notified.

Situation & goals: Migrate a production Postgres primary from on‑prem to RDS/Aurora with zero user downtime and guaranteed data consistency.

Approach summary:

• Use logical replication (pglogical/AWS DMS or built‑in logical replication) for schema flexibility and minimal downtime. Use physical replication only if exact binary copy + same major Postgres version is required (but physical cannot be applied to RDS as a target directly).
• Plan: prepare target, set up replication, perform iterative validation, staged schema changes for breaking migrations, cutover (switchover), post-cutover verification, and a clear rollback plan.

Replication setup:

• Prep target: provision RDS/Aurora with same or compatible major version, parameter tuning, instance sizing, networking (VPC, security groups), IAM, backups/retention, and maintenance windows.
• Initial snapshot: take a consistent base backup of on‑prem (pg_basebackup or pg_dump/pg_restore for logical). For logical replication prefer a consistent logical dump of schema and static data.
• Configure logical replication:
  • On source: create publication(s) for all tables: CREATE PUBLICATION p_all FOR ALL TABLES;
  • On target: create subscription to source: CREATE SUBSCRIPTION s_to_rds CONNECTION 'host=... user=... dbname=...' PUBLICATION p_all;
  • Or use AWS DMS for heterogeneous environments; enable ongoing replication.
• Ensure WAL settings: on source set wal_level = logical, sufficient max_replication_slots, max_wal_senders, and retention to avoid slot removal.

Schema migration strategy (handling breaking changes):

• Prefer backward‑compatible, phased migrations:
  1. Additive changes first (add columns with defaults as NULL, new tables, new indexes).
  2. Deploy code that writes both old and new schemas (dual‑write) or tolerates both.
  3. Backfill data on target (once replicated) and run reads from new schema if safe.
  4. Switch read traffic to new fields, monitor.
  5. Remove old fields in a later release once clients and replication are stable.
• For truly breaking changes (rename, change type):
  • Use shadow columns + backfill + application feature flags to toggle read/write.
  • If unavoidable, schedule brief coordinated migration window; keep transactions tiny and use retries.

Data validation strategies:

• Continuous checks while replication running:
  • Row counts per table between source & target.
  • Checksums/hashes for sampled primary keys: compute md5(concat_ws(...)) for partitions and compare.
  • Full table checks for small tables (SELECT count(*), checksum) and spot checks for large tables.
  • Use logical replication slot lag metrics and pg_stat_subscription / pg_stat_replication.
  • Use AWS DMS validation feature or tools like pt-table-checksum (for MySQL) equivalent scripts for Postgres.
  • Application-level sanity tests and end-to-end transactions in staging.
• Final consistency check before cutover: run a high‑confidence diff for critical tables.

Switchover steps (zero-downtime plan):

1. Freeze non‑essential writes if possible (graceful quiesce) — aim for app to continue normal operations, but reduce background heavy writes.
2. Ensure replication catch-up: confirm subscription is caught up (no pending apply) and replication lag ~0.
3. Put application in read-only mode for a short coordinated window if dual-write not implemented (optional). With logical replication + dual-write, you can avoid freezing.
4. Promote target as primary:
   • For RDS/Aurora: if using read replica mode, promote the replica to writer via AWS Console/CLI (failover). If using subscription-based logical replication, redirect app connection strings to the RDS endpoint.
5. Drain connections to old primary, redirect traffic using DNS/connection pooler (PgBouncer) switch with low TTL.
6. Verify writes succeed on new primary and run sanity transactions.
7. Monitor for errors and run targeted consistency checks for hot tables.

Rollback plan:

• Keep original on‑prem primary writable until final decommission; do not drop it immediately.
• If cutover fails or data inconsistency found:
  • Switch app back to old primary via DNS/connection pooler.
  • If target received writes, ensure you capture them and either replicate back or replay via logical replication from target to source (establish subscription back), or apply diffs. This is why maintaining dual‑write or short freeze minimizes asymmetric writes.
• Maintain backups & snapshots of both systems before and after cutover to expedite recovery.

Monitoring & post-cutover validation:

• Key metrics to monitor: replication lag, WAL usage, CPU/memory, disk I/O, connection counts, error rates, query latency, transaction commit rates.
• Use pg_stat_subscription, pg_stat_replication, CloudWatch (RDS/Aurora) and source Postgres metrics.
• Implement automated consistency checks (periodic row counts, sampled checksums) and alert on divergences exceeding thresholds.
• Run application smoke tests and synthetic transactions continuously for 24–72 hours.
• Keep enhanced logging and enable enhanced monitoring on RDS; review error logs for conflicts/failed applies.
• Post-cutover schedule: keep both systems in read/write shadow for a defined stabilization window, then decommission source once confident.

Trade-offs and notes:

• Logical replication allows online schema changes and cross‑version migrations but requires careful handling of DDL (DML during replication could need extra tooling).
• Using AWS DMS can simplify setup but watch DMS limitations and validation costs.
• Dual‑write or feature flags increases complexity but gives safest zero‑downtime path.
• Test the entire plan in staging with scale‑representative data and run a dry run rehearsal.

This plan provides a stepwise, testable approach emphasizing consistency, observability, and clear rollback procedures to achieve a zero‑downtime migration from on‑prem Postgres to RDS/Aurora.