PostgreSQL on a VPS: reliable, controlled database

Managed databases (RDS, Cloud SQL) bill per GB stored, per IOPS and per connection, and often limit available extensions. By deploying PostgreSQL on your own VPS, you enable pg_stat_statements, PostGIS, pgvector or TimescaleDB without restriction, choose your exact version (15, 16, 17) and keep your data in a jurisdiction you control. A VPS with an SSD disk offers very low I/O latencies, ideal for the transactional workloads of a production application. You pay a fixed and predictable flat rate, and you alone decide on your strategy for replication, encryption and backup retention.

For a development PostgreSQL instance or a small production project, plan for a VPS with 2 vCPU, 4 GB of RAM and 40 GB of NVMe SSD: PostgreSQL likes memory for its page cache. For a heavier load (several hundred connections or datasets of several tens of GB), aim for 4 vCPU and 8 GB of RAM. On the software side: Ubuntu 22.04 or 24.04 LTS, Docker Engine and the Docker Compose v2 plugin, a domain name pointing to the VPS (e.g. db.mydomain.com) if you expose remote access, and a ufw firewall configured to let through only the strict minimum. Also reserve a dedicated Docker volume for PGDATA to decouple the data from the container.

An untested backup is a backup that does not exist. Three complementary approaches cover different needs.

pg_dump + cron is the starting point: simple to set up, it produces a logical SQL file restorable on any compatible version. Schedule it at 3 AM, compress with --format=custom (compressed binary format, selective restore by table), and copy the archive to external storage (S3-compatible object, another VPS) with rclone or scp. Keep 7 days locally and 30 days remotely.

pgBackRest goes further: it supports incremental and differential backups, parallel compression and point-in-time restore (PITR) from WAL archives. It is the recommended solution once your database exceeds a few GB or you need to rewind to a precise minute after an accidental DELETE. It is configured via a pgbackrest.conf file and integrates with a cron job or systemd timer.

Monthly restore test: regularly run pg_restore -d test_restore <dump> on a test VPS to verify archive integrity. A weekly script that restores the latest backup and checks a table count or a control query is enough to catch silent corruption before it becomes critical.

PostgreSQL high availability on a VPS does not necessarily require Patroni or a complex Pacemaker cluster. Native streaming replication covers most needs with minimal configuration.

Manual streaming replication: on the primary VPS, enable wal_level = replica, add a dedicated replicator user in pg_hba.conf and set max_wal_senders = 3. On the secondary (standby) VPS, run pg_basebackup to initialize a physical copy, create a standby.signal file and configure primary_conninfo in postgresql.conf. Replication starts automatically on restart. With hot_standby = on, the standby accepts read-only queries — perfect for offloading reports and monitoring.

Patroni adds automatic failover: it monitors the primary via a key in etcd, Consul or ZooKeeper, and automatically promotes the standby if the primary becomes unreachable for more than 30 seconds. On two VPS, a third-party etcd (lightweight VM or managed service) for consensus is sufficient. Patroni is the recommended path when you cannot tolerate a 5-to-15-minute manual intervention during a failure.

Replication lag monitoring: SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS lag_bytes FROM pg_stat_replication on the primary gives you the real-time lag in bytes. A lag above a few MB indicates network saturation or an overloaded standby.

OOM killer kills PostgreSQL: the OOM signal in /var/log/syslog (oom_kill_process) means memory is exhausted. Main causes: shared_buffers set too high, work_mem miscalibrated against connection count, or a concurrent process (Elasticsearch, Node.js) consuming all swap. Diagnosis: cat /proc/meminfo | grep -E 'MemFree|Cached|Buffers' and ps aux --sort=-%mem | head -10. Fix: reduce work_mem, enable huge_pages = try in postgresql.conf, or upgrade the VPS.

Connections exhausted (FATAL: sorry, too many clients already): max_connections is reached. Short term: SELECT count(*), state FROM pg_stat_activity GROUP BY state to identify idle connections. Long term: deploy pgbouncer in transaction mode (pool_mode = transaction) in front of PostgreSQL — it multiplexes hundreds of application connections onto a pool of 20-50 real connections.

Autovacuum saturated: tables with many UPDATE or DELETE operations accumulate dead tuples that bloat tables and slow down scans. SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10 reveals the problematic tables. Speed up autovacuum by reducing autovacuum_vacuum_scale_factor (e.g. 0.01 instead of 0.2) for critical tables via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01).

Checkpoints too frequent (LOG: checkpoint occurring too frequently): WAL fills faster than checkpoint_timeout (default 5 min). Increase max_wal_size (e.g. 2-4 GB) and checkpoint_timeout to 15-30 min to space out checkpoints without risking excessive data loss on a crash.