Category: Databases
Managed PostgreSQL in your account: replicas, backups and tested PITR
Your database with a replica, recovery to a specific minute and planned major upgrades, on your own servers or alongside your provider's managed service.
- Where it runs
- In your account, in your name
- Backups
- With tested restores
- Updates
- Planned and documented
In short
PostgreSQL is an open source relational database system. Managing it means answering for it being available, fast and recoverable. We set up replicas with automatic failover, backups with point-in-time recovery (PITR) that we restore in tests, connection pooling, metrics and major upgrades. And we tell you when your provider's managed database is the better deal.
What do we take care of?
A database can run for years without anyone looking at it, until the day the disk fills up with WAL or the nightly backup turns out to be empty. Our job is to make sure that day never comes and, if it does, that recovery is a procedure that has already been rehearsed.
Primary server
Memory configuration, autovacuum, connections and slow query logging.
Replicas
Streaming replication with Patroni to fail over only if the primary goes down.
Backups and WAL
Base backups and continuous archiving for point-in-time recovery.
PgBouncer
Connection pooling between the applications and the database.
Metrics
postgres_exporter in Prometheus: replication lag, locks, size and transactions.
Access
One user per application, TLS and nothing exposed to the internet.
What architecture do we recommend?
For production, a primary and at least one replica on another machine, with Patroni deciding which is the primary based on a consensus store such as etcd. Applications connect through PgBouncer or an address that always points to the primary, so a failover does not require touching their configuration.
| Situation | What we set up | Why |
|---|---|---|
| Small database, a short outage is acceptable | One server and off-site WAL archiving | Tested recovery without the complexity of a cluster |
| Production that cannot stop | Patroni with a primary and a replica, plus WAL archiving | Automatic failover in seconds |
| Provider with a managed database | RDS, Cloud SQL, Azure Database or another | Fewer moving parts; we monitor, tune and test restores |
A replica is not a backup: if someone drops a table, the deletion reaches the replica within milliseconds. That is why WAL archiving is always in place, replica or not.
What problems do we come across most often?
- pg_dump backups that nobody restores: they take hours, only recover to the moment of the backup and sometimes fail silently.
- Disk full of WAL: broken archiving or an abandoned replication slot holds on to WAL until the disk fills up.
- Connections exhausted: each application process opens its own and the server rejects new ones at peak times.
- Poorly tuned autovacuum: bloated tables, ever slower queries and the risk of running out of transaction IDs.
- An unsupported version: several major versions behind, with no security patches.
- A single account for everything: every application logs in as the superuser.
How do we prove the backup works?
With pgBackRest or WAL-G we take regular base backups and archive every WAL segment, encrypted, to object storage in another account or with another provider. At least once a month we restore to a specific point in time on a test server, check that the database starts and run some control queries. The time it takes is your real recovery time, and we note it in the report so it is not an estimate.
How do we carry out a major upgrade?
There are two routes. With pg_upgrade the upgrade is done on the same server and the outage is short, especially with file links. With logical replication we prepare a new server on the new version, keep it in sync and switch the applications over once it has been tested; it takes longer to prepare but the outage is minimal, and it is the same method we use to migrate away from RDS or Cloud SQL. In both cases we rehearse first on a copy and review the extensions.
Is RDS or Cloud SQL better than your own PostgreSQL?
| Aspect | RDS, Cloud SQL or OVHcloud managed PostgreSQL | Your own administered PostgreSQL |
|---|---|---|
| Control and extensions | Only the extensions and parameters the provider supports, with no real superuser or operating system access | Any extension and parameter, with full access to the server |
| Versions and upgrades | Minor patches in the maintenance window; the provider decides how long each major version is supported | You decide when; we prepare and rehearse the major upgrade with pg_upgrade or logical replication |
| Backups and point-in-time recovery | Automatic backups and, depending on the provider, point-in-time recovery, with a maximum retention and inside their account | pgBackRest or WAL-G, with the retention we agree and backups in another account or with another provider |
| Cost | More expensive than an equivalent machine, and it grows with size, replicas and high availability | Lower infrastructure cost, but you have to add the administration hours |
| Provider lock-in | Higher: leaving requires a migration, for example with logical replication | Lower: standard PostgreSQL, which moves to another provider with the same tools |
| Who is responsible | The provider, for the service infrastructure under its service level agreement; configuration, data and queries remain yours | We are, for operations, backups and recovery, as agreed; the provider, only for the machine |
Often, yes: when it is available, the workload is normal and you need nothing special. RDS, Cloud SQL, Azure Database or the managed databases from DigitalOcean and OVHcloud handle patching, replicas and low-level backups well. We run PostgreSQL ourselves when the provider does not offer it, as with Hetzner, when you need extensions or parameters the service does not allow, or when the cost of the managed service shoots up with size. Even so, there is work to do: parameters, indexes, slow queries, alerts and restore tests. If you want that oversight to be continuous, it fits within SRE as a service; and the metrics end up in monitoring with Grafana and Prometheus.
What about TimescaleDB?
TimescaleDB is not a different database: it is a PostgreSQL extension for time series, such as sensor readings, metrics or prices. It turns large tables into hypertables partitioned by time, compresses old data and deletes what expires with retention policies. What you know about PostgreSQL still applies, and we manage it in the same way: a replica, backups with pgBackRest and tested restores.
There are two particular details. Compression, continuous aggregates and retention policies come under the Timescale License, not Apache 2.0: you can use them in your account, but many providers’ managed databases do not offer the extension or only include the Apache part, so we usually set it up on a self-run PostgreSQL. And before every major upgrade we check that the extension version is compatible with the target PostgreSQL version, and upgrade it first.
Where do we set it up?
In your account, with the provider you already use or the one that fits best. If the data has to stay in the EU, Hetzner and OVHcloud are the European options.
Providers we set it up on
- Hetzner
- AWS
- Google Cloud
- Azure
- DigitalOcean
- OVH
- Own server
| Provider | How we set it up | Things to bear in mind |
|---|---|---|
| Hetzner | Cloud or dedicated servers with PostgreSQL, Patroni and pgBackRest set up by us, on a private network. WAL archiving to Hetzner Object Storage, which is S3-compatible. | Hetzner does not offer a managed database: we provide the high availability and the backups. |
| AWS | RDS for PostgreSQL or Aurora when they fit; otherwise, EC2 with Patroni and backups to S3. | With RDS we review parameters, read replicas, maintenance windows and that restores actually work. |
| Google Cloud | Cloud SQL for PostgreSQL, or Compute Engine with Patroni and backups to Cloud Storage. | Cloud SQL has point-in-time recovery; we test it all the same. |
| Azure | Azure Database for PostgreSQL (flexible server), or virtual machines with Patroni. | We choose zone-redundant high availability when the workload justifies it. |
| DigitalOcean | DigitalOcean managed PostgreSQL with standby nodes, or Droplets with Patroni. | |
| OVH | OVHcloud managed database for PostgreSQL, or Public Cloud instances with Patroni. | |
| Own server | PostgreSQL with Patroni on your servers or virtual machines, and WAL archiving to external S3 storage. | The replica should be on another physical machine; otherwise the high availability is only apparent. |
What is included
- PostgreSQL with streaming replication and automatic failover with Patroni, or your provider's managed service if it is a better fit.
- Base backups and continuous WAL archiving with pgBackRest or WAL-G, off the server and encrypted.
- Regular point-in-time test restore, with the time it takes noted in the report.
- PgBouncer so that applications do not run out of connections.
- Metrics with postgres_exporter and alerts on replication lag, disk, locks and slow queries.
- Review of slow queries, indexes and memory configuration with pg_stat_statements.
- Major upgrades with pg_upgrade or logical replication, rehearsed first on a copy.
- Per-application users and permissions, encrypted connections and access only from the private network.
Frequently asked questions
Can we migrate from RDS to another provider without stopping the application?
Almost without stopping. With logical replication from RDS to the new database, the data stays in sync while you test; the cutover comes down to the moment you change the connection string. Beforehand we review extensions and sequences, which are not replicated on their own.
To what point in time can we recover the database?
With continuous WAL archiving, to any moment within the retention period we agree, for example the minute before an accidental deletion. The retention period and the measured restore time are written down.
Do you also manage MySQL or MariaDB?
Yes, with the same approach of replicas, tested backups and planned upgrades, although with their own tools.
Tell us about your case
In a free 20-minute call we look at where you are and where to start. No commitment.
Official documentation consulted
The technical details on this page are based on:
- PostgreSQL Documentation: Continuous Archiving and Point-in-Time Recovery (PITR)
- PostgreSQL Documentation: pg_upgrade
Reviewed on