Why self-host PostgreSQL on a VPS
Managed databases (RDS, Cloud SQL) bill per GB stored, per IOPS and per connection, and often limit the available extensions. By deploying PostgreSQL on your own VPS, you enable pg_stat_statements, PostGIS, pgvector or TimescaleDB without restriction, you choose your exact version (15, 16, 17) and you keep your data in a jurisdiction you control. A VPS with an NVMe SSD disk and a dedicated CPU 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.
The concrete benefits of a self-hosted PostgreSQL
- Fixed and predictable cost: no billing per GB, per IOPS or per number of active connections.
- Free extensions:
pgvectorfor AI,PostGISfor geospatial,TimescaleDBfor time series. - Full control of backups: scheduled
pg_dump,pg_basebackupor physical replication to a second VPS. - Fine tuning of
postgresql.conf(shared_buffers, work_mem, WAL) impossible with most managed offerings. - Data sovereignty: your data stays on an infrastructure you choose.
- Real superuser access to audit, profile and optimize without a support ticket.
Hardware and software prerequisites
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.
Deploy PostgreSQL with Docker in production
Prepare the VPS and the firewall
Connect via SSH, update the system with apt update && apt upgrade -y, install Docker via curl -fsSL https://get.docker.com | sh. Block port 5432 from the outside with ufw deny 5432: the database must never be directly exposed to the Internet.
Write the docker-compose.yml
Create a postgres:17 service, mount a named volume on /var/lib/postgresql/data, and define POSTGRES_PASSWORD, POSTGRES_DB and POSTGRES_USER via a .env file that is never committed. Add shm_size: 256mb for large sorts and a healthcheck with pg_isready.
Launch and initialize the database
Start with docker compose up -d, check the logs via docker compose logs -f postgres, then connect with docker compose exec postgres psql -U app -d mabase. Create your application roles with minimal privileges rather than using the superuser.
Expose access via a tunnel or a TLS proxy
For secure remote access, never open 5432 in the clear: use an SSH tunnel (ssh -L 5432:localhost:5432) or place pgbouncer behind a reverse proxy. Enable SSL on the PostgreSQL side with a Let's Encrypt certificate mounted in the container (ssl = on in postgresql.conf).
Tune the configuration
Adjust shared_buffers to about 25% of the RAM, effective_cache_size to 50-75%, and work_mem according to your queries. Enable pg_stat_statements to identify slow queries. Use the PGTune tool as a starting point.
Automate the backups
Schedule a daily pg_dump via a cron task on the host, compress into custom .dump format, and copy the archives to external storage. Regularly test restoration with pg_restore: an untested backup does not exist.
For high availability, deploy a second VPS in physical streaming replication (primary_conninfo + replication slot). You get a warm standby ready to fail over, and you can offload your heavy read queries to it (hot_standby = on) to relieve the primary. Combined with pgBackRest for differential incremental backups, you reach an RPO of a few minutes without depending on a managed service.
The official documentation
For advanced configuration, tool-specific options and version changes, refer to the official PostgreSQL documentation. This guide covers going live on a ServOrbit VPS; the vendor documentation remains the reference for fine tuning.
