Using Postgres on Home Server Deployments with Docker Compose

When running containerized services on a home server, many call for a Postgres database in the cluster. It’s helpful if you spend a little time configuring this container for ease of backups and other maintenance. In this post, we’ll set up PostgreSQL as a service with docker-compose, manage environment configurations, establish a robust backup routine to a NAS volume, and cover the restoration process from these backups to ensure your data remains secure and recoverable.

Prerequisites

This guide assumes you have:

• a working docker / docker-compose (or similar) setup on your home server
• basic understanding of PostgreSQL operations

Step 1: The Docker Compose Configuration

Here’s a simple docker-compose.yml to get us started:

version: '3'

services:
  db:
    image: postgres:16-alpine
    restart: unless-stopped
    shm_size: 256mb
    healthcheck:
      test: ['CMD', 'pg_isready', '-U', "${POSTGRES_USER}"]
      interval: 10s
      timeout: 5s
      retries: 5
    volumes:
      - ${POSTGRES_DATA_DIR}:/var/lib/postgresql/data
      - ${BACKUP_PATH}:/backups
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}

This defines a db service using the latest version of Postgres 16, including volume mounts for data persistence and backups.

Step 2: The Environment File

Create a .env file in the same directory:

# PostgreSQL Variables
POSTGRES_DB=mydatabase
POSTGRES_USER=myuser
POSTGRES_PASSWORD=mypassword

# SSD mount for working postgresql data
POSTGRES_DATA_DIR=/path/to/postgres/data/dir

# NAS mount for persisted backups
BACKUP_PATH=/mnt/nas/PostgreSQLBackups

The .env file stores our configuration, keeping sensitive data out of the docker-compose.yml.

Step 3: The Backup Script

The backup.sh script will be scheduled to run daily:

#!/bin/bash

# Load environment variables
source .env

# Format for the backup filename
BACKUP_DATE=$(date +%F)
BACKUP_FILENAME="/backups/${POSTGRES_DB}_${BACKUP_DATE}.dump"

# Perform the backup
docker-compose exec db pg_dump -U ${POSTGRES_USER} ${POSTGRES_DB} > ${BACKUP_FILENAME}

# Optional: Clean up old backups
# find /backups/* -mtime +30 -exec rm {} \;

Make the script executable: chmod +x backup.sh.

Step 4: Scheduling the Backup with Cron

Add this line to your crontab (crontab -e) on your home server to run the backup at 3 a.m. daily:

0 3 * * * /path/to/backup.sh

Step 5: The Restoration Script

For restoring a database, use restore.sh:

#!/bin/bash

# Load environment variables
source .env

# Ensure there's a filename argument
if [ "$#" -ne 1 ]; then
  echo "Usage: $0 <backup_filename>"
  exit 1
fi

BACKUP_FILENAME=$1

# Check the backup file exists
if [ ! -f "$BACKUP_PATH/$BACKUP_FILENAME" ]; then
  echo "Backup file not found: $BACKUP_PATH/$BACKUP_FILENAME"
  exit 1
fi

# Restore the database
cat $BACKUP_PATH/$BACKUP_FILENAME | docker-compose exec db psql -U $POSTGRES_USER -d $POSTGRES_DB

echo "Database restored from $BACKUP_FILENAME"

Run a restore like this:

./restore.sh mydatabase_YYYY-MM-DD.dump

Conclusion

By leveraging Docker Compose for PostgreSQL service management and implementing NAS-based backups, we create a reliable and streamlined workflow. Adjust paths and variables to suit your setup, and always test your procedures regularly.

---

Disclaimer: This blog post is for educational purposes only. Test your setup in a non-production environment before implementing.