Using PostgreSQL in Docker
Technically, to implement the database, I created a docker config based on PostgreSQL official image. This feature is available in the dedicated githut repository :
https://github.com/kolok/myits-db
On each part of the infrastructure, I will create a sub project host in its own github repository. In each project a README file explains the specificity of the subject.
For the PostgreSQL part which is the subject in this post, the README file can be found here :
https://github.com/kolok/myits-db/blob/develop/README.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 myits-db 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 container_name: myits-db
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 :
- myits.01.users.sql
- myits.02.compagnies.sql
- myits.03.compagnies_users_roles.sql
- myits.04.products.sql
- myits.05.subscriptions.sql
You’ll find how to use all these files in the README.md file.
Example :
$ docker exec -i myits-db 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 myits-db.
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.
Leave a Reply