Architecture

PostgreSQL cluster architectures and implmenetation details.

Component Overview

Here is how PostgreSQL module components and their interactions. From top to bottom:

  • Cluster DNS is resolved by DNSMASQ on infra nodes
  • Cluster VIP is manged by vip-manager, which will bind to cluster primary.
    • vip-manager will acquire cluster leader info written by patroni from etcd cluster directly
  • Cluster services are exposed by Haproxy on nodes, services are distinguished by node ports (543x).
    • Haproxy port 9101: monitoring metrics & stats & admin page
    • Haproxy port 5433: default service that routes to primary pgbouncer: primary
    • Haproxy port 5434: default service that routes to replica pgbouncer: replica
    • Haproxy port 5436: default service that routes to primary postgres: default
    • Haproxy port 5438: default service that routeroutesto offline postgres: offline
    • HAProxy will route traffic based on health check information provided by patroni.
  • Pgbouncer is a connection pool middleware that buffers connections, exposes extra metrics, and brings extra flexibility @ port 6432
    • Pgbouncer is stateless and deployed with the Postgres server in a 1:1 manner through a local unix socket.
    • Production traffic (Primary/Replica) will go through pgbouncer by default (can be skipped by pg_default_service_dest )
    • Default/Offline service will always bypass pgbouncer and connect to target Postgres directly.
  • Postgres provides relational database services @ port 5432
    • Install PGSQL module on multiple nodes will automatically form a HA cluster based on streaming replication
    • PostgreSQL is supervised by patroni by default.
  • Patroni will supervise PostgreSQL server @ port 8008 by default
    • Patroni spawn postgres servers as the child process
    • Patroni uses etcd as DCS: config storage, failure detection, and leader election.
    • Patroni will provide Postgres information through a health check. Which is used by HAProxy
    • Patroni metrics will be scraped by prometheus on infra nodes
  • PG Exporter will expose postgres metrics @ port 9630
    • PostgreSQL’s metrics will be scraped by prometheus on infra nodes
  • Pgbouncer Exporter will expose pgbouncer metrics @ port 9631
    • Pgbouncer’s metrics will be scraped by prometheus on infra nodes
  • pgBackRest will work on the local repo by default (pgbackrest_method)
    • If local (default) is used as the backup repo, pgBackRest will create local repo under the primary’s pg_fs_bkup
    • If minio is used as the backup repo, pgBackRest will create the repo on the dedicated MinIO cluster in pgbackrest_repo.minio
  • Postgres-related logs (postgres,pgbouncer,patroni,pgbackrest) are exposed by promtail @ port 9080
    • Promtail will send logs to Loki on infra nodes

pigsty-arch.jpg


Last modified 2024-02-29: update content (34b2b75)