Pingu
Computer MySQL PostgreSQL Books Publications
Spielereien Kanu Geopolitik Business TopoDB POI Klettersteigen History TransPool Thermal Baden Brokenstuben Goldwaschen
Blog Contact
Shinguz
Google
/ch/open

PostgreSQL High Availabilty Solutions

/ home / computer / postgresql / postgresql for mysql admins / .

Table of Contents

Details
  1. Overview

  2. Cluster manager / management software

  3. Other PostgreSQL high availability setups


Overview

  • Failover solutions
    • Shared Disk Failover
    • File System or Block Device Replication (DRBD)
  • Write-Ahead Log (WAL) Shipping
    • built-in
    • sync = on/off
    • read-only replicas: hot standby
    • continous archiving -> warm standby or log shipping
    • file shipping vs. streaming replication
  • Logical Replication
    • built-in
    • pglogical
  • Trigger-Based Replication
    • Slony-I
    • Londiste
  • Middleware Based Repllication
    • Pgpool-II
    • Continuent Tungsten
  • Asynchronous Multimaster Replication
    • Bucardo
  • Synchronous Multimaster Replication

Terminology:

  • Primary (MySQL: Master/Source)
  • Standby (MySQL: Slave/Replica)

Mechanism is through WAL:

  • Archived WAL shipping
  • Recent WAL streaming

Source: https://www.postgresql.org/docs/current/different-replication-solutions.html

Use cases

  • High Availability (HA)
  • Disaster failover (remote)
  • Redundancy (HA)
  • Backup Standby
  • Cascaded Replication (Relay)
  • Security (WAL shipping)
  • Delayed replication (WAL shipping)
  • Full-Synchronous replication
  • Read-only Standby (read scaleout)

Trigger based replication

Trigger-based replication systems such as Slony, Londiste and Bucardo


PostgreSQL high availability setups

  1. PostgreSQL high availability setups
    • Reasons for high availability
    • Availability / fail-over
    • Reporting replica
    • Backup replica?
    • Read scale-out?
    • Upgrade path

See also Overview on the very top!!!


Cluster manager

t watches the Distributed Configuration Store (DCS) / leader info (patroni, etcd, Consul, ZooKeeper, etc.).

Patroni

Based on PostgreSQL (physical) Streaming Replication (async or sync).

vip-manager

Patroni + etcd HaProxy PgBouncer

etcd or Zookeeper

https://www.dbi-services.com/blog/setup-a-two-node-postgres-ha-cluster-using-edb-advanced-server-and-efm/ https://indico.cern.ch/event/1471762/contributions/6280213/attachments/2998599/5283395/P-DBI-E-20240323-PGconf-Architecture-for-Seamless-PostgreSQL-Upgrades.pdf https://www.dbi-services.com/blog/the-dbi-services-postgresql-reference-architecture-1-the-commercial-approach/ https://www.dbi-services.com/blog/the-dbi-services-postgresql-reference-architecture-2-the-community-approach/

Where external_pid_file still earns its place is clusterware, the Pacemaker and Red Hat Cluster resource agents that predate that model and still expect a PID file at a known path. It’s just a small piece of glue between PostgreSQL and whatever is responsible for starting and stopping it. https://thebuild.com/blog/all-your-gucs-in-a-row-external_pid_file/