Installing and setting up PostgreSQL on Debian and Ubuntu

PostgreSQL is installed on Debian and Ubuntu in two minutes. The remaining two hours usually go into figuring out why nobody can log in. This article takes the installation all the way to the finish line: package choice, first access, database and user, the access rules in pg_hba.conf and a backup that you know can actually be restored.

All commands run as root. If you log in as a normal user, prefix them with sudo. Replace the version number 17 in paths with the version that is actually installed on your system.

Which PostgreSQL version ships with which distribution

The most important difference between the four common systems is the major version that comes out of the distribution repository. It is tied to the release and does not change over the lifetime of the distribution.

That spread has practical consequences. A dump from Debian 13 cannot simply be loaded into Ubuntu 22.04. And if you work on Ubuntu 22.04, you should know that PostgreSQL 14 drops out of community support on 12 November 2026 according to the versioning policy of the PostgreSQL Global Development Group. Ubuntu keeps shipping security updates for 22.04 as part of the LTS cycle, but no more upstream fixes flow in. For new projects on 22.04 that is a strong argument for reaching for the PGDG repository right away.

A look at the package database tells you what is available on your system before you install anything:

apt update
apt-cache policy postgresql

The Candidate line shows a version number such as 17+283. The number in front of the plus sign is the PostgreSQL major version, the rest is the version number of the Debian metapackage.

Installing from the distribution package

For most use cases the distribution package is the right choice. It is covered by the security updates of the distribution, it works with the system libraries and it causes no trouble during a release upgrade.

apt install -y postgresql postgresql-contrib

postgresql-contrib brings the bundled extensions along, among them pgcrypto, uuid-ossp and pg_stat_statements. Without that package many applications later fail with an ERROR: could not open extension control file, and tracking it down takes longer than the installation.

During installation Debian and Ubuntu automatically create a first cluster called main and start it. Cluster here means a running instance with its own data directory, its own port and its own configuration. Whether that worked is not answered by the exit code of apt, but by this:

pg_lsclusters

The output has to show online in the Status column:

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

If it says down, start the service afterwards. service postgresql start works on all four systems, including containers without systemd. On a normal server systemctl start postgresql does the job just as well.

service postgresql start
pg_isready

pg_isready answers with /var/run/postgresql:5432 - accepting connections and returns exit code 0. That is the first hard proof that the server is reachable, and it can be reused in monitoring scripts.

When the PGDG repository pays off and how to wire it in cleanly

The official repository at apt.postgresql.org provides all supported major versions in parallel for trixie, bookworm, noble and jammy. It is the right choice when you need a specific major version because the application requires it, when you need extensions that Debian does not package, or when your distribution level points at a version that will soon run out of support.

The key belongs in a file of its own, no longer in the deprecated apt-key keyring. Debian 13 and Ubuntu 24.04 also prefer the deb822 format with the .sources extension, which works on Debian 12 and Ubuntu 22.04 as well:

apt install -y curl ca-certificates
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

cat > /etc/apt/sources.list.d/pgdg.sources <<EOF
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $(. /etc/os-release && echo $VERSION_CODENAME)-pgdg
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

The line with $(. /etc/os-release ...) automatically fills in trixie-pgdg, bookworm-pgdg, noble-pgdg or jammy-pgdg. After that:

apt update
apt-cache policy postgresql-18

The package postgresql-common ships a ready-made script for the same purpose, /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh. It is an alternative to the key and the pgdg.sources above, not an addition. If you run both, the script additionally creates a /etc/apt/sources.list.d/pgdg.list, and apt then warns on every run: W: Target Packages (main/binary-amd64/Packages) is configured multiple times in /etc/apt/sources.list.d/pgdg.list:1 and /etc/apt/sources.list.d/pgdg.sources:1. If you prefer the script over the manual steps above, install gnupg alongside it first, so apt install -y curl ca-certificates gnupg. The older version of the script on Ubuntu 22.04 still imports the key through apt-key and aborts without gnupg with E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation and exit code 255. On Debian 13, Debian 12 and Ubuntu 24.04 the newer version stores the key directly as an .asc file and runs through without an extra package.

The trap: two clusters, two ports

