PostgreSQL is the open-source database we reach for most often: reliable, fast and rich in features. This guide installs PostgreSQL 17 on Ubuntu 24.04 LTS, secures it, creates a database for your application, allows remote connections safely and sets up backups. Every command was written for a fresh server; read the explanation before running each one.
Which version should you install?
Each major version is supported for five years. Pick the newest one your application, drivers and hosting support.
| Version | Released | Supported until | Choose it when |
|---|---|---|---|
| 18 | 25 September 2025 | 14 November 2030 | Starting something new |
| 17 | 26 September 2024 | 8 November 2029 | Your tools don't support 18 yet |
| 16 | 14 September 2023 | 9 November 2028 | You want Ubuntu's own package |
You are reading the guide for PostgreSQL 17, released on 26 September 2024 and supported until 8 November 2029.
Prerequisites
- A server running Ubuntu 24.04 with at least 1 GB of RAM (2 GB or more for production).
- A user with
sudorights. Don't work asroot. - The UFW firewall enabled, with SSH allowed:
sudo ufw allow OpenSSH && sudo ufw enable.
Step 1 — Add the official PostgreSQL repository
Ubuntu 24.04's own repositories only include PostgreSQL 16. Version 17 comes from the PostgreSQL project's own repository (PGDG), which is signed and maintained by the PostgreSQL developers. The postgresql-common package includes a script that adds it safely:
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shThe script asks you to press Enter, then imports the repository's signing key and adds the source for your Ubuntu release. The same steps work on Ubuntu 22.04 and 26.04.
Step 2 — Install PostgreSQL 17
Install the server with its version number in the package name. That pins you to 17: a later apt upgrade installs new 17.x security releases but never jumps to a new major version by surprise.
sudo apt install -y postgresql-17Step 3 — Check that the server is running
The package creates a database cluster called main and starts it straight away. List the clusters:
pg_lsclustersVer Cluster Port Status Owner Data directory Log file
17 main 5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.logNote the paths — every version has its own:
- Configuration:
/etc/postgresql/17/main/postgresql.conf - Client access rules:
/etc/postgresql/17/main/pg_hba.conf - Data:
/var/lib/postgresql/17/main
Make sure it starts after a reboot:
sudo systemctl enable --now postgresqlStep 4 — Connect with psql
Installation creates an operating-system user and a database superuser, both called postgres. Open the PostgreSQL shell as that user:
sudo -u postgres psqlConfirm the version, then quit with \q:
SELECT version();PostgreSQL 17.x (Ubuntu 17.x-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc …Step 5 — Create a user and a database for your application
Never connect your application as postgres. Create a separate role with a strong password and a database it owns:
sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdbPasswords are stored with SCRAM-SHA-256, the secure default since PostgreSQL 14. Test the new login over a local TCP connection:
psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user, current_database();"Step 6 — Allow remote connections (only if you need them)
By default PostgreSQL listens only on localhost. If your application runs on the same server, skip this step — it is the safest setup.
To allow one application server (here 203.0.113.10) to connect, edit /etc/postgresql/17/main/postgresql.conf:
listen_addresses = 'localhost,10.0.0.5' # this server's private IPThen add one rule to the end of /etc/postgresql/17/main/pg_hba.conf. It allows only that database, that user and that address, over an encrypted connection:
hostssl appdb appuser 203.0.113.10/32 scram-sha-256Restart PostgreSQL and open the port only for that address:
sudo systemctl restart postgresql
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcpStep 7 — Basic tuning
The defaults are deliberately small. For a server that runs mainly PostgreSQL, these are sensible starting points in postgresql.conf (example for 8 GB of RAM):
shared_buffers = 2GB # about 25% of RAM
effective_cache_size = 6GB # about 75% of RAM
work_mem = 16MB # per sort/hash operation, so keep it modest
maintenance_work_mem = 512MBRestart after changing shared_buffers. Measure with your real queries before tuning further.
Step 8 — Back up your data
Take a compressed logical backup with pg_dump, and test that it restores:
sudo -u postgres pg_dump -Fc appdb > appdb-$(date +%F).dump
sudo -u postgres createdb appdb_restore_test
sudo -u postgres pg_restore -d appdb_restore_test appdb-$(date +%F).dumpRun backups on a schedule and copy them to another server or to object storage — a backup on the same disk is not a backup.
For large databases, PostgreSQL 17 and later can also take incremental physical backups with pg_basebackup --incremental and merge them with pg_combinebackup.
Upgrading to a newer major version later
Debian and Ubuntu packages make major upgrades manageable with pg_upgradecluster. Install the new version, remove the empty cluster it creates, then upgrade your existing cluster (example: from 17 to a newer NEW):
sudo apt install -y postgresql-NEW
sudo pg_dropcluster NEW main --stop
sudo pg_upgradecluster -m upgrade 17 mainTest your application on the new version, then remove the old cluster with sudo pg_dropcluster 17 main. Always take a full backup first.
Conclusion
You now have PostgreSQL 17 running on Ubuntu 24.04 with a dedicated application user, safe remote access rules, basic tuning and tested backups. Next steps: set up monitoring (for example pg_stat_statements), automate backups, and plan an upgrade well before 8 November 2029.