PostgreSQL for Homelabs: Complete Database Setup Guide

Introduction

PostgreSQL (often called Postgres) is the world’s most advanced open-source relational database. It’s the backbone of countless applications—from small personal projects to enterprise systems handling billions of transactions. For homelabbers, PostgreSQL offers the perfect combination of power, reliability, and flexibility without enterprise licensing costs.

This comprehensive guide will take you from zero to a production-ready PostgreSQL deployment in your homelab. You’ll learn not just how to run Postgres, but how to manage it like a professional database administrator.

What You’ll Learn:

Deploy PostgreSQL with Docker in single-node and replicated configurations
Install and configure pgAdmin for graphical database management
Implement comprehensive backup and recovery strategies
Optimize performance for your specific workloads
Secure your database with authentication, encryption, and network isolation
Set up replication for high availability
Monitor database health and performance metrics
Integrate PostgreSQL with popular homelab applications

Prerequisites:

Docker and Docker Compose installed
Basic understanding of SQL and relational databases
Command-line familiarity
Homelab server with minimum 2GB RAM, 20GB storage
Recommended: 4GB+ RAM for production workloads

Part 1: Understanding PostgreSQL

What is PostgreSQL?

PostgreSQL is an object-relational database management system (ORDBMS) that combines the traditional relational database model with object-oriented features. First released in 1996, it has evolved into one of the most feature-rich and standards-compliant databases available.

Key Characteristics:

ACID Compliant: Guarantees data integrity with Atomicity, Consistency, Isolation, Durability
MVCC Architecture: Multi-Version Concurrency Control allows high concurrent access
Extensible: Add custom functions, data types, and operators
Standards Compliant: Implements SQL:2016 standard
Open Source: PostgreSQL License (similar to MIT/BSD)
Cross-Platform: Linux, Windows, macOS, BSD

Why PostgreSQL for Homelabs?

Technical Advantages:

Feature-Rich Without Cost
- Advanced indexing (B-tree, Hash, GiST, SP-GiST, GIN, BRIN)
- Full-text search built-in
- JSON/JSONB support for document storage
- Foreign data wrappers to query remote databases
- Window functions, CTEs, and advanced SQL features
Data Integrity
- Referential integrity with foreign keys
- Constraints (CHECK, UNIQUE, NOT NULL)
- Triggers and stored procedures
- Transactional DDL (can rollback schema changes)
Scalability
- Handles databases from KB to TB+
- Connection pooling with PgBouncer
- Horizontal scaling with partitioning
- Streaming replication for read replicas
Extensibility
- TimescaleDB for time-series data
- PostGIS for geographic data
- pgvector for AI/ML vector search
- Custom extensions for specific needs

Common Homelab Use Cases:

Application Backend: Nextcloud, Gitlab, Zabbix, Grafana, Authentik
Home Automation: Home Assistant, Node-RED
Media Management: PhotoPrism, Immich
Development: Local development databases
Analytics: Data warehousing and reporting
Monitoring: Storing metrics and logs

PostgreSQL vs. Alternatives

Feature	PostgreSQL	MySQL/MariaDB	MongoDB	SQLite
License	PostgreSQL	GPL/BSL	SSPL	Public Domain
Type	Relational	Relational	Document	Embedded
ACID	Full	Full	Configurable	Full
Replication	Advanced	Good	Built-in	None
JSON Support	Excellent	Good	Native	Limited
Complex Queries	Excellent	Good	Limited	Good
Concurrent Writes	Excellent	Good	Excellent	Limited
Learning Curve	Moderate	Easy	Easy	Easy
Best For	Complex apps	Web apps	Flexible schema	Single-user apps

Architecture Overview

PostgreSQL Process Model:

PostgreSQL Instance
├── Postmaster (main process)
│   ├── Backend processes (one per connection)
│   ├── Background workers
│   │   ├── WAL Writer (Write-Ahead Log)
│   │   ├── Checkpointer
│   │   ├── Autovacuum daemon
│   │   ├── Stats collector
│   │   └── Background writer
│   └── Replication processes (if configured)
├── Shared memory
│   ├── Shared buffers (data cache)
│   └── WAL buffers
└── Data directory
    ├── Base (database files)
    ├── pg_wal (transaction logs)
    ├── pg_xlog (archive logs)
    └── Configuration files

