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 the app role. CREATEROLE is REQUIRED: the audit-hardening migration
# creates the mantis_audit_reader / mantis_audit_writer roles and grants them to
# this role, which aborts without it (see note below).
CREATE USER mantis WITH PASSWORD 'your-secure-password' CREATEROLE;

# 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;

:::caution Audit roles are required to migrate The audit-hardening migration creates three roles used for row-level security on audit_log_entries — mantis_audit_reader, mantis_audit_writer (both NOLOGIN), and a pre-existing mantis_audit_retention — and grants them to the app role. If the app role lacks CREATEROLE, diesel migration run aborts on the very first setup with Cannot grant the audit roles to mantis.

Grant CREATEROLE as above, or pre-create and grant the roles yourself before migrating:

CREATE ROLE mantis_audit_reader NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE mantis_audit_writer NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE mantis_audit_retention NOLOGIN NOSUPERUSER NOBYPASSRLS;
GRANT mantis_audit_reader, mantis_audit_writer, mantis_audit_retention TO mantis WITH ADMIN OPTION;

These same roles are also needed for backup/restore of the audit table. :::

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. The app role must have CREATEROLE
# (or the mantis_audit_* roles must be pre-granted WITH ADMIN OPTION) — see
# "Create User and Database" above — or this aborts on the audit-hardening step.
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