PostgreSQL usually sits under other systems
PostgreSQL is a relational database distributed under the PostgreSQL License, a permissive open-source license with no paid edition of the core server. Many self-hosted platforms keep their state in it, including Keycloak, GitLab, Mattermost and Airflow, along with the metadata stores of Superset and Metabase. When a cluster stops, the applications on top of it stop too, so the database often carries the strictest recovery requirements in the stack.
List what depends on each cluster: the owner, tolerable data loss and downtime, peak connections and required extensions. Those answers decide whether applications share a cluster, whether replication must be synchronous, and how far back point-in-time recovery has to reach.
Choose a topology for the workload
The right shape depends on recovery targets, the platform you already run and who will operate it.
Single server with backups
One primary with continuous WAL archiving to separate storage. Simple to run where a restore window is acceptable, with recovery resting entirely on tested backups.
Patroni on virtual machines
Patroni manages streaming replicas and automatic failover, using a configuration store such as etcd for leader election. Clients reach the primary through a proxy, virtual IP or DNS entry.
CloudNativePG on Kubernetes
The CloudNativePG operator runs a primary and replicas as Kubernetes resources and handles failover, rolling updates and backup to object storage. It fits teams already operating persistent volumes well.
Air-gapped networks
Packages, images and extensions come from an internal mirror. Backup repositories, monitoring and certificate authorities also live inside the boundary, so plan them with the cluster.
What drives sizing
Measure these with representative workload before choosing instance sizes and storage.
| Driver | Why it matters | What to measure |
|---|---|---|
| Working set | Queries are fastest when frequently read data fits in shared buffers and the operating system cache. | Cache hit ratio and read latency for the busiest tables. |
| Write volume | Writes generate WAL, which drives replica lag, archive storage and backup size. | WAL generated per hour at peak and during batch jobs. |
| Connections | Each connection is served by its own backend process, so idle connections still use memory. | Peak connections per application and how many are active at once. |
| Storage latency | Commit time depends on how quickly WAL reaches durable storage. | Commit latency on the chosen volumes under load. |
| Recovery window | Point-in-time recovery needs every WAL file since the oldest retained base backup. | Archive size for the agreed retention and measured restore time. |
Plan failover before it happens
Asynchronous streaming replication can lose the latest commits during failover. Synchronous replication waits for a standby and adds commit latency. Choose per cluster, based on the data-loss tolerance each application owner signed off.
Let one tool decide which node is primary, and make sure the old primary cannot accept writes afterward. Two writable primaries produce divergent data that is hard to reconcile. Rehearse a planned switchover and an unplanned failover, and record what each dependent application saw.
Give applications one stable endpoint. libpq connection strings can list several hosts and request a read-write session, and a pooler or proxy can follow the primary. Confirm each application retries after a dropped connection.
Back up for point-in-time recovery
Base backups plus continuous WAL archiving let you restore a cluster to a chosen moment, such as just before a faulty migration ran. pgBackRest and Barman manage base backups, archiving, retention, compression and parallel restore. Keep the repository outside the cluster's failure domain and encrypt it.
Replicas are a different protection: a dropped table reaches every standby within seconds. Logical dumps from pg_dump help when moving one database or restoring one table, but they cannot replay to a point in time. Most production clusters need physical backups and an occasional logical export.
A backup is proven only by a restore. On a schedule, restore to a chosen timestamp in an isolated environment, start the dependent application against it, and record the duration and any manual steps.
Harden authentication, roles and extensions
Control who connects, from where and how, in pg_hba.conf. Require TLS on network connections and use SCRAM for passwords. PostgreSQL 18 deprecates md5 password authentication, so move remaining md5 users to SCRAM before a future release removes it.
For people, PostgreSQL can authenticate against a directory through LDAP or GSSAPI (Kerberos, including Active Directory). PostgreSQL 18 adds OAuth 2.0 authentication through a validator module, which opens a path to an identity provider such as Keycloak. Give each application its own least-privilege role, keep superuser access for named administrators, and load credentials from a secrets manager.
Keep an approved extension list, such as pgvector for vector search or pgAudit for audit logging, and confirm each is available for the target version before an upgrade. Send connection and privileged-change logs to central storage and review role grants on a schedule.
Version policy and upgrade methods
Each major version is supported for five years, with minor releases at least every three months. PostgreSQL 14 reaches end of life in November 2026, so clusters still on it need an upgrade plan now.
| Change | Method | Plan for |
|---|---|---|
| Minor release | Install new binaries and restart, one node at a time in a replicated cluster. | Release notes; some minor releases ask for extra steps such as reindexing. |
| Major version in place | pg_upgrade migrates the data directory, quickly in link mode. PostgreSQL 18 can keep planner statistics across the upgrade. | Extension availability, a fresh base backup afterward, and the PostgreSQL 18 checksum default when the old cluster has checksums off. |
| Major version with replication | Logical replication feeds a new cluster on the newer version, then clients switch over. | Schema changes and sequence values need separate handling, and tables without a primary key need a replica identity. |
| Dump and restore | pg_dump or pg_dumpall into a new cluster. | Downtime grows with database size; suits small databases or a deliberate cleanup. |
PostgreSQL operating ownership
The database platform team runs the clusters; application teams own their schemas, queries and data.
Database platform
- Monitor replication lag, WAL archiving, disk growth and failover state.
- Apply minor releases and plan major upgrades ahead of end of life.
- Tune pooling, memory and autovacuum with evidence.
Application owners
- Review schema migrations and slow queries before release.
- Stay within agreed connection limits.
- Request extensions through review.
Security
- Review roles, pg_hba.conf changes and superuser access.
- Rotate application credentials and certificates.
- Protect backup encryption keys and audit logs.
Recovery
- Run scheduled point-in-time restores and failover drills.
- Keep runbooks for switchover, restore and replica rebuild.
- Record measured recovery time per dependent application.
PostgreSQL deployment questions
Should several applications share one cluster?
Sharing means fewer clusters to run but ties maintenance windows, upgrades and failure domains together. Separate clusters suit applications with different recovery targets, owners or extensions. A shared cluster still needs one database and role per application.
Do we need PgBouncer?
A pooler helps when applications open many short-lived or idle connections. Check that the pooling mode suits each application, since transaction pooling changes how session state and some prepared statements behave.
Can PostgreSQL run on Kubernetes?
Yes, with an operator such as CloudNativePG and storage that behaves predictably under load. It suits teams that already run stateful workloads on Kubernetes. Virtual machines with Patroni remain a sound choice for others.
How do we move an existing database onto the new cluster?
Within the same major version, a physical replica or a restore from backup is simplest. Across versions or from a managed cloud database, use logical replication or dump and restore. Compare extensions, roles, collations and settings first.
Scope and handover
DeployOpen plans the topology with you, deploys PostgreSQL with the chosen high-availability tooling, configures pooling, TLS and authentication, sets up backups and runs a restore test, carries out upgrades, documents runbooks and provides ongoing support if you want it. Effort depends on the number of clusters and dependent applications, environments, recovery targets, network restrictions and how much data has to move.
Handover includes the cluster inventory with owners, failover design, pg_hba and role model, approved extensions, backup schedule and retention, the latest restore and failover results, alert routes, and the upgrade plan with end-of-life dates.
