Skip to main content
DevOps // PostgreSQL

Your database is the business. Let's make it hard to kill.

Streaming replication, automatic failover, connection pooling, and backups you've restored at least once, for Odoo and every application that lives on PostgreSQL.

~/anvexis/devops/postgresql-ha

$ anvexis tldr

Replicate, pool connections, automate failover, rehearse restores.

$ anvexis facts

Replication
Streaming · logical
Failover
Patroni or managed
Pooling
PgBouncer

The HA toolkit

Streaming replicas

Standby servers that can take over and serve read-only reporting.

Automatic failover

Leader election that promotes a replica when the primary fails.

Connection pooling

PgBouncer in front of busy apps so connections don't exhaust memory.

Point-in-time recovery

Continuous WAL archiving to restore to any moment, not just last night.

Pick your recovery targets

SetupTypical recovery pointTypical recovery time
Nightly dumps onlyUp to a day of dataHours
WAL archiving + base backupsMinutesDepends on database size
Streaming replica + failoverSecondsUnder a few minutes

Actual targets depend on workload and configuration and should be confirmed by failover and restore drills.

Why the database deserves special care

Applications can be redeployed in minutes. Data cannot. When the database server fails, everything that depends on it stops, and if the latest backup is a day old, a day of orders, invoices, and stock movements may be gone. For an ERP, the database is effectively the business.

PostgreSQL has excellent built-in tools for resilience: streaming replication keeps standby servers continuously up to date, continuous archiving of the write-ahead log makes point-in-time recovery possible, and mature tooling automates failover. Getting them configured, monitored, and tested together is where most setups fall short.

We design for two numbers you agree with the business: how much data you can afford to lose (RPO) and how long you can afford to be down (RTO). Every technical choice follows from those.

Architecture options

SetupTypical recoveryGood for
Single server + nightly dumpsRestore from dump; data loss up to a dayDevelopment and very small systems
Single server + WAL archivingPoint-in-time restore; minutes of data lossSmall production systems
Primary + streaming standbyManual promotion; seconds of data lossMost business-critical systems
Patroni cluster with automatic failoverAutomatic promotion in tens of secondsSystems that must stay up without a person on call
Managed cloud PostgreSQL (multi-zone)Provider-managed failoverTeams that prefer not to run the database themselves

Actual recovery times depend on data size, network, and configuration, and are measured in your drills.

What we set up

Replication

Streaming replication to one or more standbys, synchronous where zero data loss is required.

Point-in-time recovery

Continuous WAL archiving with pgBackRest or WAL-G to off-site storage.

Automated failover

Patroni with a consensus store, plus a connection layer that follows the primary.

Connection pooling

PgBouncer to protect the database from connection storms.

Monitoring

Replication lag, connection counts, slow queries, disk growth, and backup freshness.

Maintenance

Vacuum, index, and upgrade procedures that don't need downtime surprises.

How we deliver HA

  1. Stage 01

    Assess

    Week 1

    Review the current setup, data size, growth, and recovery expectations.

  2. Stage 02

    Design

    Week 1

    Agree RPO and RTO, and pick the architecture that meets them.

  3. Stage 03

    Build

    Week 2–3

    Provision standbys, archiving, failover, and pooling as code.

  4. Stage 04

    Test

    Week 3

    Run failover and restore drills, and measure the real numbers.

  5. Stage 05

    Hand over

    Week 4

    Runbooks, alerts, and a drill schedule your team can follow.

Signs your database needs attention

  • Backups exist but have never been restored
  • Nobody knows how long a restore would take
  • The disk has filled up at least once
  • Failover would need a specific person to be awake
  • Major version upgrades keep getting postponed

Common pitfalls we help you avoid

The first pitfall is trusting failover that has never been tested. Automatic failover that has only ever run in a vendor's documentation can surprise you, so we run drills and measure what actually happens.

The second is connection handling. After a failover, applications must find the new primary; without a connection layer that follows it, the database is up but the application still points at the old server.

