Supercharge Your Postgres Docker Setup with Extensions

Hi there! I'm Shrijith Venkatrama, founder of Hexmos. Right now, I’m building LiveAPI, a first of its kind tool for helping you automatically index API endpoints across all your repositories. LiveAPI helps you discover, understand and use APIs in large tech infrastructures with ease.

Running a PostgreSQL database in Docker is already a game-changer for developers, but adding extensions like pg_cron can take it to the next level. Extensions let you add powerful features to Postgres, like scheduling tasks or geospatial queries, without breaking a sweat. In this guide, I’ll walk you through how to customize a Postgres Docker image with pg_cron for automated task scheduling, plus tips on adding other extensions. We’ll keep it practical, with complete, runnable code examples and clear steps.

This post dives into building a custom Postgres Docker image, installing pg_cron, configuring it properly, and exploring other extensions like PostGIS. Expect straightforward explanations, scannable code, and tables to make sense of the details. Let’s get started.

Why Customize Postgres with Extensions?

PostgreSQL’s strength lies in its extensibility. Extensions like pg_cron let you schedule tasks directly in the database, while others like PostGIS add geospatial superpowers. By baking these into a Docker image, you get a portable, reproducible setup that’s easy to deploy across environments.

Here’s what we’ll cover:

• Building a custom Postgres Docker image with pg_cron.
• Installing and configuring pg_cron for task scheduling.
• Testing the setup with a real example.
• Adding other extensions like PostGIS.
• Best practices for managing custom images.

Key benefit: A custom image ensures your database is pre-configured with the tools you need, saving setup time and reducing errors.

Building the Custom Postgres Docker Image

Let’s start by creating a Docker image based on the official postgres:16.9-bookworm image. We’ll add pg_cron to enable scheduled jobs. The Dockerfile below installs the necessary dependencies, adds pg_cron, and sets up an initialization script to configure it.

FROM postgres:16.9-bookworm

LABEL maintainer="LiveAPI Team"

