---
title: "How to Install and Configure PostgreSQL on Ubuntu 24.04"
description: "Install PostgreSQL 16 on Ubuntu 24.04, create a database and user, allow secure remote connections, tune memory settings and schedule daily backups."
category: "Database Management"
language: en
source: https://cubepath.com/docs/database-management/postgresql-installation-and-configuration
updated: 2026-09-25
---

# How to Install and Configure PostgreSQL on Ubuntu 24.04

PostgreSQL is an open-source relational database known for strict SQL compliance, reliable transactions and extensions such as PostGIS and pgvector. In this tutorial you will install PostgreSQL 16 on Ubuntu 24.04, create a database and an application user, allow encrypted connections from a trusted host, apply basic memory tuning, and schedule daily backups with `pg_dump` and a systemd timer.

## Prerequisites

To follow this guide you need:

- A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with at least 1 GB of RAM (2 GB or more for production).
- A non-root user with `sudo` privileges.
- UFW enabled with SSH allowed, if you plan to accept remote connections.
- The IP address of the application server that will connect to the database, referred to as `your_app_server_ip`, if you need remote access.

## Step 1 - Installing PostgreSQL

Ubuntu 24.04 ships PostgreSQL 16 in its main repository, which receives security updates for the life of the release. Update the package index and install the server together with the `contrib` extensions:

```bash
sudo apt update
sudo apt install postgresql postgresql-contrib
```

The package creates a cluster called `main`, starts it and enables it at boot. Check the cluster with the Debian/Ubuntu helper `pg_lsclusters`:

```bash
pg_lsclusters
```

```output
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
```

Confirm the systemd service is active:

```bash
sudo systemctl status postgresql
```

> Note: If you need a newer major version (17 or 18), add the official PGDG repository with `sudo apt install postgresql-common` followed by `sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh`, then install `postgresql-17` or `postgresql-18`. The paths in this guide change from `16` to the version you install.

On Ubuntu the configuration lives in `/etc/postgresql/16/main/` and the data in `/var/lib/postgresql/16/main/`:

| File | Purpose |
|------|---------|
| `postgresql.conf` | Server settings: listen address, memory, logging |
| `pg_hba.conf` | Which users can connect, from where, and how they authenticate |
| `conf.d/` | Drop-in directory read after `postgresql.conf` |

## Step 2 - Connecting as the postgres role

Installation creates a Linux account and a database superuser, both named `postgres`. Local connections use `peer` authentication, which maps your Linux user to the database role of the same name, so you connect by switching to that account:

```bash
sudo -u postgres psql
```

Inside `psql`, check the server version:

```sql
SELECT version();
```

```output
 PostgreSQL 16.x (Ubuntu 16.x-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc ...
```

Type `\q` to leave `psql`. Keep the `postgres` superuser for administration only and create a dedicated role for each application.

## Step 3 - Creating a database and an application user

Create a role called `appuser` that can log in with a password. The `--pwprompt` option asks for the password so it does not end up in your shell history:

```bash
sudo -u postgres createuser --pwprompt appuser
```

Create a database called `appdb` owned by that role:

```bash
sudo -u postgres createdb --owner=appuser appdb
```

Since PostgreSQL 15, ordinary users cannot create objects in the `public` schema of a database they do not own, so making `appuser` the owner is what lets the application create its tables.

Test the login over TCP, which uses password authentication (`scram-sha-256` by default in PostgreSQL 16):

```bash
psql -h 127.0.0.1 -U appuser -d appdb -c '\conninfo'
```

```output
Password for user appuser:
You are connected to database "appdb" as user "appuser" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```

The SSL line appears because Ubuntu enables TLS by default using the server's self-signed "snakeoil" certificate.

## Step 4 - Allowing remote connections

Skip this step if the application runs on the same server. Otherwise, you need to change two things: the address PostgreSQL listens on, and the `pg_hba.conf` rule that allows the remote host.

Create a drop-in file so your changes stay separate from the packaged `postgresql.conf`:

```bash
sudo nano /etc/postgresql/16/main/conf.d/10-network.conf
```

```ini
listen_addresses = 'localhost,your_server_private_ip'
```

Replace `your_server_private_ip` with the address the application reaches, preferably a private network address. Use `'*'` only if you need every interface.

Next, open the client authentication file:

```bash
sudo nano /etc/postgresql/16/main/pg_hba.conf
```

Add this line at the end. `hostssl` only matches encrypted connections, so plain-text logins from that host are rejected:

```text
hostssl  appdb  appuser  your_app_server_ip/32  scram-sha-256
```

Restart PostgreSQL, because `listen_addresses` only changes on restart:

```bash
sudo systemctl restart postgresql
```

Confirm it listens on the new address:

```bash
sudo ss -tlnp | grep 5432
```

```output
LISTEN 0  200  10.0.0.5:5432   0.0.0.0:*  users:(("postgres",pid=4121,fd=7))
LISTEN 0  200  127.0.0.1:5432  0.0.0.0:*  users:(("postgres",pid=4121,fd=6))
```

Allow port 5432 only from the application server:

```bash
sudo ufw allow from your_app_server_ip to any port 5432 proto tcp
```

From the application server, with the `postgresql-client` package installed, test the connection and require TLS:

```bash
psql "host=your_server_private_ip dbname=appdb user=appuser sslmode=require" -c '\conninfo'
```

