PostgreSQL DBA Part 2: Install PostgreSQL 18 on Ubuntu

This is Part 2 of the PostgreSQL DBA series on SQL Tips. To install PostgreSQL 18 on Ubuntu, you add the official PostgreSQL apt repository (called PGDG), run sudo apt install postgresql-18, and Ubuntu does the rest: it creates the postgres operating system user, builds a database cluster called 18 main, and starts it as a systemd service. By the end of this part you will have a running PostgreSQL 18 server, you will know where every file was put, and you will be able to start, stop, restart, reload and check the server like a DBA.

In Part 1 we covered what a PostgreSQL DBA does and how PostgreSQL works: the processes, the memory and the files. Now you build the server those ideas run on. Every command in this part was run on a real Ubuntu 24.04 LTS machine with PostgreSQL 18.6 from the PGDG repository, and the outputs are the real ones.

What you will learn

• Why DBAs install PostgreSQL from the PGDG repository instead of Ubuntu's built-in package
• How to add the repository and install PostgreSQL 18 step by step
• What the installer created: the postgres user, the cluster, the config and data directories
• How to start, stop, restart, reload and check the server with systemctl, pg_ctlcluster and pg_lsclusters
• Where the server log is and how to read it after a start or stop
• How to fix the errors beginners hit most often right after installing

Prerequisites

• Part 1 of this series (processes, memory and files). You do not need anything else from earlier parts.
• One Ubuntu Server LTS machine (26.04, 24.04 or 22.04, all supported by PGDG) on amd64 or arm64. A small virtual machine with 2 GB of RAM is enough for learning.
• A user account that can run sudo.
• Internet access to both Ubuntu's own package mirrors and apt.postgresql.org. PostgreSQL 18 needs a few libraries from Ubuntu's mirrors, so both must be reachable.

How it works

Two places to get PostgreSQL

Ubuntu ships its own PostgreSQL package. The catch is that each Ubuntu release takes one PostgreSQL major version and keeps it for the whole life of that release. If you want a specific version, such as 18, on any supported Ubuntu, you use the PostgreSQL Apt Repository, usually called PGDG (PostgreSQL Global Development Group). It is run by the PostgreSQL project, it carries every supported major version for every supported Ubuntu LTS, and it delivers minor-release bug fixes through the normal apt upgrade.

Think of it like getting a phone app from the developer's own store instead of a store that only updates once every two years. Same app, but you choose the version and you get fixes sooner.

What the Ubuntu packages give you

PostgreSQL itself is one program, postgres, plus client tools such as psql. Ubuntu wraps it in a helper package called postgresql-common. That package adds tools to run several clusters side by side, even of different major versions, on one machine. A cluster is one running PostgreSQL server with its own data directory and port (the documentation defines it as a collection of databases managed by a single instance of a running database server). On Ubuntu every cluster has a name made of two parts, the major version and a cluster name, so the first one is 18 main.

Notice that the configuration files are under /etc, not inside the data directory. That is an Ubuntu choice. The plain PostgreSQL documentation keeps them inside the data directory, so remember this difference when you read it.

Three ways to control the server

You will meet three tools that start and stop the same server. They are layers, each one calling the one below it:

systemctl (systemd)  ->  pg_ctlcluster (postgresql-common)  ->  pg_ctl (PostgreSQL)  ->  postgres

• systemctl is the normal way on a production Ubuntu server. systemd watches the server and starts it at boot.
• pg_ctlcluster is Ubuntu's wrapper. It knows where each cluster's files are, so you only give it a version and a name. When you run it with sudo as root on a systemd machine, it hands the action to systemctl for you.
• pg_ctl is PostgreSQL's own tool. On Ubuntu you rarely call it directly, because you would have to pass every path yourself.

Add the official PostgreSQL repository

Step 1: Install the helper packages

Where: on the database server (db1), as your normal user with sudo.

First refresh the package list and install postgresql-common (it contains the script that adds the repository), plus curl and ca-certificates so apt can download over HTTPS.

