Oskar Dudycz

Pragmatycznie o programowaniu

How to automatically setup pgAdmin with a Docker database

2024-06-21 oskar dudyczDevOps

2024 06 21 cover

Developer experience is a phrase repeated in multiple ways. In our industry, we finally realised how important it is to reduce the cognitive load. As our profession became mainstream, we realised that hacked mode doesn’t scale. As with lean manufacturing, we should cut waste. Waste can mean repetitive tasks that distract us from the work we do.

Of course, I’m not saying we should try to get “in the zone”. Or we should spend hours configuring Vim keybindings and learning all grep params by heart before we can code. No, I don’t believe in 10x developers. What I mean is cutting the annoying papercuts.

Today, I’d like to show you a few tricks on configuring the PostgreSQL local developer environment using Docker Compose.

Let’s start with the basic setup:

version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            - POSTGRES_DB=postgres
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"

The most basic setup, besides adding two additional configurations through environment variables:

  • POSTGRES_DB - instructs PostgreSQL container to automatically create a default database with the provided name,
  • POSTGRES_USER - sets the default username.
  • POSTGRES_PASSWORD - sets the custom password for the PostgreSQL user.

We could skip those variables if we’d like to use the default one, but let’s make it a bit more spicy in preparing for what comes next.

If we run:

docker-compose up

Then, a new PostgreSQL container will be created and available on the localhost:5432 (as we also exposed this port to the host). We can now try to connect our application.

That’s nice, but what if we’d like to set up a basic database structure, such as a predefined set of tables, indexes, data, etc.? A Default Postgres image also helps here—or actually a Docker convention supported by it.

Most relational databases support a special docker-entrypoint-initdb.d folder. This folder is used to initialise the database automatically when the container is first created. You can put .sql or .sh scripts there, and Docker will automatically. This happens only the first time the container is started, not on subsequent restarts.

Let’s try that and add a basic script called 001-init.sql:

BEGIN;

-- structure setup

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- data setup

INSERT INTO users (username, email) 
VALUES ('user1', 'user1@example.com');

INSERT INTO users (username, email) 
VALUES ('user2', 'user2@example.com');

COMMIT;

It’s a simple script that shows that we can run the script transactionally (see BEGIN and COMMIT). We can also set up a database structure and insert some data.

We could split the script into two files: 001-init-structure.sql and 002-init-data.sql. They will be run in alphabetical order. As you can see, you could even put the sequence of the migration scripts exported from your application there.

Cool, but how do you put it inside the container? We can use volumes for that. Pardon the self-quote, but I wrote in my my other article that:

Volumes enable storing the container data. You can restart the container, and the data will remains. It also allows to mount/bind the host operating system files to the container. It can go both ways, you can send files to the container, but you can also see generated files from the container in the host storage.

How to do it? Simple as that:

version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            - POSTGRES_DB=postgres
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"
	# VOLUMES CONFIG
        volumes:
            - ./docker/postgres:/docker-entrypoint-initdb.d

Where ./docker/postgres is a local folder path relative to the Docker Compose file. We can put our init files there, and they will be automatically copied to the Docker container during the build and then run on the first run of the Docker container instance. It’s pretty simple and helpful, isn’t it?

Ok, let’s add more spice and show the example of the shell script run on database initialisation. Why would we do it?

As you see, the existing PostgreSQL database container configuration is flexible. But each flexibility has its limits. What if we’d like to set up multiple databases instead of one? The existing setup won’t help. We need to do it on our own. As with any other database, PostgreSQL has its command line. It could be used to set up databases, run SQL scripts, etc.

We could add a new script called 000-create-multiple-postgresql-databases.sh and put there the following script:

#!/bin/bash

set -e
set -u

function create_database() {
	local database=$1
	echo "  Creating Database '$database' for '$POSTGRES_USER'"
	psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
	    CREATE DATABASE $database;
	    GRANT ALL PRIVILEGES ON DATABASE $database TO $POSTGRES_USER;
EOSQL
}

if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
	echo "Multiple database creation requested: $POSTGRES_MULTIPLE_DATABASES"
	for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr ',' ' '); do
		create_database $db
	done
	echo "Multiple databases created"
fi

It checks if there’s a POSTGRES_MULTIPLE_DATABASES environment variable. If there is, it gets database names by splitting the value by a comma. Then, it runs the create_database function, which creates a new database and grants all permissions to the user provided through the POSTGRES_USER environment variable.