How PostgreSQL Handles Queries:

Client connection → Postmaster → Backend process created
    ↓
Parse SQL query → Query planner → Execution plan
    ↓
Execute plan → Access shared buffers → If not cached, read from disk
    ↓
Modify data → Write to WAL first → Then to shared buffers
    ↓
Commit → Flush WAL to disk → Return success to client
    ↓
Background writer → Eventually writes dirty buffers to disk

Part 2: Installing PostgreSQL with Docker

Quick Start Deployment

Create project directory:

mkdir -p ~/postgres-homelab
cd ~/postgres-homelab

Create docker-compose.yml:

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    hostname: postgres
    restart: unless-stopped

    environment:
      # Required: Set superuser password
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-ChangeMe123!}
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres

      # Optional: Performance tuning
      POSTGRES_SHARED_BUFFERS: 256MB
      POSTGRES_EFFECTIVE_CACHE_SIZE: 1GB
      POSTGRES_WORK_MEM: 16MB

      # Timezone
      TZ: America/New_York
      PGTZ: America/New_York

    ports:
      - "5432:5432"

    volumes:
      # Data persistence
      - postgres-data:/var/lib/postgresql/data

      # Custom configuration (optional)
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro

      # Initialization scripts
      - ./init-scripts:/docker-entrypoint-initdb.d:ro

      # Backups
      - ./backups:/backups

    # Custom command (if using custom config)
    # command: postgres -c config_file=/etc/postgresql/postgresql.conf

    networks:
      - postgres-network

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 30s

    # Resource limits
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
        reservations:
          cpus: '0.5'
          memory: 512M

volumes:
  postgres-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /mnt/storage/postgres-data

networks:
  postgres-network:
    driver: bridge

Create environment file (.env):

# Generate secure password
POSTGRES_PASSWORD=$(openssl rand -base64 32)

# Save to .env
cat > .env <<EOF
POSTGRES_PASSWORD=$POSTGRES_PASSWORD
EOF

chmod 600 .env

echo "PostgreSQL password: $POSTGRES_PASSWORD"
echo "Save this password securely!"

Deploy PostgreSQL:

# Start PostgreSQL
docker-compose up -d

# View logs
docker-compose logs -f postgres

# Verify it's running
docker-compose ps
docker exec postgres pg_isready -U postgres

Connect to PostgreSQL:

# Using psql inside container
docker exec -it postgres psql -U postgres

# Once connected, try these commands:
l              # List databases
du             # List users
dt             # List tables in current database
q              # Quit

Creating Initialization Scripts

PostgreSQL automatically runs scripts in /docker-entrypoint-initdb.d on first startup.

Create init-scripts/01-create-databases.sql:

-- Create application databases
CREATE DATABASE nextcloud;
CREATE DATABASE homeassistant;
CREATE DATABASE grafana;
CREATE DATABASE authentik;

-- Create application users
CREATE USER nextcloud_user WITH PASSWORD 'nextcloud_password_change_me';
CREATE USER ha_user WITH PASSWORD 'ha_password_change_me';
CREATE USER grafana_user WITH PASSWORD 'grafana_password_change_me';
CREATE USER authentik_user WITH PASSWORD 'authentik_password_change_me';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE nextcloud TO nextcloud_user;
GRANT ALL PRIVILEGES ON DATABASE homeassistant TO ha_user;
GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana_user;
GRANT ALL PRIVILEGES ON DATABASE authentik TO authentik_user;

-- Create read-only user for backups
CREATE USER backup_user WITH PASSWORD 'backup_password_change_me';
GRANT CONNECT ON DATABASE postgres TO backup_user;

Create init-scripts/02-configure-extensions.sql:

-- Connect to each database and enable extensions

c nextcloud
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

c homeassistant
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

c grafana
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

c authentik
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Back to postgres database
c postgres

Make scripts executable:

