How to Install PostgreSQL on Ubuntu 24.04

In this tutorial, you’ll install PostgreSQL 18 on Ubuntu 24.04 from the official PostgreSQL APT repository, create an application database and role, verify SCRAM password authentication, keep the database local to the VM, and complete an authenticated CRUD test.

PostgreSQL is an open-source relational database system used for transactional applications, SaaS platforms, analytics, and data-intensive services. Ubuntu 24.04 includes PostgreSQL 16 in its distribution repositories, while the official PostgreSQL APT repository provides supported releases including PostgreSQL 18.

Raff supports 3,000+ customers and 15,000+ VMs in its us-east region. Raff Linux VMs provide KVM virtualization, full root access, NVMe storage, and 3 Gbps unmetered bandwidth for self-hosted database workloads.

Prerequisites:

• A Raff Linux VM running Ubuntu 24.04
• SSH access with a user that has sudo privileges
• At least 2 GB RAM for development or light workloads; 4 GB or more is a better production starting point
• Basic familiarity with Linux package management and SQL
• A backup plan before changing an existing PostgreSQL installation

📌 Local-only design: This tutorial keeps PostgreSQL bound to the loopback interface. Applications on the same VM can connect over 127.0.0.1, while port 5432 is not exposed publicly.

The original workflow was tested on a Raff VM with 2 vCPU and 4 GB RAM. The PostgreSQL repository, authentication, and package guidance were reviewed against current PostgreSQL 18 documentation in July 2026.

Step 1 — Update Ubuntu and install prerequisites

Update the package index and install current system updates:

sudo apt update
sudo apt upgrade -y

Install the packages required for PostgreSQL’s official APT repository and local firewall checks:

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

Verify the Ubuntu release codename:

. /etc/os-release
printf '%s %s\n' "$PRETTY_NAME" "$VERSION_CODENAME"

Expected output includes:

Ubuntu 24.04
noble

Step 2 — Add the official PostgreSQL APT repository

The PostgreSQL project provides an automated repository configuration script through the postgresql-common package. Run it to add the correct PGDG source for Ubuntu 24.04:

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

The script imports the repository signing key, creates the PGDG APT source, and refreshes the package index.

Verify the repository file:

cat /etc/apt/sources.list.d/pgdg.list 2>/dev/null || \
cat /etc/apt/sources.list.d/pgdg.sources

Expected output includes:

apt.postgresql.org
noble-pgdg

Verify that PostgreSQL 18 is available:

apt-cache policy postgresql-18 | sed -n '1,12p'

Expected output includes:

postgresql-18:
  Candidate:
  Version table:

The exact package revision changes as PostgreSQL publishes maintenance and security releases.

Step 3 — Install PostgreSQL 18

Install the PostgreSQL 18 server and matching client package:

sudo apt install -y postgresql-18 postgresql-client-18

Verify the client version:

psql --version

Expected output follows this format:

psql (PostgreSQL) 18.x

List the PostgreSQL clusters managed on the VM:

pg_lsclusters

Expected output includes:

Ver Cluster Port Status Owner    Data directory
18  main    5432 online postgres /var/lib/postgresql/18/main

If another PostgreSQL major version was already installed, review the cluster list carefully before changing ports, packages, or data directories.

Step 4 — Verify the PostgreSQL service

PostgreSQL packages normally create and start the 18/main cluster automatically. Verify the cluster-specific service:

systemctl is-active postgresql@18-main

Expected output:

active

Confirm that the cluster starts at boot:

systemctl is-enabled postgresql@18-main

Expected output:

enabled

Check whether PostgreSQL is ready to accept local connections:

sudo -u postgres pg_isready -h 127.0.0.1 -p 5432

Expected output:

127.0.0.1:5432 - accepting connections

Query the running server version:

sudo -u postgres psql -tAc "SELECT version();"

Expected output includes:

PostgreSQL 18

Step 5 — Create an application database and role

Open the PostgreSQL interactive terminal as the administrative postgres role:

sudo -u postgres psql

You are now inside the PostgreSQL prompt, indicated by:

postgres=#

Create a login role without placing its password in shell history:

CREATE ROLE raffappuser WITH LOGIN;
CREATE DATABASE raffapp OWNER raffappuser;
\password raffappuser

When \password prompts you, enter a unique application password twice. PostgreSQL does not display the password while you type.

Exit the PostgreSQL prompt:

\q

Verify that the role exists:

sudo -u postgres psql -tAc \
  "SELECT rolname FROM pg_roles WHERE rolname = 'raffappuser';"

Expected output:

raffappuser

Verify that the database exists and is owned by the application role:

sudo -u postgres psql -tAc \
  "SELECT datname || ' | ' || pg_get_userbyid(datdba) FROM pg_database WHERE datname = 'raffapp';"

