Docker PostgreSQL AIO (All-In-One)

A production-ready Docker setup for PostgreSQL 18.1 with comprehensive monitoring, performance optimization, and advanced extensions.

Overview

This project provides a complete Docker-based PostgreSQL 18.1 environment with:

• Advanced Performance Monitoring: Built-in pg_stat_statements, pg_stat_kcache, and POWA dashboard
• Automatic Memory Optimization: Intelligent configuration based on container resources
• Comprehensive Auditing: pgaudit extension for detailed logging
• High Availability Features: Streaming replication support
• Extended Functionality: 20+ PostgreSQL extensions pre-installed
• Production Hardening: Optimized configuration for high-concurrency environments

Quick Start

Prerequisites

• Docker and Docker Compose
• At least 4GB RAM available (8GB recommended)
• 20GB+ disk space for PostgreSQL data

Installation

1. Clone or download this repository:

cd docker-postgresql-aio

2. Create a .env file in the root directory:

POSTGRES_USER=postgres
POSTGRES_PASSWORD=your_secure_password_here
POSTGRES_DB=maindb
PG_TARGET_PERCENT=70
PG_CONN_ESTIMATE_MB=25
PG_MAX_CONN_CAP=2000

3. Start the PostgreSQL container:

docker-compose up -d

4. Verify the service is running:

docker-compose ps
docker exec db pg_isready -U postgres

Configuration

Environment Variables

Variable	Default	Description
POSTGRES_USER	postgres	PostgreSQL superuser username
POSTGRES_PASSWORD	-	PostgreSQL superuser password (required)
POSTGRES_DB	postgres	Initial database to create
PG_TARGET_PERCENT	70	Target percentage of memory for shared_buffers
PG_CONN_ESTIMATE_MB	25	Estimated memory per connection (MB)
PG_MAX_CONN_CAP	2000	Maximum number of connections

Customization

PostgreSQL Configuration

Edit config/postgresql.conf to customize:

• Connection limits (max_connections: 2000)
• Memory allocation (shared_buffers: 8GB, effective_cache_size: 24GB)
• WAL configuration for replication
• Logging verbosity and format
• Performance parameters

Host-Based Access Control

Edit config/pg_hba.conf to configure:

• Network access permissions
• Authentication methods
• User/database restrictions

Initialization Scripts

Custom SQL scripts in docker/initdb/ run automatically on first startup:

• 01_init_dba.sh - Creates extensions and POWA repository database

Key Features

Performance Optimization

• Adaptive Configuration: Automatically adjusts settings based on available CPU and memory
• Connection Pooling Ready: Optimized for use with pgBouncer
• Parallel Query Execution: max_parallel_workers set to 16
• Query Caching: Efficient buffer management with huge_pages support

Monitoring & Observability

Preloaded Extensions:

• pg_stat_statements: Track query performance and execution statistics
• pg_stat_kcache: Measure kernel cache performance
• auto_explain: Automatic query plan logging
• pg_wait_sampling: Wait event analysis
• pgaudit: Comprehensive audit logging
• POWA: PostgreSQL Workload Analyzer

Logging Features:

• All SQL statements logged with duration
• Connection/disconnection tracking
• Lock wait monitoring
• Detailed error reporting with verbose output

Security & Audit

• pgaudit Extension: Complete audit trail of all database operations
• SCRAM-SHA-256: Modern password encryption
• Flexible Authentication: Configurable via pg_hba.conf

Advanced Functionality

Preinstalled extensions include:

• uuid-ossp: UUID generation
• pgcrypto: Cryptographic functions
• pglogical: Logical replication
• timescaledb: Time-series data handling
• pg_hint_plan: Query optimization hints
• pg_partman: Automated table partitioning
• pgautofailover: Automatic failover management
• And 10+ more specialized extensions

Project Structure

docker-postgresql-aio/
├── docker-compose.yml          # Service orchestration
├── config/
│   ├── postgresql.conf         # PostgreSQL main configuration
│   └── pg_hba.conf            # Host-based authentication
├── docker/
│   ├── Dockerfile             # Container image definition
│   ├── pg-entrypoint.sh       # Startup script with auto-tuning
│   └── initdb/
│       └── 01_init_dba.sh     # Initial database setup
└── README.md                  # This file

Performance Characteristics

Memory Settings

• shared_buffers: 8GB (efficient for large datasets)
• effective_cache_size: 24GB (OS cache estimate)
• work_mem: 8MB per operation
• maintenance_work_mem: 1GB (for VACUUM, CREATE INDEX)

I/O Optimization

• Effective I/O concurrency: 300 (SSD optimized)
• Parallel workers: Up to 16 parallel queries
• WAL compression: Enabled for efficient replication

Connection Management

• Max connections: 2000
• Reserved superuser connections: 10
• Autovacuum workers: 8

Troubleshooting

Container fails to start

Check logs:

docker-compose logs db

Verify environment variables in .env file.

Out of memory errors

Reduce container memory limits or adjust PG_TARGET_PERCENT in .env.

Connection refused

Ensure the container is running:

docker-compose ps

Check if port 5432 is available on the host.

Slow queries

Enable query monitoring:

SELECT * FROM pg_stat_statements 
ORDER BY total_time DESC LIMIT 10;

Health Check

The container includes a health check that verifies PostgreSQL is accepting connections:

docker-compose ps  # Shows health status

Maintenance

Update PostgreSQL Version

1. Backup your data
2. Update PostgreSQL version in Dockerfile
3. Rebuild and redeploy:

docker-compose down
docker-compose build --no-cache
docker-compose up -d

Rotate Logs

Logs rotate automatically every 10 minutes. View logs:

docker exec db tail -f /var/log/postgresql/main.log

Monitor Extensions

Check active extensions:

docker exec db psql -U postgres -c "SELECT * FROM pg_extension;"

Advanced Configuration

Streaming Replication

The configuration includes WAL settings for replica setup:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 8GB

Connection Pooling

This setup works well with external poolers like pgBouncer:

pgBouncer (external) → Docker PostgreSQL → Applications

Time Zone

Default timezone is set to Europe/Istanbul. Change in:

• docker-compose.yml: TZ environment variable
• config/postgresql.conf: timezone parameter

Performance Tuning Tips

1. For more connections: Increase max_connections in postgresql.conf
2. For faster writes: Adjust wal_compression and synchronous_commit
3. For heavy analytics: Increase max_parallel_workers and work_mem
4. For OLTP workloads: Reduce shared_buffers and increase connection pool

Support & Contributions

This is a production-ready PostgreSQL setup maintained for Docker-based environments.

For issues or improvements, please review the configuration files and PostgreSQL documentation.

Additional Resources

• PostgreSQL 18 Documentation
• POWA Documentation
• pgaudit Documentation
• Docker Documentation

Version: PostgreSQL 18.1
Docker Image: akinbicer/postgres:18.1

---

Issues, Feature Requests or Support

Please use the Issue > New Issue button to submit issues, feature requests or support issues directly to me. You can also send an e-mail to akin.bicer@outlook.com.tr.