sudo apt update
sudo apt install -y postgresql-common curl ca-certificates

Check it worked: the script from the next step must now exist.

ls /usr/share/postgresql-common/pgdg/

apt.postgresql.org.asc  apt.postgresql.org.gpg  apt.postgresql.org.sh

The .sh file is the setup script. The .asc and .gpg files are the repository's signing key, which apt uses to prove the packages really come from the PostgreSQL project.

Step 2: Run the repository setup script

Where: on db1, with sudo.

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

This script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be noble-pgdg.

Using keyring /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
Writing /etc/apt/sources.list.d/pgdg.sources ...

Running apt-get update ...
Hit:6 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease
Reading package lists...

You can now start installing packages from apt.postgresql.org.

The script found the Ubuntu codename (noble is 24.04; on 26.04 you will see resolute-pgdg), wrote a new apt source file and refreshed the package lists.

Check it worked: look at the source file and ask apt which version it would install.

cat /etc/apt/sources.list.d/pgdg.sources
apt-cache policy postgresql-18 | head -3

Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Components: main
Architectures: amd64
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
postgresql-18:
  Installed: (none)
  Candidate: 18.6-1.pgdg24.04+2

A Candidate ending in pgdg24.04 proves the package will come from PGDG. Your minor version may be newer than 18.6.

Undo: to remove the repository, delete the source file and refresh: sudo rm /etc/apt/sources.list.d/pgdg.sources && sudo apt update.

Install PostgreSQL 18

Step 3: Install the server package

Where: on db1, with sudo.

sudo apt install -y postgresql-18

Always name the version (postgresql-18). That way you get exactly the major version you planned for. apt pulls in the client tools (postgresql-client-18) automatically.

The following NEW packages will be installed:
  postgresql-18 postgresql-client-18
...
Setting up postgresql-18 (18.6-1.pgdg24.04+2) ...
Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /var/lib/postgresql/18/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

This output is worth reading line by line, because it tells you what the installer did for you:

• Creating new PostgreSQL cluster 18/main: the package ran initdb, the PostgreSQL program that builds an empty data directory, at /var/lib/postgresql/18/main.
• --auth-local peer: local socket connections use peer authentication, meaning PostgreSQL trusts the Linux user name. That is why sudo -u postgres psql works without a password. Part 6 covers this in detail.
• --auth-host scram-sha-256: network connections need a password, checked with the SCRAM-SHA-256 method.
• locale "C.UTF-8": the locale (sorting and character rules) comes from the server's language setting. Your machine may show en_US.UTF-8 or en_IN.UTF-8 instead. Locale and encoding are hard to change later, so set the server's locale before you install.
• Data page checksums are enabled: new in PostgreSQL 18, initdb turns on checksums by default. PostgreSQL stores a small check value on each data page and can then detect pages silently damaged by the disk. Before 18 you had to ask for this with --data-checksums.
• max_connections 100, shared_buffers 128MB: safe starting defaults, not production values. Part 5 and Part 28 show how to change them.

On a normal Ubuntu server with systemd, the package then starts the new cluster straight away.

Step 4: Confirm the server is running

Where: on db1. pg_lsclusters can run as any user; the psql command runs as the postgres user through sudo.

pg_lsclusters
sudo -u postgres psql -c "SELECT version();"

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
                                                               version
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

pg_lsclusters is the first command a DBA runs on an unfamiliar Ubuntu database server. One line per cluster tells you its version, name, port, whether it is online or down, who owns it and where its data and log are.

What the installer created

Step 5: Meet the postgres operating system user

Where: on db1, any user.

id postgres
getent passwd postgres

uid=102(postgres) gid=104(postgres) groups=104(postgres),101(ssl-cert)
postgres:x:102:104:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

The package created a Linux user called postgres (your uid and gid numbers may differ). The server always runs as this user, never as root. The documentation advises a separate account that owns only the data, so that a compromised server process cannot change the PostgreSQL programs. That is exactly what you have: the binaries in /usr/lib/postgresql/18/bin/ belong to root, while the data belongs to postgres.

