postgres-docker-init

A self-contained project that provisions a PostgreSQL database using Docker and Docker Compose, seeds it with CSV data via a SQL initialisation script, and interacts with it through Python scripts.

---

Table of Contents

• Overview
• Project Structure
• Prerequisites
• Setup Instructions
  • macOS / Linux
  • Windows
• Environment Variables
• Usage
• Troubleshooting
• Contributing
• Acknowledgements
• License

---

Overview

This project spins up a Dockerised PostgreSQL 18 instance, automatically creates an ink_store schema, defines a customers table, and loads it with data from a CSV file — all on first container start. A Python layer handles database connection and query execution via psycopg2, with credentials managed through environment variables.

Key capabilities:

• Dockerised PostgreSQL 18 setup with Docker Compose
• Automatic schema creation, table definition, and CSV ingestion via init.sql
• Python scripts for database connection management and query execution
• Environment-based configuration for portability and security

---

Project Structure

postgres-docker-init/
├── data/
│   └── customers.csv                        # Dataset loaded into ink_store.customers
├── infrastructure_scripts/
│   └── init.sql                             # Creates schema, table, and loads CSV data
├── src/
│   ├── db_manager.py                        # Database connection and query logic
│   └── main.py                              # Entry point — executes a sample query
├── .env.example                             # Environment variable template
├── .gitignore
├── docker-compose.yml                       # Docker Compose configuration
├── docker-compose.template                  # Parameterised compose template
├── requirements.txt                         # Python dependencies
├── LICENSE
└── README.md

---

Prerequisites

Tool	Version	Notes
Docker	Latest	Must be running
Docker Compose	v2+	Included with Docker Desktop
Python	3.7+
pip	Latest	Comes with Python

---

Setup Instructions

macOS / Linux

1. Clone the repository:

   git clone https://github.com/victorcezeh/postgres-docker-init.git
   cd postgres-docker-init

2. Create your .env file:

   cp .env.example .env
   # Fill in your values

3. Install Python dependencies:

   pip install -r requirements.txt

4. Start the Postgres container:

   docker compose up -d

5. Run the Python script:

   python src/main.py

Windows

1. Clone the repository:

   git clone https://github.com/victorcezeh/postgres-docker-init.git
   cd postgres-docker-init

2. Create your .env file:

   • Duplicate .env.example, rename it to .env, and fill in your values.

3. Install Python dependencies:

   pip install -r requirements.txt

4. Start the Postgres container:

   docker compose up -d

5. Run the Python script:

   python src\main.py

---

Environment Variables

Create a .env file at the root of the project based on .env.example:

POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_secure_password
POSTGRES_DB=your_db
POSTGRES_PORT=5434
POSTGRES_HOST=localhost

Note: Never commit your .env file to version control. It is already listed in .gitignore.

---

Usage

Start the Postgres container

docker compose up -d

On first start, init.sql runs automatically — it creates the ink_store schema, defines the customers table, and loads data from customers.csv.

Query the database via Python

python src/main.py

Connects to the running PostgreSQL instance and executes a row count query against ink_store.customers, printing the result to stdout.

Connect via psql

docker exec -it <container_name> psql -U $POSTGRES_USER -d $POSTGRES_DB

Once connected, inspect the schema and table:

\dt ink_store.*
SELECT * FROM ink_store.customers LIMIT 5;

Connect via pgAdmin 4

Field	Value
Host	localhost
Port	5434
Database	value of POSTGRES_DB in your .env
Username	value of POSTGRES_USER in your .env
Password	value of POSTGRES_PASSWORD in your .env

Stop the container

docker compose down

To remove the volume and reset all data:

docker compose down -v

---

Troubleshooting

Container won't start / data directory conflict

• This typically occurs when the pg_data volume contains data written by a different PostgreSQL version.
• Fix: delete the pg_data directory and restart.

  docker compose down -v
  docker compose up -d

Running PostgreSQL older than version 18

• PostgreSQL 18 changed the expected volume mount point from /var/lib/postgresql/data to /var/lib/postgresql. Using the wrong path will cause the container to fail on startup.
• docker-compose.yml uses the PostgreSQL 18+ mount point: ./pg_data:/var/lib/postgresql
• If you are running PostgreSQL 17 or earlier, use docker-compose.template instead, which mounts at ./pg_data:/var/lib/postgresql/data.

Python can't connect to the database

• Confirm the container is running: docker ps
• Verify all values in .env match the docker-compose.yml configuration.
• Ensure psycopg2-binary is installed: pip install psycopg2-binary

Tables not found after container starts

• init.sql only runs on first container initialisation. If pg_data already existed from a previous run, the script is skipped.
• Fix: wipe the volume with docker compose down -v and restart.

Port conflict

• The default port is 5434. If something else is using it, update POSTGRES_PORT in your .env.

---

Contributing

1. Fork the repository.
2. Create a new branch: git checkout -b feature/your-feature-name
3. Commit your changes: git commit -m "Add your message"
4. Push to your branch: git push origin feature/your-feature-name
5. Open a pull request with a clear description of your changes.

---

Acknowledgements

Special thanks to AltSchool Africa and JesuFemi-O for the guidance and curriculum that made this project possible.

---

License

This project is licensed under the MIT License.