Step 1 — Update Ubuntu and verify the release
Update package metadata and installed packages:
sudo apt update
sudo apt upgrade -y
Install the packages used by the PGDG setup and later checks:
sudo apt install -y curl ca-certificates postgresql-common
Verify the Ubuntu release:
. /etc/os-release
printf '%s\n%s\n' "$PRETTY_NAME" "$VERSION_CODENAME"
Expected values include:
Check whether a reboot is required:
if [ -f /var/run/reboot-required ]; then
echo "Reboot required before continuing"
else
echo "No reboot required"
fi
Verify: the host reports Ubuntu 24.04 with codename noble, package updates complete successfully, and any required reboot is handled before PostgreSQL installation.
Step 2 — Add the official PostgreSQL Apt repository
Add the PGDG repository using the helper shipped by postgresql-common:
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Verify the configured repository:
grep -R "apt.postgresql.org" \
/etc/apt/sources.list.d/pgdg.list \
/etc/apt/sources.list.d/pgdg.sources 2>/dev/null
The output includes apt.postgresql.org and noble-pgdg.
Confirm PostgreSQL 18 is available:
apt-cache policy postgresql-18 | sed -n '1,14p'
A non-empty Candidate from the PGDG repository confirms the package is available.
Verify: the configured source references apt.postgresql.org, targets noble-pgdg, and exposes a PostgreSQL 18 candidate package.
Step 3 — Install PostgreSQL 18
Install the PostgreSQL 18 server and matching client:
sudo apt install -y postgresql-18 postgresql-client-18
Verify the client version:
Expected format:
List the PostgreSQL clusters managed by Ubuntu tooling:
A normal new installation includes an 18/main cluster, commonly on port 5432:
Ver Cluster Port Status Owner Data directory
18 main 5432 online postgres /var/lib/postgresql/18/main
Query the running server version:
sudo -u postgres psql -tAc "SHOW server_version;"
PostgreSQL 18.6 is the current PostgreSQL 18 maintenance release as of September 17, 2026. Version 18.5 was not released because a regression was found before shipment.
If another PostgreSQL major version already exists, inspect pg_lsclusters before changing ports, packages, or data directories.
Verify: psql reports PostgreSQL 18.x, pg_lsclusters shows the intended cluster online, and SHOW server_version reports PostgreSQL 18.6 or a later supported 18.x maintenance release.
Step 4 — Verify service health and local readiness
Check the cluster-specific systemd unit:
systemctl is-active postgresql@18-main
systemctl is-enabled postgresql@18-main
Expected output:
Check the local TCP endpoint:
sudo -u postgres pg_isready -h 127.0.0.1 -p 5432
Expected output:
127.0.0.1:5432 - accepting connections
Read the data directory and active configuration file from PostgreSQL itself:
sudo -u postgres psql -tAc "SHOW data_directory;"
sudo -u postgres psql -tAc "SHOW config_file;"
Verify: the 18/main service is active and enabled, pg_isready reports that PostgreSQL is accepting connections, and the server returns the expected 18/main paths.
Step 5 — Create an application role and database
Open psql as the local PostgreSQL administrator:
Create a dedicated login role and application database:
CREATE ROLE raffappuser WITH LOGIN;
CREATE DATABASE raffapp OWNER raffappuser;
\password raffappuser
Enter a unique application password twice when prompted, then exit:
Verify the role:
sudo -u postgres psql -tAc \
"SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'raffappuser';"
Expected output:
Verify database ownership:
sudo -u postgres psql -tAc \
"SELECT datname || '|' || pg_get_userbyid(datdba) FROM pg_database WHERE datname = 'raffapp';"
Expected output:
Do not use the postgres superuser from application code.
Verify: raffappuser can log in and owns the raffapp database.
Step 6 — Verify SCRAM authentication
Find the active host-based authentication file:
HBA_FILE="$(sudo -u postgres psql -tAc 'SHOW hba_file;' | xargs)"
echo "$HBA_FILE"
Inspect the parsed HBA rules:
sudo -u postgres psql -P pager=off -x -c \
"SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY line_number;"
Verify the password-encryption setting:
sudo -u postgres psql -tAc "SHOW password_encryption;"
Expected output:
Test the application role over TCP:
psql -h 127.0.0.1 -U raffappuser -d raffapp -W \
-c "SELECT current_user, current_database();"
Expected output includes:
current_user | current_database
--------------+-----------------
raffappuser | raffapp
Do not change authentication to trust to work around a failed login. Inspect the matching pg_hba.conf rule and correct the underlying account or rule issue.
Verify: password_encryption is scram-sha-256, localhost TCP requires authentication, and raffappuser connects successfully to raffapp.
Step 7 — Keep PostgreSQL off the public network
Check the configured listen addresses:
sudo -u postgres psql -tAc "SHOW listen_addresses;"
For this single-VM deployment, expected output is:
Inspect active listeners:
sudo ss -lntp | grep ':5432'
Expected listeners are loopback only, such as:
127.0.0.1:5432
[::1]:5432
If PostgreSQL listens on 0.0.0.0, [::], or a public interface, locate the active configuration file:
CONFIG_FILE="$(sudo -u postgres psql -tAc 'SHOW config_file;' | xargs)"
echo "$CONFIG_FILE"
Set:
listen_addresses = 'localhost'
Restart and verify again:
sudo systemctl restart postgresql@18-main
sudo ss -lntp | grep ':5432'
Check UFW state without enabling it blindly from a remote shell:
If UFW is already active, confirm there is no broad public allow rule for 5432/tcp. Add an explicit deny only when it does not conflict with an intentional private-network design:
sudo ufw deny 5432/tcp
sudo ufw status numbered
For an application on another VM, use Raff VPC, bind PostgreSQL only to the required private interface, and restrict pg_hba.conf to the application's private source range.
Verify: PostgreSQL listens only on loopback for this tutorial and no firewall rule exposes TCP 5432 to the public internet.
Step 8 — Run an authenticated CRUD test
Create a temporary PGPASSFILE so automated checks do not put the application password in command history:
read -rsp "PostgreSQL password: " DB_PASSWORD
echo
PGPASSFILE="$(mktemp)"
chmod 600 "$PGPASSFILE"
printf '127.0.0.1:5432:*:raffappuser:%s\n' "$DB_PASSWORD" > "$PGPASSFILE"
unset DB_PASSWORD
export PGPASSFILE
Run create, insert, update, and select operations:
psql -h 127.0.0.1 -U raffappuser -d raffapp <<'SQL'
CREATE TABLE tutorial_check (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
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';
SQL
Expected result includes:
name | status
----------------------+----------
raff-postgresql-test | verified
Confirm the application role owns the table:
psql -h 127.0.0.1 -U raffappuser -d raffapp -tAc \
"SELECT tableowner FROM pg_tables WHERE schemaname='public' AND tablename='tutorial_check';"
Expected output:
Leave the test row and table in place for the restore test in the next step.
Verify: the non-superuser application role creates, reads, updates, and owns its application table over authenticated TCP.
Step 9 — Back up and restore the database
Create a protected backup directory and a custom-format logical dump:
BACKUP_DIR="$HOME/postgresql-tutorial-backup"
mkdir -p "$BACKUP_DIR"
chmod 700 "$BACKUP_DIR"
pg_dump \
-h 127.0.0.1 \
-U raffappuser \
-d raffapp \
-F c \
-f "$BACKUP_DIR/raffapp.dump"
Confirm the dump is non-empty and readable:
test -s "$BACKUP_DIR/raffapp.dump" && echo "Backup file is non-empty"
pg_restore -l "$BACKUP_DIR/raffapp.dump" | head -n 20
Create an isolated restore database:
sudo -u postgres createdb -O raffappuser raffapp_restore_test
Restore the backup without replaying original ownership or privilege metadata:
pg_restore \
-h 127.0.0.1 \
-U raffappuser \
-d raffapp_restore_test \
--no-owner \
--no-privileges \
"$BACKUP_DIR/raffapp.dump"
Query the restored row:
psql -h 127.0.0.1 -U raffappuser -d raffapp_restore_test -c \
"SELECT name, status FROM tutorial_check WHERE name='raff-postgresql-test';"
Expected result:
raff-postgresql-test | verified
Remove only the disposable restore database and tutorial table:
sudo -u postgres dropdb --if-exists raffapp_restore_test
psql -h 127.0.0.1 -U raffappuser -d raffapp -c \
"DROP TABLE tutorial_check;"
Remove the temporary password file:
rm -f "$PGPASSFILE"
unset PGPASSFILE
A single pg_dump is not a complete production recovery strategy. For production recovery design, use PostgreSQL Replication vs Backups vs Snapshots.
Verify: the backup is non-empty, pg_restore can read it, the isolated restore succeeds, and the restored row contains verified.
Step 10 — Verify the complete PostgreSQL installation
Run the final server checks:
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 server_version;"
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo ss -lntp | grep ':5432'
Verify the application role and database still exist:
sudo -u postgres psql -tAc \
"SELECT rolname FROM pg_roles WHERE rolname='raffappuser';"
sudo -u postgres psql -tAc \
"SELECT datname FROM pg_database WHERE datname='raffapp';"
The installation is complete when all of these conditions are true:
postgresql@18-main is active and enabledSHOW server_version reports a supported PostgreSQL 18 maintenance releasepg_isready accepts local connections on port 5432raffappuser authenticates to raffapp- the authenticated CRUD test succeeded
- the logical backup restored successfully into an isolated database
- PostgreSQL listens only on loopback for this deployment
- no public firewall rule exposes port 5432
Verify: all commands above complete successfully and the final state matches every item in the checklist.
Cleanup (optional)
Use this cleanup only if raffapp and raffappuser were created solely for this tutorial.
Warning
The commands below permanently delete the raffapp database and its data. Back up anything you need before continuing.
Terminate remaining sessions, drop the database, and then drop the 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 queries return no rows.
Do not purge PostgreSQL packages or delete /var/lib/postgresql unless you have identified every cluster on the VM and confirmed that none contains data you need.
Troubleshooting
postgresql-18 has no installation candidate
Check the Ubuntu codename and PGDG repository:
. /etc/os-release
echo "$VERSION_CODENAME"
grep -R "apt.postgresql.org" /etc/apt/sources.list.d 2>/dev/null
sudo apt update
apt-cache policy postgresql-18
If PGDG is missing, rerun the official repository helper:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install postgresql installed PostgreSQL 16
That is the expected Ubuntu 24.04 distro path. Ubuntu's postgresql metapackage tracks PostgreSQL 16. Use PGDG and install postgresql-18 when PostgreSQL 18 is required.
Check existing clusters before removing anything:
The 18/main cluster is offline
Inspect cluster state and logs:
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo journalctl -u postgresql@18-main --no-pager -n 100
Check for a port conflict:
sudo ss -lntp | grep ':5432'
After correcting the reported issue, restart the service:
sudo systemctl restart postgresql@18-main
systemctl is-active postgresql@18-main
Password authentication fails
Confirm the role can log in:
sudo -u postgres psql -tAc \
"SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname='raffappuser';"
Inspect parsed HBA rules:
sudo -u postgres psql -P pager=off -x -c \
"SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules ORDER BY line_number;"
Reset the role password without putting it in shell history:
Then run:
ALTER ROLE raffappuser WITH LOGIN;
\password raffappuser
\q
Retest:
psql -h 127.0.0.1 -U raffappuser -d raffapp -W \
-c "SELECT current_user, current_database();"
PostgreSQL listens on a public interface
Find the active configuration file:
CONFIG_FILE="$(sudo -u postgres psql -tAc 'SHOW config_file;' | xargs)"
echo "$CONFIG_FILE"
For this tutorial, set:
listen_addresses = 'localhost'
Restart and verify:
sudo systemctl restart postgresql@18-main
sudo ss -lntp | grep ':5432'
pg_restore fails during the restore test
Confirm the dump is a valid custom-format archive:
file "$BACKUP_DIR/raffapp.dump"
pg_restore -l "$BACKUP_DIR/raffapp.dump" | head
Confirm the disposable restore database exists and is owned by raffappuser:
sudo -u postgres psql -tAc \
"SELECT datname || '|' || pg_get_userbyid(datdba) FROM pg_database WHERE datname='raffapp_restore_test';"
Drop and recreate only the disposable restore database before retrying if a previous failed restore left objects behind.
FAQ
Which PostgreSQL version is included with Ubuntu 24.04?
Ubuntu 24.04's standard postgresql package tracks PostgreSQL 16. The current Ubuntu 24.04 PostgreSQL 16 security package is 16.15 as of September 17, 2026.
What is the current PostgreSQL 18 version?
PostgreSQL 18.6 is the current stable maintenance release as of September 17, 2026. PostgreSQL 19 remains a development release and is not the production target used here.
Should PostgreSQL port 5432 be open to the internet?
No. Keep PostgreSQL on localhost when the application shares the VM. For separate servers, use private networking and allow only required private source ranges.
How should I create a PostgreSQL application user?
Create a dedicated login role, make it owner of the intended application database when that model fits, and set its password with \password so the secret is not stored in shell history.
Is pg_dump enough for production backups?
Not by itself. Production recovery also needs retention, off-server copies, monitoring, restore testing, and, when required, WAL archiving and point-in-time recovery.
Should I self-host PostgreSQL or use managed PostgreSQL?
Self-host when you need host-level control and can own patching, backups, monitoring, upgrades, and incidents. Use managed PostgreSQL when reducing operational ownership matters more.
Conclusion
You now have PostgreSQL 18 running on a Raff Ubuntu 24.04 VM with a dedicated application role, SCRAM authentication, private-by-default networking, verified CRUD operations, and a restore-tested logical backup.
For production planning, continue with PostgreSQL for SaaS Apps and Back Up PostgreSQL to Raff Object Storage with Restic. The replication-versus-backup guide and Raff Managed PostgreSQL option are linked alongside the relevant setup decisions above.
