PostgreSQL is an advanced, open-source object-relational database management system with over 35 years of active development. It supports SQL compliance, ACID transactions, JSONB, full-text search, and extensibility through custom functions and extensions, making it suitable for both simple applications and complex enterprise workloads.

How much does it cost to run PostgreSQL on Raff?

A Raff VM suitable for PostgreSQL starts at $9.99/month (1 vCPU, 2 GB RAM, 50 GB NVMe SSD) for development and light workloads. For production database servers, the 2 vCPU / 4 GB RAM tier at $19.99/month or the 4 vCPU / 8 GB RAM tier at $36.00/month provides better performance for concurrent queries. All tiers include NVMe SSD storage for fast I/O.

What are the data transfer costs?

We offer unlimited, untracked bandwidth with no inbound or outbound charges! The only exception is suspicious or abusive usage.

Should I use PostgreSQL or MySQL?

PostgreSQL is preferred for applications requiring complex queries, JSONB support, advanced data types, or strict SQL standards compliance. MySQL is a solid choice for simpler workloads and has broader CMS compatibility. Both are production-ready; choose based on your application's requirements.

What version of PostgreSQL does Ubuntu 24.04 include?

Ubuntu 24.04's default repositories include PostgreSQL 16, which is a stable, long-term supported version. If you need a newer version such as PostgreSQL 17, you can add the official PostgreSQL APT repository from apt.postgresql.org.

How do I back up my PostgreSQL database?

Use the pg_dump utility to create logical backups of individual databases, or pg_dumpall for the entire cluster. For point-in-time recovery, configure continuous WAL archiving. You can also use Raff's VM snapshot feature for full server-level backups.

Install PostgreSQL on Ubuntu 24.04

In this tutorial, you’ll install PostgreSQL 18 on a Raff Ubuntu 24.04 VM, create an application database and role, configure local password authentication, and verify the setup with authenticated CRUD operations.

PostgreSQL is an open-source relational database system used for applications, analytics, and transactional workloads. This tutorial installs PostgreSQL 18 from the official PostgreSQL APT repository instead of the default Ubuntu package snapshot, then keeps the database local to the VM with firewall protection. Raff Technologies has supported 10,000+ VM deployments across its compute platform, and Raff Linux VMs can be provisioned in 60 seconds with NVMe storage and unmetered bandwidth.

This tutorial is designed for a Raff Ubuntu 24.04 VM with at least 2 vCPU, 4 GB RAM, and 80 GB NVMe storage.

Tested on Raff infrastructure by Aybars Altınyay, platform engineer at Raff Technologies.

Prerequisites:

A Raff Ubuntu 24.04 VM
SSH access with sudo privileges
A terminal connected to the VM
Basic familiarity with Linux package management and SQL commands

📌 Note: This tutorial keeps PostgreSQL bound to localhost. Applications running on the same VM can connect to PostgreSQL, while direct public access to port 5432 remains blocked.

Step 1 — Update system packages

Update the Ubuntu package index and install the tools required for the PostgreSQL APT repository:

Bash

sudo apt update
sudo apt upgrade -y
sudo apt install -y curl ca-certificates gnupg lsb-release ufw

Verify that package upgrades are complete:

Bash

sudo apt list --upgradable

Expected output:

Text

Listing... Done

Step 2 — Add the official PostgreSQL APT repository

Create the directory for the PostgreSQL repository signing key:

Bash

sudo install -d /usr/share/postgresql-common/pgdg

Download the PostgreSQL repository signing key:

Bash

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

Create the PostgreSQL APT source file for your Ubuntu release:

Bash

. /etc/os-release

cat <<EOF | sudo tee /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: ${VERSION_CODENAME}-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

Reload the package index:

Bash

sudo apt update

Verify that PostgreSQL 18 is available from the PGDG repository:

Bash

apt-cache policy postgresql-18

Expected output includes:

Text

postgresql-18:
  Candidate: 18.
  Version table:
     18.
        https://apt.postgresql.org/pub/repos/apt noble-pgdg/main

Step 3 — Install PostgreSQL 18

Install PostgreSQL 18 and the matching client package:

Bash

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

Verify the installed client version:

Bash

psql --version

Expected output includes:

Text

psql (PostgreSQL) 18.

Verify the PostgreSQL cluster:

Bash

pg_lsclusters

Expected output includes:

Text

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

Step 4 — Start and verify PostgreSQL

PostgreSQL usually starts automatically after installation. Start and enable it explicitly:

Bash

sudo systemctl enable --now postgresql

Verify that PostgreSQL is active:

Bash

systemctl status postgresql --no-pager

Expected output includes:

Text

Active: active

Verify that the server responds locally:

Bash

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

Expected output includes:

Text

PostgreSQL 18

Verify the default PostgreSQL port:

Bash

sudo ss -lntp | grep 5432

Expected output includes:

Text

127.0.0.1:5432

Step 5 — Create an application database and role