The ssl-cert group membership lets the server read Ubuntu's default self-signed certificate. Part 40 replaces it with a real one.

To work as postgres in a shell, use sudo -i -u postgres. The -i option starts a login shell, so you land in the postgres home directory, /var/lib/postgresql. Type exit to go back to your own user.

Step 6: Look at the configuration and program files

Where: on db1, any user.

ls -l /etc/postgresql/18/main/
ls /usr/lib/postgresql/18/bin/

total 64
drwxr-xr-x 2 postgres postgres  4096 Oct 20 12:48 conf.d
-rw-r--r-- 1 postgres postgres   315 Oct 20 12:48 environment
-rw-r--r-- 1 postgres postgres   143 Oct 20 12:48 pg_ctl.conf
-rw-r----- 1 postgres postgres  5934 Oct 20 12:48 pg_hba.conf
-rw-r----- 1 postgres postgres  2681 Oct 20 12:48 pg_ident.conf
-rw-r--r-- 1 postgres postgres 32830 Oct 20 12:48 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Oct 20 12:48 start.conf
clusterdb  createdb  createuser  dropdb  dropuser  initdb  oid2name  pg_amcheck
pg_archivecleanup  pg_basebackup  pg_checksums  pg_combinebackup  pg_config
pg_controldata  pg_createsubscriber  pg_ctl  pg_dump  pg_dumpall  pg_isready
pg_receivewal  pg_recvlogical  pg_resetwal  pg_restore  pg_rewind  pg_test_fsync
pg_test_timing  pg_upgrade  pg_verifybackup  pg_waldump  pg_walsummary  pgbench
postgres  psql  reindexdb  vacuumdb  vacuumlo

Most of those binaries are not on your PATH. Ubuntu puts small wrappers for the client tools (psql, pg_dump and others) in /usr/bin, and they pick the right version for the cluster you connect to. Server-side tools such as initdb or pg_controldata must be called with their full path.

Step 7: Ask the server where everything is

Where: on db1, as the postgres user through sudo. Asking the running server is better than guessing paths, because these values are what the server is really using.

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('data_directory','config_file','hba_file','port','listen_addresses','unix_socket_directories','data_checksums','server_version') ORDER BY name;"

          name           |                 setting
-------------------------+-----------------------------------------
 config_file             | /etc/postgresql/18/main/postgresql.conf
 data_checksums          | on
 data_directory          | /var/lib/postgresql/18/main
 hba_file                | /etc/postgresql/18/main/pg_hba.conf
 listen_addresses        | localhost
 port                    | 5432
 server_version          | 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
 unix_socket_directories | /var/run/postgresql
(8 rows)

Two lines matter for security. listen_addresses = localhost means the server only accepts network connections from this machine; nobody on your network can reach it yet. That is a safe default, and Parts 5 and 6 show how to open it carefully. data_checksums = on confirms the PostgreSQL 18 default from Step 3.

You can also check the connection itself with psql's \conninfo. In PostgreSQL 18 it prints a table instead of the single sentence older versions printed:

sudo -u postgres psql -c '\conninfo'

           Connection Information
      Parameter       |        Value
----------------------+---------------------
 Database             | postgres
 Client User          | postgres
 Socket Directory     | /var/run/postgresql
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 3686
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(12 rows)

Password Used: false and Socket Directory show peer authentication over the local socket at work.

Manage the PostgreSQL service

Step 8: Check, stop and start with systemctl

Where: on db1, with sudo. This is the method to use on a production server, because systemd then supervises the server and starts it again at boot.

Ubuntu uses two systemd units. postgresql@18-main controls this one cluster. postgresql (no @) is an umbrella unit that starts, stops or reloads every cluster whose start.conf says auto. When you have one cluster they behave the same, but get used to naming the cluster: on a server with several clusters it stops you restarting the wrong one.

sudo systemctl status postgresql@18-main --no-pager

● postgresql@18-main.service - PostgreSQL Cluster 18-main
     Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled)
     Active: active (running) since Tue 2026-10-20 12:48:06 UTC; 2min ago
   Main PID: 3506 (postgres)

