CertGrid CertGrid
Installation·PostgreSQL

PostgreSQL Installation on Ubuntu 26.04

One apt command installs PostgreSQL 18 and starts a cluster. Then `ss` shows it on 127.0.0.1 only, and `psql` as your own user fails - because peer authentication needs a database role with your name, and there is not one.

Foundations Guide 1 of 47 Beginner

Written against the versions above. Ubuntu's package is the Debian layout: a cluster manager, configuration under /etc, data under /var/lib. The PostgreSQL project's own packages put configuration inside the data directory, so upstream documentation will not match these paths.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. See what the archive offers

    postgresql is a meta-package that pulls the current major version; postgresql-contrib adds the extension modules - pg_stat_statements, pgcrypto and others - that most real deployments end up wanting.

    The candidate here is 18. Ubuntu ships one major version per release, so the version you get is decided by your distribution unless you add the PostgreSQL project's own repository.

    bash Example session 
    apt-cache policy postgresql postgresql-contrib | head -8postgresql:  Installed: (none)  Candidate: 18+290ubuntu1  Version table:     18+290ubuntu1 500        500 http://in.archive.ubuntu.com/ubuntu resolute/main amd64 Packagespostgresql-contrib:  Installed: (none)

    Expected resultInstalled: (none) and a candidate on the 18 series.

    Success conditionYou know which major version you are about to run.

  2. Install it

    Unlike MySQL, this does more than install software: the package creates a cluster and starts it. A PostgreSQL cluster is one server process with its own data directory, port and set of databases - not a group of machines, which is what the word usually means elsewhere.

    Output is trimmed. What matters is that it succeeded.

    bash Example session 
    sudo apt-get install -y postgresql postgresql-contribThe following packages were automatically installed and are no longer required:  linux-headers-7.0.0-14 linux-headers-7.0.0-14-generic  linux-image-unsigned-7.0.0-14-generic  linux-main-modules-zfs-7.0.0-14-generic linux-modules-7.0.0-14-generic  linux-tools-7.0.0-14 linux-tools-7.0.0-14-genericUse 'sudo apt autoremove' to remove them.The following additional packages will be installed:  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5  libtypes-serialiser-perl liburing2 postgresql-18 postgresql-18-jit  postgresql-client-18 postgresql-client-common postgresql-common ssl-cert

    Expected resultThe packages install and a cluster is created.

    Success conditionPostgreSQL is installed and a cluster exists.

  3. Confirm the service and see the Debian cluster view

    enabled and active, as with any packaged service.

    pg_lsclusters is the command to learn here and it does not exist upstream - it is part of Debian's postgresql-common. One line per cluster: version 18, named main, on port 5432, online, owned by the postgres OS user, with its data directory and log file.

    That layout is what lets one machine run several major versions side by side, each with its own port - which is how a Debian-family upgrade works, and why the version number appears in every path.

    bash Example session 
    psql --versionpsql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)systemctl is-enabled postgresql ; systemctl is-active postgresqlenabledactivepg_lsclustersVer Cluster Port Status Owner    Data directory              Log file18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

    Expected result18.6, enabled, active, and one online cluster on port 5432.

    Success conditionYou can list every cluster on the machine and its state.

  4. Find out where it is listening

    127.0.0.1:5432, and nothing else - the same loopback-only default MySQL has, for the same reason.

    PostgreSQL calls the setting listen_addresses rather than bind-address. It is in postgresql.conf and needs a restart to change; the replication track does exactly that when the standby needs to reach it.

    Note there is only one line. MySQL had a second port for its X protocol; PostgreSQL has one port and one protocol.

    bash Example session 
    sudo ss -lntp | grep postgresLISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=25580,fd=6))

    Expected resultOne LISTEN row on 127.0.0.1:5432.

    Success conditionYou know the server is unreachable from other machines, deliberately.

  5. Learn the two directories that confuse everyone

    Ask the server rather than guessing, because this is where Debian differs most from upstream.

    data_directory is /var/lib/postgresql/18/main - the actual data. config_file is /etc/postgresql/18/main/postgresql.conf and hba_file is beside it. Upstream PostgreSQL keeps the configuration inside the data directory; Debian moves it to /etc where a sysadmin expects it.

    So documentation telling you to edit $PGDATA/postgresql.conf is describing a file that exists here but is not the one being read. Always ask the server: SHOW config_file is definitive.

    bash Example session 
    sudo -u postgres psql -c "SHOW data_directory" -c "SHOW config_file" -c "SHOW hba_file"       data_directory----------------------------- /var/lib/postgresql/18/main(1 row)                config_file----------------------------------------- /etc/postgresql/18/main/postgresql.conf(1 row)               hba_file------------------------------------- /etc/postgresql/18/main/pg_hba.conf(1 row)ls -d /etc/postgresql/*/main /var/lib/postgresql/*/main/etc/postgresql/18/main/var/lib/postgresql/18/main

    Expected resultData under /var/lib/postgresql/18/main, config under /etc/postgresql/18/main.

    Success conditionYou know which file the running server actually reads.

  6. Try to connect as yourself, and fail

    First as the postgres OS user, via sudo -u postgres: it works, and reports current_user as postgres with the full version string.

    Then as your own login user, and it fails: FATAL: role "sysadmin" does not exist, exit 2.

    That message is precise and worth reading carefully. It is not "access denied" or "wrong password" - the connection reached the server, which looked for a role matching the operating system user and found none. This is *peer* authentication: over a local socket, PostgreSQL trusts the OS to say who you are and then requires a role of the same name.

    The parallel with MySQL's auth_socket is close, with one difference that matters: MySQL ships a root account already mapped, PostgreSQL ships only postgres. Creating your own role is the next guide.

    bash Example session 
    sudo -u postgres psql -c "SELECT current_user, session_user, version()" current_user | session_user |                                                             version--------------+--------------+--------------------------------------------------------------------------------------------------------------------------------- postgres     | postgres     | PostgreSQL 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit(1 row)psql -c "SELECT 1" ; echo "exit=$?"psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "sysadmin" does not existexit=2

    Expected resultpostgres for the first, then FATAL: role "sysadmin" does not exist and exit=2.

    Success conditionYou understand why a fresh install rejects your own user.

Troubleshooting

Official sources

← All PostgreSQL guides