• A cleanshaven middle-aged man wearing glasses and a dark green sweater
  • 4:53 am

PiSQL

Preface

In combination with the Raspberry Pi OS for a server and Using XCA to create private SSL certificates articles, this article describes setting up a Raspberry Pi Model B+ as a PostgreSQL server with external storage.

Caveat

This will only handle low volumes of requests per second, so is not a good choice for acting as the database for things like file synchronization programs (like Nextcloud, when used for file sync).

Preliminaries

Prepare the server and certificates

  1. Setup a Pi following the Raspberry Pi OS for a server article.
  2. Create internal SSL CA and server certificate and key by following the Using XCA to create private SSL certificates article.

Prepare storage for the database

  • Since we’re using a separate logical volume (partition) for the PostgreSQL data, we need to create the volume, create the filesystem and mount in the location where Debian will store the PostgreSQL data.

  1. sudo lvcreate -n postgres -L 60G vg1 — assuming the server setup described in the mentioned article. You also may want to use a different size than 60G depending on how much data you expect to have. If you’ve been following along and have space available for allocation you should be able to grow the volume and filesystem should the need arise.

  2. sudo mkfs.ext4 -E lazy_itable_init=0,lazy_journal_init=0 /dev/vg1/postgres — formats the volume fully before returning control (without the ‘-E’ options, Linux will use lazy init which will use background cycles and I/O to complete the task, but will return immediately. It is not as safe and the Pi doesn’t really have the resources to operate well with the background processing going on).

  3. sudo mkdir -p /var/lib/postgresql

  4. sudoedit /etc/fstab

    1. Add a line such as:

      /dev/vg1/postgres /var/lib/postgresql ext4 defaults,relatime,noexec 1 1

  5. sudo mount -a — should complete with no errors

  6. df -h should show /dev/mapper/vg1-postgres mounted on /var/lib/postgresql

Install PostgreSQL on the Pi

sudo apt install -y postgresql postgresql-client

sudo ss -ltpn should should show user postgres with localhost listeners on port 5432 (e.g. 127.0.0.1:5432 and [::1]:5432 should be reported in that command).

Configure PostgreSQL

Stop PostgreSQL while configuring

sudo systemctl stop postgresql

Configure SCRAM-SHA-256 passwords

  1. sudoedit /etc/postgresql/11/main/conf.d/05-scram-password-encryption.conf
  2. Add a line such as password_encryption = scram-sha-256
  3. Save and exit

Configure SSL

The server private key and server certificate are the ones you created following the XCA guide mentioned above. You will need to substitute with your actual names of course.

  1. Copy the server private key into /etc/ssl/private/pisql.use-your-domain.example.com.key
  2. sudo chgrp ssl-cert /etc/ssl/private/pisql.use-your-domain.example.com.key
  3. sudo chmod 0640 /etc/ssl/private/pisql.use-your-domain.example.com.key
  4. Copy the server certificate into /etc/ssl/certs/pisql.use-your-domain.example.com.crt
  5. Tell PostgreSQL to use them when doing SSL:

    1. sudoedit /etc/postgresql/11/main/conf.d/10-ssl-cert-key.conf and add lines such as:

      ssl_cert_file = '/etc/ssl/certs/pisql.use-your-domain.example.com.crt'
ssl_key_file = '/etc/ssl/private/pisql.use-your-domain.example.com.key'

    2. Save and exit

  6. Verify the SSL configuration:
    1. sudo systemctl start postgresql
    2. sudo pg_conftool show ssl_cert_file — Should report the certificate filename you configured
    3. sudo pg_conftool show ssl_key_file — Should report key filename you configured
    4. sudo systemctl stop postgresql

Configure SSL client verification

  1. Copy the CA certificate you created to /etc/postgresql-common/root.crt and make sure is owned by root:root and has permissions 0644.

Listen on all interfaces

  1. sudoedit /etc/postgresql/11/main/conf.d/50-listen-all-interfaces.conf and a line such as:

    listen_addresses = '*'

  2. Save and exit

  3. Verify if you wish (e.g. start postgresql, check listening ports using ss and configuration options using pg_conftool)

  4. Allow traffic in through the firewall

    sudo ufw allow in on eth0 proto tcp from any to any port 5432
    • Of course, if you know what you are doing, you can be more restrictive.

Configure PostgreSQL to allow incoming connections

It is not enough to listen for connections, PostgreSQL also needs to be configured what connections to accept.

sudoedit /etc/postgresql/11/main/pg_hba.conf

Enter lines such as:

hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::/0 scram-sha-256

This tell PostgreSQL to all allow SSL network connections for any database and any user from any address provided the user is able to authenticate using their ‘scram-sha-256’ encoded password.

Verify PostgreSQL configuration

Start PostgreSQL and verify status

sudo systemctl start postgresql sudo systemctl status postgresql — it should report ‘active (exited)’ and ‘status=0/SUCCESS`

The following commands should complete without errors:

  1. sudo su - postgres
  2. createuser --createdb --pwprompt testuser
  3. createdb -U testuser -h \<address-of-your-pi> testdb "\c sslmode=require"
  4. exit
  5. psql -U testuser postgresql://\<address-of-your-pi>:5432/testdb?sslmod=require
  6. \l
  7. \q
  8. sudo su - postgres
  9. dropdb testdb
  10. dropuser testuser
  11. exit

Configure backups

  1. sudo su - postgres

  2. mkdir restic-files

  3. cd restic-files

  4. chmod 700 .

  5. touch password-file

  6. chmod 600 password-file

  7. sensible-editor password-file

  8. In the editor, add a strong password (e.g. 30 alphanumeric and special characters), then save and close (having a file with the password not ideal, but avoiding it is rather complicated, and out of scope for this article).

  9. If using SFTP for backups, create a passwordless SSH keypair using:

    1. ssh-keygen -t rsa -f restic-postgres@piserver -C restic-postgres@piserver -N ''
    2. Copy the contents of restic-postges@piserver.pub to you destination’s `authorized_keys`` file.

  10. Initialize your destination restic repository

  11. Do an initial backup

    1. (assuming you have configured, ~/.ssh/config so that restic-postgres@backupserver.example.com uses the restic-postgres@piserver created above:

      pg_dumpall -c --if-exists | restic -r sftp:restic-postgres@backupserver.example.com:/path/to/repo  --password-file ./password-file backup --stdin --stdin-filename postgresql.sql

  12. Now create a crontab entry to do this every four hours:

    1. crontab -e

    2. Add an entry such as:

      23 */4 *   *   *  pg_dumpall -c --if-exists 2>/dev/null | restic -r sftp:restic@backupserver.example.com:/path/to/repo --password-file ./password-file backup --stdin --stdin-filename postgresql.sql --cleanup-cache --quiet 2>&1 | logger -t restic

    3. Save and exit the editor

  13. exit

Server ready

Your server should now be ready for use.

See Also