Install PostgreSQL on Debian

PostgreSQL is a solid choice for an application that needs strict transactions, rich types or complex queries. On Debian, installation creates a ready-to-use cluster, but authentication and network listening must be understood before opening anything. This is the minimal path, from package to a tested application connection.

Install and check the cluster

sudo apt update
sudo apt install postgresql
sudo pg_lsclusters

Debian 13 installs PostgreSQL 17. The main cluster listens on localhost:5432 and uses a Unix socket. The systemd service is called postgresql; systemctl status postgresql covers every cluster. The port and configuration directory appear in the pg_lsclusters output.

Connect with psql

sudo -u postgres psql

Socket peer authentication maps the Unix user postgres to the PostgreSQL role of the same name. In this shell, \du lists roles, \l lists databases and \q quits. Do not work permanently as superuser: create an application role.

Create a role and a database

sudo -u postgres psql <<'SQL'
CREATE ROLE app LOGIN PASSWORD 'strong-password';
CREATE DATABASE app OWNER app ENCODING 'UTF8';
SQL

A LOGIN role can connect; OWNER grants rights on the database but not superuser status. Do not use GRANT ALL ON *.* for an application. Test the TCP connection:

psql "host=127.0.0.1 dbname=app user=app" -c "SELECT current_user;"

Authentication and local listening

Two files live in /etc/postgresql/17/main/: postgresql.conf for listen_addresses, pg_hba.conf for authentication rules. The default listen_addresses = 'localhost' is enough when the application runs on the same machine. Do not change it to '*' without a firewall.

In pg_hba.conf, a line host all all 127.0.0.1/32 scram-sha-256 allows local TCP connections with a hashed password. peer only works on the local socket and depends on the Unix user name. After a change:

sudo systemctl reload postgresql
sudo pg_isready

A reload is enough for pg_hba.conf and postgresql.conf; a restart drops active connections.

Verify and troubleshoot

journalctl -u postgresql -n 50 shows startup errors. password authentication failed comes from pg_hba.conf or the password; database "app" does not exist from a database created in another cluster; connection refused from a stopped service or listening on another address. Check listening sockets with ss -ltnp | grep 5432.

For backups, use pg_dump with a dedicated read-only role rather than copying data files while the server is running. A database exposed without TLS or a firewall must be fixed before any other tuning.