PostgreSQL Across Regions on PaaS: A Primary in Frankfurt, Hot Standbys in London and New York, Pgpool-II in Each

A database cluster on separate hosts survives a server failure. It does not survive the building, and a board that has read about data centre fires will eventually ask about the building. The answer is a database whose standbys are in other cities, and the hard part has always been building and operating it. This post follows a CTO deploying the PostgreSQL Multi-Region Cluster on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic, and answering the board's question with a diagram.

The company is a European HR software vendor we will call Copperfield People, with EU customers served from Frankfurt, a UK subsidiary and a growing US customer base. The PostgreSQL Multi-Region Cluster package deploys a primary in the first region you choose and hot standbys in the others, joined by streaming replication, with a Pgpool-II load-balancing layer in each region.

The topology the package builds

PostgreSQL's only officially supported replication topology is primary-secondary, and the package uses it with one addition for disaster recovery: the final layout is primary-secondary-secondary, one primary and two standbys, each in a different region. The order of the regions in the install dialog matters: the first is the primary. Copperfield lists Frankfurt, London, New York. Each region also gets a Pgpool-II node, so applications in every region have a local entry point that knows the whole cluster's topology, balances reads and monitors the nodes. The entry point for the cluster as a whole is the leader Pgpool-II in the first region; if it fails, any other Pgpool-II node serves, and a client that supports multiple hosts in its connection string fails over on its own.

The installer creates the three environments in one environment group, optionally isolated, and configures replication across the inter-region network with the authentication and settings each standby needs. What used to be a week of careful work for a DBA is a form with a region list.

Local reads, remote writes, and being honest about latency

The US application reads employee records, org charts and payslip histories, and most of that is reads. Pointing its read traffic at the New York Pgpool-II, which balances read-only statements to the local standby, keeps those queries on the same continent. Writes, a new hire or a salary change, go to Frankfurt, and the round trip from New York to Frankfurt is roughly 90 milliseconds. Copperfield's application already batches its writes and shows optimistic UI, so a 90 millisecond commit is invisible; a chatty write path would notice. The CTO's rule for the engineering team is written down: reads local, writes are a network call, design accordingly.

Replication to the remote standbys is asynchronous, so the London and New York copies lag the primary by the network delay plus replay time, normally well under a second. A US user who changes their address and immediately reloads the page could read the old value from the local standby; the application handles this with a short read-after-write window pinned to the primary, the same pattern any read-replica design needs.

What losing a region actually means

The board's question has three answers, one per region.

• London or New York is lost. Nothing changes for writes. Reads in that region fail over to another Pgpool-II node and are served from a remote standby, slower but correct. The platform restores the standby's container when the region returns and it catches up from the primary.
• Frankfurt is lost. Writes stop until a standby is promoted. The Pgpool-II nodes in London and New York detect the primary is unreachable; promotion of a standby is the step the CTO chose to keep as a deliberate action, because an automatic promotion during a network partition between regions risks two primaries. The runbook is: confirm Frankfurt is truly down and not merely unreachable from one place, promote London with pg_ctl promote, reconfigure New York to follow London, repoint the application's write connection to London's Pgpool-II. Rehearsed on a clone, it takes about ten minutes. Data loss is bounded by the replication lag at the moment of failure, typically under a second.
• Frankfurt returns. The old primary is rebuilt as a standby of London with a fresh base backup, and the cluster is again primary-secondary-secondary, with the primary now in London. Moving it back to Frankfurt is a planned switchover during a quiet window.

Backups are still not optional

Three copies in three cities protect against losing cities. They do not protect against a bad deployment that deletes rows in Frankfurt and replicates the deletion to London and New York in under a second. The CTO installs the Database Backup/Restore add-on on the London standby with nightly dumps to a Backup Storage node in a fourth location, and enables WAL archiving on the primary for point-in-time recovery. The compliance annex now has two sentences: data is replicated to three EU and US regions with failover, and backups are retained for 35 days in a separate location with quarterly restore tests.

Encryption across the wire

Replication traffic between regions travels the platform's inter-region network, and Copperfield's security policy requires encryption in transit regardless. The PostgreSQL SSL/TLS add-on is installed on the PostgreSQL and Pgpool-II layers in every region; it enables SSL with generated certificates and moves authentication to SCRAM-SHA-256. Client connections use sslmode=verify-ca. The container firewall exposes nothing to the public internet; the application environments in each region reach their local Pgpool-II over internal addresses.

Cost and the alternative

Three PostgreSQL nodes and three Pgpool-II nodes, scaling vertically by the hour. Copperfield's database is a few tens of gigabytes with moderate load, so the PostgreSQL nodes average around 16 cloudlets each and the Pgpool nodes around 4 (a cloudlet is 128 MiB of RAM plus 400 MHz of CPU): roughly 60 cloudlets an hour in total, about $145 a month before discounts at MassiveGRID's published $0.003372 per cloudlet-hour, plus inter-region replication traffic, which for an HR system is small. The alternative the CTO had been quoted was a managed multi-region database service priced per instance-hour and per replica with cross-region transfer fees, at several times that figure, and with a region list that did not include Frankfurt.

Frequently Asked Questions

Can I write in more than one region?

Not with PostgreSQL's native replication, which has one primary. If you need multi-primary writes across regions, the marketplace's MariaDB Galera Multi-Region Cluster provides it for MySQL-compatible workloads, with the constraints Galera brings. For PostgreSQL, design writes to go to the primary region and keep them coarse.

How far behind are the remote standbys?

Normally milliseconds to a fraction of a second: the inter-region latency plus WAL replay time. During a bulk write, lag grows until the standby catches up. Monitor it with pg_stat_replication on the primary, and set a load alert on the standby's disk so WAL accumulation never fills it.

Why not let Pgpool-II promote a standby automatically across regions?

You can configure it to, and within one data centre that is common. Across regions, a network partition can make the primary look dead from one region while it is still accepting writes in another, and automatic promotion would create two primaries. Many teams prefer a human confirmation step for cross-region failover; the promotion itself is one command.