If you now install a new major version on a system that already carries the distribution package, a second cluster comes into existence. It does not get port 5432, but the next free one, so 5433. Applications keep connecting to the old version, and no error message points that out. This is the most common reason for the sentence "but I did install PostgreSQL 18, and SELECT version() still shows 15".

apt install -y postgresql-18
pg_lsclusters

There are now two lines with different ports. If you want to carry the data over into the new version, pg_upgradecluster is the right tool, not a hand-made dump. It requires both server packages to be installed, and it leaves the old cluster lying around stopped instead of deleting it:

pg_upgradecluster 15 main

Afterwards check with pg_lsclusters which cluster sits on port 5432, and test the application before you remove the old cluster for good with pg_dropcluster --stop 15 main. That command deletes the data directory without asking.

First access, and why root is not allowed to run psql

The classic stumbling block right after the installation:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

That is not a fault, it is the expected behavior. Debian and Ubuntu configure local socket connections with the peer method. PostgreSQL asks the kernel which system user opened the connection, and it requires a database role of the same name. Only the role postgres exists, so you first have to switch into that system user.

su - postgres

Then you start psql without arguments. exit leaves the shell again. For individual commands out of a script, switching per command is more practical:

runuser -u postgres -- psql -c "SELECT version();"

If you have sudo installed, write sudo -u postgres psql -c "SELECT version();" instead. Both variants are equivalent. With sudo the warning could not change directory to "/root": Permission denied often shows up. It has no consequences, because the user postgres is not allowed to enter the working directory of root, but the command is executed anyway.

These three queries tell you what you are dealing with, and they are the first step in any troubleshooting session:

runuser -u postgres -- psql -c "SHOW server_version;"
runuser -u postgres -- psql -c "SHOW config_file;"
runuser -u postgres -- psql -c "SHOW hba_file;"

The last command is especially useful. It names the path that the running server really reads, typically /etc/postgresql/17/main/pg_hba.conf. If you edit a file and nothing changes, you are almost always looking at the configuration of a different cluster.

Inside psql the meta commands help: \l lists databases, \du the roles, \dt the tables of the current database, \conninfo shows who you are connected as and where to, and \q ends the session.

Creating a database and a user

Every application deserves a role of its own and a database of its own. The order matters, because the database should belong to the role directly.

runuser -u postgres -- psql -c "CREATE ROLE appuser LOGIN PASSWORD 'YourStrongPassword';"
runuser -u postgres -- psql -c "CREATE DATABASE appdb OWNER appuser;"

Since PostgreSQL 14 passwords are stored with scram-sha-256 by default, so that applies to all four systems covered here. The password does not land in the database in clear text, but it does land in your shell history. To avoid that, use \password appuser inside psql instead, which prompts for it interactively.

The trap since PostgreSQL 15: permission denied for schema public

One behavior that many older guides do not know about yet: from PostgreSQL 15 on, not every user is allowed to create objects in the public schema. Debian 12, Debian 13 and Ubuntu 24.04 are affected. Only Ubuntu 22.04 with PostgreSQL 14 still follows the old pattern. The error looks like this:

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE customers (id serial primary key);

The clean way is the one shown above: the database belongs to the role. Since version 15 the public schema belongs to the role pg_database_owner, and the respective owner of the database is implicitly a member of it. If you created the database without OWNER, grant the permission after the fact. Note that this statement has to be executed in the affected database, not in postgres:

runuser -u postgres -- psql -d appdb -c "GRANT ALL ON SCHEMA public TO appuser;"

Proof that the permissions are correct

A CREATE ROLE without an error does not yet mean that the application can log in. The proof is a real login over TCP followed by a write. PGPASSWORD is only meant for testing here, for permanent operation see further below:

PGPASSWORD='YourStrongPassword' psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user, current_database();"
PGPASSWORD='YourStrongPassword' psql -h 127.0.0.1 -U appuser -d appdb -c "CREATE TABLE probe (id int);"

If both run through, the combination of role, password, database and schema permissions is complete. The probe table is deliberately left in place, because the backup test further below needs at least one object in the database, otherwise it checks nothing. To verify, list the objects:

runuser -u postgres -- psql -c "\du"
runuser -u postgres -- psql -c "\l"

A word on character encoding: if the system locale was not set to UTF-8 when the cluster was created, the template database may be SQL_ASCII. A CREATE DATABASE ... ENCODING 'UTF8' then fails with ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII). The way out is TEMPLATE template0 at creation time, the clean solution is a system with a UTF-8 locale.

