POSTGRESQL, DATABASE, DOCKER

How to create PostgreSQL Docker Image with custom extensions?

Make font:
smaller
|
larger

Problem

When using standard PostgreSQL docker image available on Docker hub - HERE everything from base PostgreSQL functionalities will be there.

However … if in your development you are using non standard extensions (ones that are not available in postgres-contrib - more HERE like pg_cron or recently popular pg_vector (AI buzz :)), you will need to expand the standard image.

Otherwise when you will for instance try to restore DB from your working environment (that have these extensions installed), to you local base image environment you will end up with following error:

postgres.public> create extension pg_cron
[2024-01-14 20:02:10] [0A000] ERROR: extension "pg_cron" is not available
[2024-01-14 20:02:10] Detail: Could not open extension control file "/usr/share/postgresql/15/extension/pg_cron.control": No such file or directory.
[2024-01-14 20:02:10] Hint: The extension must first be installed on the system where PostgreSQL is running.

Solution

In order to solve this problem you need to create your own image based on the based one from Docker Hub.

In newly created Dockerfile you will need to install extensions that you want.

Example: pg_cron

On pg_cron github page (here), you will find an instruction how to install it for your selected Linux Distro.

For Debian you will want to use

sudo apt-get -y install postgresql-16-cron

But hey, what is 16 in the name? 16 as you may wisely deduct Sherlock stands for PostgreSQL Version. If you want to install it for different PostgreSQL version check availability HERE

Thus, if you are using for instance PostgreSQL version 15 you will want to use postgresql-15-cron

Having that in mind we can right now build our desired Dockerfile.

Working Example 


FROM postgres:15-bullseye

LABEL maintainer="DataCraze Postgres v.15 images based on postgres:15-bullseye with pg_cron and pg_vector extensions."

RUN apt-get update

RUN apt-get update \
      && apt-get install -y curl \
      && apt-get -y install postgresql-15-pgvector \
      && apt-get -y install postgresql-15-cron

RUN echo "shared_preload_libraries='pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample
RUN echo "cron.database_name='<YOUR_SPECIAL_DB_NAME>'" >> /usr/share/postgresql/postgresql.conf.sample

With that you can simply build it: docker build -f Dockerfile -t YOUR_FANCY_IMAGE_TAG_WITH_VERSION .

Thanks,
Krzysztof

Want to be up to date with new posts?
Use below form to join Data Craze Weekly Newsletter!

Data Craze Weekly

Weekly dose of curated informations from data world!
Data engineering, analytics, case studies straight to your inbox.

    No spam. Unsubscribe at any time.


    The administrator of personal data necessary in the processing process, including the data provided above, is Data Craze - Krzysztof Bury, Piaski 50 st., 30-199 Rząska, Poland, NIP: 7922121365. By subscribing to the newsletter, you consent to the processing of your personal data (name, e-mail) as part of Data Craze activities.


    This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

    Comment this article on Twitter.