Using PostgreSQL in Docker

Technically, to implement the database, I created a docker config based on PostgreSQL official image. The feature branch is available here :

https://github.com/kolok/technical-stack-for-b-to-c-business/tree/feature/postgresql

On each part of the infrastructure, I will create sub folder and in each subfolder, I will add a README file to explain this part directly in the code repository. For the PostgreSQL part which is the subject in this post, the README file can be found here :

https://github.com/kolok/technical-stack-for-b-to-c-business/blob/feature/postgresql/postgresql/README.postgresql.md

PostgreSQL docker-compose

Here is the docker-compose.yml file which implement database :

# My Ideal Technical Stack
# Owner: Nicolas Oudard
# Usage: docker-compose up -d

# docker-compose needed
version: '3'

# docker network should be defined
# let's start with one network "default"
networks:
  default:

services:
# Here is the postgreSQL engine exposed on 5431
  db:
    image: postgres
    restart: always
# opened port 5432 to be able to connect to the db using a postgresql client direct in a shell
# Ex: psql -p 5432 -h 127.0.0.1 -U myits-user
# Else, if the port is not opened you can use the "docker exec" command
# docker exec -it technicalstackforbtocbusiness_db_1 psql -p 5432 -h 127.0.0.1 -U myits-user
#    ports:
#      - 5432:5432
    environment:
# Use a dedicate user for this pproject with a complex password
      POSTGRES_USER: myits-user
      POSTGRES_PASSWORD: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# PGDATA is the volume where the postgresql data file will be stored
      PGDATA: /var/lib/postgresql/data/myitsdata
# mount posgresql volume on your own computer to persist the data
    volumes:
        - /var/lib/postgresql/data/myitsdata:/var/lib/postgresql/data/myitsdata:rw

Official postgres docker image is used.

  db:
    image: postgres
    restart: always

Some environment variables are set to defined credentials and where should be store the postgre related files in the docker

    environment:
# Use a dedicate user for this pproject with a complex password
      POSTGRES_USER: myits-user
      POSTGRES_PASSWORD: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# PGDATA is the volume where the postgresql data file will be stored
      PGDATA: /var/lib/postgresql/data/myitsdata

Volume is mounted to be able to keep the data even if the docker is stopped and restarted

    volumes:
        - /var/lib/postgresql/data/myitsdata:/var/lib/postgresql/data/myitsdata:rw

We don’t need to open the port 5432 but if you need to interact with your database from your computer, you can do it using the setting

    ports:
      - 5432:5432

Initialize the Database

To set the database schema the first time you need it or to reset the schema, I create a script here.

drop   database myitsdb;
create database myitsdb;

\c myitsdb;
create type status AS ENUM ('active', 'inactive', 'deleted_at');
create type roles AS ENUM ('admin', 'employee');

create table users (
    id serial primary key,
    email varchar(255) not null,
    ....
);

create table compagnies (
    id serial primary key,
    name varchar(255) default null,
    ....
);
.....

It began by dropping the database and create it again, then connect to it.

drop   database myitsdb;
create database myitsdb;

\c myitsdb;

Then, it create the roles

create type status AS ENUM ('active', 'inactive', 'deleted_at');
create type roles AS ENUM ('admin', 'employee');

And the tables

create table users (
    ....
);

create table compagnies (
    ....
);

create table compagnies_users_roles (
    ....
);

create table products (
    ....
);

create table subscriptions (
    ....
);

create table tokens (
    ....
);

I create also some fixtures to populate the database with samples :

You’ll find how to use all these files in the README.postgresql.md file.

Example :

  $ docker exec -i technicalstackforbtocbusiness_db_1 psql -p 5432 -h 127.0.0.1 -U myits-user < postgresql/myits.00.db.sql

it run:

psql -p 5432 -h 127.0.0.1 -U myits-user < postgresql/myits.00.db.sql

Inside the docker technicalstackforbtocbusiness_db_1.

I am over for now about configuring PostgreSQL in a docker. I am sure that I forgot some tricks that would be useful but as any computer science project I will iterate and comeback to edit this post if necessary.

And now, let’s work on API and user management !

< Previous post
Next post >