It usually starts with a single database server. It runs for years and nobody thinks about it. Then a disk dies or the kernel hangs, and you find out what an hour of downtime really costs. Or there is a backup, but nobody has ever tried to restore from it.

Sometimes it’s the other way round: the database is alive but can’t keep up. Reports, search and analytics read the same tables the application writes to, and everything slows down together.

Replication helps with both: a second (and third) copy of the database that keeps catching up with the main one. But only if you’re clear about what it gives you and what it doesn’t.

What we build

For each project we pick the simplest setup that covers the actual risk. Most of the time it’s one of three.

PostgreSQL with automatic failover:

  • three PostgreSQL nodes with streaming replication: one primary takes writes, two replicas replay its WAL;
  • Patroni on every node looks after its local PostgreSQL and decides who the leader is;
  • etcd on the same three nodes stores a single fact, “who is the leader”, and provides quorum;
  • HAProxy gives the application two stable endpoints: one for writes (always routed to the primary) and one for reads (spread across replicas). PgBouncer is added for connection pooling when needed.

MariaDB Galera:

  • three nodes with synchronous replication, each technically able to accept writes;
  • no external arbiter: quorum is built into Galera itself;
  • ProxySQL sits in front of the cluster. It understands SQL, so it sends SELECTs to readers and writes to a single writer on its own. The application sees one address and knows nothing about the topology.

The simple option: one primary, one asynchronous replica and proper backups. Failover is manual, following a runbook. For many projects that is more than enough.

Nodes are spread across separate physical servers, and the whole configuration lives in Ansible.

How it works, and where it bites

Why three nodes, not two. Quorum means majority. With two nodes, once one fails there is no majority and nobody can make a decision. For automatic failover, two nodes are worse than one: more complexity, no benefit. Three nodes survive one failure, five survive two. Hence always an odd number. Quorum is what protects you from split-brain, when the network splits and both halves decide they’re in charge. The half without a majority simply refuses to accept writes.

How PostgreSQL fails over. The leader keeps refreshing an “I’m alive” key in etcd. When it dies, the key expires, and the replica with the freshest data takes it over and becomes primary. HAProxy asks Patroni who the leader is and moves the traffic:

listen pg_write
    bind *:5000
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server db1 db1:5432 check port 8008
    server db2 db2:5432 check port 8008
    server db3 db3:5432 check port 8008

From failure to recovery takes roughly 20–30 seconds, with no human involved. When the old primary comes back, it rejoins as a replica by itself.

The price of synchronous replication. Asynchronous replication is fast, but a crash can cost you the last few milliseconds of transactions. Galera doesn’t lose them, because every COMMIT waits for the cluster to confirm. The trade-off: slower writes, the cluster writes at the pace of its slowest node, and Galera doesn’t belong on a WAN. It also has schema requirements: InnoDB only and a primary key on every table. A MyISAM table will silently drift out of sync across nodes without a single error. That’s why we audit the schema before migrating.

Multi-master doesn’t speed up writes. Every Galera node applies every change in the cluster, so three nodes don’t write three times faster. It’s better to write to one node: concurrent changes to the same rows on different nodes end in a deadlock, and only at COMMIT time.

Reading from replicas has a catch. A write goes to the writer, the next read goes to a replica that hasn’t caught up yet. On an idle Galera cluster you never see it; under load, a share of reads quietly returns stale data. So we turn on wsrep_sync_wait before enabling read/write splitting, not after the first strange bug report. In PostgreSQL, reads that must see freshly written data stay on the primary.

Replication is not a backup

This needs saying plainly. Drop a table, and a moment later it’s gone from every replica too. Ransomware, a broken migration, an UPDATE without a WHERE — replication will faithfully copy all of it.

So we always set up backups alongside the cluster:

  • PostgreSQL — pgBackRest: full and incremental backups plus WAL archiving. That gives you PITR, restoring the database to a specific minute, say 14:32, just before things went wrong;
  • MariaDB — mariabackup plus binlogs for the same point-in-time recovery;
  • backups are taken from a replica, so the primary isn’t loaded;
  • copies live on separate storage, not on the same servers;
  • restores are tested regularly: we bring up a copy on a separate machine and check the data. A backup nobody has ever restored from is just a hope.

Prometheus and Grafana track replication lag, cluster state (how many nodes are in quorum, who the leader is) and the age of the last backup. You get an alert when a replica falls behind, a node drops out or a backup doesn’t run on time.

When you don’t need a cluster

A cluster is more complex than a single server: more nodes, more moving parts, its own pitfalls during upgrades. If an hour of downtime won’t sink you, we’ll honestly recommend one primary, an async replica and good PITR backups. And if the database can’t keep up with writes, we look at indexes, queries and hardware first. Replication scales reads, not writes.

What you get

  • A working PostgreSQL or MariaDB cluster (or a simpler setup if that’s enough) with a single connection point for the application.
  • Automatic failover when a node dies, tested by actually shutting nodes down, not just on paper.
  • PITR backups on separate storage and regular restore tests.
  • Dashboards and alerts: replication lag, quorum state, backup age.
  • Ansible roles and configs in your repository.
  • Documentation: the architecture, how to check cluster state, how to switch the primary by hand, how to restore from backup.
  • Full access to everything we deploy.

Tell us what database you run, how much data you have and what hurts: downtime, slow queries or doubts about your backups. We’ll tell you which setup you actually need.