Install PostgreSQL on a Debian 12 VPS

Why install PostgreSQL on a Debian 12 VPS?

You have just provisioned a fresh Debian 12 VPS, and your application needs a reliable relational database. Reaching for the default SQLite might work for a prototype, but the moment you need concurrent writes, geospatial data, or replication, you want PostgreSQL. It is battle-tested, standards-compliant, and gets frequent security patches straight from the official apt repository. This guide walks you through a production-grade install on Debian 12, from adding the upstream repo to enabling remote access over a firewalled IPv4, the kind of setup you would run on a Vietnam VPS with an NVMe drive.

Prerequisites

• A Debian 12 VPS with a non-root sudo user (root works but is not recommended for daily use).
• An active IPv4 address (your VPS provider assigns one). If you need a dedicated Vietnam IPv4, rent VPS from a provider that offers rDNS as well.
• Basic familiarity with the command line: editing files with nano or vim, running commands as root via sudo.
• Ufw or nftables for the firewall (we use ufw in this guide for simplicity).

Step 1, Add the official PostgreSQL 16 repository

Debian 12 ships with PostgreSQL 15 in its default repos. You can use that, but we want the latest minor version (16.4 as of writing) for better performance and newer features like pg_stat_statements with query pipelining. The official PostgreSQL apt repository gives you exactly that without waiting for Debian backports.

# Import the signing key
sudo apt update
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Add the repository
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Update and install
sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

Verify: Check that the service started and the version matches.

sudo systemctl status postgresql
psql --version

Expect psql (PostgreSQL) 16.4 and a green active (exited) status. The service shows "exited" because PostgreSQL uses pg_ctl internally, the postgresql.service unit simply launches pg_ctlcluster which manages the cluster lifecycle.

Step 2, Configure authentication and security defaults

Out of the box, PostgreSQL only listens on 127.0.0.1 and uses peer authentication for local connections. That is safe for a single-user setup, but we want to tighten it further: force scram-sha-256 (not the older md5), and set a strong password for the postgres superuser. We also enable password_encryption in postgresql.conf so newly created roles automatically get the best hashing algorithm.

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

Find and change these lines:

password_encryption = scram-sha-256
listen_addresses = 'localhost'   # keep localhost for now; change later only if remote access is needed

Save and reload the configuration:

sudo systemctl reload postgresql

Now set a strong password for the postgres user. Connect to the local instance as the OS postgres user:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'YourStrong!Passw0rd';"

Replace YourStrong!Passw0rd with a long random string, store it in a password manager. This password is needed later if you enable remote logins or use connection pooling.

Step 3, Tune autovacuum and memory for a VPS

A default PostgreSQL install is tuned for a desktop workstation, not a 2, 8 GB RAM VPS. The two biggest knobs are shared_buffers (set to 25% of RAM) and autovacuum_vacuum_scale_factor (lower it so vacuum runs more frequently on small tables). The defaults for autovacuum_vacuum_scale_factor (0.2) means vacuum only triggers when 20% of a table changes, fine for huge databases, but on a small VPS you want it to act sooner to prevent bloat.

sudo nano /etc/postgresql/16/main/postgresql.conf

Assuming a 2 GB RAM VPS (adjust accordingly):

shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_threshold = 50
autovacuum_naptime = 1min

For a 4 GB VPS double the shared_buffers to 1024MB and effective_cache_size to 3072MB. On a cheap VPS pricing plan with 1 GB RAM, set shared_buffers = 256MB and effective_cache_size = 768MB.

sudo systemctl restart postgresql

Verify that the new values took effect:

sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW autovacuum_vacuum_scale_factor;"

Expected output: 512MB and 0.01 respectively.

Step 4, Open the firewall (if remote clients need access)

By default, ufw blocks all incoming traffic. If you plan to connect to PostgreSQL from another server (e.g., your application backend), you need to allow port 5432 through the firewall. The safest approach is to whitelist only the application server's IP address, never open the port to 0.0.0.0/0.

Enable ufw first if it is not already active:

sudo ufw allow OpenSSH
sudo ufw enable

Then allow PostgreSQL from a specific source IP (replace 203.0.113.5 with your app server's IP):

sudo ufw allow from 203.0.113.5 to any port 5432 proto tcp

If you only need local access (both database and app on the same VPS), skip this step entirely. For a production Linux VPS, never expose PostgreSQL directly to the internet, use an SSH tunnel or a WireGuard VPN instead.

Step 5, Enable remote TCP connections (with caution)

Editing postgresql.conf alone is not enough; you also need to modify pg_hba.conf to allow authentication over TCP. This is the step where many beginners accidentally expose their database to the world.

sudo nano /etc/postgresql/16/main/postgresql.conf

Change the listen_addresses line from localhost to '*' (or a specific IP if your VPS has multiple addresses):

listen_addresses = '*'

Now edit the client authentication file:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add a line before the host entries, at the end of the file, for your trusted subnet. Replace 10.0.0.0/24 with your actual network range:

host    all             all             10.0.0.0/24            scram-sha-256

Save and reload PostgreSQL:

sudo systemctl reload postgresql

Verify from a remote machine (with the PostgreSQL client installed):

psql -h your-vps-ip -U postgres -d postgres -c "SELECT version();"

You should see the PostgreSQL version. If connection times out, double-check the ufw rule and the listen_addresses value.

Troubleshooting

Problem: "could not connect to server: Connection refused"
Check that PostgreSQL is running: sudo systemctl status postgresql. Also confirm listen_addresses is not stuck on localhost, run sudo ss -tlnp | grep 5432. If the output shows only 127.0.0.1:5432, you forgot step 5.

Problem: "FATAL: no pg_hba.conf entry for host"
Your pg_hba.conf does not have a matching host line for the client's IP. Add one using the correct CIDR notation (e.g., 192.168.1.0/24) and reload.

Problem: Autovacuum never runs or runs too often
Check pg_stat_user_tables with sudo -u postgres psql -c "SELECT schemaname,relname,n_dead_tup,last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 0;". If the vacuum threshold is too low and you see constant activity, increase autovacuum_vacuum_threshold to 100 or raise autovacuum_vacuum_scale_factor to 0.02.

FAQ

Related articles

• Install PostgreSQL on a Debian 12 VPS
• Configure swap and optimize memory on a low RAM VPS
• Reverse proxy with automatic HTTPS using Caddy
• Security audit and hardening with Lynis on a VPS