PostgreSQL support: concrete implementation plan

[renecannao]

PostgreSQL Support — What's Needed to Make It Real

Current state

We have:

• DatabaseProvider interface (go/inst/provider.go)
• PostgreSQLProvider implementation (go/inst/provider_postgresql.go)
• Config fields: PostgreSQLTopologyUser, PostgreSQLTopologyPassword, PostgreSQLSSLMode

We do NOT have:

• The core orchestrator loop using the provider interface (it calls MySQL DAOs directly)
• Any functional testing against real PostgreSQL
• PostgreSQL-specific analysis codes (failure detection)
• PostgreSQL-specific recovery logic
• Tutorials or documentation for real usage

Implementation phases

Phase A: Wire provider into discovery loop

Estimated effort: 2-3 days

The discovery loop in go/logic/orchestrator.go and go/inst/instance_dao.go calls MySQL-specific functions directly. We need to:

1. Add a ProviderType config field ("mysql" or "postgresql", default "mysql")
2. In ReadTopologyInstance() (go/inst/instance_dao.go), branch based on provider type:
   • MySQL path: existing code (unchanged)
   • PostgreSQL path: call GetProvider().GetReplicationStatus() etc.
3. Map PostgreSQL replication concepts to orchestrator's internal model:
   • Primary → Master
   • Standby → Replica
   • WAL LSN → replication position
   • pg_stat_replication → replica list
   • pg_stat_wal_receiver → replication status
4. Store discovered PostgreSQL instances in the same instance table

Key challenge: Orchestrator's Instance struct has many MySQL-specific fields (binlog coordinates, GTID sets, etc.). Need to decide: extend the struct with PG fields, or use the provider abstraction to normalize.

Phase B: PostgreSQL failure detection

Estimated effort: 2-3 days

1. Add PostgreSQL-specific analysis codes:
   • DeadPrimary (equivalent to DeadMaster)
   • StandbyNotReplicating
   • PrimaryReadOnly
2. Implement PostgreSQL failure detection in go/inst/analysis_dao.go
3. PostgreSQL detection relies on:
   • Can we connect to the primary?
   • Are standbys receiving WAL?
   • Is pg_stat_replication showing connected standbys?

Phase C: PostgreSQL recovery

Estimated effort: 3-5 days

1. Implement pg_promote() for standby promotion
2. Reconfigure remaining standbys to follow the new primary (primary_conninfo in postgresql.auto.conf + pg_reload_conf())
3. Handle timeline changes after promotion
4. Pre/post recovery hooks (same mechanism as MySQL)
5. Integration with PgBouncer (similar to ProxySQL hooks for MySQL)

Phase D: Functional testing

Estimated effort: 2-3 days

1. Add PostgreSQL containers to tests/functional/docker-compose.yml:
   • PostgreSQL primary + 2 streaming replicas
   • PgBouncer (optional)
2. Test scripts:
   • Discovery of PostgreSQL topology
   • Failure detection (stop primary)
   • Automatic promotion of standby
   • Replication reconfiguration
3. Add to GitHub Actions functional test workflow

Phase E: Documentation and tutorials

Estimated effort: 1-2 days

1. Tutorial: "Setting up orchestrator with PostgreSQL streaming replication"
2. Update reference manual with PostgreSQL config fields
3. Update website features page
4. Blog post / announcement

Total estimated effort: 10-16 days

Dependencies and risks

• Risk: The Instance struct is deeply MySQL-centric. A clean abstraction may require significant refactoring of the struct itself, not just the DAO layer.
• Risk: PostgreSQL replication management tools (Patroni, repmgr) are mature. Orchestrator needs to offer a clear value proposition for PostgreSQL users.
• Dependency: Phase A must complete before B/C. D can start after C. E after D.

Success criteria

PostgreSQL support is "real" when:

• orchestrator -c discover discovers a PostgreSQL primary + standbys
• Web UI shows PostgreSQL topology
• Orchestrator detects dead primary automatically
• Orchestrator promotes a standby and reconfigures replication
• All of the above runs in CI with real PostgreSQL containers
• A user can follow a tutorial from scratch and have it work

[coderabbitai]

⚠️ Possible Duplicate Issue(s)

• PostgreSQL replication provider #53

🔗 Related PRs

#36 - Add built-in ProxySQL failover hooks [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

All 5 phases complete: PR #70 (A+B+C), PR #71 (D), PR #72 (E)