This workshop will guide you through setting up and managing a PostgreSQL database using Docker.
You will also explore how to use pgAdmin GUI to interact with the database and perform various tasks.

You will have to add to the Workshop Files & Scripts section your own specific implementation

• see: Markdown Guide and Writing and Formatting in Github for modifying this Readme.md file accordingly.

Before you begin, ensure you have the following installed on your system:

• Docker: Install Docker

By the end of this workshop, you will:

• Understand how to set up PostgreSQL and pgAdmin using Docker.
• Learn how to use Docker volumes to persist database data.
• Gain hands-on experience with basic and advanced database operations.

This workshop introduces key database concepts and provides hands-on practice in a controlled, containerized environment using PostgreSQL within Docker.

1. Entity-Relationship Diagram (ERD):

   • Designed an ERD to model relationships and entities for the database structure.
   • Focused on normalizing the database and ensuring scalability.

   [Add ERD Snapshot Here]

images/erd/addimagetoreadme.PNG

images/erd/airbnb.PNG

images/erd/one.jpg

(Upload or link to the ERD image or file)

2. Data Structure Diagram (DSD):

   • Described the DSD for the model relationships and entities of the database structure.
   • Focused on normalizing the database and ensuring scalability.

   [Add DSD Snapshot Here]

3. Creating Tables:

   • Translated the ERD into actual tables, defining columns, data types, primary keys, and foreign keys.
   • Utilized SQL commands for table creation.

   [Add Table Creation Code Here] (Provide or link to the SQL code used to create the tables)

4. Generating Sample Data:

   • Generated sample data to simulate real-world scenarios using SQL Insert Statements.
   • Used scripts to automate bulk data insertion for large datasets.

   [Add Sample Data Insert Script Here] (Upload or link to the sample data insert scripts)

5. Writing SQL Queries:

   • Practiced writing SELECT, JOIN, GROUP BY, and ORDER BY queries.
   • Learned best practices for querying data efficiently, including indexing and optimization techniques.

   [Add Example SQL Query Here] (Provide or link to example SQL queries)

6. Stored Procedures and Functions:

   • Created reusable stored procedures and functions to handle common database tasks.
   • Used SQL to manage repetitive operations and improve performance.

   [Add Stored Procedures/Function Code Here] (Upload or link to SQL code for stored procedures and functions)

7. Views:

   • Created views to simplify complex queries and provide data abstraction.
   • Focused on security by limiting user access to certain columns or rows.

   [Add View Code Here] (Provide or link to the SQL code for views)

8. PostgreSQL with Docker:

   • Set up a Docker container to run PostgreSQL.
   • Configured database connections and managed data persistence within the containerized environment.

   [Add Docker Configuration Code Here] (Link to or provide the Docker run command and any configuration files)

By the end of this workshop, you should be able to:

• Design and create a database schema based on an ERD.
• Perform CRUD (Create, Read, Update, Delete) operations with SQL.
• Write complex queries using joins, aggregations, and subqueries.
• Create and use stored functions and procedures for automation and performance.
• Work effectively with PostgreSQL inside a Docker container for development and testing.

• Use pg_dump to back up your database and pg_restore or psql to restore it.

# Backup the database
pg_dump -U postgres -d your_database_name -f backup.sql

# Restore the database
psql -U postgres -d your_database_name -f backup.sql

• Create indexes on frequently queried columns and analyze query performance.

-- Create an index
CREATE INDEX idx_your_column ON your_table(your_column);

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

• Create user roles and assign permissions to database objects.

-- Create a user role
CREATE ROLE read_only WITH LOGIN PASSWORD 'password';

-- Grant read-only access to a table
GRANT SELECT ON your_table TO read_only;

• Write advanced SQL queries using window functions, recursive queries, and CTEs.

-- Example: Using a window function
SELECT id, name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

• Use PostgreSQL's built-in tools to monitor database performance.

-- View active queries
SELECT * FROM pg_stat_activity;

-- Analyze table statistics
SELECT * FROM pg_stat_user_tables;

• Install and use PostgreSQL extensions like pgcrypto or postgis.

-- Install the pgcrypto extension
CREATE EXTENSION pgcrypto;

