PostgreSQL on a cloud server

PostgreSQL is in the repositories of all our operating systems. Below you will find installation, a database for your application, access from outside and backups.

Installation

Ubuntu and Debian — the service starts straight away:

sudo apt install postgresql

On Ubuntu 26.04 this installs PostgreSQL 18, and on Debian 13 it installs PostgreSQL 17.

AlmaLinux — after installation you need to initialise the database and enable the service:

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

On AlmaLinux 10 this is PostgreSQL 16. On AlmaLinux 9 the default is PostgreSQL 13 — for new projects, choose a newer version: before installing, run sudo dnf module enable postgresql:16.

Database and user

The PostgreSQL administrator is the system user postgres. To open the database console:

sudo -u postgres psql

Create a user and a database for your application — run these commands in the psql console:

CREATE USER app WITH PASSWORD 'a-strong-password';
CREATE DATABASE app OWNER app;

To leave psql, type \q. Your application connects to localhost, port 5432, with this username and password. To test the login: psql -h localhost -U app app.

Where the settings are

After editing pg_hba.conf, sudo systemctl reload postgresql is enough; after editing postgresql.conf you usually need a restart.

Access from another server

First, ask yourself whether you need it. If you are the only one connecting to the database — with a program on your computer — you don’t need to open the port; an SSH tunnel is enough: ssh -L 5432:127.0.0.1:5432 ubuntu@203.0.113.10.

If an application on another server needs to reach the database:

1. In postgresql.conf, listen on all addresses: listen_addresses = '*'.
2. At the end of pg_hba.conf, allow one address to log in, with a password only: host app app 198.51.100.7/32 scram-sha-256.
3. Restart the database: sudo systemctl restart postgresql.
4. In the firewall, open port 5432 for that address only — How to open a port.

Backups

A backup of one database in compressed format:

sudo -u postgres pg_dump -Fc app > app-$(date +%F).dump

To restore into an empty database:

sudo -u postgres createdb -O app app_restored
sudo -u postgres pg_restore -d app_restored app-2026-10-12.dump

All databases and users at once — sudo -u postgres pg_dumpall > all.sql; to restore — sudo -u postgres psql -f all.sql.

To run backups on a schedule, use cron. Keep copies somewhere other than this server: a server snapshot and a copy next to the database will disappear along with the server.

Moving from another server

Dump the database on the old server (pg_dump -Fc), copy the file across — for example, with scp — then create the user and an empty database on the new server and restore with pg_restore. A dump from an older version of PostgreSQL can be restored into a newer one.