Tutorials

Docker Compose setup for Postgres13 and PgAdmin4

Posted on 04-Feb-2021

In the following article I'll show you how to create a local setup for Postgres and PgAdmin4 with Docker Compose.

To follow the steps in this tutorial, you'll need to have docker and docker-compose commands installed.

Table of content:

Create project directory and structure

Run the following commands in your terminal

# create project directory
mkdir postgres-pgadmin

# go into project directory
cd postgres-pgadmin

# create pgadmin configurations
mkdir -p docker/pgadmin/{config,sql}

# create postgres directory
mkdir -p docker/postgres

Create postgres files

In the beginning we'll have only one file for postgres configuration and that would be the declaration of envrionment variables. Create file ./docker/postgres/postgres.env with the folloing content:

POSTGRES_DB=kisphp
POSTGRES_USER=kisphpuser
POSTGRES_PASSWORD=kisphppass

Create pgadmin files

Just like with postgres, we'll start with the environment variables file for pgadmin as well. Create file ./pgadmin/pgadmin.env with the following content:

PGADMIN_DEFAULT_EMAIL=admin@example.com
PGADMIN_DEFAULT_PASSWORD=parknow

The envrionment variables from ./pgadmin/pgadmin.env file will be used to login into pgadmin platform, NOT for the postgres database.

Create file ./docker/pgadmin/config/servers.json with the following content:

{
  "Servers": {
    "1": {
      "Name": "postgres",
      "Group": "Servers",
      "Host": "postgres13",
      "Port": "5432",
      "MaintenanceDB": "postgres",
      "Username": "kisphpuser",
      "SSLMode": "prefer",
      "PassFile": "/pgpassfile"
    }
  }
}
The servers.json file is optional, but without it, you'll have to configure the server connection everytime you start pgadmin.

Let's have a closer look of what we have in this file:

Key Description
Name The name of the server displayed in the list of available servers to connect
Group A group that you can provide to better structure your servers
Host The hostname or IP of your server. In our case, we'll use the name of the service declared in docker-compose.yml file.
Port The port on which the database is listening on
Username The username that you provide in docker compose file

Create file ./docker/pgadmin/Dockerfile with the following content:

FROM dpage/pgadmin4

COPY config/servers.json /pgadmin4/servers.json
COPY sql/ /var/lib/pgadmin/storage/admin_example.com/

# the following commands are used for debug purposes only
USER root
RUN apk add bash curl jq vim

Create ./docker/pgadmin/sql/create-table.sql file with the following content:

CREATE TABLE lunchorders(student_id int, orders jsonb);

Create ./docker/pgadmin/sql/insert-rows.sql file with the following content:

INSERT INTO lunchorders VALUES(100, '{
	"order_date": "2020-12-11",
	"order_details": {
    	"cost": 4.25,
        "entree": ["pizza"],
    	"sides": ["apple", "fries"],
    	"snacks": ["chips"]}
	}'
);

INSERT INTO lunchorders VALUES(100, '{
	"order_date": "2020-12-12",
	"order_details": {
    	"cost": 4.89,
        "entree": ["hamburger"],
    	"sides": ["apple", "salad"],
    	"snacks": ["cookie"]}
	}'
);

Create ./docker/pgadmin/sql/filter-orders.sql file with the following content:

SELECT orders
FROM lunchorders
WHERE orders ->> 'order_date' = '2020-12-11';

Create ./docker/pgadmin/sql/list-databases.sql file with the following content:

SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname ASC

Create docker-compose file

Create file ./docker-compose.yml with the following content:

version: "3"
services:
  postgres13:
    image: postgres:13
    env_file:
      - docker/postgres/postgres.env
    ports:
    - 5432:5432

  pgadmin:
    build:
      dockerfile: Dockerfile
      context: docker/pgadmin
    env_file:
      - docker/pgadmin/pgadmin.env
    ports:
    - 8484:80

What we're doing here ?

First, we'll run a docker container for postgres 13 with environment variables loaded from our file to setup credentials and to precreate the database.

Second, we'll run a docker container for pgadmin 4, which will be built from our Dockerfile and will load the environment variables from our file.

To see it in action run the following command:

docker-compose up --build -d

Then open in your browser url: http://localhost:8484 and authenticate with admin@example.com and password: admin

Once you login into the platform, you'll see the dashboard of pgadmin, and in the left side (tree menu), click on the Servers item. You will be prompted to add the database password which should be kisphppass if you used the values from the example. There will be a checkbox to save the password, and if you click it, it will save the password internally in pgadmin's database (sqlite3).

Now, in the tree menu, navigate to Servers > Postgres > Databases > kisphp > Schemas > Public > Tables.

There shouldn't be any table in our database since we've just created the database server.

Let's run the queries we added earlier:

Right click on Tables and select Query Tool.

The query editor page will open and if you click the open button, you'll see the list with our sql files.

clik on open button

From the files list select create-table.sql and then click on Select button.

On the same line with the open button, you'll be able to see a play button. Click on it to execute the query and create the database.

Now, do the same thing for insert-rows.sql, filter-orders.sql and list-databases.sql files.

As you have added those files, you can add more useful queries for daily usage.