Expected output:

raffapp | raffappuser

⚠️ Credential rule: Use a unique secret for the real application. Do not place production database passwords directly in source code, deployment scripts, screenshots, or shell commands.

Step 6 — Verify SCRAM password authentication

PostgreSQL controls client authentication through pg_hba.conf. Find the active file instead of assuming its path:

HBA_FILE="$(sudo -u postgres psql -tAc 'SHOW hba_file;' | xargs)"
echo "$HBA_FILE"

Expected output:

/etc/postgresql/18/main/pg_hba.conf

Inspect the localhost TCP authentication records:

sudo grep -nE \
  '^[[:space:]]*host[[:space:]]+all[[:space:]]+all[[:space:]]+(127\.0\.0\.1/32|::1/128)' \
  "$HBA_FILE"

Expected records use:

scram-sha-256

PostgreSQL 18 uses scram-sha-256 as the default password-encryption method. Verify it directly:

sudo -u postgres psql -tAc "SHOW password_encryption;"

Expected output:

scram-sha-256

Test authenticated access without placing the password in the command history:

psql -h 127.0.0.1 -U raffappuser -d raffapp -W \
  -c "SELECT current_user, current_database();"

Enter the password created in Step 5 when prompted.

Expected output includes:

 current_user | current_database
--------------+-----------------
 raffappuser  | raffapp

If the localhost host records do not use scram-sha-256, back up pg_hba.conf, change only the matching 127.0.0.1/32 and ::1/128 records, test the configuration, and reload PostgreSQL:

sudo cp "$HBA_FILE" "${HBA_FILE}.backup"
sudo nano "$HBA_FILE"
sudo -u postgres psql -tAc "SELECT pg_reload_conf();"

Step 7 — Keep PostgreSQL local to the VM

Confirm the configured listening addresses:

sudo -u postgres psql -tAc "SHOW listen_addresses;"

Expected output:

localhost

Verify the listening sockets:

sudo ss -lntp | grep ':5432'

Expected output includes loopback addresses only:

127.0.0.1:5432
[::1]:5432

Allow your SSH access path before enabling UFW:

sudo ufw allow OpenSSH

If the OpenSSH profile is unavailable, allow the SSH port directly:

sudo ufw allow 22/tcp

Add a deny rule for PostgreSQL as defense in depth, then enable UFW:

sudo ufw deny 5432/tcp
sudo ufw --force enable

Verify the rules:

sudo ufw status numbered

Expected output includes:

OpenSSH                    ALLOW IN
5432/tcp                   DENY IN

📌 Important: The firewall rule is not a replacement for listen_addresses = 'localhost'. The bind address prevents PostgreSQL from listening on public interfaces; UFW adds a second control.

For multi-VM architectures, do not expose PostgreSQL to the public internet. Use a private network and allow only the application server’s private address. Review managed vs self-hosted databases before deciding which operational model fits the workload.

Step 8 — Run an authenticated CRUD test

Create a temporary password file with restrictive permissions so the test can run non-interactively without placing the password in shell history:

read -rsp "PostgreSQL password: " DB_PASSWORD
echo

PGPASSFILE="$(mktemp)"
chmod 600 "$PGPASSFILE"
printf '127.0.0.1:5432:raffapp:raffappuser:%s\n' "$DB_PASSWORD" > "$PGPASSFILE"
unset DB_PASSWORD
export PGPASSFILE

Run the create, insert, update, select, delete, and drop checks:

