🐘 PostgreSQL 17.5 with pg_cron & pg_partman

A production-ready PostgreSQL 17.5 Docker image with essential extensions for modern applications:

• 🕐 pg_cron - Schedule SQL commands directly from PostgreSQL
• 📊 pg_partman - Automated table partitioning for large datasets
• 🔒 Enhanced Security - SCRAM-SHA-256 authentication by default
• ⚡ Optimized Configuration - Performance-tuned for containerized environments

🚀 Quick Start

Using Docker

# Basic usage
docker run -d --name postgres-app \
-e POSTGRES_PASSWORD=your_secure_password \
-e POSTGRES_DB=your_database \
-p 5432:5432 \
qonicsinc/postgres-pgcron:17.5

Using Docker Compose

version: '3.8'services:
postgres:
image: qonicsinc/postgres-pgcron:17.5environment:
POSTGRES_DB: myappPOSTGRES_PASSWORD: secure_passwordports:
- "5432:5432"volumes:
- postgres_data:/var/lib/postgresql/datahealthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]interval: 30stimeout: 10sretries: 3volumes:
postgres_data:

📋 Environment Variables

Variable	Default	Description
POSTGRES_DB	postgres	Default database name
POSTGRES_USER	postgres	PostgreSQL superuser name
POSTGRES_PASSWORD	Required	PostgreSQL password
POSTGRES_INITDB_ARGS	--auth-host=scram-sha-256	Additional initdb arguments

🔧 Pre-installed Extensions

pg_cron (v1.6)

Schedule and run SQL commands on a recurring basis.

-- Schedule a daily cleanup jobSELECTcron.schedule(
'daily-cleanup',
'0 2 * * *', -- Every day at 2 AM'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days'';'
);
-- List all scheduled jobsSELECT jobid, jobname, schedule, active FROMcron.job;
-- View job execution historySELECT*FROMcron.job_run_detailsORDER BY start_time DESCLIMIT10;

pg_partman (v5.2.4)

Automated table partitioning management for improved performance on large datasets.

-- Create a partitioned tableCREATETABLEsales_data (
id SERIAL,
sale_date DATENOT NULL,
amount DECIMAL(10,2),
customer_id INTEGER
) PARTITION BY RANGE (sale_date);
-- Set up automatic monthly partitioningSELECTpartman.create_parent(
p_parent_table =>'public.sales_data',
p_control =>'sale_date',
p_interval =>'1 month'
);
-- Schedule automatic partition maintenanceSELECTcron.schedule(
'partition-maintenance',
'0 1 * * *', -- Daily at 1 AM'SELECT partman.run_maintenance_proc();'
);

💡 Real-World Examples

E-commerce Analytics Pipeline

-- Create partitioned events tableCREATETABLEuser_events (
id BIGSERIAL,
user_id INTEGERNOT NULL,
event_type VARCHAR(50) NOT NULL,
event_data JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- Set up weekly partitions with 4-week retentionSELECTpartman.create_parent(
p_parent_table =>'public.user_events',
p_control =>'created_at',
p_interval =>'1 week'
);
UPDATEpartman.part_configSET retention ='4 weeks', retention_keep_table = false
WHERE parent_table ='public.user_events';
-- Daily aggregation jobSELECTcron.schedule(
'daily-analytics',
'0 6 * * *',
'INSERT INTO daily_stats  SELECT DATE(created_at), event_type, COUNT(*)  FROM user_events  WHERE created_at >= CURRENT_DATE - INTERVAL ''1 day'' GROUP BY DATE(created_at), event_type;'
);

Financial Data with 6-Month Partitions

-- Account balances with 6-month partitionsCREATETABLEaccount_balances (
id SERIAL,
account_id INTEGERNOT NULL,
balance DECIMAL(19,4) NOT NULL,
currency CHAR(3) NOT NULL,
as_of_date DATENOT NULL
) PARTITION BY RANGE (as_of_date);
-- 6-month partitions, keep 2 years of dataSELECTpartman.create_parent(
p_parent_table =>'public.account_balances',
p_control =>'as_of_date',
p_interval =>'6 months'
);
UPDATEpartman.part_configSET retention ='2 years',
premake =2-- Keep 2 future partitions readyWHERE parent_table ='public.account_balances';

🔐 Security Features

• SCRAM-SHA-256 Authentication: Modern password authentication
• Configurable pg_hba.conf: Customize connection security
• Non-root Execution: PostgreSQL runs as dedicated postgres user
• Secure Defaults: Production-ready security configuration

⚡ Performance Optimizations

The image includes performance-tuned configuration:

# Memory settings optimized for containersshared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
# Connection and concurrencymax_connections = 200
effective_io_concurrency = 200
# WAL and checkpointswal_level = replica
max_wal_size = 2GB
checkpoint_completion_target = 0.9
# Query optimizationrandom_page_cost = 1.1

📊 Monitoring & Management

Health Checks

# Container health check
docker exec postgres-app pg_isready -U postgres
# Extension verification
docker exec postgres-app psql -U postgres -c " SELECT name, installed_version  FROM pg_available_extensions  WHERE installed_version IS NOT NULL  AND name IN ('pg_cron', 'pg_partman');"

Job Monitoring

-- Monitor cron job performanceSELECT jobname,
schedule,
COUNT(*) as executions,
AVG(EXTRACT(EPOCH FROM (end_time - start_time))) as avg_duration_seconds,
SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) as successful_runs
FROMcron.job_run_detailsWHERE start_time >= NOW() - INTERVAL '7 days'GROUP BY jobname, schedule;
-- Check partition sizesSELECT schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables WHERE tablename LIKE'user_events_%'ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

