Postgresql high availability
From wikinotes
Documentation
postgres: high availability https://www.postgresql.org/docs/current/high-availability.html postgres: logical replication https://www.postgresql.org/docs/10/logical-replication.html
Discussion
HA Postgres Options Overview -- 2019 postgres-(8.0-9.2) https://www.youtube.com/watch?v=fsMvr96gTjY Evolution of HA Postgres -- 2018 postgres-(8.0-10.0) https://www.youtube.com/watch?v=ntVjVNnilX0
Overview
There are a handful of different ways to do this, but most seem to be centered around the following concepts;
- Alternate modes are configured in
postgres.conf
- In some modes, transactions are recorded on disk as WAL files (wal==Write-Ahead-Log)
- You or Postgres-mode copies these WAL files to all standby servers.
- Standby servers ingest WAL files
Standby servers can be in separate datacenters, but in almost every case, only the primary database is ever written to. Strategies involving Hot Standbies mean the servers can be read from.
Strategies
Postgres-8.0 Postgres HA: Warm Standby Postgres-8.2+ Postgres HA: Hot Standby Postgres-10.0+ Postgres HA: Logical Replication (hot standby)