psql -h 127.0.0.1 -U raffappuser -d raffapp <<'SQL'
CREATE TABLE tutorial_check (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO tutorial_check (name, status)
VALUES ('raff-postgresql-test', 'created');

UPDATE tutorial_check
SET status = 'verified'
WHERE name = 'raff-postgresql-test';

SELECT name, status
FROM tutorial_check
WHERE name = 'raff-postgresql-test';

DELETE FROM tutorial_check
WHERE name = 'raff-postgresql-test';

DROP TABLE tutorial_check;
SQL

Expected output includes:

CREATE TABLE
INSERT 0 1
UPDATE 1
        name          |  status
----------------------+----------
 raff-postgresql-test | verified
DELETE 1
DROP TABLE

Delete the temporary credential file immediately after the test:

rm -f "$PGPASSFILE"
unset PGPASSFILE

Run the final verification sequence:

systemctl is-active postgresql@18-main
pg_lsclusters
sudo -u postgres pg_isready -h 127.0.0.1 -p 5432
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo ufw status numbered

The installation is complete when:

• PostgreSQL 18 is installed and the 18/main cluster is online
• The cluster-specific systemd service is active
• PostgreSQL accepts local connections on port 5432
• raffappuser can authenticate to the raffapp database
• The authenticated CRUD test succeeds
• PostgreSQL listens only on loopback addresses
• UFW blocks inbound public connections to port 5432

Cleanup (Optional)

Use this cleanup only to remove the tutorial database and role. It does not uninstall PostgreSQL or delete unrelated databases.

⚠️ Warning: Dropping the database permanently removes all data stored in raffapp.

Terminate active sessions to the tutorial database, then remove it and its role:

sudo -u postgres psql <<'SQL'
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'raffapp'
  AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS raffapp;
DROP ROLE IF EXISTS raffappuser;
SQL

Verify removal:

sudo -u postgres psql -tAc \
  "SELECT datname FROM pg_database WHERE datname = 'raffapp';"

sudo -u postgres psql -tAc \
  "SELECT rolname FROM pg_roles WHERE rolname = 'raffappuser';"

Both commands should return no rows.

Do not purge PostgreSQL packages or remove /var/lib/postgresql unless you have confirmed that the VM contains no other clusters or databases you need.

Troubleshooting

PostgreSQL 18 does not appear in APT

Cause: The PGDG repository was not added, the repository script failed, or the VM is using an unsupported Ubuntu release.

Fix:

. /etc/os-release
echo "$VERSION_CODENAME"
cat /etc/apt/sources.list.d/pgdg.list 2>/dev/null || \
cat /etc/apt/sources.list.d/pgdg.sources
sudo apt update
apt-cache policy postgresql-18

Expected checks include:

noble
apt.postgresql.org
Candidate:

Re-run the official repository setup when necessary:

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

The PostgreSQL cluster is not online

Cause: The cluster failed to initialize or start, its port conflicts with another service, or its configuration is invalid.

Fix:

pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo journalctl -u postgresql@18-main --no-pager -n 80
sudo ss -lntp | grep ':5432'

Restart the cluster after correcting the reported issue:

sudo systemctl restart postgresql@18-main
systemctl is-active postgresql@18-main

Password authentication fails

Cause: The password is incorrect, the role cannot log in, the wrong database was selected, or the matching pg_hba.conf record does not use SCRAM authentication.

Fix:

Reset the password securely:

sudo -u postgres psql

Then run:

ALTER ROLE raffappuser WITH LOGIN;
\password raffappuser
\q

Confirm the active HBA file and localhost records:

HBA_FILE="$(sudo -u postgres psql -tAc 'SHOW hba_file;' | xargs)"
sudo grep -nE \
  '^[[:space:]]*host[[:space:]]+all[[:space:]]+all[[:space:]]+(127\.0\.0\.1/32|::1/128)' \
  "$HBA_FILE"

Test again:

psql -h 127.0.0.1 -U raffappuser -d raffapp -W \
  -c "SELECT current_user;"

The application role cannot create tables

Cause: The application role does not own the database or lacks permission to create objects in the public schema.

Fix:

sudo -u postgres psql <<'SQL'
ALTER DATABASE raffapp OWNER TO raffappuser;
\connect raffapp
GRANT USAGE, CREATE ON SCHEMA public TO raffappuser;
SQL

Verify with an authenticated connection:

psql -h 127.0.0.1 -U raffappuser -d raffapp -W \
  -c "CREATE TABLE permission_check (id integer); DROP TABLE permission_check;"

PostgreSQL listens on a public interface

Cause: listen_addresses was changed to *, 0.0.0.0, or a public IP.

Fix:

Find the active configuration file:

CONFIG_FILE="$(sudo -u postgres psql -tAc 'SHOW config_file;' | xargs)"
echo "$CONFIG_FILE"

Edit the file:

sudo nano "$CONFIG_FILE"

Set:

listen_addresses = 'localhost'

Restart the cluster and verify the sockets:

sudo systemctl restart postgresql@18-main
sudo ss -lntp | grep ':5432'

Expected output contains only 127.0.0.1:5432 and [::1]:5432.

Port 5432 is already in use

Cause: Another PostgreSQL cluster or database process is already listening on the default port.

Fix:

pg_lsclusters
sudo ss -lntp | grep ':5432'

Do not stop or delete an existing cluster until you have identified its data and confirmed it is safe to change. Use a different port for the new cluster when both versions must run at the same time.

Conclusion

You installed PostgreSQL 18 on Ubuntu 24.04 from the official PostgreSQL APT repository, created a dedicated application role and database, verified SCRAM authentication, completed authenticated CRUD operations, and kept PostgreSQL restricted to local connections.

Next, compare managed and self-hosted databases, review VPS sizing for database workloads, and build a database backup strategy for SaaS applications.

For another self-hosted data platform, follow Install MongoDB on Ubuntu 24.04. Explore Raff Managed Databases when your team prefers automated provisioning, patching, backups, monitoring, and operational support.