How to Install PostgreSQL on a VPS

PostgreSQL is a powerful, standards-compliant open-source database loved for its reliability and rich feature set — JSON support, full-text search, and rock-solid transactions. This guide installs PostgreSQL on Ubuntu 24.04, explains its role system, and creates a database and application user correctly.

Install PostgreSQL

The version in Ubuntu's default repos is recent and well-supported:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

The postgresql-contrib package adds useful extensions like uuid-ossp and pg_stat_statements. The service starts automatically:

systemctl status postgresql

Understand roles and the postgres user

PostgreSQL uses roles for both users and groups, and by default authenticates local connections via "peer" auth — matching your Linux username to a Postgres role. Installation creates a postgres superuser role and a matching postgres system account. To get an admin prompt:

sudo -u postgres psql

You are now at the postgres=# prompt as the superuser. This peer-auth default is secure: no password is exposed, and only the system postgres user can become the database superuser.

Create a database and application role

From the psql prompt, create a role with a password and a database it owns:

CREATE ROLE appuser WITH LOGIN PASSWORD 'a-strong-password-here';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

Making appuser the owner means it can create tables and manage its own schema without superuser help — exactly what an app needs. \q quits psql.

Connect as the app user

To let appuser log in with a password over TCP, connect explicitly to localhost:

psql -h 127.0.0.1 -U appuser -d appdb

If this is rejected, PostgreSQL's client auth rules in pg_hba.conf need a line allowing password (scram-sha-256) auth for local TCP. On Ubuntu this usually works out of the box for 127.0.0.1; if not, edit /etc/postgresql/16/main/pg_hba.conf, ensure a line like host all all 127.0.0.1/32 scram-sha-256, and reload:

sudo systemctl reload postgresql

Keep it private

By default PostgreSQL listens only on localhost — verify with:

sudo ss -ltnp | grep 5432

For an app on the same VPS, leave it that way. To allow remote connections, set listen_addresses = '*' in postgresql.conf, add a scoped pg_hba.conf entry, restrict port 5432 in UFW, and use TLS. An SSH tunnel is safer than opening the port outright.

Backups

Back up a single database with pg_dump:

pg_dump -U appuser -h 127.0.0.1 appdb > appdb-$(date +%F).sql

Schedule it nightly with a cron job.

Enable useful extensions

PostgreSQL's power grows with extensions. Two you will likely want — UUID generation and query statistics — enable per-database from psql:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The first lets you generate UUID primary keys with uuid_generate_v4(); the second records execution stats for every query so you can find the slow ones later.

Tune for a small VPS

Postgres defaults are conservative. On a modest server a couple of settings in postgresql.conf help — set shared_buffers to about a quarter of available RAM and give queries a little more working memory:

shared_buffers = 256MB
work_mem = 16MB

Restart with sudo systemctl restart postgresql after changing shared_buffers, which needs a full restart. Do not over-allocate work_mem — it is per-operation, so many concurrent queries multiply it. Modest, deliberate values keep Postgres quick without pushing the box into swap.

Useful psql commands

• List databases: \l
• Connect to a database: \c appdb
• List tables: \dt
• List roles: \du
• Quit: \q

FAQ

PostgreSQL or MySQL — which should I pick?

PostgreSQL offers stricter SQL compliance, advanced types (JSONB, arrays, ranges), and powerful indexing; MySQL is more common in PHP/WordPress hosting and slightly simpler to start with. For new app development, many teams prefer Postgres. Compare with our MySQL install guide.

What is peer authentication?

For local socket connections, PostgreSQL matches your operating-system username to a database role of the same name — no password needed. That is why sudo -u postgres psql just works. Password auth applies to TCP connections.

How do I reset a role's password?

Connect as superuser (sudo -u postgres psql) and run ALTER ROLE appuser WITH PASSWORD 'new-password';.

Which config files matter?

postgresql.conf controls server settings like listen_addresses, and pg_hba.conf controls who can connect and how. Both live under /etc/postgresql/<version>/main/ on Ubuntu. Reload after edits with sudo systemctl reload postgresql.

Nxeon VPS hosting for developers gives you full root and fast NVMe storage to run PostgreSQL right next to your app — with free migration help to move an existing database over.