active (running) is what you want to see. The main PID is the postmaster process you met in Part 1.

Step 9: Do the same with pg_ctlcluster

Where: on db1, as the postgres user through sudo. pg_ctlcluster takes its options first, then the version, the cluster name and an action. Its documented actions are start, stop, restart, reload, status, promote and logrotate.

sudo -u postgres pg_ctlcluster 18 main status

pg_ctl: server is running (PID: 3506)
/usr/lib/postgresql/18/bin/postgres "-D" "/var/lib/postgresql/18/main" "-c" "config_file=/etc/postgresql/18/main/postgresql.conf"

Status shows the PID and the exact command line the server was started with. You can see the data directory (-D) and the Ubuntu config path passed in with -c config_file=.

Now stop it, check, and start it again:

sudo -u postgres pg_ctlcluster 18 main stop
pg_lsclusters
sudo -u postgres pg_ctlcluster 18 main status; echo "exit code: $?"

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
pg_ctl: no server running
exit code: 3

The status is now down. Exit code 3 is pg_ctl's documented code for "server not running", which is useful in monitoring scripts.

sudo -u postgres pg_ctlcluster 18 main start
pg_isready

/var/run/postgresql:5432 - accepting connections

pg_isready is a tiny tool that only asks "are you accepting connections?". It exits with 0 when the answer is yes, so it is ideal for scripts that must wait for the server.

The three shutdown modes come from PostgreSQL itself:

Step 10: Read the server log

Where: on db1, with sudo (the log is readable by postgres and root only). After any start, stop or problem, the log is the first place to look.

sudo tail -n 12 /var/log/postgresql/postgresql-18-main.log

2026-10-20 12:49:04.995 UTC [3506] LOG:  received fast shutdown request
2026-10-20 12:49:04.997 UTC [3506] LOG:  aborting any active transactions
2026-10-20 12:49:04.999 UTC [3506] LOG:  background worker "logical replication launcher" (PID 3515) exited with exit code 1
2026-10-20 12:49:05.000 UTC [3510] LOG:  shutting down
2026-10-20 12:49:05.000 UTC [3510] LOG:  checkpoint starting: shutdown immediate
2026-10-20 12:49:05.014 UTC [3506] LOG:  database system is shut down
2026-10-20 12:49:05.328 UTC [3725] LOG:  starting PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
2026-10-20 12:49:05.328 UTC [3725] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-10-20 12:49:05.329 UTC [3725] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-10-20 12:49:05.333 UTC [3731] LOG:  database system was shut down at 2026-10-20 12:49:04 UTC
2026-10-20 12:49:05.338 UTC [3725] LOG:  database system is ready to accept connections

You can follow the whole stop and start. received fast shutdown request confirms the default mode. checkpoint starting: shutdown immediate is the final checkpoint of a clean shutdown; here "immediate" means the checkpoint runs at full speed, not that it was an immediate-mode shutdown. On start, database system was shut down at ... tells you the previous shutdown was clean, so no crash recovery was needed. The line you wait for is database system is ready to accept connections.

Also note listening on IPv4 address "127.0.0.1": that is the listen_addresses = localhost setting from Step 7 in action.

Step 11: Check that the cluster starts at boot

Where: on db1, any user.

grep -v '^#' /etc/postgresql/18/main/start.conf

auto

auto means the cluster starts and stops with the postgresql umbrella unit, which is enabled at boot. The other values are manual (systemd does not start it at boot, but you can start it by hand) and disabled (the tools refuse to start or stop it, a guard against accidents during maintenance). If you edit this file, run sudo systemctl daemon-reload afterwards so systemd notices.

Common mistakes and how to fix them

"unmet dependencies" during apt install

The following packages have unmet dependencies:
 postgresql-18 : Depends: liburing2 (>= 2.5) but it is not installable
E: Unable to correct problems, you have held broken packages.

