Postgres worksheet

This project provides a preconfigured environment for working with PostgreSQL database infrastructure using Docker Compose. It includes the following services:

• PostgreSQL - the primary database service with custom configuration;
• PgBouncer - a lightweight connection pooler for PostgreSQL;
• pgAdmin - a web-based PostgreSQL database management tool.

Architecture

Components

Features

This preconfigured project environment provides the following features:

• PostgreSQL

  • Custom configuration through a mounted postgresql.conf file;
  • Automated data model creation with scripts from mounted docker-entrypoint-initdb.d folder;
  • Persistent data model storage via volumes;
  • Resources optimal setup - CPU (2) and memory (4G);
  • Health checks to ensure the database is ready before other services start;
  • Isolated in the internal Docker network;

• PgBouncer;

  • Connection pooling for database to optimize resource usage;
  • SCRAM-SHA-256 authentication for secure connections;
  • Resources optimal setup - CPU (0.5) and memory (1G);
  • Accessible via the external Docker network;

• pgAdmin;

  • Persistent configurations storage via volumes;
  • Resources optimal setup - CPU (0.5) and memory (1G);

Data model

In this project, an e-commerce data model is implemented by default to manage customers, products and related orders. The data model is created automatically when the database is first started and is populated with synthetic records:

• Customers - table with information about 10,000 randomly generated customers;
• Products - table with information about 100,000 randomly generated products;
• Orders - table with information about 1,000,000 randomly generated orders;

Usage

Prerequisites

Make sure you have Docker and Docker Compose installed on your machine.

Setup

Project uses an .env file with environment variables to manage configurations and tool settings. The file should be created using the .env.example file provided in the root directory using the following command:

cp .env.example .env

And then filled with the corresponding values:

POSTGRES_DB=<database-name>
POSTGRES_USER=<postgres-user>
POSTGRES_PASSWORD=<postgres-password>

PGADMIN_DEFAULT_EMAIL=<pgadmin-email>
PGADMIN_DEFAULT_PASSWORD=<pgadmin-password>

POSTGRESQL_HOST=postgres
POSTGRESQL_PORT=5432

Run

Build and start the database services infrastructure by running:

docker compose up -d --build

Note

First run will take some time, as all necessary docker images will be loaded and the data model will be created and populated with synthetic records.

Check that all services are successfully started by running:

docker compose ps

To stop all running services, execute:

docker compose down

Note

After stopping the services, the data will not be deleted and will be available when the services are restarted.

If you want to stop the services and remove all associated data use:

docker compose down --volumes

Warning

This command deletes all service data (e.g. data model, stored passwords and configurations).

License

This project is licensed under the MIT License.