🐳 Production Deployment

Kubernetes

apiVersion: apps/v1kind: StatefulSetmetadata:
name: postgres-pgcronspec:
serviceName: postgres-pgcronreplicas: 1selector:
matchLabels:
app: postgres-pgcrontemplate:
metadata:
labels:
app: postgres-pgcronspec:
containers:
- name: postgresimage: qonicsinc/postgres-pgcron:17.5env:
- name: POSTGRES_PASSWORDvalueFrom:
secretKeyRef:
name: postgres-secretkey: password
- name: POSTGRES_DBvalue: "production_db"ports:
- containerPort: 5432volumeMounts:
- name: postgres-storagemountPath: /var/lib/postgresql/datalivenessProbe:
exec:
command:
- pg_isready
- -U
- postgresinitialDelaySeconds: 30periodSeconds: 10volumeClaimTemplates:
- metadata:
name: postgres-storagespec:
accessModes: ["ReadWriteOnce"]resources:
requests:
storage: 100Gi

Docker Swarm

version: '3.8'services:
postgres:
image: qonicsinc/postgres-pgcron:17.5environment:
POSTGRES_PASSWORD_FILE: /run/secrets/postgres_passwordPOSTGRES_DB: production_dbsecrets:
- postgres_passwordvolumes:
- postgres_data:/var/lib/postgresql/datadeploy:
replicas: 1placement:
constraints:
- node.role == managerrestart_policy:
condition: on-failuresecrets:
postgres_password:
external: truevolumes:
postgres_data:
driver: local

🔧 Customization

Custom Configuration

# Mount custom postgresql.conf
docker run -d \
-v ./my-postgresql.conf:/etc/postgresql/postgresql.conf:ro \
qonicsinc/postgres-pgcron:17.5

Custom Initialization Scripts

# Add custom SQL scripts
docker run -d \
-v ./init-scripts:/docker-entrypoint-initdb.d:ro \
qonicsinc/postgres-pgcron:17.5

🐛 Troubleshooting

Common Issues

Extensions not found:

-- Check if extensions are availableSELECT*FROM pg_available_extensions WHERE name IN ('pg_cron', 'pg_partman');
-- Verify current databaseSELECT current_database();

pg_cron jobs not running:

-- Check cron configuration
SHOW shared_preload_libraries;
SHOW cron.database_name;
-- Verify job statusSELECT*FROMcron.jobWHERE active = true;

Connection issues:

# Check container logs
docker logs postgres-app
# Verify container health
docker exec postgres-app pg_isready -U postgres

📖 Documentation

• PostgreSQL 17 Documentation
• pg_cron Extension
• pg_partman Extension

🤝 Contributing

Issues and pull requests are welcome! Please visit our GitHub repository for more information.

📄 License

This image is based on the official PostgreSQL Docker image and includes additional open-source extensions. See individual component licenses for details.