> Important: `sslmode=require` encrypts the connection but does not verify the server certificate. For connections over the public internet, install a certificate from a trusted CA in `ssl_cert_file` and `ssl_key_file` and connect with `sslmode=verify-full`.

## Step 5 - Tuning memory and logging

PostgreSQL's defaults are sized for very small machines. The following values are a reasonable starting point for a server dedicated to PostgreSQL with 4 GB of RAM; scale them with your memory:

| Setting | Rule of thumb | 4 GB server |
|---------|---------------|-------------|
| `shared_buffers` | 25% of RAM | `1GB` |
| `effective_cache_size` | 50-75% of RAM | `3GB` |
| `work_mem` | Per sort/hash operation, keep it small | `16MB` |
| `maintenance_work_mem` | Used by VACUUM and CREATE INDEX | `256MB` |

Create a second drop-in file:

```bash
sudo nano /etc/postgresql/16/main/conf.d/20-tuning.conf
```

```ini
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

# SSD storage: make index scans cheaper for the planner
random_page_cost = 1.1

# Log slow queries and useful events
log_min_duration_statement = 500ms
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
```

`work_mem` applies per operation, and one query can use several at once, so multiplying it by `max_connections` (100 by default) gives the worst case. Raise it for specific reporting sessions with `SET work_mem` instead of globally.

Restart to apply `shared_buffers`, then check the values PostgreSQL loaded:

```bash
sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;" -c "SHOW effective_cache_size;"
```

```output
 shared_buffers
----------------
 1GB
(1 row)

 effective_cache_size
----------------------
 3GB
(1 row)
```

If PostgreSQL fails to start after a change, the reason is in the cluster log:

```bash
sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log
```

Autovacuum is enabled by default and should stay on: it reclaims space from updated and deleted rows and keeps planner statistics current.

## Step 6 - Scheduling daily backups

`pg_dump` creates a consistent logical backup of one database while it stays online. The custom format (`-Fc`) is compressed and lets `pg_restore` restore selected tables.

Create a backup directory owned by `postgres`:

```bash
sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
```

Create the backup script:

```bash
sudo nano /usr/local/bin/pg-backup
```

```bash
#!/usr/bin/env bash
set -euo pipefail

backup_dir="/var/backups/postgresql"
retention_days=7
stamp="$(date +%Y%m%d-%H%M%S)"

# Dump every non-template database in custom format
for db in $(psql -AtX -c "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
    pg_dump -Fc -f "${backup_dir}/${db}-${stamp}.dump" "$db"
done

# Roles and other global objects are not included in pg_dump
pg_dumpall --globals-only > "${backup_dir}/globals-${stamp}.sql"

find "$backup_dir" -type f -mtime +"$retention_days" -delete
```

Make it executable:

```bash
sudo chmod 755 /usr/local/bin/pg-backup
```

Create a systemd service that runs it as `postgres`:

```bash
sudo nano /etc/systemd/system/pg-backup.service
```

```ini
[Unit]
Description=Dump PostgreSQL databases

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-backup
```

And a timer that triggers it every night:

```bash
sudo nano /etc/systemd/system/pg-backup.timer
```

```ini
[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

[Install]
WantedBy=timers.target
```

Enable the timer and run the service once to test it:

```bash
sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
sudo systemctl start pg-backup.service
sudo ls -lh /var/backups/postgresql
```

```output
-rw-r--r-- 1 postgres postgres 3.1K Sep 25 10:12 appdb-20260925-101204.dump
-rw-r--r-- 1 postgres postgres  512 Sep 25 10:12 globals-20260925-101204.sql
-rw-r--r-- 1 postgres postgres 1.2K Sep 25 10:12 postgres-20260925-101204.dump
```

Check the next run with `systemctl list-timers pg-backup.timer`. Copy the backups off the server as well, for example to object storage, because a local copy does not survive losing the disk.

### Restoring a backup

To restore into an empty database, create it and use `pg_restore`:

```bash
sudo -u postgres createdb --owner=appuser appdb_restored
sudo -u postgres pg_restore -d appdb_restored /var/backups/postgresql/appdb-20260925-101204.dump
```

Test a restore regularly: a backup you have never restored is not a verified backup.

## Troubleshooting

**`psql: error: connection to server ... failed: FATAL: Peer authentication failed for user "appuser"`**: you connected through the Unix socket, which uses `peer` authentication. Add `-h 127.0.0.1` to use password authentication over TCP.

**`no pg_hba.conf entry for host "x.x.x.x", user "appuser", database "appdb", SSL off`**: the client did not use TLS, or its IP does not match the `hostssl` rule. Connect with `sslmode=require` and check the address in `pg_hba.conf`. Changes to `pg_hba.conf` only need `sudo systemctl reload postgresql`.

**Connection times out from the application server**: check that `listen_addresses` includes the right IP (`ss -tlnp`), that UFW allows the source (`sudo ufw status`), and that no provider-level firewall blocks port 5432.

**PostgreSQL does not start after tuning**: an oversized `shared_buffers` or a typo in a `conf.d` file prevents startup. Read the cluster log shown in Step 5, fix the value and restart.

## Conclusion

You now have PostgreSQL 16 running on Ubuntu 24.04 with a dedicated application database, TLS-only remote access limited to one host, sensible memory settings and nightly backups. As next steps, consider enabling the `pg_stat_statements` extension to find your most expensive queries, adding a connection pooler such as PgBouncer for applications that open many connections, and setting up streaming replication for high availability.