Install PostgreSQL and create your first database

Install PostgreSQL, create a role and database for your app, enable password logins, connect remotely without exposing the port, and take backups with pg_dump.

  • Intermediate
  • Linux VPS
  • 3 min read

By RunRacks Team · Published

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):

Terminal
sudo apt install -y postgresql

AlmaLinux and Rocky Linux need the data folder created first:

Terminal
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:

Terminal
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

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

Then test a password login over TCP:

Terminal
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:

Text
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:

Terminal
ssh -N -L 5433:127.0.0.1:5432 [email protected]

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

Back up and restore

Terminal
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

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Linux VPS

Deploy a Node.js app with PM2 behind Nginx

Install a Node.js LTS release, keep your app running with PM2 across crashes and reboots, and serve it through Nginx with HTTPS.

  • Intermediate
  • Linux VPS
  • 3 min read

Linux VPS

Create an SFTP-only user limited to one folder

Give someone file access over SFTP without a shell, locked inside one folder. Configure internal-sftp with a Match block, set the chroot ownership right and test with WinSCP.

  • Advanced
  • Linux VPS
  • 5 min read