Install PostgreSQL on Ubuntu: Roles, Connections and Backups

This setup takes PostgreSQL from an Ubuntu package installation to a database that an application can connect to. It also includes a restore test, so you can check the backup workflow before you need it.

Use a sudo-capable account on a fresh server. If PostgreSQL is already installed, inspect its clusters first: an Ubuntu machine can host more than one version or cluster. Installing the generic package does not mean that every cluster uses port 5432.

Install PostgreSQL and identify the cluster

sudo apt update
apt-cache policy postgresql
sudo apt install postgresql postgresql-contrib
pg_lsclusters

Note the version, cluster name, port and status. The examples below assume the intended cluster is online on port 5432; substitute its actual port otherwise. The umbrella postgresql service can show active (exited), so use cluster status and a database connection to check readiness.

sudo -u postgres psql -p 5432 -d postgres

SELECT version();
SHOW port;

The Ubuntu PostgreSQL guide explains local peer authentication: the operating-system identity is used for local connections under the matching rule. Switching to the postgres operating-system account lets you perform the initial administrative work.

Create a login role and a database it owns

Inside psql, create the role, set its password interactively and create its database:

CREATE ROLE appuser LOGIN;
SET password_encryption = 'scram-sha-256';
\password appuser
CREATE DATABASE appdb OWNER appuser;
\q

\password prompts for the new secret instead of placing a literal password in the SQL example. The role is not a superuser. Ownership allows it to manage its own database, which is useful for this initial setup. For production applications, separate schema migrations from a more restricted runtime role when practical. PostgreSQL documents the available attributes under CREATE ROLE.

Test a TCP connection to localhost, rather than a socket connection that might match a peer rule:

psql -h 127.0.0.1 -p 5432 -U appuser -W -d appdb -c "SELECT current_user, current_database();"

Find the real configuration files

sudo -u postgres psql -p 5432 -d postgres -c "SHOW config_file;"
sudo -u postgres psql -p 5432 -d postgres -c "SHOW hba_file;"

These commands avoid hard-coding a PostgreSQL version into the path. pg_hba.conf controls client authentication. Its first matching rule wins; a failed authentication attempt does not fall through to a later rule. A rule limited to the example database, role and IPv4 loopback address is:

host    appdb    appuser    127.0.0.1/32    scram-sha-256

Review existing rules before adding one. After editing the file, reload the configuration:

sudo -u postgres psql -p 5432 -d postgres -c "SELECT pg_reload_conf();"

Remote access also requires a listener on the appropriate interface and firewall access. If needed, restrict it to the application server's private address and configure TLS or a protected tunnel. Do not add an all-databases, all-users rule for the entire internet. The authentication rule reference explains rule order and address matching.

Back up the database and test a restore

From your shell, save a custom-format archive in a directory you can write to. Shell redirection creates the file as your current user:

umask 077
sudo -u postgres pg_dump -p 5432 -Fc appdb > appdb.dump

A database dump does not include cluster-wide roles. In this same-cluster test, appuser already exists; prepare ownership and roles separately when restoring elsewhere. See the pg_dump documentation for archive options.

sudo -u postgres createdb -p 5432 -O appuser appdb_restore
sudo -u postgres pg_restore -p 5432 --exit-on-error -d appdb_restore < appdb.dump

Use a new, empty appdb_restore database. Check restored objects and application queries before treating the backup as usable. pg_restore can also list an archive's contents with pg_restore --list appdb.dump.

When something does not start

Run pg_lsclusters again and inspect the log path it reports for the affected cluster. Resolve startup and connection errors before changing memory settings. Increasing work_mem indiscriminately can raise total memory use across concurrent operations; a slow query needs investigation, not the same tuning values on every server.