Setup Production Postgres Raspberry Pi 5

This guide outlines steps to turn a Raspberry Pi 5 with 16 GB RAM into a production-ready PostgreSQL server using Ubuntu Server.

1. Prerequisites

• Raspberry Pi 5 (4/8/16 GB RAM)
• Ubuntu Server 24.04 or newer installed
• External SSD for data storage (recommended)
• Static IP configured
• SSH access

2. System Preparation

Update and Upgrade

sudo apt update && sudo apt upgrade -y

Set Hostname and Timezone

sudo hostnamectl set-hostname postgres-server
sudo timedatectl set-timezone America/Chicago

3. Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y

4. Configure PostgreSQL for Production

Tune postgresql.conf

Located at /etc/postgresql/*/main/postgresql.conf :

• listen_addresses = '*'
• shared_buffers = 4GB
• work_mem = 16MB
• maintenance_work_mem = 512MB
• effective_cache_size = 12GB
• wal_level = replica
• max_wal_size = 2GB

Enable Remote Access

Edit pg_hba.conf :

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

Update authentication methods:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256

# IPv4 local connections:
host    all             all             192.168.1.0/24          md5

# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256e

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

# Allow local TCP (IPv4) connections from localhost with md5 (password) auth
host    all             all              127.0.0.1/32           md5

Replace 192.168.1.0/24 with your LAN subnet. Avoid 0.0.0.0/0 unless you use a firewall or VPN.

Reload PostgreSQL

sudo systemctl restart postgresql

Then check the status to ensure settings are correct:

sudo systemctl status postgresql

5. Move Data to External SSD

TODO: Explain how you got the Pi hat to make this super fast DB server using NVMe

sudo systemctl stop postgresql
sudo rsync -av /var/lib/postgresql /mnt/datadrive/
sudo nano /etc/postgresql/*/main/postgresql.conf
# Set: data_directory = '/mnt/datadrive/postgresql/<YOUR_VERSION>/main'
sudo chown -R postgres:postgres /mnt/datadrive/postgresql
sudo systemctl start postgresql

Then check the status again:

sudo systemctl status postgresql

If the light's green, we're good to go. 🟢

6. Create Database and User

Create a User

sudo -u postgres createuser --interactive

This command will guide you through setting up a user. This user is tied to a specific database. This limits the risk to other applications running on the database server, as each should have its own user and password.

Create a Database Owned by That User

Replace <app_user> and <production_db> with the appropriate values:

sudo -u postgres createdb -O <app_user> <production_db>

7. Enable Password Authentication & Clean Up Roles

Set or Update Passwords for Active Users

sudo -u postgres psql

Then:

ALTER USER app_user WITH PASSWORD 'StrongSecurePass123';
ALTER USER postgres WITH PASSWORD 'AnotherSecurePass!';

Identify and Remove Unused Roles

List All Roles

\du

Take note of any unneeded users or roles .

Disable Login for Unused Roles

Ensure the unneeded users are not able to login.

ALTER ROLE test_user NOLOGIN;

Drop Unused Roles

Or, if you prefer to drop the unused roles. But make sure they aren't needed first:

REASSIGN OWNED BY old_user TO new_owner;
DROP OWNED BY old_user;
DROP ROLE old_user;

Test Connection

From a remote machine:

psql -h 192.168.1.<xxx> -U <app_user> -d <production_db>

You should be prompted for the password and successfully connect if all is configured correctly.

8. Set Up Backups

Install pgbackrest or use pg_dump :

sudo -u postgres pg_dump <production_db> > /mnt/share_3/external_ssd/db_backups/production_db_$(date +%F).sql

The production_db should be replaced with the name of the database you want to back up. And replace /mnt/share_3/external_ssd/db_backups/production_db_ with the drive to where you would like the database backup saved.

Create a Backup Script

sudo vi /usr/local/bin/pg_backup.sh

And add the following:

#!/bin/bash

# Usage: pg_backup.sh <database_name|--all> <backup_directory> [days_to_keep] [--compress]

DB_NAME="$1"
BACKUP_DIR="$2"
DAYS_TO_KEEP="${3:-60}"
COMPRESS="$4"

# Validate input
if [[ -z "$DB_NAME" || -z "$BACKUP_DIR" ]]; then
  echo "Usage: $0 <database_name|--all> <backup_directory> [days_to_keep] [--compress]"
  exit 1
fi

# Ensure backup directory exists
mkdir -p "$BACKUP_DIR"

DATE_TAG="$(date +%F)"
FILENAME=""
DUMP_CMD=""

# Decide between pg_dump and pg_dumpall
if [[ "$DB_NAME" == "--all" ]]; then
  FILENAME="all_dbs_${DATE_TAG}.sql"
  DUMP_CMD="sudo -u postgres pg_dumpall"
else
  FILENAME="${DB_NAME}_${DATE_TAG}.sql"
  DUMP_CMD="sudo -u postgres pg_dump $DB_NAME"
fi

# If compression is requested
if [[ "$COMPRESS" == "--compress" ]]; then
  FILENAME="${FILENAME}.gz"
  $DUMP_CMD | gzip > "${BACKUP_DIR}/${FILENAME}"
else
  $DUMP_CMD > "${BACKUP_DIR}/${FILENAME}"
fi

# Cleanup old backups
# (matches either dbname_*.sql or all_dbs_*.sql depending on mode)
PATTERN="*.sql"
[[ "$COMPRESS" == "--compress" ]] && PATTERN="$PATTERN.gz"

find "$BACKUP_DIR" -type f -name "$PATTERN" -mtime +$DAYS_TO_KEEP -delete

The script can be used with the following command:

pg_backup.sh <database_name|--all> <backup_directory> [days_to_keep] [--compress]

For example:

bash /usr/local/bin/pg_backup.sh --all /mnt/share_3/external_ssd/db_backups 90 --compress

Or for a specific database:

bash /usr/local/bin/pg_backup.sh memory /mnt/share_3/external_ssd/db_backups 90 --compress

Make the file executable:

sudo chmod +x /usr/local/bin/pg_backup.sh

To further strengthen, we could add encryption at rest for the databases. I'll leave that for the user.

Let's create a cron entry to run this script periodically.

sudo crontab -e

Then add the entry:

0 2 * * * /usr/local/bin/pg_backup.sh --all /mnt/share_3/external_ssd/db_backups 90 --compress >> /var/log/pg_backup.log 2>&1

9. Enable Logging and Monitoring

• Enable logging in postgresql.conf
• Use logrotate to manage log size
• Monitor with pg_stat_activity , pg_top , or Prometheus exporters

10. Secure the Server

• Enable UFW:

sudo ufw allow ssh
sudo ufw allow 5432/tcp
sudo ufw enable

• Disable password login via SSH (use keys)
• Regularly update:

sudo apt update && sudo apt upgrade -y

11. Optional: Install pgAdmin (Remote Management)

Consider installing pgAdmin on a separate device for GUI management.

With a solid SSD, tuned configs, and tight access controls, the Raspberry Pi 5 can act as a low-power, high-efficiency PostgreSQL server for home labs, dev work, or small-scale production.