PostgreSQL VPS setup: sizing, tuning, security and backups

A PostgreSQL VPS is often the first piece of infrastructure a growing project moves off a managed platform or a shared app server. It gives you predictable resources, full control over configuration and extensions, and a database you can place right next to your application. It also makes you responsible for tuning, security and backups. This guide works through those responsibilities in the order you will meet them, with commands for Ubuntu that you can run on a fresh server.

PostgreSQL VPS sizing: a short checklist

Answer these questions before choosing a size. They decide more than any tuning parameter.

• Where does the application run? Put the database in the same city as the app servers. Every query pays the round trip, and an app that runs fifty queries per page multiplies it. The VPS locations guide lists all 14 Cloudj countries and their cities.
• How large is the working set? The data and indexes you touch often should fit in RAM. A database much larger than memory is fine if most of it is cold.
• Is the load steady or bursty? Databases dislike CPU contention. For steady query load, choose a dedicated CPU server, which gives you dedicated cores. Shared CPU is acceptable for development and low-traffic internal tools.
• How much data can you afford to lose? If the answer is "minutes", you need continuous WAL archiving, not a nightly dump.

A sensible start for a small production database is 2 dedicated vCPU and 4 to 8 GB RAM, with disk sized for data, indexes, WAL and growth. You can resize CPU and RAM and grow the disk later from the console, so you do not need to buy next year's server today. Check cloud server options for CPU types, and see live prices per location.

Install PostgreSQL

Ubuntu's own repository ships a supported version. If you want the newest major release, use the official PostgreSQL apt repository:

sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql

Check the installed version and cluster:

pg_lsclusters

The output shows the version number and data directory. The paths below use version 16; replace it with yours. Create a role and database for your application:

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb

Tune memory for the server size

The defaults are deliberately conservative. Edit /etc/postgresql/16/main/postgresql.conf (or drop a file into conf.d/). These starting values assume a dedicated 8 GB RAM server that runs only PostgreSQL:

shared_buffers = 2GB              # about 25% of RAM
effective_cache_size = 6GB        # about 75% of RAM; a planner hint, not an allocation
work_mem = 16MB                   # per sort or hash, per query; keep it modest
maintenance_work_mem = 512MB      # VACUUM and index builds
random_page_cost = 1.1            # SSD storage
max_connections = 100

work_mem is the setting that bites people: a single complex query can use it several times, and every connection can run such a query. If you need hundreds of client connections, put PgBouncer in front of PostgreSQL rather than raising max_connections. Restart and confirm:

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

Turn on slow-query logging so you tune from evidence:

log_min_duration_statement = 500ms

Expose the database only to your app servers

Never leave port 5432 open to the internet. Three layers work together.

First, tell PostgreSQL which interface to listen on. In postgresql.conf:

listen_addresses = 'localhost,198.51.100.5'   # this server's own IP address

Second, allow only known clients in /etc/postgresql/16/main/pg_hba.conf, with SCRAM passwords and TLS:

hostssl  appdb  appuser  203.0.113.10/32  scram-sha-256
hostssl  appdb  appuser  203.0.113.11/32  scram-sha-256

Ubuntu's package generates a self-signed certificate and sets ssl = on by default; the PostgreSQL documentation on SSL explains how to use your own.

Third, filter at the firewall so only the application servers can reach the port:

sudo ufw allow OpenSSH
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo ufw allow from 203.0.113.11 to any port 5432 proto tcp
sudo ufw enable
sudo systemctl reload postgresql

The UFW firewall guide explains rule order and how to avoid locking yourself out.

Backups: two layers, both off the server

A backup stored on the same disk as the database is a copy, not a backup. Plan for two layers.

Logical dumps with pg_dump

Simple, portable between versions, and good for small databases or extra safety before migrations:

sudo -u postgres pg_dump -Fc appdb > /var/backups/appdb-$(date +%F).dump

Restore into an empty database with pg_restore -d appdb file.dump. Schedule it with cron and copy the file off the server.

Continuous archiving with pgBackRest

For point-in-time recovery, pgBackRest takes full and incremental backups and archives WAL continuously to an S3-compatible bucket. Install it with sudo apt install -y pgbackrest and write /etc/pgbackrest.conf:

[global]
repo1-type=s3
repo1-s3-endpoint=s3.example-storage.com
repo1-s3-bucket=db-backups
repo1-s3-region=eu-central
repo1-s3-key=ACCESS_KEY
repo1-s3-key-secret=SECRET_KEY
repo1-path=/pgbackrest
repo1-retention-full=2

[main]
pg1-path=/var/lib/postgresql/16/main

Enable WAL archiving in postgresql.conf, restart, and create the first full backup:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main info

Choose a bucket in a different location from the database. Cloudj lists object storage on its site; any S3-compatible provider works with this configuration.

Test the restore

Once a month, deploy an hourly server, restore the latest backup, run a few queries against it, then delete the server. Hourly servers are billed for as long as they exist, so deleting it after the test keeps the cost to the hours you used.

Where this fits

Trading systems are a common example: a bot that records fills and positions needs its database close to the bot, not across an ocean. The guides on crypto trading bots in Singapore, forex VPS in London and forex VPS in New York show how location choices follow the workload, and the same reasoning applies to the database.

FAQ

Shared or dedicated CPU for PostgreSQL?

Dedicated for production with steady load, because query latency suffers when CPU time is contended. Shared is fine for development, staging and small internal tools.

Is a managed database better than PostgreSQL on a VPS?

A managed service handles patching, failover and backups for you. A VPS gives you full control over versions, extensions and configuration, in exchange for doing that work yourself. If nobody on the team will own backups and upgrades, a managed service is the safer choice.

How often should I run pg_dump?

Daily is a common baseline for small databases. If losing a day of data is not acceptable, add continuous WAL archiving with pgBackRest.

Can I upgrade the server size later?

Yes. You can resize CPU and RAM and grow the disk from the console. Plan a short maintenance window for the restart.

Get your database server running

Place the database next to your application, start with dedicated CPU and enough RAM for the working set, and prove your backups with a real restore. The Cloudj console shows the exact price for each size before you deploy.