Install PostgreSQL and create your first database

PostgreSQL is a powerful open-source database used by Django, Rails, many Node.js apps and countless SaaS products. This guide gets a working database for one application.

1. Install

Ubuntu and Debian install and start it in one step (PostgreSQL 16 on Ubuntu 24.04, 14 on 22.04, 15 on Debian 12):

sudo apt install -y postgresql

AlmaLinux and Rocky Linux need the data folder created first:

sudo dnf install -y postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Newer major versions are available on AlmaLinux and Rocky as module streams (dnf module list postgresql) or from the official PostgreSQL repository.

2. Open the psql shell

PostgreSQL creates a postgres system user that owns the server:

sudo -u postgres psql

Useful commands inside psql: \l lists databases, \du lists roles, \dt lists tables, \q quits.

3. Create a role and a database

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

Then test a password login over TCP:

psql -h 127.0.0.1 -U appuser appdb

4. Fix password logins on AlmaLinux and Rocky

If step 3 fails with Ident authentication failed, the default rules expect ident, not passwords. Edit /var/lib/pgsql/data/pg_hba.conf and change the method on the host lines for 127.0.0.1/32 and ::1/128 to scram-sha-256:

host    all    all    127.0.0.1/32    scram-sha-256
host    all    all    ::1/128         scram-sha-256

Reload with sudo systemctl reload postgresql. Ubuntu and Debian already accept passwords on these addresses.

Connect from your computer

The safest way is an SSH tunnel; port 5432 stays closed to the internet:

ssh -N -L 5433:127.0.0.1:5432 alex@203.0.113.25

Then connect your client (pgAdmin, DBeaver, psql) to 127.0.0.1 port 5433.

Back up and restore

sudo -u postgres pg_dump -Fc appdb > appdb-$(date +%F).dump
sudo -u postgres pg_restore --clean --if-exists -d appdb < appdb-2026-01-31.dump

The custom format (-Fc) is compressed and lets you restore single tables.

Troubleshooting

• Where is the config? Ask the server: sudo -u postgres psql -c 'SHOW config_file;' and SHOW hba_file;.
• "role does not exist": you ran psql as a Linux user with no matching role. Use -U appuser or sudo -u postgres.
• Service fails after an edit: sudo journalctl -u postgresql -n 50 --no-pager. On Ubuntu/Debian the unit is versioned, for example postgresql@16-main.

Next steps

• Deploy a Python app with Gunicorn
• Back up your Linux VPS