PostgreSQL Active-Active with OSS Manager
Overview
Applications with region-local writes need selected PostgreSQL data available at both sites while preserving an explicit, testable, and auditable conflict model.
Logical replication does not automatically make arbitrary schemas safe for multi-writer operation. Keys, sequences, DDL, unsupported objects, write ownership, conflict handling, and reconciliation must be engineered before active-active traffic begins.
The recommended design uses conflict-aware multi-writer logical replication under a governed OSS Manager workflow. The operating model keeps approved tables, logical changes, replication origins, sequences, conflicts, and reconciliation evidence within the engine's native consistency and recovery semantics while standardizing planning, approvals, status, evidence, and day-two operations.
OSS Manager coordinates lifecycle operations and operational evidence. Engine-native consistency and application-level correctness remain explicit design responsibilities.
Use Case and Business Need
Why Current Operating Model is Insufficient
Current ChallengeManual operations obscure what changed and whether the application remained correct. The solution connects the workload requirements above to explicit ownership, safe change and a tested recovery path.
Intended Outcomes
Solution Goals- ✓A repeatable, reviewable architecture rather than a collection of host-specific steps.
- ✓Preflight checks that stop execution when capacity, access, topology, or safety assumptions are false.
- ✓Explicit separation between planning, approval, execution, and verification.
- ✓Engine-native health checks combined with application-level acceptance tests.
- ✓Evidence that L1, L2, platform, security, and audit teams can interpret without privileged data access.
Target Architecture
Architecture Responsibilities
Two independently recoverable PostgreSQL sites publish and subscribe approved table sets. Globally unique keys, explicit write ownership, DDL coordination, conflict capture, and reconciliation are designed before dual writes are enabled.
Design Principles and Control Boundaries
- •Declare before changing: Topology and policy are reviewed in configuration before any mutation.
- •Observe before owning: Brownfield or recovery workflows start by discovering runtime state and differences.
- •Plan is not execution: A plan may read and validate but cannot silently cross into an apply, restore, or removal action.
- •Explicit write ownership: A data directory has one runtime owner. Multi-writer designs additionally require a defined key, row or topic ownership model and conflict policy.
- •Protect the last healthy copy: No move, removal, or restore may destroy the only known usable copy of required data.
- •Verify business behavior: Process health is necessary but not sufficient; representative application paths must pass.
- •Retain rollback evidence: The pre-change state, artifacts, configuration, and decision record remain available through the rollback window.
Prerequisites and Discovery
Before implementation, customer and delivery teams must complete and approve the following discovery package.
OSS Manager Solution
OSS Manager validates table eligibility and identities, provisions replication objects, gates activation on conflict and fencing policy, observes slots and lag, records exceptions, and coordinates isolation, recovery, and failback tests. Business conflict resolution remains with application and data owners.
Capability model
Configuration and Deployment Contract
The deployment specification captures conflict-aware multi-writer logical replication. Configuration is reviewed against the schema supported by the chosen release; architecture intent is not a substitute for a validated runtime configuration.
- •Topology and resources: Two independently recoverable PostgreSQL sites publish and subscribe approved table sets. Globally unique keys, explicit write ownership, DDL coordination, conflict capture, and reconciliation are designed before dual writes are enabled.
- •Operational controls: table eligibility, replica identity, key strategy, write ownership, DDL coordination, conflict policy, lag gates, reconciliation, fencing, and safe failback.
- •Workload and recovery sizing: transaction and change rate, table size, WAL volume, slot retention, cross-site latency, conflict probability, catch-up bandwidth, and recovery window.
- •Identity and security: named operators, protected credential references, network boundaries and an approved access model.
Configuration review checklist
- •Record approved versions, paths, thresholds, sites, ports and service identities.
- •Reference secrets through protected environment or vault integration; never store passwords or private keys in the document or YAML repository.
- •Confirm that every declared host and service identity has the minimum required permissions.
- •Review destructive, removal, ownership-transfer, and recovery fields with the named approver.
- •Commit the reviewed configuration and record its immutable revision or checksum in the change ticket.
Implementation Workflow
Implementation follows the release-supported workflow for this engine. Each stage has a clear exit condition before the next action begins.
Staged rollout
- Laboratory rehearsal with representative topology, data shape, and failure injection.
- Non-production deployment with application owners and monitoring connected.
- Recovery or rollback rehearsal from retained artifacts and previous configuration.
- Production canary or lowest-risk segment, where the architecture permits segmentation.
- Production rollout with live stop conditions, named approvers, and post-change observation window.
- Closure only after evidence, exceptions, and operational handover are accepted.
Capacity and Infrastructure Planning
Sizing must be based on transaction and change rate, table size, WAL volume, slot retention, cross-site latency, conflict probability, catch-up bandwidth, and recovery window. Benchmark with representative data and concurrency before production commitment.
Security and Compliance
- •Use named human and service identities. Shared administrator accounts are not an acceptable operating model.
- •Apply least privilege to SSH, engine, monitoring, backup, and application identities independently.
- •Keep secrets out of YAML, command history, support bundles, screenshots, and document examples.
- •Encrypt management, client, replication, and monitoring traffic where the target security standard requires it.
- •Record configuration revisions, approvals, operator identity, execution timestamps, and before-and-after status.
- •Sanitize customer data, credentials, internal addresses, and private keys from evidence before external sharing.
- •Review licensing, third-party notices, package provenance, signatures, and SBOM evidence for the exact shipped artifacts.
Observability and Day Two Operations
The operational dashboard and alert policy should cover subscription health; apply lag; replication slots; retained WAL; conflicts; rejected changes; sequence range; reconciliation difference; site health. Thresholds must be tuned during non-production load and recovery tests.
Failure Modes and Edge Cases
The design and test plan must explicitly cover concurrent same-row update, key or sequence collision, unsupported DDL, slot growth, apply error, replication gap, site partition, and unsafe failback.
Validation and Acceptance Gates
Rollback and Recovery Strategy
Rollback is prepared before execution. The team retains the approved configuration, observed pre-change state, packages or runtime references, manifests, service metadata, and data protection artifacts required to return safely. Rollback must not overwrite the only healthy copy or start competing authorities.
- •Stop new application traffic or writes when continued activity would make rollback ambiguous.
- •Capture current failure evidence before changing state again.
- •Confirm the authoritative data copy and the exact rollback target.
- •Execute the reviewed rollback in reverse dependency order.
- •Verify engine topology, data, security, observability, and application behavior.
- •Record the decision, elapsed time, data exposure, unresolved exceptions, and corrective actions.
Limitations and Required Design Decisions
- •This reference does not replace workload benchmarking, engine vendor guidance, or environment-specific architecture review.
- •Supported operations and topology options depend on the selected OSS Manager release and engine version.
- •OSS Manager can standardize orchestration and evidence but cannot decide business conflict, reconciliation, residency, or failure policy on behalf of the application owner.
- •High availability does not replace backup; backup completion does not replace tested recovery; process health does not replace business validation.
- •Performance, cost, RPO and RTO targets are established through workload measurements and recovery rehearsals.
Reference Use Cases
BFSI: payments, fraud, customer 360, regulatory reporting, digital lending, risk, and resilient transaction platforms; Media and OTT: content metadata, playback events, entitlements, recommendations, sessions, and audience analytics; Retail: catalog, pricing, inventory, orders, loyalty, personalization, and real-time fulfillment events; Telco: subscriber data, charging, network events, assurance, policy, customer care, and digital channels; Gaming: profiles, matchmaking, leaderboards, inventory, telemetry, events, and live-operations services.
The same operating model can be adapted where the engine capability and data semantics are equivalent, subject to an environment-specific design and release validation.
Customer Success Criteria
For PostgreSQL, success is demonstrated through an agreed operational baseline, a rehearsed recovery path and application acceptance evidence, rather than service status alone.
- •Business owners can see whether continuity and data-correctness objectives were met.
- •Operations teams can identify the current owner, configuration and next recovery action.
- •Security teams can trace access and changes without exposing credentials or customer data.
Delivery Checklist
- ✓Architecture and scope approved
- ✓Prerequisites and capacity evidence complete
- ✓Security and credential model approved
- ✓Configuration revision and plan retained
- ✓Non-production and negative-path tests passed
- ✓Rollback or recovery rehearsal passed
- ✓Production window and stop conditions approved
- ✓Post-change engine and application validation passed
- ✓L1 and L2 operational handover complete
- ✓Service objectives and customer acceptance recorded