# Install build dependencies and pg_cron extension
RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        postgresql-server-dev-16 \
        postgresql-16-cron \
        build-essential \
        ca-certificates \
        git \
    && rm -rf /var/lib/apt/lists/*

# Add pg_cron to shared_preload_libraries
RUN echo "shared_preload_libraries = 'pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample

# Create initialization script directly in the Dockerfile
RUN mkdir -p /docker-entrypoint-initdb.d
RUN echo '#!/bin/bash\nset -e\n\necho "Creating pg_cron extension..."\n\n# Set the cron.database_name parameter to the current database\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "ALTER SYSTEM SET cron.database_name TO \047$POSTGRES_DB\047;"\n\n# Reload the configuration\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "SELECT pg_reload_conf();"\n\n# Create the extension\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "CREATE EXTENSION IF NOT EXISTS pg_cron;"\n\necho "pg_cron extension has been installed and configured."' > /docker-entrypoint-initdb.d/10_pg_cron.sh
RUN chmod +x /docker-entrypoint-initdb.d/10_pg_cron.sh

What’s Happening Here?

• Base image: We use postgres:16.9-bookworm (Debian-based) for compatibility with most extensions.
• Dependencies: We install postgresql-server-dev-16, postgresql-16-cron, and tools like build-essential for compiling extensions.
• Shared libraries: We append pg_cron to shared_preload_libraries in the Postgres config to enable it.
• Initialization script: A shell script in /docker-entrypoint-initdb.d runs when the container starts, setting up pg_cron in the database.
• Cleanup: We remove temporary files to keep the image lean.

Build the image:

docker build -t custom-postgres:16.9-pg-cron .

Run it:

docker run -d --name my-postgres \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  custom-postgres:16.9-pg-cron

Output: The container starts, and the initialization script runs, creating the pg_cron extension. You’ll see logs like:

Creating pg_cron extension...
pg_cron extension has been installed and configured.

For more details on the base image, check the official Postgres Docker Hub page.

Configuring pg_cron for Task Scheduling

Now that pg_cron is installed, let’s configure it to schedule tasks. The initialization script in the Dockerfile sets the cron.database_name to the default database and creates the extension. But what if you want to grant access to a non-superuser or add custom configurations? Here’s a standalone script to handle that.

#!/bin/bash
set -e

# Function to run SQL commands
psql_command() {
  psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "$1"
}

echo "Creating pg_cron extension..."
psql_command "CREATE EXTENSION IF NOT EXISTS pg_cron;"

# Grant usage to regular user if different from postgres
if [ "$POSTGRES_USER" != "postgres" ]; then
  echo "Granting pg_cron usage to $POSTGRES_USER..."
  psql_command "GRANT USAGE ON SCHEMA cron TO $POSTGRES_USER;"
fi

echo "pg_cron extension has been installed and configured."

How It Works

• Script location: Place this in /docker-entrypoint-initdb.d (e.g., as 20_init-pg-cron.sh) to run after the Dockerfile’s script.
• Error handling: set -e ensures the script stops on errors.
• User permissions: Grants cron schema access to non-superusers, which is critical for production setups.
• Output: Logs confirm the extension is created and permissions are set:

Creating pg_cron extension...
Granting pg_cron usage to myuser...
pg_cron extension has been installed and configured.

To use this, copy the script into your project, ensure it’s executable (chmod +x init-pg-cron.sh), and include it in your Docker build.

Testing pg_cron with a Real Example

Let’s test pg_cron by scheduling a job to clean up old records. We’ll create a table, insert some data, and schedule a job to delete records older than a day.

Connect to the database:

docker exec -it my-postgres psql -U myuser -d mydb

Create a test table and insert data:

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('Test log 1'), ('Test log 2');

Schedule a cleanup job with pg_cron:

SELECT cron.schedule(
  'daily_cleanup',
  '0 0 * * *', -- Every day at midnight
  $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '1 day'$$
);

Verify the job:

SELECT * FROM cron.job;

Output:

jobid	schedule	command	nodename	nodeport	database	username	active
1	0 0 * * *	DELETE FROM logs WHERE created_at < NOW() - INTERVAL '1 day'	localhost	5432	mydb	myuser	true

This confirms the job is scheduled. It’ll run daily, deleting old logs. You can check the cron.job_run_details table to see execution history:

SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 5;

Tip: Use cron.unschedule('daily_cleanup') to remove the job if needed.

For more on pg_cron, see its GitHub page.

Adding Other Extensions Like PostGIS

Want to add more extensions, like PostGIS for geospatial data? With Debian-based images like bookworm, it’s straightforward. Here’s how to extend the Dockerfile to include PostGIS.

Modified Dockerfile snippet:

FROM postgres:16.9-bookworm

LABEL maintainer="LiveAPI Team"

# Install build dependencies, pg_cron, and PostGIS
RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        postgresql-server-dev-16 \
        postgresql-16-cron \
        postgresql-16-postgis-3 \
        build-essential \
        ca-certificates \
        git \
    && rm -rf /var/lib/apt/lists/*

# Add pg_cron to shared_preload_libraries
RUN echo "shared_preload_libraries = 'pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample

# Create initialization script for pg_cron and PostGIS
RUN mkdir -p /docker-entrypoint-initdb.d
RUN echo '#!/bin/bash\nset -e\n\necho "Creating extensions..."\n\n# Set cron.database_name\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "ALTER SYSTEM SET cron.database_name TO \047$POSTGRES_DB\047;"\n\n# Reload config\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "SELECT pg_reload_conf();"\n\n# Create extensions\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "CREATE EXTENSION IF NOT EXISTS pg_cron;"\npsql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -c "CREATE EXTENSION IF NOT EXISTS postgis;"\n\necho "Extensions installed and configured."' > /docker-entrypoint-initdb.d/10_extensions.sh
RUN chmod +x /docker-entrypoint-initdb.d/10_extensions.sh

Key Points

• Package: postgresql-16-postgis-3 adds PostGIS to the image.
• Initialization: The script now creates both pg_cron and postgis extensions.
• Output: Logs will show:

Creating extensions...
Extensions installed and configured.

Test PostGIS:

SELECT PostGIS_Version();

Output: Something like 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1.

For a full example, check the PostGIS Docker GitHub.

Note: If you’re using Alpine-based images, you’ll need to compile extensions like PostGIS from source, which is more complex. Stick with Debian for simplicity unless you have a specific reason to use Alpine.

Best Practices for Managing Custom Postgres Images

To keep your custom Postgres Docker setup clean and efficient, follow these tips:

Practice	Why It Matters
Use specific image tags	Tags like 16.9-bookworm ensure consistency across builds. Avoid latest.
Clean up apt caches	Reduces image size by removing /var/lib/apt/lists/*.
Test initialization scripts	Run docker logs to verify scripts execute without errors.
Version control Dockerfiles	Track changes to your image in Git for reproducibility.
Limit shared_preload_libraries	Only include necessary extensions to minimize memory usage.

• Backup configurations: Store postgresql.conf changes in version control.
• Monitor extension versions: Check compatibility with your Postgres version (e.g., postgresql-16-cron for Postgres 16).
• Use multi-stage builds: For complex extensions, consider multi-stage Docker builds to keep the final image small.

Moving Forward with Your Custom Postgres

Customizing a Postgres Docker image with extensions like pg_cron or PostGIS gives you a powerful, tailored database setup. You can schedule tasks, handle geospatial data, or add other extensions to fit your app’s needs. The key is to start with a solid Dockerfile, test your setup, and follow best practices to keep things maintainable.

Try experimenting with other extensions like pg_stat_statements for query performance or timescaledb for time-series data. Each follows a similar pattern: install the package, update configs, and initialize the extension. With this approach, you’ll have a flexible, production-ready Postgres setup that’s easy to deploy anywhere.

Got a favorite Postgres extension or Docker tip? Share it in the comments—I’d love to hear how you’re supercharging your database!