Now, if we put this file into the ./docker/postgres folder, it will be automatically run before our scripts. This will come true because we mapped this folder to the volume, and the scripts are run alphabetically.

We need to change the of POSTGRES_DB into POSTGRES_MULTIPLE_DATABASES:

version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            # UPDATED TO MULTIPLE DATABASES
            - POSTGRES_MULTIPLE_DATABASES="postgres,blogs,auth"
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"
        volumes:
            - ./docker/postgres:/docker-entrypoint-initdb.d

Now, besides the default postgres database, two more will be created blogs and auth. You can be more creative here and use the shell scripts to customise even more database setup.

So we have a fully set-up database and can connect to it from our application, but wouldn’t it be good to have an IDE to view data?

PostgreSQL has a decent open-source web IDE called pgAdmin. It’s possible to use it as a Docker image. Let’s to it by extending our configuration!

version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            - POSTGRES_MULTIPLE_DATABASES="postgres,blogs,auth"
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"
        volumes:
            - ./docker/postgres:/docker-entrypoint-initdb.d


    pgadmin:
        container_name: pgadmin_container
        image: dpage/pgadmin4
        environment:
            - PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
            - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD:-postgres}
            - PGADMIN_CONFIG_SERVER_MODE=False
            - PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED=False
        ports:
            - "${PGADMIN_PORT:-5050}:80"
        depends_on:
            - postgres

Let’s discuss those a bit cryptic environment variables setup.

  • PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD - Sets the default credentials for the pgAdmin user. pgAdmin can also be hosted as a regular service (e.g. on a test environment) and have a more advanced user setup, but a single user for local development is more than enough.
  • PGADMIN_CONFIG_SERVER_MODE - determines whether pgAdmin runs in server mode (multi-user) or desktop mode (single-user). We’re setting it to false, so we won’t be prompted for login credentials. This is an annoying papercut we’re removing.
  • PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED - controls whether a master password is required to access saved server definitions and other sensitive information. By setting this to false, we skip the additional layer of password protection for server details in pgAdmin.

As you can see, we’re cutting security corners by just configuring the local development environment.

You may have noticed a weird syntax: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}. This setup means that if PGADMIN_DEFAULT_EMAIL is defined in the host environment, then its value is used. Otherwise, it will fall back to the default value (in our case pgadmin4@pgadmin.org).

You could pass such variables in the shell:

export PGADMIN_DEFAULT_EMAIL=myemail@example.com
export PGADMIN_DEFAULT_PASSWORD=securepassword
export PGADMIN_PORT=6666
docker-compose up

Or define the .env file in the same folder as our docker-compose.yml file, and Docker will use it automatically.

PGADMIN_DEFAULT_EMAIL=myemail@example.com
PGADMIN_DEFAULT_PASSWORD=securepassword
PGADMIN_PORT=6666

It’s pretty useful for security and changing variables without modifying the main script. Check also Docker Compose Profiles, one the most useful and underrated features to learn more about running the same file with multiple variations.

Getting back to our Docker Compose configuration. If we start containers now, we’ll also get the pgAdmin running on http://localhost:5050. We’ll be automatically logged in, but…

…but we won’t see any database automatically. How come?!

pgAdmin doesn’t do any automatic discovery. We could setup the connection manually, but then we’d need to repeat it each time we clean up our volumes. And that happens often if we’d like to clean our test data in a fresh environment (you can do it by running docker compose down -v).

Let’s change that and set up our server list automatically. Let’s start by defining the servers.json file inside the new docker/pgAdmin folder. And put there:

{
    "Servers": {
        "1": {
            "Group": "Servers",
            "Name": "Docker",
            "Host": "postgres",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres", "
            "Password": "Password12!",
            "SSLMode": "prefer",
            "Favorite": true
        }
    }
}

As you can see, we’re just configuring our database. We could define even more if we’d like to by adding “2”: { }” etc.

If you’re generating password randomly (e.g. in the CI/CD) then you can also define passfile and replace “Password”: “Password12!“, with “PassFile”: “/pgpass”,. Then you can keep the server setup intact, but just define the database password inside the passfile. We could put it in the docker/pgAdmin folder and put it inside:

postgres:5432:*:postgres:Password12!

Let’s go on this longer path to show the full setup. We need to adjust a bit our config:

