Running PostgreSQL on a Cloud Server

What This Covers, and What It Assumes

Running PostgreSQL yourself on a cloud server is entirely reasonable, and for a great many workloads it is the sensible choice. It is also a real responsibility: you now own the upgrades, the backups and the 3am restart.

This guide walks the sequence a competent operator follows on a fresh Linux server, in order, with the settings that actually matter called out. It assumes a single primary serving an application, which is the shape most teams start with and stay in far longer than they expect.

If you are still weighing this against a managed service, read managed databases versus self-hosted first. The decision is mostly about whose weekend gets interrupted, not about technical capability.

Everything below refers to the current major version's behaviour. The authoritative reference throughout is the PostgreSQL documentation, which is unusually good and should be the tiebreaker against anything here.

Installing From the Right Repository

Distribution packages lag, sometimes by years, and pin you to whatever major version the release shipped with. Use the PostgreSQL project's own apt or yum repository instead, which carries every supported major version and lets you choose.

On a Debian or Ubuntu server the sequence installs the repository, then the server package for the version you want. The package creates a service, a system user, and an initial cluster with a data directory under /var/lib/postgresql.

Two choices at creation time are effectively permanent, so make them deliberately.

The first is the data directory location. If you intend to put the database on a separate volume, and you usually should, mount that volume before initialising the cluster rather than moving files later. The reasoning about which volume is in the NVMe storage article.

The second is locale and encoding. Create the cluster as UTF-8, and think about the collation, because changing it later means a dump and reload of every affected index. The C collation is faster and stable across library upgrades; a locale-aware collation sorts text the way humans expect. Pick according to whether you sort user-visible text.

Confirm what you ended up with before going further:

sudo -u postgres psql -c "\l"

The Settings Worth Changing First

The shipped configuration is deliberately conservative so that PostgreSQL starts on almost any machine. On a dedicated database server it leaves most of the hardware unused. A handful of parameters in postgresql.conf account for nearly all the difference.

The full list of what each one does, with the real units and constraints, is in the resource consumption chapter. Change a few at a time, restart or reload as required, and measure. A configuration copied wholesale from a blog post fits somebody else's hardware.

One structural point that beats all of the above: put a connection pooler such as PgBouncer between the application and the database. PostgreSQL uses a process per connection, so hundreds of idle application connections are hundreds of processes. A pooler in transaction mode turns that into a small, stable set.

Who Can Connect, and From Where

Two files control this and they work in sequence. postgresql.conf decides which addresses the server listens on. pg_hba.conf decides which connections are accepted once they arrive.

Set listen_addresses to the private interface address, never to every interface by reflex. A database with a public listener is found by scanners within hours of existing. The private network setup is covered in private networking between cloud servers.

Then write the host-based authentication rules. The file is evaluated top to bottom and the first matching line wins, which is the single most common source of confusion when a rule seems to be ignored. A minimal production set looks like this in shape: local socket connections for the postgres superuser, and one line per application granting a specific role access to a specific database from a specific network, using scram-sha-256.

Reload after editing, and verify from the application server rather than from the database host:

psql "host=10.0.1.20 dbname=appdb user=app sslmode=require"

Roles deserve the same restraint. Create one role per application with ownership of only its own schema, and keep the superuser for administration. The full syntax and the precedence rules are in the pg_hba.conf documentation, and the broader principle is the same least privilege habit you would apply to any other credential.

The Maintenance Nobody Mentions Until It Bites

PostgreSQL keeps old row versions until they are cleaned up, which is what makes its concurrency model work and what creates its characteristic operational task.

Autovacuum is not optional. It reclaims dead rows and refreshes planner statistics. On a busy table the default thresholds can be too lax, letting bloat accumulate; tuning the scale factors per table is a normal thing to do. Never disable it.

Watch transaction age. A long-running or abandoned transaction prevents cleanup across the whole database, and in the extreme the server will refuse writes to protect itself. Query pg_stat_activity for old transactions and alert on the oldest one.

Track slow queries. Enable the pg_stat_statements extension. It aggregates execution statistics per normalised query and will tell you in one query where the time goes, which guesswork never does.

Plan the major upgrade. Each major version is supported for a fixed period. pg_upgrade makes the jump quick, but it needs a rehearsal and a window. Put it in the calendar rather than discovering the deadline.

Feed the relevant numbers into whatever you already use for alerting. Connection count, replication lag, oldest transaction, disk space and cache hit ratio cover most of what you need.

Backups and the Replica Question

A dump taken with pg_dump is a fine logical backup and a poor recovery strategy on its own, because it only ever restores to the moment it ran. Anything beyond a hobby project wants a base backup plus continuous archiving of the write-ahead log, which permits recovery to a chosen second.

Tools such as pgBackRest and Barman handle the whole cycle: base backups, log archiving, retention, and a verified restore command. Set the retention policy from how much data loss you can tolerate, and store the result somewhere the database server cannot delete. The mechanics are covered in database backups and point-in-time recovery.

A streaming replica is a different thing and solves a different problem. It gives you a warm standby for failure and a place to send read-only reporting queries. It is not a backup, because a deletion replicates faithfully within seconds.

For a production single-primary setup, a replica in another availability zone plus archived logs going off-site covers both failure modes without much complexity.

Any provider that gives you a private network and separate volumes works for this. A Hetzner account or a Linode account both offer the necessary pieces, and the Hetzner and Linode comparison covers the wider trade between raw value and mature managed services.

Questions From the First Month