chmod +x init-scripts/*.sql

Rebuild to apply initialization:

docker-compose down
docker volume rm postgres-data  # Warning: Deletes data!
docker-compose up -d

Advanced Docker Configuration

Multi-container setup with pgAdmin:

version: '3.8'

services:
  postgres:
    # ... (previous postgres config)

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin
    restart: unless-stopped

    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD:-Admin123!}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
      PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: 'False'

    ports:
      - "5050:80"

    volumes:
      - pgadmin-data:/var/lib/pgadmin
      - ./pgadmin-servers.json:/pgadmin4/servers.json:ro

    networks:
      - postgres-network

    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres-data:
    driver: local
  pgadmin-data:
    driver: local

networks:
  postgres-network:
    driver: bridge

Create pgadmin-servers.json (auto-configure PostgreSQL connection):

{
  "Servers": {
    "1": {
      "Name": "Homelab PostgreSQL",
      "Group": "Homelab",
      "Host": "postgres",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "SSLMode": "prefer",
      "PassFile": "/tmp/pgpassfile"
    }
  }
}

Start both containers:

docker-compose up -d

Access pgAdmin at http://your-server-ip:5050

Part 3: Configuration and Optimization

Understanding postgresql.conf

Create custom postgresql.conf:

# Extract default config
docker exec postgres cat /var/lib/postgresql/data/postgresql.conf > postgresql.conf

Essential Settings for Homelabs:

# Connection Settings
listen_addresses = '*'
port = 5432
max_connections = 100
superuser_reserved_connections = 3

# Memory Settings (adjust based on your RAM)
shared_buffers = 512MB              # 25% of RAM (for 2GB system)
effective_cache_size = 1536MB       # 75% of RAM
work_mem = 8MB                      # RAM / max_connections
maintenance_work_mem = 128MB        # For VACUUM, INDEX creation
wal_buffers = 16MB

# Query Planner
random_page_cost = 1.1              # Lower for SSDs (default 4.0 for HDD)
effective_io_concurrency = 200      # Higher for SSDs (1 for HDD)

# Write-Ahead Log
wal_level = replica                 # Enables replication
fsync = on                          # Do NOT disable (data safety)
synchronous_commit = on             # Can set to 'off' for performance (some risk)
wal_compression = on                # Compress WAL
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 80MB

# Replication (if using replicas)
max_wal_senders = 3
wal_keep_size = 512MB

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%m [%p] %u@%d '
log_min_duration_statement = 1000   # Log queries taking over 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_statement = 'ddl'               # Log all DDL statements

# Autovacuum (automatic cleanup)
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# Performance Monitoring
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on
track_functions = all

# Locale and Formatting
datestyle = 'iso, mdy'
timezone = 'America/New_York'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

Apply custom configuration:

# In docker-compose.yml
services:
  postgres:
    volumes:
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
    command: postgres -c config_file=/etc/postgresql/postgresql.conf

Restart to apply:

docker-compose restart postgres

Performance Tuning for Different Workloads

OLTP (Online Transaction Processing) - Many small transactions:

# Optimize for high concurrency
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 2GB
work_mem = 4MB
maintenance_work_mem = 64MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_buffers = 16MB
default_statistics_target = 100

OLAP (Online Analytical Processing) - Complex queries:

# Optimize for complex queries
max_connections = 40
shared_buffers = 1GB
effective_cache_size = 4GB
work_mem = 50MB
maintenance_work_mem = 512MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_buffers = 16MB
default_statistics_target = 500

Mixed Workload (typical homelab):

# Balanced configuration
max_connections = 100
shared_buffers = 512MB
effective_cache_size = 2GB
work_mem = 8MB
maintenance_work_mem = 128MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_buffers = 16MB
default_statistics_target = 100

Calculating Optimal Settings

Use PGTune for automatic recommendations:

Visit https://pgtune.leopard.in.ua/ or calculate manually:

# Total RAM available to PostgreSQL
TOTAL_RAM_GB=4

# Recommended settings
SHARED_BUFFERS=$((TOTAL_RAM_GB * 1024 / 4))MB        # 25% of RAM
EFFECTIVE_CACHE=$((TOTAL_RAM_GB * 1024 * 3 / 4))MB   # 75% of RAM
WORK_MEM=$((TOTAL_RAM_GB * 1024 / 100))MB            # RAM / max_connections
MAINTENANCE_WORK_MEM=$((TOTAL_RAM_GB * 1024 / 16))MB # RAM / 16

echo "shared_buffers = ${SHARED_BUFFERS}"
echo "effective_cache_size = ${EFFECTIVE_CACHE}"
echo "work_mem = ${WORK_MEM}"
echo "maintenance_work_mem = ${MAINTENANCE_WORK_MEM}"

Part 4: Backup Strategies

Understanding PostgreSQL Backups

PostgreSQL offers three backup approaches:

SQL Dump: Logical backup (human-readable SQL)
File System Backup: Physical copy of data directory
Continuous Archiving (PITR): Point-in-Time Recovery with WAL archiving

SQL Dumps with pg_dump

Basic database dump:

# Single database
docker exec postgres pg_dump -U postgres -d nextcloud > nextcloud_backup.sql

# With compression
docker exec postgres pg_dump -U postgres -d nextcloud | gzip > nextcloud_backup.sql.gz

# Custom format (faster restore, compression)
docker exec postgres pg_dump -U postgres -Fc -d nextcloud > nextcloud_backup.dump

Dump all databases:

docker exec postgres pg_dumpall -U postgres > all_databases_backup.sql

Automated backup script (backup-postgres.sh):

#!/bin/bash
# PostgreSQL Backup Script

BACKUP_DIR="/mnt/backups/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p "$BACKUP_DIR"

echo "Starting PostgreSQL backup at $TIMESTAMP"

# Backup all databases
docker exec postgres pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_databases_${TIMESTAMP}.sql.gz"

# Backup individual databases
for DB in nextcloud homeassistant grafana authentik; do
    echo "Backing up database: $DB"
    docker exec postgres pg_dump -U postgres -Fc -d $DB > "$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
done

# Backup global objects (users, roles, tablespaces)
docker exec postgres pg_dumpall -U postgres --globals-only > "$BACKUP_DIR/globals_${TIMESTAMP}.sql"

# Calculate sizes
echo "Backup sizes:"
ls -lh "$BACKUP_DIR"/*${TIMESTAMP}*

# Cleanup old backups
echo "Removing backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.sql" -mtime +$RETENTION_DAYS -delete

echo "Backup completed successfully!"

Make executable and schedule:

chmod +x backup-postgres.sh

# Add to crontab (daily at 2 AM)
crontab -e
0 2 * * * /home/user/postgres-homelab/backup-postgres.sh >> /var/log/postgres-backup.log 2>&1

Restoring from SQL Dumps

Restore single database:

# From plain SQL
docker exec -i postgres psql -U postgres -d nextcloud < nextcloud_backup.sql

# From gzipped SQL
gunzip < nextcloud_backup.sql.gz | docker exec -i postgres psql -U postgres -d nextcloud

# From custom format
docker exec -i postgres pg_restore -U postgres -d nextcloud < nextcloud_backup.dump

Restore all databases:

docker exec -i postgres psql -U postgres < all_databases_backup.sql

Clean restore (recreate database):

# Drop existing database
docker exec postgres psql -U postgres -c "DROP DATABASE IF EXISTS nextcloud;"

# Create fresh database
docker exec postgres psql -U postgres -c "CREATE DATABASE nextcloud OWNER nextcloud_user;"

# Restore
docker exec -i postgres pg_restore -U postgres -d nextcloud -v < nextcloud_backup.dump

Continuous Archiving (Point-in-Time Recovery)

Configure WAL archiving (postgresql.conf):

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal_archive/%f && cp %p /backups/wal_archive/%f'
archive_timeout = 300              # Force WAL switch every 5 minutes
max_wal_senders = 3
wal_keep_size = 1GB

Create WAL archive directory:

mkdir -p /mnt/backups/postgres/wal_archive
chmod 700 /mnt/backups/postgres/wal_archive

Create base backup:

# Take base backup
docker exec postgres pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P

# Or as separate command
docker exec postgres bash -c "pg_basebackup -U postgres -D /tmp/base_backup -Ft -z -P && tar -czf /backups/base_backup_$(date +%Y%m%d).tar.gz -C /tmp base_backup"

Recovery from PITR backup:

# Stop PostgreSQL
docker-compose stop postgres

# Clear data directory
docker volume rm postgres-data

# Extract base backup
tar -xzf base_backup_20241216.tar.gz -C /mnt/storage/postgres-data/

# Create recovery configuration
cat > /mnt/storage/postgres-data/recovery.signal <<EOF
restore_command = 'cp /backups/wal_archive/%f %p'
recovery_target_time = '2024-12-16 14:30:00'
EOF

# Start PostgreSQL (will enter recovery mode)
docker-compose start postgres

# Monitor recovery in logs
docker-compose logs -f postgres

Backup to Remote Storage

Using Restic with PostgreSQL:

#!/bin/bash
# Backup PostgreSQL to remote storage with Restic

export RESTIC_REPOSITORY="s3:https://s3.homelab.local/postgres-backups"
export AWS_ACCESS_KEY_ID="your-access-key"
export AWS_SECRET_ACCESS_KEY="your-secret-key"
export RESTIC_PASSWORD="your-restic-password"

# Dump databases
docker exec postgres pg_dumpall -U postgres > /tmp/postgres_dump.sql

# Backup with Restic
restic backup /tmp/postgres_dump.sql     --tag postgres     --tag daily

# Cleanup temp file
rm /tmp/postgres_dump.sql

# Prune old backups (keep last 7 daily, 4 weekly, 12 monthly)
restic forget     --keep-daily 7     --keep-weekly 4     --keep-monthly 12     --prune

Part 5: Security Configuration

Authentication and Access Control

Configure pg_hba.conf (Host-Based Authentication):

# Get current config
docker exec postgres cat /var/lib/postgresql/data/pg_hba.conf > pg_hba.conf

Edit pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections (Unix socket)
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# Docker network connections
host    all             all             172.16.0.0/12           scram-sha-256

# Homelab network
host    all             all             192.168.1.0/24          scram-sha-256

# Specific application access
host    nextcloud       nextcloud_user  172.20.0.0/16           scram-sha-256
host    homeassistant   ha_user         172.20.0.0/16           scram-sha-256

# Deny all other connections
host    all             all             0.0.0.0/0               reject

# IPv6 connections
host    all             all             ::1/128                 scram-sha-256
host    all             all             fd00::/8                scram-sha-256

Apply updated config:

# In docker-compose.yml
services:
  postgres:
    volumes:
      - ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf:ro

docker-compose restart postgres

User Management Best Practices

Create application-specific users:

-- Create user with limited privileges
CREATE USER app_user WITH PASSWORD 'strong_password_here';

-- Grant only necessary permissions
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_user;

Create read-only user:

CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE app_db TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Revoke public schema access:

-- Prevent all users from creating objects in public schema
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO postgres;

Encryption

Enable SSL/TLS:

# Generate self-signed certificate
openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=postgres.homelab.local"

chmod 600 server.key
chmod 644 server.crt

# Copy to PostgreSQL
docker cp server.crt postgres:/var/lib/postgresql/data/server.crt
docker cp server.key postgres:/var/lib/postgresql/data/server.key
docker exec postgres chown postgres:postgres /var/lib/postgresql/data/server.{crt,key}

Enable SSL in postgresql.conf:

ssl = on
ssl_cert_file = '/var/lib/postgresql/data/server.crt'
ssl_key_file = '/var/lib/postgresql/data/server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on

Require SSL in pg_hba.conf:

# Require SSL for remote connections
hostssl    all             all             192.168.1.0/24          scram-sha-256

Test SSL connection:

docker exec postgres psql "host=localhost user=postgres dbname=postgres sslmode=require"

Part 6: Replication and High Availability

Setting Up Streaming Replication

Primary server configuration (postgresql.conf):

# Replication settings
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB
synchronous_commit = on
hot_standby = on

Create replication user:

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replication_password';

Allow replication in pg_hba.conf:

# Replication connections
host    replication     replicator      192.168.1.0/24          scram-sha-256

Docker Compose with replica:

version: '3.8'

services:
  postgres-primary:
    image: postgres:16-alpine
    container_name: postgres-primary
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: postgres
    volumes:
      - postgres-primary-data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf:ro
    ports:
      - "5432:5432"
    networks:
      postgres-network:
        ipv4_address: 172.20.0.10
    command: postgres -c config_file=/etc/postgresql/postgresql.conf

  postgres-replica:
    image: postgres:16-alpine
    container_name: postgres-replica
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: postgres
      PGUSER: replicator
      PGPASSWORD: replication_password
    ports:
      - "5433:5432"
    networks:
      postgres-network:
        ipv4_address: 172.20.0.11
    depends_on:
      - postgres-primary
    command: |
      bash -c "
      if [ ! -s /var/lib/postgresql/data/PG_VERSION ]; then
        pg_basebackup -h postgres-primary -D /var/lib/postgresql/data -U replicator -v -P -W
        cat > /var/lib/postgresql/data/postgresql.auto.conf <<EOF
      primary_conninfo = 'host=postgres-primary port=5432 user=replicator password=replication_password'
      EOF
        touch /var/lib/postgresql/data/standby.signal
      fi
      postgres
      "
    volumes:
      - postgres-replica-data:/var/lib/postgresql/data

volumes:
  postgres-primary-data:
  postgres-replica-data:

networks:
  postgres-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.20.0.0/16

Verify replication:

-- On primary
SELECT * FROM pg_stat_replication;

-- On replica
SELECT pg_is_in_recovery();  -- Should return true
SELECT pg_last_wal_receive_lsn();

Part 7: Monitoring and Maintenance

Essential Monitoring Queries

Check database sizes:

SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Check table sizes:

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Check slow queries:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Check active connections:

SELECT
    datname,
    usename,
    application_name,
    client_addr,
    state,
    query,
    age(clock_timestamp(), query_start) AS query_age
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_age DESC;

Maintenance Tasks

VACUUM and ANALYZE:

# Vacuum single database (reclaim space, update statistics)
docker exec postgres vacuumdb -U postgres -d nextcloud -z -v

# Vacuum all databases
docker exec postgres vacuumdb -U postgres -a -z -v

# Full vacuum (locks table, reclaims more space)
docker exec postgres vacuumdb -U postgres -d nextcloud -f -z -v

REINDEX:

# Reindex single database
docker exec postgres reindexdb -U postgres -d nextcloud -v

# Reindex all databases
docker exec postgres reindexdb -U postgres -a -v

Automated maintenance script:

#!/bin/bash
# PostgreSQL Maintenance Script

echo "Starting PostgreSQL maintenance..."

# Vacuum all databases
docker exec postgres vacuumdb -U postgres -a -z -v

# Reindex system catalogs
docker exec postgres reindexdb -U postgres -s -v

# Analyze all databases
docker exec postgres vacuumdb -U postgres -a -Z -v

echo "Maintenance completed!"

Prometheus Monitoring

Install postgres_exporter:

# Add to docker-compose.yml
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    container_name: postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:${POSTGRES_PASSWORD}@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    networks:
      - postgres-network
    depends_on:
      - postgres

Prometheus configuration:

# prometheus.yml
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['postgres-exporter:9187']

Import Grafana dashboard:

Dashboard ID: 9628 (PostgreSQL Database)

Conclusion

You now have a production-ready PostgreSQL deployment in your homelab with comprehensive backup, security, and monitoring capabilities.

Key Takeaways:

Deployment: Docker Compose provides easy, reproducible PostgreSQL deployments
Configuration: Tune settings based on your workload and hardware
Backups: Implement multiple backup strategies (dumps + WAL archiving)
Security: Use proper authentication, encryption, and network isolation
Replication: Set up replicas for high availability and read scaling
Monitoring: Track performance and health with pgstat* views and Prometheus

Next Steps:

Integrate PostgreSQL with your homelab applications
Set up automated backup verification
Implement connection pooling with PgBouncer
Explore advanced features (partitioning, full-text search, JSON)
Practice disaster recovery procedures

Resources:

Happy database management!

Guide Version: 1.0 Last Updated: December 2024 Target Audience: Homelab administrators, developers Difficulty Level: Intermediate Estimated Time: 60 minutes implementation