active_postgres

PostgreSQL high-availability automation for Ruby and Rails applications.

Active Postgres configures hosts you control over SSH. It is an operations tool, not a managed database service: you remain responsible for network isolation, capacity planning, monitoring, backups, recovery drills, and failover decisions.

Features

• High Availability: Primary/standby replication with automatic failover (repmgr)
• Connection Pooling: PgBouncer integration
• Rails Integration: Automatic database.yml config, migration guard, read replica routing
• Modular Components: Core, Performance Tuning (opt-in), repmgr, PgBouncer, pgBackRest, Monitoring, SSL, Extensions

Quick Start

1. Install

gem install active_postgres
# or add to Gemfile: gem 'active_postgres'

2. Configure (Rails)

rails generate active_postgres:install

Edit config/postgres.yml:

production:
  version: 18
  user: ubuntu
  ssh_key: ~/.ssh/id_rsa
  ssh_known_hosts_file: config/ssh_known_hosts

  jump_hosts:
    app-oci:
      host: 129.213.194.40

  primary:
    host: 34.12.234.81        # Public IP for SSH
    private_ip: 10.8.0.10     # Private IP for database connections

  standby:
    - host: 52.23.45.67
      private_ip: 10.8.0.11
    - host: 100.63.4.227
      private_ip: 10.8.0.12
      jump_host: app-oci
      seed_method: pgbackrest
      repmgr:
        auto_failover: false
        priority: 0

  components:
    repmgr: {enabled: true}
    pgbouncer: {enabled: true}

  secrets:
    superuser_password: $POSTGRES_SUPERUSER_PASSWORD
    replication_password: $POSTGRES_REPLICATION_PASSWORD
    repmgr_password: $POSTGRES_REPMGR_PASSWORD
    monitoring_password: $POSTGRES_MONITORING_PASSWORD
    app_password: $POSTGRES_APP_PASSWORD

jump_host keeps the operator SSH key on the workstation and proxies the connection through a declared host. A standby-level repmgr block overrides the cluster defaults, so a cross-provider disaster-recovery replica can remain manual-promotion while the ordinary standby retains automatic failover. seed_method: pgbackrest restores the latest valid backup with standby recovery settings before repmgr registration, avoiding a full base-backup stream from the primary. The pgbackrest component must be enabled.

Set a unique positive integer node_id on every primary and standby entry before permanently changing their roles or reordering standbys. Match the existing IDs in repmgr cluster show, and keep each ID with its host after a switchover. Without explicit IDs, the primary defaults to 1 and standbys use their list position starting at 2. Duplicate or invalid IDs are rejected.

Add credentials (rails credentials:edit):

postgres:
  username: myapp
  password: "your_app_password"
  database: myapp_production
  primary_host: 10.8.0.10
  replica_host: 10.8.0.11
  port: 6432  # 6432 for PgBouncer, 5432 for direct

3. Deploy

rake postgres:setup   # Deploy cluster
rake postgres:status  # Check health
rake postgres:overview  # Control tower overview
rake postgres:help    # Task summary

Common Operations

Rake Tasks

rake postgres:setup                    # Deploy HA cluster
rake postgres:status                   # Check cluster status
rake postgres:verify                   # Comprehensive health check
rake postgres:promote[host]            # Promote standby to primary

# Backups (requires pgBackRest)
rake postgres:backup:full
rake postgres:backup:incremental
rake postgres:backup:list
rake postgres:backup:restore[backup_id]
rake postgres:backup:restore_at["2026-01-29 01:15:00",promote]

# Credential rotation (zero downtime)
rake postgres:credentials:rotate_random
rake postgres:credentials:rotate_all

# Rolling updates (zero downtime)
rake postgres:update:version[18]       # Major version upgrade
rake postgres:update:patch             # Security patches

CLI (Standalone)

active_postgres setup --environment=production
active_postgres setup-standby HOST
active_postgres status
active_postgres overview
active_postgres promote HOST
active_postgres backup --type=full
active_postgres cache-secrets

Components

Component	Description	Config
Core	PostgreSQL installation	Always enabled
Performance Tuning	Auto-optimization	Disabled by default
repmgr	HA & automatic failover	repmgr: {enabled: true}
PgBouncer	Connection pooling	pgbouncer: {enabled: true}
pgBackRest	Backup & restore	pgbackrest: {enabled: true}
Monitoring	postgres_exporter + optional node_exporter/Grafana	monitoring: {enabled: true}
SSL	Encrypted connections	ssl: {enabled: true}
Extensions	pgvector, PostGIS, etc.	extensions: {enabled: true, list: [pgvector]}

Monitoring credentials

postgres_exporter uses a dedicated pg_monitor user. Provide a password in secrets and optionally set the username. Enable node_exporter for host-level metrics, and Grafana if you want a local UI:

components:
  monitoring:
    enabled: true
    user: postgres_exporter
    node_exporter: true
    node_exporter_port: 9100
    # node_exporter_listen_address: 10.8.0.10
    # grafana:
    #   enabled: true
    #   host: grafana.example.com
    #   listen_address: 10.8.0.10
    #   port: 3000
    #   prometheus_url: http://prometheus.example.com:9090