-- Example: Encrypt data
INSERT INTO users (username, password) VALUES ('alice', crypt('password', gen_salt('bf')));

• Automate database maintenance tasks (e.g., backups) using cron jobs.

# Example: Schedule a daily backup at 2 AM
0 2 * * * pg_dump -U postgres -d your_database_name -f /backups/backup_$(date +\%F).sql

• Write unit tests for your database using pgTAP.

-- Example: Test if a table exists
SELECT * FROM tap.plan(1);
SELECT tap.has_table('public', 'your_table', 'Table should exist');
SELECT * FROM tap.finish();

• Problem: Unable to connect to the PostgreSQL or pgAdmin container.
• Solution:
  • Ensure both the PostgreSQL and pgAdmin containers are running. You can check their status by running:

    docker ps

  • Verify that you have the correct container names. If you are unsure of the names, you can list all containers (running and stopped) with:

    docker ps -a

  • Ensure that the correct ports are mapped (e.g., 5432:5432 for PostgreSQL and 5050:80 for pgAdmin).
  • Verify that the postgres container's name is used in pgAdmin's connection settings.
  • If using localhost and experiencing connection issues, try using the container name instead (e.g., postgres).
  • Check the logs for any error messages:

    docker logs postgres
    docker logs pgadmin

  • If you are still having trouble, try restarting the containers:

    docker restart postgres
    docker restart pgadmin

• Problem: You've forgotten the password for pgAdmin or PostgreSQL.
• Solution:
  • For pgAdmin:
    1. Stop the pgAdmin container:

       docker stop pgadmin

    2. Restart the container with a new password:

       docker run --name pgadmin -d -p 5050:80 -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=new_password dpage/pgadmin4:latest

  • For PostgreSQL:
    1. If you've forgotten the POSTGRES_PASSWORD for PostgreSQL, you’ll need to reset it. First, stop the container:

       docker stop postgres

    2. Restart it with a new password:

       docker run --name postgres -e POSTGRES_PASSWORD=new_password -d -p 5432:5432 -v postgres_data:/var/lib/postgresql/data postgres

• Problem: Port is already in use on the host machine (e.g., port 5432 or 5050).
• Solution:
  • If a port conflict occurs (for example, PostgreSQL's default port 5432 is already in use), you can map a different host port to the container's port by changing the -p flag:

    docker run --name postgres -e POSTGRES_PASSWORD=your_password -d -p 5433:5432 -v postgres_data:/var/lib/postgresql/data postgres

    This would map PostgreSQL’s internal 5432 to the host’s 5433 port.
  • Similarly, for pgAdmin, you can use a different port:

    docker run --name pgadmin -d -p 5051:80 -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=admin dpage/pgadmin4:latest

• Problem: You cannot access pgAdmin through http://localhost:5050 (or other port you have set).
• Solution:
  • Ensure the pgAdmin container is running:

    docker ps

  • Double-check that the port mapping is correct and no firewall is blocking the port.
  • If using a non-default port (e.g., 5051 instead of 5050), ensure you access it by visiting http://localhost:5051 instead.

• Problem: After stopping or removing the PostgreSQL container, the data is lost.
• Solution:
  • Ensure that you are using a Docker volume for data persistence. When starting the container, use the -v flag to map the volume:

    docker run --name postgres -e POSTGRES_PASSWORD=your_password -d -p 5432:5432 -v postgres_data:/var/lib/postgresql/data postgres

  • To inspect or back up the volume:

    docker volume inspect postgres_data

• Problem: If you are trying to connect from pgAdmin to PostgreSQL and the connection is unsuccessful.
• Solution:
  • Make sure both containers (PostgreSQL and pgAdmin) are on the same Docker network:

    docker network create pg_network
    docker run --name postgres --network pg_network -e POSTGRES_PASSWORD=your_password -d -p 5432:5432 -v postgres_data:/var/lib/postgresql/data postgres
    docker run --name pgadmin --network pg_network -d -p 5050:80 -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=admin dpage/pgadmin4:latest

  • This ensures that both containers can communicate over the internal network created by Docker.

• PostgreSQL Documentation
• pgAdmin Documentation
• Docker Documentation