How to install postgresql on docker

You have succeeded in setting up docker engine in Fedora 32 and installing docker compose as well.

Now it is time to install postgresql as a docker container.

But first things first, Why install postgresql as a docker container rather than the usual popular way?

Reasons for containerization of applications/databases.

1. Software installation is quite chaotic especially if you have various versions running in your system. version clashes, upgrades and alot of productive time lost in this mundane task.
2. Container images launches faster as compared to applications installed directly in your Operating System
3. In this age of DevOps Culture,a methodology defined by high levels of interactivity between various stakeholders(DevOps Engineers, QA Automation Engineers and of, course Developers),the abstraction actually enhances the flexibility, portability and hence the agility of these teams in terms of delivery.
4. From a personal experience, this containerization has actually helped me alot when dealing with my highly partitioned OS, prior to that some of them e.g postgresql and plant-uml server were quite hard when configuring them.

Enough of that.Not convinced? Anyway, here is the process of setting up postgres as a docker container.

Step 1: Create a Docker Compose file and run it

Ensure the docker service is running, trigger this manually.

1

$ sudo systemctl start docker

1.1 Create a Docker Compose file

Here is your docker compose file configured with latest postgres The docker-compose.yml file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

version: "3.3"
services:
  database:
    image: postgres:latest # use latest official image
    env_file:
      - database.env # configure postgres
    volumes:
      - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
    ports:
      - 5432:5432
volumes:
  database-data: # names volumes can be managed easier using docker-compose

1.2 Create an environment variable file

Now database.env file

POSTGRES_USER=tester
POSTGRES_PASSWORD=test
POSTGRES_DB=family

Type the following command to run postgres docker container.Make sure the above files are in the same directory and the terminal is also pointing there before executing the following command.

1
2

$ docker-compose up

Expected output from terminal logs

1
2
3
4
5
6
7

database_1  | 2020-05-15 18:03:32.502 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
database_1  | 2020-05-15 18:03:32.503 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
database_1  | 2020-05-15 18:03:32.503 UTC [1] LOG:  listening on IPv6 address "::", port 5432
database_1  | 2020-05-15 18:03:32.568 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
database_1  | 2020-05-15 18:03:32.749 UTC [25] LOG:  database system was shut down at 2020-05-15 18:03:24 UTC
database_1  | 2020-05-15 18:03:32.839 UTC [1] LOG:  database system is ready to accept connections

We are interested in this part database system is ready to accept connections

Step 2: Connect your running container with you database client

In my case,I usually use DBeaver, you can use pgAdmin or Adminer Client.

TODO: Actually you can use a docker container for the client e.g Adminer Docker Image but that is for another day.

Step 3: Create tables in your database and add some data

Right click PostgreSQL - family > SQL Editor .Paste and execute the following sql scripts.

3.1 Create tables

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DROP TABLE IF EXISTS parents CASCADE;
CREATE TABLE parents (
  parent_id INTEGER PRIMARY KEY,
  name VARCHAR
);

DROP TABLE IF EXISTS children;
CREATE TABLE children (
  child_id INTEGER PRIMARY KEY,
  name VARCHAR,
  birthdate DATE,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES parents
);

3.2 Insert sample data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO parents VALUES
    (1, 'Jim')
  , (2, 'Jenny')
  ;

INSERT INTO children VALUES
    (1, 'Tamara', '2017-02-01', 1)
  , (2, 'Tim', '2000-11-02', 2)
  , (3, 'Tom', '2005-10-01', 1)
  , (4, 'Theresa', '2017-04-30', 2)
  , (5, 'Tonja', '2011-07-17', 1)
  ;

3.3 Do some queries

1

SELECT * FROM parents JOIN children USING (parent_id) ORDER BY parent_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    parent_id
  , p.name
  , array_agg(json_build_object(
      'child_id', c.child_id
    , 'name', c.name
    , 'birthdate', c.birthdate
  )) as children
FROM parents p
JOIN children c USING (parent_id)
GROUP BY 1, 2;

After closing our database client and stopping the container, we should later confirm if indeed our data was persisted.

Conclusion

You have achieved the following:

1. Created a Docker Compose file and an environment file as well, the run it.
2. Connected your running container with your database client.
3. Created tables in your database and added some data.

Having done that now we have chance to look into automating starting of some specific docker containers at system boot.

Troubleshooting

Sometimes you may find out that you did something to your database and can’t seem to fix it.You just wish to nuke everything and start on a clean slate.So it would be better to completely delete the docker volumes holding the databases and recreating them afresh.

Here are steps you can follow:

1. Delete all the containers

This is done prior to deleting the volumes.

1

$ docker rm $(docker ps -a -q) -f

If it is running you can either stop it or forcefully delete it using the following command

1

$ docker rm --force <container_id>

container_id, you will get it from the first command.

2. Delete all the volumes

1

$ docker volume prune

or be more specific if you don’t want to delete all the volumes

1

$ docker volume ls

1

$ docker volume rm <volume_name>

3. Recreate the volumes

Recreating is simply starting the application

1

$ docker-compose up build

If using docker-compose.yml, you may as well recreate the database.

1

$ docker-compose up -d --force-recreate database

The name database is actually one of our service in the docker-compose file(just look at it closely).

But before executing the above command you may need to check your environment variables.I had actually messed up on this one.

1

$ docker-compose exec database env

You can try connecting to server using following command, verify the role tester here is actually the one appearing in the output of above command.

1

$ docker-compose exec database  psql -U tester

If you need to kill the container docker-compose kill, then docker-compose rm to remove it.

Sometimes removing the images, containers etc, works so docker-compose down.

References

• Getting Started with PostgreSQL using Docker-Compose