PostgreSQL 18 Production Database Server

Production-grade PostgreSQL 18 instance with pgvector enabled, optimized for 8-core/4GB memory allocation.

Quick Start

# Create environment file
cp .env.example .env
# Edit .env and set POSTGRES_PASSWORD

# Start the service
docker-compose up -d

# Check status
docker logs postgres18 -f

Database Management

Create New Database

-- Connect as postgres user
psql -h localhost -U postgres -d postgres

-- Create database and user
CREATE DATABASE myapp_prod;
CREATE USER myapp_prod WITH ENCRYPTED PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE myapp_prod TO myapp_prod;

Enable pgvector in an Existing Database

CREATE EXTENSION IF NOT EXISTS vector;

New clusters created from this repo will also enable vector in template1, so future databases inherit it automatically.

Connection Details

• Host: localhost
• Port: 5432
• Username: postgres
• Password: Set in .env file
• Format: postgresql://username:password@localhost:5432/database_name

Monitoring

# Container health
docker ps | grep postgres18
docker exec postgres18 pg_isready -U postgres

# Connection count
docker exec postgres18 psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

Directory Structure

~/postgres/
├── .forgejo/workflows/     # Forgejo deployment workflow
├── docker-compose.yml      # Container configuration
├── .env                    # Environment variables
├── README.md              # This file
├── init/                  # Database initialization scripts
├── backups/               # Pre-deploy database dumps (7-day retention)
└── data/18/docker/        # PostgreSQL data files

Deployment Safety

Before each Forgejo deployment, the workflow creates a timestamped pg_dumpall backup in /home/server/postgres/backups and keeps it for 7 days. This gives a short rollback window before the updated container is started.

Troubleshooting

# Check container logs
docker logs postgres18 -f

# Connect to container shell
docker exec -it postgres18 bash

# Check PostgreSQL configuration
docker exec postgres18 psql -U postgres -c "SHOW shared_buffers;"