version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            - POSTGRES_MULTIPLE_DATABASES="postgres,blogs,auth"
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"
        volumes:
            - ./docker/postgres:/docker-entrypoint-initdb.d


    pgadmin:
        container_name: pgadmin_container
        image: dpage/pgadmin4
        environment:
            - PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
            - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD:-postgres}
            - PGADMIN_CONFIG_SERVER_MODE=False
            - PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED=False
        ports:
            - "${PGADMIN_PORT:-5050}:80"
        depends_on:
            - postgres
        user: root
        entrypoint: /bin/sh -c "chmod 600 /pgpass; /entrypoint.sh;"
        volumes:
            - ./docker/pgAdmin/pgpass:/pgpass
            - ./docker/pgAdmin/servers.json:/pgadmin4/servers.json

We defined that our user needs to be root, as we need to be able to set up the proper permissions to the passfile. We can do such a setup by changing the entrypoint:

/bin/sh -c "chmod 600 /pgpass; /entrypoint.sh;"

Entrypoint is used to specify the command executed when the container is started. Here, we’re saying that before running the regular start-up command, run an additional shell script to set proper pgpass permissions using the chmod function.

We’re also mapping volumes to place pgpass and servers.json into proper folders. As you see, we can also map specific files instead of just directories.

Now, if we run the:

docker compose up

We should get pgAdmin with a preconfigured database. Sweet!

Some can say: “Boy that’s a lot of plumbing!”. One can be correct here, but this is the setup you do once and don’t care about. You can put this code inside the project repository and call it a day.

Yet, there’s another option. If you’d prefer to keep it simpler for others to use and customise to your project conventions, you can build your own pgAdmin image embedding the setup.

You could add Dockerfile with the following config (e.g. inside docker/pgAdmin folder):

# Use the official pgAdmin image as the base
FROM dpage/pgadmin4

# Set environment variables
ENV PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
ENV PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD:-postgres}
ENV PGADMIN_CONFIG_SERVER_MODE=False
ENV PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED=False

# Copy custom configuration files
COPY pgpass /pgpass
COPY servers.json /pgadmin4/servers.json

# Ensure the pgpass file has the correct permissions
RUN chmod 600 /pgpass

# Use root user to allow necessary permissions (as in your original setup)
USER root

# Set the entrypoint to the original entrypoint script
ENTRYPOINT ["/entrypoint.sh"]

# Expose the default port (80 inside the container)
EXPOSE 80

Then you can build it publish it to your container repository, and use it as:

```yml
version: "3"
services:
    postgres:
        image: postgres:15.1-alpine
        container_name: postgres
        environment:
            - POSTGRES_MULTIPLE_DATABASES="postgres,blogs,auth"
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=Password12!
        ports:
            - "5432:5432"
        volumes:
            - ./docker/postgres:/docker-entrypoint-initdb.d

     pgadmin:
        container_name: pgadmin_container
        image: event_driven_io_pgadmin:latest
        ports:
            - "${PGADMIN_PORT:-5050}:80"
        depends_on:
            - postgres

Read more in:

Of course, there’s a tradeoff that you then need to keep up to date, which has its costs. Typically, I’d not recommend doing it, but it can make sense for tools like pgAdmin that don’t change a lot. The choice is yours!

I hope that this article shows you how and why tweaking the default Docker setup can be useful and how that can cut those annoying papercuts, improving the overall developer experience.

If you get to this place, then you may also like my other articles around Docker and Continuous Integration:

Also feel free to contact me! if you think that I could help your project. I’m open on doing consultancy and mentoring to help you speed up and streghten your systems.

Cheers!

Oskar

p.s. Ukraine is still under brutal Russian invasion. A lot of Ukrainian people are hurt, without shelter and need help. You can help in various ways, for instance, directly helping refugees, spreading awareness, putting pressure on your local government or companies. You can also support Ukraine by donating e.g. to Red Cross, Ukraine humanitarian organisation or donate Ambulances for Ukraine.

👋 If you found this article helpful and want to get notification about the next one, subscribe to Architecture Weekly.

✉️ Join over 6500 subscribers, get the best resources to boost your skills, and stay updated with Software Architecture trends!

Loading...
Event-Driven by Oskar Dudycz
Oskar Dudycz For over 15 years, I have been creating IT systems close to the business. I started my career when StackOverflow didn't exist yet. I am a programmer, technical leader, architect. I like to create well-thought-out systems, tools and frameworks that are used in production and make people's lives easier. I believe Event Sourcing, CQRS, and in general, Event-Driven Architectures are a good foundation by which this can be achieved.

Filtering EventStoreDB subscriptions by event types

Setting up NGINX load balancer for .NET WebApi