The third is unbounded growth. Disks fill with logs, bloated tables, or archived WAL that was never cleaned up, and a full disk stops PostgreSQL just as surely as a hardware failure.

What we measure

MeasureWhy it matters
Replication lagHow much data a failover could lose
Failover time in drillsThe real recovery time, not the theoretical one
Backup freshnessTime since the last successful backup and archive
Disk growth trendWarning long before space runs out

Who does what

We handle

  • Architecture, setup, and automation
  • Monitoring, alerts, and drills
  • Runbooks and handover

Your team handles

  • Agreeing recovery targets
  • Scheduling maintenance windows
  • Joining drills and approving results

A worked example

Picture an Odoo database of a few hundred gigabytes on a single server with nightly dumps. A disk failure would lose up to a day of orders and take hours to restore. Adding a streaming standby, continuous WAL archiving to off-site storage, and Patroni-managed failover changes that picture.

In a drill, the primary is shut down on purpose, the standby takes over, and the application reconnects through the pooler. The measured failover time and data loss are recorded and compared with the agreed targets.

Before you start

  • Current database size and growth
  • Agreed data-loss and downtime targets
  • Where standbys and backups can live
  • A maintenance window for the switch

What success looks like

Success is a database failure that becomes a short, uneventful incident: the standby takes over, users reconnect, and the measured data loss stays within the agreed target.

The trade-offs, from the PostgreSQL docs

PostgreSQL's documentation describes warm and hot standby servers kept current by reading the write-ahead log; if the main server fails, the standby has almost all its data and can quickly become the new primarySource [1]. Its comparison table marks 'primary failure will never lose data' only for WAL shipping with synchronous replicationSource [1], which is the trade-off between safety and write latency you decide with us.

High availability isn't backup. Continuous archiving supports point-in-time recovery to any moment since the base backup was taken, and the docs advise setting up and testing WAL archiving before the first base backup and keeping several backup setsSource [2]. We set up both, and prove recovery with scheduled restores.

Frequently asked questions

Should we use a managed database?
Managed services like RDS or Cloud SQL handle much of the HA work and are a good default.
Can reporting use the replica?
Yes. Moving heavy reports to a read replica protects the primary.
How often should we test failover?
Regularly, at least quarterly, and after major changes.
Does Odoo support read replicas?
Odoo writes to the primary; replicas serve external reporting and BI tools.
What PostgreSQL version should we run?
A currently supported major version that your applications are tested against.
Does Odoo work with replicas?
Odoo writes to the primary. Read replicas are useful for reporting and BI so heavy queries don't slow down users.
Synchronous or asynchronous replication?
Synchronous avoids data loss on failover but adds latency to every commit. We choose based on your RPO and workload.
How do you upgrade PostgreSQL versions?
With logical replication or pg_upgrade, rehearsed on a copy first, and scheduled with a short, planned cut-over.
Can you take over an existing cluster?
Yes. We start with an audit and fix the riskiest gaps first, usually backups and monitoring.
Is managed cloud PostgreSQL good enough?
Often, yes. It removes a lot of operational work. You still need to check backup retention, failover behavior, and extension support.
How much downtime does the switch to HA need?
Usually a short planned window to move to the new setup, rehearsed beforehand on a copy.
Can we run PostgreSQL across two data centers?
Yes, with a standby in the second location. Latency between sites affects whether synchronous replication is practical.
Do you tune query performance too?
Yes. Slow query analysis, indexing, and configuration tuning are often part of the same engagement.
What monitoring do you add for PostgreSQL?
Replication lag, connections, locks, slow queries, cache hit rates, disk growth, vacuum health, and backup freshness, with alerts on the ones that need action.
Do you support PostgreSQL for systems other than Odoo?
Yes. The same replication, backup, and failover practices apply to any application built on PostgreSQL.
Do we need synchronous replication?
Only if losing any committed transaction on failover is unacceptable. It's the only mode the docs mark as never losing data, at some latency costSource [1].
If we have a standby, do we still need backups?
Yes. A standby replicates mistakes too. Point-in-time recovery from backups lets you go back before an errorSource [2].