secrets:
  monitoring_password: $POSTGRES_MONITORING_PASSWORD
  # grafana_admin_password: $GRAFANA_ADMIN_PASSWORD

When Grafana is enabled, grafana_admin_password is required and Grafana will be installed on the configured host. If prometheus_url is provided, a Prometheus datasource is provisioned.

pgBackRest S3-compatible endpoints

For Tigris/MinIO/Wasabi/DO Spaces, set a custom endpoint and use path-style URLs:

components:
  pgbackrest:
    enabled: true
    repo_type: s3
    s3_bucket: myapp-backups
    s3_region: auto
    s3_endpoint: t3.storage.dev
    s3_uri_style: path

Point-in-time recovery (PITR)

Ensure WAL retention is long enough for your recovery window:

components:
  pgbackrest:
    retention_full: 7
    retention_archive: 14
    # Reduce incremental backup churn for large, frequently changed relations.
    # Block backups require repository bundling.
    repo_bundle: true
    repo_block: true
    # You can schedule full, differential, and incremental backups separately:
    # schedule_full: "0 2 * * 0"
    # schedule_differential: "35 2 * * 1-6"
    # schedule_incremental: "0 * * * *"

pgBackRest setup installs cron, enables it at boot, and checks that it is active. Backup schedules run only on the primary; configured log-archive schedules run on each database host.

Run a PITR restore:

rake postgres:backup:restore_at["2026-01-29 01:15:00",promote]

If you want the old primary to rejoin without a full re-clone after failover, use repmgr node rejoin --force-rewind once the node is ready to reattach:

repmgr node rejoin -f /etc/repmgr.conf --force-rewind

Stable app endpoint with PgBouncer

If you run PgBouncer on each PostgreSQL node and want a fixed app URL, enable follow_primary. Each PgBouncer instance periodically repoints to the current primary using repmgr metadata. Put a TCP load balancer or DNS record in front of the PgBouncer nodes.

components:
  pgbouncer:
    enabled: true
    follow_primary: true
    follow_primary_interval: 5

Automatic DNS updates on failover (dnsmasq)

If you use Messhy’s mesh DNS (dnsmasq), repmgr can update writer/reader DNS records on failover. Enable dns_failover under repmgr and provide DNS server IPs or hostnames. If you run setup from outside the mesh, use objects with both the public SSH host and the private WireGuard IP so setup can SSH in and failover updates stay on the mesh. If you run setup from inside the mesh, you can use a simple list of private IPs.

components:
  repmgr:
    enabled: true
    dns_failover:
      enabled: true
      provider: dnsmasq
      domain: mesh.internal
      # Or use multiple domains during cutover:
      # domains: [mesh.internal, mesh.v2.internal]
      dns_servers:
        - host: 18.170.173.14
          private_ip: 10.8.0.10
        - host: 98.85.183.175
          private_ip: 10.8.0.110
      primary_record: db-primary.mesh.internal
      replica_record: db-replica.mesh.internal
      # Or multiple records explicitly:
      # primary_records: [db-primary.mesh.internal, db-primary.mesh.v2.internal]
      # replica_records: [db-replica.mesh.internal, db-replica.mesh.v2.internal]

This installs an event hook so repmgr updates /etc/dnsmasq.d/active_postgres.conf on the DNS servers whenever the primary changes. DNS servers must be reachable over the private network and allow SSH from the database nodes (active_postgres installs SSH keys on first setup).

Secrets Management

secrets:
  # Environment variables
  password: $POSTGRES_PASSWORD

  # Command execution
  password: $(op read "op://vault/item/field")
  password: rails_credentials:postgres.password
  password: credentials:postgres.password

  # AWS Secrets Manager
  password: $(aws secretsmanager get-secret-value --secret-id myapp/postgres --query SecretString)

Read/Write Splitting (Rails 6+)

class ApplicationRecord < ActiveRecord::Base
  connects_to database: { writing: :primary, reading: :primary_replica }
end

# Writes go to primary, reads go to replica
User.create(name: "Alice")  # → Primary
User.all                     # → Replica

Requirements

• Ruby 4.0+
• A PostgreSQL version supported by the target Linux distribution
• Ubuntu 20.04+ / Debian 11+ with systemd
• SSH key-based authentication (hosts must be in ~/.ssh/known_hosts)
• Rails 8.1+ (optional)

Operational safety

• Keep PostgreSQL, PgBouncer, exporters, and backup endpoints behind a private network or narrowly scoped firewall rules.
• Test promotion, application reconnection, backup restore, and point-in-time recovery in a disposable environment before using them in production.
• Review generated configuration and package changes before each rollout; automatic failover cannot determine whether an application is safe to write after a network partition.
• Store database, replication, monitoring, and backup credentials in a secret manager. Do not commit resolved values or generated configuration.

SSH host key verification

By default, host keys are verified strictly (always). For first-time provisioning you can set:

ssh_host_key_verification: accept_new

This trusts a host key the first time and then pins it; prefer always once hosts are known.

License

MIT