Understanding pg_hba.conf, the most common source of errors

The file pg_hba.conf (host-based authentication) decides before any password check whether a connection is permitted at all. It is read from top to bottom, and the first matching line wins. If no line matches, the connection is rejected. A generous rule further down does not help you if a stricter one above it matches first. This is by far the most common configuration mistake.

The shipped state on Debian and Ubuntu looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

That is how it stands on the four systems covered here. On older levels, for example Debian 11 with PostgreSQL 13, the two host lines still carry md5 instead of scram-sha-256. Logging in over TCP works in both cases, but you should no longer rely on md5.

The columns mean the following: local stands for the Unix socket, host for TCP with or without TLS, hostssl for TLS connections only. After that come database, role, network range in CIDR notation and the method. Four methods matter. peer checks the system user and only works over the socket. scram-sha-256 is the modern password method and the right choice for everything over TCP. md5 is deprecated and should no longer appear in new configurations. trust lets anyone in without a check and has no place on a reachable server.

The error messages word for word

Once you can tell them apart, you save yourself a lot of guessing:

FATAL:  Peer authentication failed for user "appuser"

You are connected over the socket, but your system user has a different name than the role. Either switch users, or connect via -h 127.0.0.1 so that the host line applies.

FATAL:  no pg_hba.conf entry for host "198.51.100.4", user "appuser", database "appdb", no encryption

The server is reachable, but no rule matches this combination of source address, role and database. Either a line is missing, or the network in the existing line does not cover the address.

FATAL:  password authentication failed for user "appuser"

The rule applies, the password is wrong. Frequently because the role was created without LOGIN, or because the password is left over from an earlier installation.

psql: error: connection to server at "203.0.113.10", port 5432 failed: Connection refused

Here pg_hba.conf was never involved at all. Either the server is not running, or it is not listening on this address, or a firewall blocks it. More on that in a moment.

Check changes before you reload

PostgreSQL offers a system view that shows the parsed rules, including line numbers and syntax errors. It answers the question of which rule the server actually sees, instead of which one you believe you wrote:

runuser -u postgres -- psql -c "SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;"

Changes to pg_hba.conf need no restart, a reload is enough and it drops no existing connections:

runuser -u postgres -- psql -c "SELECT pg_reload_conf();"

Alternatively service postgresql reload. A real restart is only necessary if you changed parameters such as listen_addresses, port or shared_buffers.

Opening up access from outside

Out of the box PostgreSQL listens on localhost only. That is a good default, and you should only give it up when it is genuinely necessary. Two things have to come together: the server has to listen on the address, and pg_hba.conf has to permit the source. If the first is missing you get Connection refused, if the second is missing you get no pg_hba.conf entry.

The configuration lives at /etc/postgresql/17/main/postgresql.conf. Debian ships the tool pg_conftool for it, which edits the file more reliably than a search run in an editor:

pg_conftool 17 main show listen_addresses
pg_conftool 17 main set listen_addresses '10.0.0.5,127.0.0.1'

Enter concrete addresses instead of *. On a server with a public and an internal address you then bind to the internal network only. After that you add a rule to pg_hba.conf, scoped as narrowly as possible:

host    appdb    appuser    10.0.0.0/24    scram-sha-256

After a restart with service postgresql restart you first check what the process really listens on. ss -lntp | grep 5432 shows the bound addresses. If only 127.0.0.1:5432 appears there, the change did not take effect, usually because a second cluster was meant or because a file under conf.d overrides the value.

The firewall needs a rule as well, and one with a source. A port 5432 left wide open on the internet gets scanned within hours:

ufw allow from 10.0.0.0/24 to any port 5432 proto tcp

Honest advice: in most cases the better solution is not to open the port at all. An SSH tunnel with ssh -L 5432:127.0.0.1:5432 user@server is entirely sufficient for maintenance access. For permanent connections between several servers a WireGuard network is the cleaner choice, because the database then still listens on a private address only. Incidentally, Debian and Ubuntu enable TLS by default with a self-signed certificate, which is why sslmode=require works immediately. Real protection against an attacker on the wire only starts with sslmode=verify-full and a certificate that the client trusts.

Backup with pg_dump, and the proof that it is worth something

