MHOSTMHOST

PostgreSQL: install and configure

In this article you'll install PostgreSQL on your mHost VPS, enable it as a systemd service, create a dedicated role and database, learn the basic psql commands, and configure access to the server — local and, if needed, remote — via pg_hba.conf, listen_addresses, and the firewall.

What you'll need

  • An mHost VPS running Ubuntu 22.04/24.04, Debian 11/12, or AlmaLinux/Rocky Linux 8/9.
  • SSH access as root or a user with sudo.
  • The IP address of the computer or server you'll connect from remotely — only needed if you want access to the database from outside this VPS (the "Remote access" section in step 5).
Tip: the commands below assume you're root — under a regular sudo account, prefix each one with sudo. The exception is sudo -u postgres psql in step 3: there, sudo -u switches to a different system user (postgres), not privilege escalation, and you need that exact command even if you're already root.

Step 1. Install PostgreSQL

Ubuntu / Debian

bash
apt update
apt install postgresql

apt installs the version pinned to your distro release — for example, PostgreSQL 14 on Ubuntu 22.04, 16 on Ubuntu 24.04, 13 on Debian 11, 15 on Debian 12. That's enough for most needs.

Check the installation:

bash
psql --version
Tip: to see the version, port, and status of the actual server cluster (not just the psql client), use pg_lsclusters — it's available only on Ubuntu/Debian, where database clusters are managed by the postgresql-common package.

If you need a specific, newer version than your distro's repository offers, add the official PGDG apt repository:

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

The script shows what it's about to do and asks for confirmation — accept it. Then install the version you need:

bash
apt update
apt install postgresql-16

Replace 16 with the version you need.

AlmaLinux / Rocky Linux

bash
dnf install postgresql-server

The AppStream version often lags behind current PostgreSQL releases. To see which version is the default (and which others are available), run:

bash
dnf module list postgresql

For a specific, newer version, add the official PGDG repository instead of using AppStream (example for AlmaLinux/Rocky Linux 9 — for version 8, replace EL-9 with EL-8 in the URL):

bash
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf install -y postgresql16-server

Replace 16 with the version you need in the package name and in every path and service name later in this article.

Important: the dnf -qy module disable postgresql command is required — without it, dnf may silently install the version from the built-in AppStream module instead of the one you specified.

Step 2. Enable and start PostgreSQL via systemd

AlmaLinux / Rocky Linux: initialize the cluster first

Unlike Debian/Ubuntu, the RPM package doesn't create a database cluster automatically — you need to initialize the data directory first.

For the AppStream package (postgresql-server):

bash
postgresql-setup --initdb
systemctl enable --now postgresql

For a versioned PGDG package (replace 16 with your version):

bash
/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable --now postgresql-16

Ubuntu / Debian

The package already creates a cluster (named main by default) and enables and starts its systemd service during installation, so this step usually just confirms the state:

bash
systemctl enable --now postgresql
Tip: a specific cluster on Ubuntu/Debian is managed by its own service named like postgresql@16-main (version and cluster name joined with @) — the generic postgresql service enables and restarts all clusters on the server at once, and there's usually just one.

Check that the server is accepting connections:

bash
pg_isready

The expected output ends with accepting connections.

Done: the PostgreSQL systemd service is enabled and running.

Step 3. Connect and create a role and a database

On install, PostgreSQL creates a system user named postgres and a matching superuser role inside the database. Local connections use peer authentication by default: it matches your OS system user against the PostgreSQL role name, so you can only log in as the postgres system user — and without a password:

bash
sudo -u postgres psql

You'll land in a psql prompt connected to the postgres maintenance database as the postgres role. You shouldn't use the superuser for your application's day-to-day work — create a dedicated role and a database owned by it instead:

sql
CREATE ROLE myuser WITH LOGIN PASSWORD 'change_me';
CREATE DATABASE mydb OWNER myuser;

Replace myuser and mydb with your own names, and change_me with a strong password of your own — this one is a placeholder.

Tip: CREATE USER myuser PASSWORD 'change_me'; is an equivalent shorthand — CREATE USER differs from CREATE ROLE only in that it implies the LOGIN attribute by default.
Tip: starting with PostgreSQL 15, CREATE privilege on the public schema is granted by default only to the database's owner (via the built-in pg_database_owner role) — other roles need it granted explicitly: GRANT CREATE ON SCHEMA public TO other_role;. Since myuser above is the owner of mydb, this restriction doesn't affect it.

Exit the session:

\q

Step 4. psql basics

The general syntax for invoking psql from the shell:

psql [option...] [dbname [username]]

The main flags: -U — role, -d — database, -h — host (without it, psql connects over a Unix socket instead of TCP), -p — port (5432 by default). The sudo -u postgres psql command from step 3 is equivalent to sudo -u postgres psql -U postgres -d postgres.

Inside psql, alongside regular SQL, meta-commands are available — they all start with a backslash. Connect as postgres and try the basics:

bash
sudo -u postgres psql
\l
\du

\l lists databases — mydb should be among them; \du lists roles — myuser and postgres itself should be there (with superuser privileges marked for the latter).

Switch to the database you created and list its tables:

\c mydb
\dt

A fresh database has no tables yet, so \dt will reply Did not find any relations. — that's expected.

