Host PostgreSQL on VeloxaHost

The one-click install uses Ubuntu 24.04's own postgresql package, which is PostgreSQL 16. It sets a generated password for the postgres user, makes the server listen on all addresses, and accepts password logins (scram-sha-256) from any IPv4 address.

Install it yourself

1. Add the PostgreSQL project's own APT repository (PGDG) to get the newest major version. Skip this step if Ubuntu's PostgreSQL 16 is enough.

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

2. Install the server. PostgreSQL 18 is the newest stable major version as of this writing.

   apt update && apt install -y postgresql-18
   

3. Create a role and a database for your application. --pwprompt asks for the password.

   sudo -u postgres createuser --pwprompt app
   sudo -u postgres createdb -O app appdb
   

4. To accept remote connections, set listen_addresses = '*' in /etc/postgresql/18/main/postgresql.conf.

5. Allow your application's address, and only that, in /etc/postgresql/18/main/pg_hba.conf. Ubuntu's packages turn on SSL with a self-signed certificate, so hostssl works out of the box.

   hostssl  appdb  app  203.0.113.10/32  scram-sha-256
   

6. Make port 5432 reachable. In US East a server is reached through its block of ports on the shared IPv4 address: on the server's page, under Ports, forward one port of the block to 5432 (or use a dedicated IPv4 address, a paid add-on with limited availability). Then restart PostgreSQL and test from the client, using the shared address and the forwarded port.

   systemctl restart postgresql
   psql "host=<shared-ip> port=<forwarded-port> dbname=appdb user=app sslmode=require"
   

7. Open port 5432 only to the addresses that need it.

   ufw allow from 203.0.113.10 to any port 5432 proto tcp
   

FAQ

What licence is PostgreSQL under?

PostgreSQL is released under the PostgreSQL License, a short permissive licence similar to BSD and MIT. You can use, modify and redistribute it, including in commercial products, without paying fees or publishing your changes.

Which version does the one-click install give me, and how long is it supported?

It installs Ubuntu 24.04's package, PostgreSQL 16. The PostgreSQL project supports each major version for five years, and version 16 is supported until November 2028. Minor releases come from apt upgrade; a major upgrade, for example 16 to 18, needs the PGDG repository and pg_upgradecluster on Ubuntu.

Where is the data, and how do I connect remotely?

Data lives in /var/lib/postgresql/16/main and configuration in /etc/postgresql/16/main. The one-click install already listens on all addresses and accepts password logins from any IPv4 address, so you connect as postgres with the generated password shown on the server's page in the console. Narrow the pg_hba.conf rule and the firewall to your application's addresses before storing real data.

Are VeloxaHost snapshots enough to back up the database?

A snapshot captures the whole disk at one instant while PostgreSQL keeps running, so restoring one is like recovering after a power cut: PostgreSQL replays its write-ahead log and starts. Schedule snapshots (hourly up to weekly, kept for 1 to 30 days) and full backups (every 6 hours up to weekly, keeping 1 to 10) from the server page. Also keep pg_dump exports, because they can restore a single database or table and move between major versions.

Does PostgreSQL use the extra memory if I resize the server?

VeloxaHost changes CPU and memory of a running container without a restart, but PostgreSQL does not retune itself. Raise shared_buffers (about a quarter of RAM is the usual starting point) and effective_cache_size to match; shared_buffers takes effect only after a PostgreSQL restart.

Which VeloxaHost plan fits?

The recommended 2 vCPU, 4 GB of RAM and 40 GB of disk fit the Veloxa 2 VPS size: 2 vCPU, 4 GB RAM and 80 GB disk for $20 a month, billed hourly and capped at that price. A small development database runs on the 1 vCPU, 512 MB minimum.