PostgreSQL Setup

Configure PostgreSQL for Mantis deployment.

Requirements

Requirement	Minimum	Recommended
PostgreSQL	16	16+
Storage	10 GB	50+ GB SSD
Memory	1 GB	4+ GB

Installation

Ubuntu/Debian

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL 16
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

RHEL/Rocky/Alma

# Install repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module
sudo dnf -qy module disable postgresql

# Install PostgreSQL 16
sudo dnf install -y postgresql16-server postgresql16-contrib

# Initialize database
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Docker

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: mantis
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: mantis
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - '5432:5432'

Database Creation

Create User and Database

# Switch to postgres user
sudo -u postgres psql

# Create user
CREATE USER mantis WITH PASSWORD 'your-secure-password';

# Create database
CREATE DATABASE mantis OWNER mantis;

# Grant permissions
GRANT ALL PRIVILEGES ON DATABASE mantis TO mantis;

# Connect to database and grant schema permissions
\c mantis
GRANT ALL ON SCHEMA public TO mantis;

Verify Connection

# Test connection
psql -h localhost -U mantis -d mantis -c "SELECT version();"

Configuration

postgresql.conf

Key settings for Mantis workloads:

# Connection Settings
listen_addresses = 'localhost'          # Or '*' for remote access
port = 5432
max_connections = 200

# Memory Settings
shared_buffers = 1GB                    # 25% of RAM
effective_cache_size = 3GB              # 75% of RAM
work_mem = 16MB
maintenance_work_mem = 256MB

# Write-Ahead Log
wal_level = replica                     # For replication
max_wal_size = 2GB
min_wal_size = 512MB

# Query Planner
random_page_cost = 1.1                  # For SSD storage
effective_io_concurrency = 200          # For SSD storage

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'ddl'                   # Log schema changes
log_min_duration_statement = 1000       # Log queries > 1s

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60

pg_hba.conf

Configure client authentication:

# Local connections
local   all             postgres                                peer
local   all             mantis                                  scram-sha-256

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

# IPv4 remote connections (for Mantis components)
host    mantis          mantis          10.0.0.0/8              scram-sha-256

# SSL connections (recommended for production)
hostssl mantis          mantis          0.0.0.0/0               scram-sha-256

Apply Configuration

# Reload configuration
sudo systemctl reload postgresql

# Or restart for settings that require it
sudo systemctl restart postgresql

SSL/TLS Configuration

Generate Certificates

# Create directory
sudo mkdir -p /etc/postgresql/16/main/certs
cd /etc/postgresql/16/main/certs

# Generate server key and certificate
openssl req -new -x509 -days 365 -nodes \
  -keyout server.key \
  -out server.crt \
  -subj "/CN=postgres.example.com"

# Set permissions
chmod 600 server.key
chown postgres:postgres server.key server.crt

Enable SSL

In postgresql.conf:

ssl = on
ssl_cert_file = '/etc/postgresql/16/main/certs/server.crt'
ssl_key_file = '/etc/postgresql/16/main/certs/server.key'
ssl_min_protocol_version = 'TLSv1.2'

Connection URL with SSL

postgres://mantis:password@db.example.com:5432/mantis?sslmode=verify-full&sslrootcert=/path/to/ca.crt

SSL Mode	Description
`disable`	No SSL
`allow`	Try SSL, fallback to non-SSL
`prefer`	Try SSL first (default)
`require`	Require SSL, no certificate verification
`verify-ca`	Verify server certificate
`verify-full`	Verify certificate and hostname

High Availability

Streaming Replication

Primary Server

In postgresql.conf:

wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on

In pg_hba.conf:

host    replication     replicator      10.0.0.0/8              scram-sha-256

Create replication user:

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure-password';

Replica Server

# Stop PostgreSQL
sudo systemctl stop postgresql

# Remove data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# Create base backup from primary
sudo -u postgres pg_basebackup \
  -h primary.example.com \
  -U replicator \
  -D /var/lib/postgresql/16/main \
  -P -R -X stream

# Start PostgreSQL
sudo systemctl start postgresql

Using PgBouncer

For connection pooling at scale:

[databases]
mantis = host=localhost port=5432 dbname=mantis

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

Mantis Configuration

Mandible Database Settings

[database]
url = "postgres://mantis:password@localhost:5432/mantis?sslmode=require"
max_connections = 10
min_idle = 2
connection_timeout_secs = 10

Environment Variables

Mandible reads the DATABASE_URL variable (it also accepts the layered MANDIBLE__DATABASE__URL override):

export DATABASE_URL="postgres://mantis:password@localhost:5432/mantis"

Initial Schema

Run Migrations

# Install Diesel CLI
cargo install diesel_cli --no-default-features --features postgres

# Run migrations from the instinct directory
cd /path/to/mantis/instinct
diesel migration run

Verify Schema

# Connect to database
psql -U mantis -d mantis

# List tables
\dt

# Check schema version
SELECT * FROM __diesel_schema_migrations ORDER BY run_on DESC LIMIT 5;

Backup Configuration

Automated Backups

Create backup script:

#!/bin/bash
BACKUP_DIR="/var/backups/mantis"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Perform backup
pg_dump -h localhost -U mantis -Fc mantis > "$BACKUP_DIR/mantis_$DATE.dump"

# Remove old backups
find "$BACKUP_DIR" -name "mantis_*.dump" -mtime +$RETENTION_DAYS -delete

Schedule with cron:

# Daily at 2 AM
0 2 * * * /opt/mantis/scripts/backup-db.sh

Point-in-Time Recovery

Enable WAL archiving:

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

Monitoring

Key Metrics

-- Connection count
SELECT count(*) FROM pg_stat_activity;

-- Database size
SELECT pg_size_pretty(pg_database_size('mantis'));

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Long running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Health Check

# Simple health check
pg_isready -h localhost -U mantis -d mantis

Troubleshooting

Connection Refused

Check PostgreSQL is running: systemctl status postgresql
Verify listen_addresses in postgresql.conf
Check pg_hba.conf allows connection

Authentication Failed

Verify password is correct
Check auth method in pg_hba.conf
Ensure user exists: \du in psql

Performance Issues

Check for long-running queries
Run ANALYZE on affected tables
Review query plans with EXPLAIN ANALYZE

Next Steps

Migrations - Database schema management
Connection Pooling - Optimize connections
Maintenance - Ongoing maintenance