A few more useful meta-commands:

  • \x — toggle expanded (vertical) output, handy for wide rows with long values;
  • \password myuser — set or change a role's password: it's prompted for interactively, twice, and never shows up in plaintext in your command history, unlike PASSWORD '...' inside CREATE ROLE/ALTER ROLE;
  • \conninfo — show which database, user, host, and port you're currently connected to;
  • \i file.sql — run SQL commands from a file;
  • \? — list all meta-commands, \h COMMAND_NAME — syntax for a specific SQL command, e.g. \h CREATE ROLE.

End the session:

\q

Logging in as myuser with a password (psql -U myuser -d mydb -h 127.0.0.1) will work once you've configured access in the next step.

Step 5. Configure access: pg_hba.conf and listen_addresses

Two settings control who can connect to the server and how: listen_addresses in postgresql.conf — which network interfaces the server listens on at all — and pg_hba.conf — which addresses, databases, roles, and authentication methods are allowed to connect. By default, a fresh server only listens on localhost — that's enough if your application runs on this same VPS. You only need to change both files if you need access from outside.

Where the files live depends on the distro and install method:

  • Ubuntu/Debian: /etc/postgresql/16/main/postgresql.conf and /etc/postgresql/16/main/pg_hba.conf (the number is the PostgreSQL version; the postgresql-common package keeps configuration separate from the data directory).
  • AlmaLinux/Rocky, AppStream package: /var/lib/pgsql/data/postgresql.conf and /var/lib/pgsql/data/pg_hba.conf.
  • AlmaLinux/Rocky, PGDG package: /var/lib/pgsql/16/data/postgresql.conf and /var/lib/pgsql/16/data/pg_hba.conf.

Local access (within this VPS)

Important: on AlmaLinux/Rocky, the pg_hba.conf that postgresql-setup --initdb generates requires the ident method for TCP connections by default (the lines for 127.0.0.1/32 and ::1/128) — it only works with a separate ident server, which your VPS doesn't have, so password logins over -h 127.0.0.1 won't work until you change these lines. On Ubuntu/Debian, scram-sha-256 is already the default there — nothing to change.

On AlmaLinux/Rocky, open pg_hba.conf, find the lines for 127.0.0.1/32 and ::1/128, and change ident to scram-sha-256 so they read:

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

Apply the change:

bash
systemctl reload postgresql

Test logging in as your new role, with a password, over TCP:

bash
psql -U myuser -d mydb -h 127.0.0.1 -W

-h forces psql to connect over TCP — through the host line in pg_hba.conf — rather than the Unix socket (the local line, which still uses peer), which is why this, and not sudo -u postgres psql, is how you test password login. -W guarantees a password prompt.

Done: psql asked for Password for user myuser:, and after entering change_me (or the password you set) it let you into the mydb database.

Remote access

If your application, or you yourself, will connect from somewhere other than this VPS — another server or your own computer — two more changes are needed.

Let PostgreSQL listen on external interfaces — in postgresql.conf, change:

ini
listen_addresses = 'localhost'

to:

ini
listen_addresses = '*'

(or set a specific IP address of the VPS instead of * if it has more than one interface). listen_addresses only takes effect at server start — reload won't apply it, you need a full restart:

bash
systemctl restart postgresql

Add a dedicated line to pg_hba.conf for the address you'll connect from:

host    mydb            myuser          203.0.113.10/32         scram-sha-256

Replace 203.0.113.10/32 with the client's real IP address (or subnet).

Important: don't use 0.0.0.0/0 ("from anywhere") unless you genuinely need to — a PostgreSQL server reachable from the whole internet without an IP restriction quickly becomes a target for password brute-forcing. Narrow the line down to the client's specific IP or subnet.

Reload the configuration:

bash
systemctl reload postgresql

Unlike listen_addresses, pg_hba.conf only needs a reload — no full restart required.

Step 6. Open port 5432 in the firewall — only if you need remote access

If all access to the database is local (the application runs on this same VPS and connects over the Unix socket or 127.0.0.1), you don't need to open the port at all — PostgreSQL is already unreachable from outside as long as pg_hba.conf has no lines for external addresses from the "Remote access" section above. Only open the port if you just set up remote access.

Ubuntu / Debian (ufw)

If ufw isn't set up on the server yet, go through the "Setting up the UFW firewall" article first — it installs ufw, allows SSH, and enables the firewall. If ufw is already running, allow access to the port only from the client IP address from the previous step:

bash
ufw allow from 203.0.113.10 to any port 5432 proto tcp
Tip: if you need access from several known addresses, repeat the command for each one — rules add up rather than overwrite each other. Opening the port for everyone (ufw allow 5432/tcp) only makes sense if clients connect from unpredictable addresses and access is already restricted at the pg_hba.conf and password level.

AlmaLinux / Rocky (firewalld)

bash
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
Tip: to restrict access to a specific IP address in firewalld, use a rich rule: firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="203.0.113.10/32" port protocol="tcp" port="5432" accept', then the same firewall-cmd --reload.

What's next

A database without backups is eventually lost along with the VPS — set up regular automated backups, see the "Database backups" article.

Related articles

NextMySQL / MariaDB: install and configure