Metabase + PSQL and pgadmin

The idea is to use Docker to facilitate sharing this project.

1. Build a metabase data viz
2. Build a PSQL db to persist local data
3. Integrade PSQL db with pgadmin to have a friendly interface

Quick Startup

git clone the repo

    git clone [email protected]:(youruserhere)/Metabase-Psql-Docker.git

Enter at the cloned repository and create the .env file

    MB_PORT=3000PSQL_SERVER_PORT=5432
    PGADM_SERVER_PORT_IN=5050
    PGADM_SERVER_PORT_OUT=80
    PSQL_USER=postgres
    PSQL_PSWD=postgres
    [email protected]
    PGADM_PSWD=postgres

Run it with docker compose

    docker-compose up

How to use it (step-by-step)

Requirements
1. Docker
2. environment variables
3. RDBMs
4. (opt) Some knowledge of docker is helpful
5. (opt) Some knowledge of data viz

Install docker engine and docker compose

If you don`t have it already, this is a must have for this to work.

Use this link to install and test it -> Read it carefully before begining

Check docker-compose

There is a way to check it the docker-compose script is working (not necessarily without running errors) and not building due to sintax errors

docker-compose -f docker-compose.yml config

This is very useful when building container that are time consuming 

set up you environment variables

First thing to do is to make you own .env file. This will be used to build the docker compose with the right configuration.

Therefore, in your root folder or ./ path create a .env file as shown below.

Create it using terminal nano for Linux kernel

Create it using a desired Graphical user interface (GUI)

Insert the .env following informations

MB_PORT=3000
PSQL_SERVER_PORT=5432
PGADM_SERVER_PORT_IN=5050
PGADM_SERVER_PORT_OUT=80
PSQL_USER=postgres
PSQL_PSWD=postgres
[email protected]
PGADM_PSWD=postgres

Using docker-compose

After everything above, you can setup you own metabase, psql and pgadmin using the following command

docker-compose up --build -d 

Start

Build

After building and runnin, check it using

docker ps 

Accessing

Pgadmin

enter a browser and insert the following

0.0.0.0:5050 

(localhost:port given by PGADM_SERVER_PORT_IN)

Use the user and password defined by PGADM_EMAIL and PGADM_PSWD env variables

The entrance panel

Register the psql server you want to directly access the data

Enter the server name

define the host or ip and enter the db user. Use psql_db to define the host (this is the internal psql container DSN)

Metabase

0.0.0.0:3000 

(localhost:port given by MB_PORT)

The admin user is created by the first access

Add a database (internal as shown below or else) Only admin can do this

Psql db

This one is a little more complicated. This is because you will need to enter the docker psql_db and run psql command to enter it. You must use a terminal for this.

Get the container ID of the psql_db container:

docker exec -it [Container_ID] bash

exec -> execute a direct command in a container
-i -> have interactivity 
-t -> with pseudo terminal 
bash -> enter the bash or container terminal

After entering the container, execute the command to access the psql application giving the user you defined in PSQL_USER

psql -d postgres -U postgres -W 

-d -> specifc database to enter (by standard, every psql starts with a postgres db)
-U -> User 
-W -> ask for password

General information

Metabase

Free dataviz to build and share analytical dashboards

Metabase

Metabase on Docker

PSQL

Relational DB useful for storing data as well as serve as building block for analytics or prototyping with SQL

PostgreSQL

PostgreSQL on Docker

pgadmin

Visual interface integrated with PSQL DB

Pgadmin

Pgadmin on Docker

References

Useful links for you own project

[1] https://www.automacaodedados.com.br/stories/configurando-metabase-e-postgresql-no-docker/

[2] https://dev.to/stefanopassador/docker-compose-with-python-and-posgresql-33kk

[3] https://www.dabbleofdevops.com/blog/setup-a-postgres-python-docker-dev-stack

[4] https://towardsdatascience.com/a-data-scientist-approach-running-postgres-sql-using-docker-1b978122e5e6

[5] https://dev.to/shree_j/how-to-install-and-run-psql-using-docker-41j2#:~:text=Connecting%20to%20the%20PSQL%20server%20via%20CLI%20%3A&text=Run%20the%20below%20command%20to,p%205432%20%2DU%20postgres%20%2DW

[6] https://towardsdatascience.com/how-to-build-slim-docker-images-fast-ecc246d7f4a7

[7] https://www.postgresqltutorial.com/postgresql-tutorial/import-csv-file-into-posgresql-table/#:~:text=First%2C%20right%2Dclick%20the%20persons,the%20delimiter%20as%20comma%20(%20%2C%20)%3A