Create a dedicated application role and database. Replace the password before using this setup for real application data.

Bash

sudo -u postgres psql

Inside the PostgreSQL shell, run:

Sql

CREATE ROLE raffappuser WITH LOGIN PASSWORD 'ChangeThisStrongAppPassword!';
CREATE DATABASE raffapp OWNER raffappuser;
\q

Verify that the database exists:

Bash

sudo -u postgres psql -lqt | cut -d '|' -f 1 | grep -w raffapp

Expected output:

Text

 raffapp

Verify that the role exists:

Bash

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

Expected output:

Text

raffappuser

⚠️ Warning: Do not publish or reuse the tutorial password. Use a unique password for each real application.

Step 6 — Configure password authentication

PostgreSQL uses the pg_hba.conf file to control local and network authentication rules. Confirm that localhost TCP connections use password authentication:

Bash

sudo grep -nE '^host\s+all\s+all\s+127\.0\.0\.1/32|^host\s+all\s+all\s+::1/128' /etc/postgresql/18/main/pg_hba.conf

Expected output includes:

Text

127.0.0.1/32            scram-sha-256
::1/128                 scram-sha-256

If the lines use another method, set localhost host authentication to scram-sha-256:

Bash

sudo sed -i 's/^host\s\+all\s\+all\s\+127\.0\.0\.1\/32\s\+.*/host    all             all             127.0.0.1\/32            scram-sha-256/' /etc/postgresql/18/main/pg_hba.conf

sudo sed -i 's/^host\s\+all\s\+all\s\+::1\/128\s\+.*/host    all             all             ::1\/128                 scram-sha-256/' /etc/postgresql/18/main/pg_hba.conf

Reload PostgreSQL so authentication changes take effect:

Bash

sudo systemctl reload postgresql

Verify authenticated access as the application user:

Bash

PGPASSWORD='ChangeThisStrongAppPassword!' psql \
  -h 127.0.0.1 \
  -U raffappuser \
  -d raffapp \
  -c "SELECT current_user, current_database();"

Expected output includes:

Text

raffappuser | raffapp

Step 7 — Configure local-only access and firewall rules

PostgreSQL listens on localhost by default. Confirm the current bind address:

Bash

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

Expected output:

Text

localhost

Verify that PostgreSQL is listening locally on port 5432:

Bash

sudo ss -lntp | grep 5432

Expected output includes:

Text

127.0.0.1:5432

Allow SSH and block public PostgreSQL access:

Bash

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

Verify the firewall status:

Bash

sudo ufw status numbered

Expected output includes:

Text

22/tcp    ALLOW IN
5432/tcp  DENY IN

Step 8 — Verify PostgreSQL end to end

Run an authenticated CRUD test as the application user:

Bash

PGPASSWORD='ChangeThisStrongAppPassword!' psql \
  -h 127.0.0.1 \
  -U raffappuser \
  -d raffapp <<'SQL'