PostgreSQL 18 from PGDG needs some libraries, such as liburing2, from Ubuntu's own mirrors. This error means apt can reach apt.postgresql.org but not the Ubuntu archive, usually because of a proxy, firewall or a broken /etc/apt/sources.list.d/ubuntu.sources. Run sudo apt update and fix every Err: or W: Failed to fetch line for the Ubuntu mirrors, then install again.

FATAL: role "root" does not exist

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

You ran psql or sudo psql as root. With peer authentication psql logs in as a database role with the same name as your Linux user, and there is no role called root. Use sudo -u postgres psql. Part 7 shows how to create a role for your own Linux user.

connection to server on socket ... failed: No such file or directory

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

The server is not running, so the socket file does not exist. Run pg_lsclusters; if the status is down, start it with sudo systemctl start postgresql@18-main and read the log from Step 10 if it fails to start.

The new cluster is on port 5433, not 5432

If the machine already had a cluster, for example Ubuntu's own PostgreSQL 16, the PostgreSQL 18 cluster gets the next free port, 5433. Your psql then connects to the old server on 5432 without any error. Always check pg_lsclusters after installing and connect with psql -p 5433 or psql --cluster 18/main.

Address already in use at start-up

LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL:  could not create any TCP/IP sockets

Something else already listens on the port, often another PostgreSQL cluster or a container. Find it with sudo ss -ltnp | grep 5432, then stop it or change the port setting of one cluster (a restart is needed).

Killing the server with kill -9

Never stop PostgreSQL with kill -9 (SIGKILL). The documentation warns that it stops the server from releasing shared memory and semaphores, and it does not pass the signal to the child processes, which you may then have to kill by hand. Use systemctl stop, or sudo pg_ctlcluster -m immediate 18 main stop in a real emergency.

Practice exercise

Ubuntu lets you run a second cluster on the same machine, which is how later parts of this series build a standby or a restore target on one server. Try it:

1. Create a second PostgreSQL 18 cluster called lab and check which port it was given.
2. Start it, connect to it and prove you are on the lab cluster by showing its data directory.
3. Delete it completely, and confirm only 18 main is left.

Quick reference

# 1. Install the helper packages
sudo apt update
sudo apt install -y postgresql-common curl ca-certificates

# 2. Add the PGDG repository
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
apt-cache policy postgresql-18 | head -3

# 3. Install PostgreSQL 18 (creates and starts cluster 18 main)
sudo apt install -y postgresql-18

# 4. Confirm the cluster and version
pg_lsclusters
sudo -u postgres psql -c "SELECT version();"

# 5. The postgres operating system user
id postgres

# 6. Configuration files and binaries
ls -l /etc/postgresql/18/main/
ls /usr/lib/postgresql/18/bin/

# 7. Ask the server where its files are
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c '\conninfo'

# 8. Manage the service with systemd
sudo systemctl status postgresql@18-main --no-pager
sudo systemctl stop postgresql@18-main
sudo systemctl start postgresql@18-main
sudo systemctl restart postgresql@18-main
sudo systemctl reload postgresql@18-main

# 9. The same with pg_ctlcluster
sudo -u postgres pg_ctlcluster 18 main status
sudo -u postgres pg_ctlcluster -m fast 18 main stop
sudo -u postgres pg_ctlcluster 18 main start
pg_isready

# 10. Read the server log
sudo tail -n 20 /var/log/postgresql/postgresql-18-main.log

# 11. Start-up mode at boot
grep -v '^#' /etc/postgresql/18/main/start.conf

Summary and what's next

• You added the official PGDG repository and know why DBAs prefer it to Ubuntu's built-in package.
• You installed PostgreSQL 18 and read what initdb did, including data checksums being on by default in 18.
• You know where the config files (/etc/postgresql/18/main), data (/var/lib/postgresql/18/main), binaries and log live.
• You can check, stop, start, restart and reload the cluster with systemctl and pg_ctlcluster, and you know the three shutdown modes.
• You can read the server log after a start or stop and fix the most common first-day errors.

In Part 3 you will open the data directory (PGDATA) and learn what each of its files and folders is for.