Overview and usage of databases for data infrastructure purposes, docker-compose
provided.
It is always important to define external volumes connected with the database. Those volumes can be easily backed-up or synced on a cloud file system.
Usage of relational databases in different projects.
Robust and stable database, used in different deployment as main database, great connections with BI tools and powerful geo spatial queries.
The useful files to create the container are under postgres.
Once created the credential for the root user
POSTGRES_USER=${DB_USER}
POSTGRES_PASSWORD=${DB_PASS}
POSTGRES_DB=api_ingest
The correspondent docker-compose is:
services:
db:
image: postgres
restart: unless-stopped
env_file:
- postgres/database.env
ports:
- '127.0.0.1:5432:5432'
volumes:
- db-data:/var/lib/postgresql/data
- ./postgres/init.sql:/docker-entrypoint-initdb.d/create_tables.sql
- ./postgres/postgres.conf:/etc/postgresql/postgresql.conf
#command: postgres -c config_file=/etc/postgresql/postgresql.conf
healthcheck:
test: ["CMD", "pg_isready", "-U", "api_ingest"]
interval: 5s
retries: 5
networks:
- traefik-net
Connections to the database can be performed locally or with an orchestrator (swarm, kubernetes).
Mariadb is an optimal option for websites and lightweight applications, it’s more performant and stable than mysql environment
MARIADB_ROOT_PASSWORD=${DB_PASS_ROOT}
MARIADB_DATABASE=${DB_NAME}
MARIADB_USER=${DB_USER}
MARIADB_PASSWORD=${DB_PASS}
docker-compose
services:
mysql:
#image: mysql
image: mariadb
restart: unless-stopped
env_file:
- mysql/mariadb.env
#- mysql/database.env
volumes:
- ${HOME}/mysql-data:/var/lib/mysql
networks:
- webserver-net
We can define each access user with such queries:
create user dash_ro with password '';
grant select on all tables in schema "public" to dash_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dash_ro;