CREATE TABLE tutorial_check (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ 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:

Text

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

Verify the service one final time:

Bash

systemctl is-active postgresql
pg_lsclusters
sudo ss -lntp | grep 5432
sudo ufw status numbered

End-to-end verification is complete when:

PostgreSQL 18 is installed
The PostgreSQL cluster is online
The raffapp database exists
The raffappuser role can authenticate with a password
The application user can create, read, update, and delete data
PostgreSQL listens on localhost
Public access to port 5432 is blocked

Cleanup (Optional)

Use this section only when you want to remove PostgreSQL, its application database, users, service files, repository files, logs, and data directory from the VM.

⚠️ Warning: The following commands permanently delete PostgreSQL databases and local PostgreSQL data. Back up any data you need before proceeding.

Drop the tutorial database and role:

Bash

sudo -u postgres psql <<'SQL'
DROP DATABASE IF EXISTS raffapp;
DROP ROLE IF EXISTS raffappuser;
SQL

Stop and disable PostgreSQL:

Bash

sudo systemctl stop postgresql
sudo systemctl disable postgresql

Remove PostgreSQL packages:

Bash

sudo apt purge "postgresql-18*" "postgresql-client-18*" -y
sudo apt autoremove -y

Remove PostgreSQL data, configuration, logs, repository files, and signing key:

Bash

sudo rm -rf /var/lib/postgresql
sudo rm -rf /etc/postgresql
sudo rm -rf /var/log/postgresql
sudo rm -f /etc/apt/sources.list.d/pgdg.sources
sudo rm -rf /usr/share/postgresql-common/pgdg

Remove the PostgreSQL firewall rule:

Bash

sudo ufw delete deny 5432/tcp
sudo ufw status numbered

Verify that PostgreSQL is removed:

Bash

systemctl status postgresql --no-pager

Expected output includes:

Text

Unit postgresql.service could not be found.

Troubleshooting

PostgreSQL 18 does not appear in APT

Cause: The PGDG repository file is missing, malformed, or using the wrong Ubuntu codename.

Fix:

Bash

cat /etc/apt/sources.list.d/pgdg.sources
. /etc/os-release
echo "$VERSION_CODENAME"
apt-cache policy postgresql-18

Expected checks:

Text

Suites: noble-pgdg
Candidate: 18.

If the repository file is wrong, recreate it:

Bash

. /etc/os-release

cat <<EOF | sudo tee /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: ${VERSION_CODENAME}-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

sudo apt update

PostgreSQL service is not active

Cause: The service failed to start, or the cluster is not online.

Fix:

Bash

systemctl status postgresql --no-pager
pg_lsclusters
sudo journalctl -u postgresql --no-pager -n 80

Restart PostgreSQL:

Bash

sudo systemctl restart postgresql

Verify again:

Bash

systemctl is-active postgresql
pg_lsclusters

Expected output includes:

Text

active
online

Password authentication fails for the application user

Cause: The password is wrong, the role was not created with LOGIN, or pg_hba.conf does not use password authentication for localhost TCP connections.

Fix:

Reset the role password:

Bash

sudo -u postgres psql -c "ALTER ROLE raffappuser WITH LOGIN PASSWORD 'ChangeThisStrongAppPassword!';"

Confirm localhost authentication lines:

Bash

sudo grep -nE '^host\s+all\s+all\s+127\.0\.0\.1/32|^host\s+all\s+all\s+::1/128' /etc/postgresql/18/main/pg_hba.conf

Reload PostgreSQL:

Bash

sudo systemctl reload postgresql

Test again:

Bash

PGPASSWORD='ChangeThisStrongAppPassword!' psql \
  -h 127.0.0.1 \
  -U raffappuser \
  -d raffapp \
  -c "SELECT current_user;"

Expected output includes:

Text

raffappuser

The application user cannot create tables

Cause: The database is not owned by the application role, or schema privileges are missing.

Fix:

Set the database owner and grant schema privileges:

Bash

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

Verify table creation:

Bash

PGPASSWORD='ChangeThisStrongAppPassword!' psql \
  -h 127.0.0.1 \
  -U raffappuser \
  -d raffapp \
  -c "CREATE TABLE permission_check (id int); DROP TABLE permission_check;"

Expected output:

Text

CREATE TABLE
DROP TABLE

PostgreSQL listens on a public interface

Cause: listen_addresses was changed from localhost to * or a public IP address.

Fix:

Open the PostgreSQL configuration file:

Bash

sudo nano /etc/postgresql/18/main/postgresql.conf

Set:

Conf

listen_addresses = 'localhost'

Restart PostgreSQL:

Bash

sudo systemctl restart postgresql

Verify the listening address:

Bash

sudo ss -lntp | grep 5432

Expected output includes:

Text

127.0.0.1:5432

Conclusion and next steps

You now have PostgreSQL 18 installed on a Raff Ubuntu 24.04 VM from the official PostgreSQL APT repository. You also created a dedicated application database and role, verified authenticated CRUD operations, confirmed local-only binding, and blocked public database access with UFW. If you have not deployed your Raff VM yet, you can spin one up in 60 seconds at rafftechnologies.com.

Next: How to Secure Your Ubuntu 24.04 Server

Guide: Firewall Best Practices for Cloud Servers

Guide: Cloud Server Backup Strategies

How to Install PostgreSQL on Ubuntu 24.04

Prerequisites

Step 1 — Update system packages

Step 2 — Add the official PostgreSQL APT repository

Step 3 — Install PostgreSQL 18

Step 4 — Start and verify PostgreSQL

Step 5 — Create an application database and role

Step 6 — Configure password authentication

Step 7 — Configure local-only access and firewall rules

Step 8 — Verify PostgreSQL end to end

Cleanup (Optional)

Troubleshooting

PostgreSQL 18 does not appear in APT

PostgreSQL service is not active

Password authentication fails for the application user

The application user cannot create tables

PostgreSQL listens on a public interface

Conclusion and next steps

Get notified when we publish new tutorials

Frequently Asked Questions

Ready to get started?

Prerequisites

Step 1 — Update system packages

Step 2 — Add the official PostgreSQL APT repository

Step 3 — Install PostgreSQL 18

Step 4 — Start and verify PostgreSQL

Step 5 — Create an application database and role

Step 6 — Configure password authentication

Step 7 — Configure local-only access and firewall rules

Step 8 — Verify PostgreSQL end to end

Cleanup (Optional)

Troubleshooting

PostgreSQL 18 does not appear in APT

PostgreSQL service is not active

Password authentication fails for the application user

The application user cannot create tables

PostgreSQL listens on a public interface

Conclusion and next steps

Get notified when we publish new tutorials

Frequently Asked Questions

What is PostgreSQL?

How much does it cost to run PostgreSQL on Raff?

What are the data transfer costs?

Should I use PostgreSQL or MySQL?

What version of PostgreSQL does Ubuntu 24.04 include?

How do I back up my PostgreSQL database?

Ready to get started?