Building a PostgreSQL docker container with debugger (pldebugger)

Further to my previous post, I wanted to enable debugging in PostgreSQL Docker container so that it can be used together with PGAdmin4.

The challenge is it is not enabled by default in any of available Docker images; I also tried to recompile from source but failed with different kinds of errors.

Just for information, the debugger has its git site where you can find all source code here: https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

At the end I found a simple solution because the debugger extension is already in Debian’s package repository (https://packages.debian.org/sid/amd64/postgresql-11-pldebugger/download) , all we need to do is to use official PostgreSQL Docker image and then install this extension.

Below is my docker-compose.yml

version: '3'
services:
  app:
    build:
        context: .
        dockerfile: docker/php/Dockerfile
    image: ci-docker
    container_name: tarwis_crm_container
    ports:
        - "8080:80"    
    volumes:
        #bind mount app to container folder /var/www/tarwis
        - ./app:/var/www/tarwis    
    #by defining the same "user defined network", all containers can talk to each other
    #do NOT use link as it may not be supported in future
    networks:
      - appnet
    environment:
        #env variables, used by your application
        DB_HOSTNAME: postgres
        #this points to the postgres db's name
        DB_DATABASE: yourDBName
        DB_PORT: 5432
        DB_USERNAME: yourUserName
        DB_PASSWORD: yourPassword
    
  postgres:
    build:
        context: .
        dockerfile: docker/postgres/Dockerfile
    image: postgres    
    #when container_name is specified, as 
    #container name is always unique you cannot scale a service beyond 1 container
    container_name: pg_container
    restart: always    
    environment:
        #specify db name
        POSTGRES_DB: yourDBName
        #default is postgres but custom it here for clarity
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: hardToGuess        
    ports:
      - "15432:5432"    
    #by defining the same "user defined network", all containers can talk to each other
    networks:
      - appnet

#definition of all networks
networks:
  #with default settings (which is a load-balanced overlay network)
  appnet:
      
#no need of volumes

Below is my Dockerfile for the PostgreSQL image, with debugger installed at the end. ( My link above is for PG V11 but V10 exists as well,  you can simply change environment variable ENV PG_MAJOR 10 to ENV PG_MAJOR 11)

FROM postgres:10.6

MAINTAINER YourName@Tarwis Tech

ENV PG_MAJOR 10
ENV PG_FULL 10.6

# Install the postgresql debugger
RUN apt-get update \
  && apt-get install -y --no-install-recommends \
  postgresql-$PG_MAJOR-pldebugger
  

EXPOSE 5432

#run below in DB to enable the extension
#CREATE EXTENSION pldbgapi;

#reference:
#https://hub.docker.com/r/codejuggle/postgresql/~/dockerfile/
#https://docs.docker.com/engine/docker-overview/#docker-registries
#https://github.com/docker-library/postgres/blob/2d40c8a89bfc888fb99463e919ac748cd54c0fcc/11/Dockerfile
#https://gist.github.com/sethbergman/1a34dcf925badee366b46b7c52526701
#https://github.com/sameersbn/docker-postgresql#enabling-extensions
#https://github.com/docker-library/postgres/blob/f5a7e06b42aa14cad6edfaeefa676a5312d27618/10/Dockerfile

Run the following commands to build and bring up the containers

Docker-compose build

Docker-compose up

Then login to the PostgreSQL instance using PGAdmin, on port 15432(this is re-directed by Docker itself)

In the DB you’re working in, install the extension

CREATE EXTENSION pldbgapi;

Done!