For individual databases the custom format is the best choice. It is compressed, it can be restored selectively and it can be read back in parallel:

runuser -u postgres -- pg_dump -Fc -d appdb -f /var/lib/postgresql/appdb.dump

A point that is frequently overlooked: pg_dump backs up no roles and no passwords. Those live cluster-wide and have to be backed up separately, otherwise a restore is missing exactly the users the application needs:

runuser -u postgres -- pg_dumpall --globals-only -f /var/lib/postgresql/globals.sql

When the versions do not match

pg_dump: error: server version: 17.5; pg_dump version: 15.10
pg_dump: error: aborting because of server version mismatch

The rule is: pg_dump may be newer than the server, never older. On Debian and Ubuntu that is easy to solve, because /usr/bin/pg_dump is only a wrapper that picks the matching program version. Install the package postgresql-client-18 and the newer build is available. And with the Debian extension --cluster you force the wrapper onto a specific cluster, here version 17, cluster main:

pg_dump --version
runuser -u postgres -- pg_dump --cluster 17/main -Fc -d appdb -f /var/lib/postgresql/appdb.dump

Keeping passwords out of the script

For automated backups the password belongs in a .pgpass file in the format host:port:database:user:password. PostgreSQL ignores the file without comment if the permissions are too wide. Just as important is whose home directory it sits in, because the file that gets read is always the one belonging to the user the command actually runs as. A ~/.pgpass as root has no effect as long as the backup runs through runuser -u postgres as in this article. In that case the home directory of postgres is what counts:

touch /var/lib/postgresql/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 0600 /var/lib/postgresql/.pgpass

An empty file has no effect either. Enter one line per connection, for example 127.0.0.1:5432:appdb:appuser:YourStrongPassword. If your backup job instead runs directly as root without a user switch, the same file belongs in /root/.pgpass.

Verifying the backup

A backup file that has never been restored is a guess. The test takes a minute. First look at the table of contents, then load it into a throwaway database and count the tables:

runuser -u postgres -- pg_restore -l /var/lib/postgresql/appdb.dump | head -n 20
runuser -u postgres -- createdb appdb_restore_test
runuser -u postgres -- pg_restore -d appdb_restore_test /var/lib/postgresql/appdb.dump
runuser -u postgres -- psql -d appdb_restore_test -c "\dt"
runuser -u postgres -- dropdb appdb_restore_test

If \dt shows the same tables as in the original, so here at least the probe table, the backup is usable. If the command instead reports Did not find any relations., the backed-up database was empty and the test proves nothing. In appdb you then clear the test table away again with runuser -u postgres -- psql -d appdb -c "DROP TABLE probe;". For day-to-day operation an entry in /etc/cron.d is enough, one that stores both files with the date in the name and cleans up older ones. What matters is that the files then leave the server. A backup on the same disk helps against an accidental DROP TABLE, not against a hardware failure.

When the cluster does not start

If the service does not start, the service status usually only tells you that something failed. The actual cause is in the cluster log:

tail -n 30 /var/log/postgresql/postgresql-*-main.log

One line you can safely skip over. The message FATAL: role "root" does not exist, known from the first section, usually comes from pg_isready: the tool builds its connection attempt with the logged-in system user, so as root, and the server logs the unknown role. The return value is still 0, the output reads accepting connections, and the cluster is perfectly fine.

On systems with systemd, journalctl -u postgresql@17-main --no-pager -n 50 delivers the same lines. Note the versioned unit: postgresql.service is only a shell that starts all clusters, and it reports success even when a single cluster has failed. That is why pg_lsclusters is the more reliable check.

Three messages cover most cases. could not bind IPv4 address "0.0.0.0": Address already in use means that another cluster occupies the port, see the section on the two clusters. A message about No space left on device while writing the postmaster.pid simply means a full disk, which you confirm with df -h. And errors about invalid permissions on the data directory show up after careless chmod or chown runs: /var/lib/postgresql/17/main has to belong to the user postgres and carry mode 0700.

One closing note on operations: PostgreSQL runs conservatively in its default setting and comes nowhere near using up the RAM of a server. Before you turn the knobs on shared_buffers and work_mem, enable pg_stat_statements from the contrib package and look at which queries actually cost time. In practice the bottleneck is almost always a missing index, not the memory parameters.