Module 2 — Practical Foundations with PostgreSQL

Section 4: Running Databases Locally

17. Installing PostgreSQL (Optional)

Native Installation:

# macOS
brew install postgresql

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib

# Verify installation
psql --version

Why skip native install?

Docker provides consistent environments
Easy cleanup, version switching
Matches production deployments

18. Running PostgreSQL Using Docker

Pull and run PostgreSQL:

docker run --name postgres-dev \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=devops \
  -e POSTGRES_DB=workshop \
  -p 5432:5432 \
  -d postgres:16

Key parameters:

--name: Container name (for easy reference)
-e POSTGRES_PASSWORD: Set admin password
-e POSTGRES_USER: Create custom user (default: postgres)
-e POSTGRES_DB: Create initial database
-p 5432:5432: Map container port to host
-d: Run in background (detached mode)

Verify container is running:

docker ps
docker logs postgres-dev

19. Connecting to PostgreSQL (CLI & GUI)

CLI Connection (psql):

# Connect via Docker exec
docker exec -it postgres-dev psql -U devops -d workshop

# Or connect from host (if psql installed)
psql -h localhost -p 5432 -U devops -d workshop
# Password: mysecretpassword

GUI Options:

pgAdmin (web-based, official PostgreSQL GUI)
DBeaver (multi-database, free)
TablePlus (macOS/Windows, sleek UI)
DataGrip (JetBrains, paid)

Connection details:

Host:     localhost
Port:     5432
User:     devops
Password: mysecretpassword
Database: workshop

20. PostgreSQL Directory Structure & Data Files

Inside the container:

docker exec -it postgres-dev bash
ls -la /var/lib/postgresql/data

Key directories:

/var/lib/postgresql/data/
├── base/           # Database files (tables, indexes)
├── global/         # Cluster-wide tables (users, databases)
├── pg_wal/         # Write-Ahead Log (transaction logs)
├── pg_xact/        # Transaction commit status
├── postgresql.conf # Main configuration file
└── pg_hba.conf     # Client authentication rules

Why it matters:

Backup targets: Need to backup base/ and pg_wal/
Performance tuning: Edit postgresql.conf
Security: Control access via pg_hba.conf

21. Using psql & Running Basic Queries

Essential psql Meta-commands:

-- Navigation & Information
\l             -- List all databases
\l+            -- List databases with size and description
\c dbname      -- Connect (change) to database 'dbname'
\c             -- Show current database connection info
\conninfo      -- Display connection information

-- Tables & Schema
\dt            -- List tables in current schema
\dt+           -- List tables with size
\dt schema.*   -- List tables in specific schema
\d tablename   -- Describe table structure
\d+ tablename  -- Detailed table description with indexes
\di            -- List indexes
\dv            -- List views
\dn            -- List schemas

-- Users & Permissions
\du            -- List users/roles
\du+           -- List users with detailed info
\dp tablename  -- Show table permissions

-- Database Objects
\df            -- List functions
\dT            -- List data types
\x             -- Toggle expanded output (useful for wide results)
\timing        -- Toggle query execution time display

-- Help & Navigation
\?             -- List all psql commands
\h SQL_COMMAND -- Help on specific SQL command (e.g., \h CREATE TABLE)
\q             -- Quit psql
\! clear       -- Clear screen (or \! cls on Windows)

-- History & Editing
\s             -- Show command history
\s filename    -- Save history to file
\i filename    -- Execute commands from file
\e             -- Open last query in editor

Switching Between Databases:

-- List all databases first
\l

-- Connect to different database
\c postgres
-- Output: You are now connected to database "postgres"

-- Switch to another database
\c workshop
-- Output: You are now connected to database "workshop"

-- Create and immediately connect to new database
CREATE DATABASE testdb;
\c testdb

-- Go back to previous database
\c workshop  -- Manual switch back

Basic Query Examples:

-- Check current connection info
SELECT current_database(), current_user, current_schema();

-- See PostgreSQL version
SELECT version();

-- Show current date and time
SELECT now(), current_date, current_time;

-- Show all databases (SQL way)
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Check active connections
SELECT datname, count(*) 
FROM pg_stat_activity 
GROUP BY datname;

-- Show all settings
SHOW all;

-- Show specific settings
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;

Interactive psql Tips:

-- Enable timing for all queries
\timing on
SELECT count(*) FROM pg_tables;
-- Shows: Time: 5.234 ms

-- Enable expanded display (great for wide results)
\x
SELECT * FROM pg_settings WHERE name LIKE '%memory%';
\x  -- Toggle off

-- Pretty formatting
\pset border 2          -- Set border style
\pset format wrapped    -- Wrap long lines

-- Set null display
\pset null '(null)'     -- Show NULL values clearly

22. Creating Tables, Users & Databases

Database Operations:

-- Create a new database
CREATE DATABASE ecommerce;

-- Create with specific encoding and owner
CREATE DATABASE ecommerce 
  WITH OWNER = postgres
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8';

-- List all databases with sizes
\l+

-- Switch to the new database
\c ecommerce
-- Output: You are now connected to database "ecommerce" as user "postgres"

-- Drop database (must disconnect first)
\c postgres  -- Switch to different database
DROP DATABASE IF EXISTS testdb;

Schema Management:

-- List schemas
\dn

-- Create custom schema
CREATE SCHEMA sales;
CREATE SCHEMA inventory;

-- Set search path (default schema)
SET search_path TO sales, public;
SHOW search_path;

Table Creation with Various Data Types:

-- Basic table
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2),
  stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Table with more data types
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  full_name VARCHAR(100),
  age INT CHECK (age >= 18),
  is_active BOOLEAN DEFAULT true,
  metadata JSONB,
  tags TEXT[],
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id) ON DELETE CASCADE,
  total_amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  order_date TIMESTAMP DEFAULT NOW()
);

-- View table structure
\d products
\d+ customers  -- Detailed view

Insert Sample Data:

-- Single insert
INSERT INTO products (name, price, stock) 
VALUES ('Laptop', 999.99, 10);

-- Multiple inserts
INSERT INTO products (name, price, stock) VALUES
  ('Mouse', 29.99, 50),
  ('Keyboard', 79.99, 30),
  ('Monitor', 299.99, 15),
  ('Headphones', 149.99, 25);

-- Insert with RETURNING (get inserted data back)
INSERT INTO products (name, price, stock)
VALUES ('USB Cable', 9.99, 100)
RETURNING id, name, created_at;

-- Insert from SELECT
INSERT INTO products (name, price, stock)
SELECT 'Copied Product', price * 0.9, stock
FROM products WHERE id = 1;

-- View data
SELECT * FROM products;
SELECT id, name, price FROM products ORDER BY price DESC;

Update and Delete Operations:

-- Update single record
UPDATE products 
SET price = 899.99, stock = 8 
WHERE id = 1;

-- Update with calculation
UPDATE products 
SET price = price * 1.10 
WHERE stock > 20;

-- Update with RETURNING
UPDATE products 
SET stock = stock - 1 
WHERE id = 2
RETURNING id, name, stock;

-- Delete specific records
DELETE FROM products WHERE stock = 0;

-- Delete with subquery
DELETE FROM products 
WHERE id IN (SELECT id FROM products WHERE price < 10);

User and Role Management:

-- Create read-only user
CREATE USER readonly_user WITH PASSWORD 'secure_pass123';

-- Create app user with specific privileges
CREATE USER app_user WITH PASSWORD 'app_pass456' LOGIN;

-- Create role (group)
CREATE ROLE developers;
CREATE ROLE analysts;

-- Grant database connection
GRANT CONNECT ON DATABASE ecommerce TO app_user;
GRANT CONNECT ON DATABASE ecommerce TO readonly_user;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant table privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Grant sequence privileges (needed for SERIAL columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Add user to role
GRANT developers TO app_user;

-- View user privileges
\du
\du app_user

-- Test user connection (from terminal)
-- psql -U app_user -d ecommerce -h localhost

-- Revoke privileges
REVOKE INSERT, UPDATE, DELETE ON products FROM readonly_user;

-- Change user password
ALTER USER app_user WITH PASSWORD 'new_password789';

-- Drop user (must revoke privileges first)
REVOKE ALL PRIVILEGES ON DATABASE ecommerce FROM app_user;
DROP USER app_user;

Useful Table Queries:

-- Show all tables with row counts
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  pg_stat_get_live_tuples(schemaname||'.'||tablename::regclass) AS rows
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Copy table structure
CREATE TABLE products_backup (LIKE products INCLUDING ALL);

-- Copy table with data
CREATE TABLE products_copy AS SELECT * FROM products;

-- Rename table
ALTER TABLE products_backup RENAME TO products_old;

-- Add column to existing table
ALTER TABLE products ADD COLUMN category VARCHAR(50);
ALTER TABLE products ADD COLUMN discount DECIMAL(5,2) DEFAULT 0.00;

-- Modify column
ALTER TABLE products ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE products ALTER COLUMN stock SET DEFAULT 10;

-- Drop column
ALTER TABLE products DROP COLUMN IF EXISTS discount;

-- Add constraint
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);
ALTER TABLE products ADD CONSTRAINT unique_product_name UNIQUE (name);

23. Basic Indexing & Query Plans

Without an index:

EXPLAIN SELECT * FROM products WHERE name = 'Laptop';

Output shows: Seq Scan (sequential scan = slow for large tables)

Create an index:

CREATE INDEX idx_products_name ON products(name);

-- Now check the plan again
EXPLAIN SELECT * FROM products WHERE name = 'Laptop';

Output shows: Index Scan (much faster)

Key metrics in EXPLAIN:

cost: Estimated query cost (lower = better)
rows: Estimated rows returned
Seq Scan: Full table scan (bad for large tables)
Index Scan: Uses index (good)

24. Lab: Build Your First Database

Scenario: Build a simple blog database with multiple related tables

Step 1: Create database and switch to it

-- From psql, create new database
CREATE DATABASE blog;

-- Switch to blog database
\c blog
-- Output: You are now connected to database "blog"

-- Verify connection
SELECT current_database();
-- Output: blog

Step 2: Create tables with relationships

-- Authors table
CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  bio TEXT,
  joined_date DATE DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT true
);

-- Categories table
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT
);

-- Posts table with foreign keys
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT REFERENCES authors(id) ON DELETE CASCADE,
  category_id INT REFERENCES categories(id) ON DELETE SET NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  published_at TIMESTAMP DEFAULT NOW(),
  views INT DEFAULT 0,
  is_published BOOLEAN DEFAULT false
);

-- Comments table
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  author_name VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Verify table creation
\dt
\d posts  -- See detailed structure

Step 3: Insert sample data

-- Insert authors
INSERT INTO authors (name, email, bio) VALUES
  ('Alice Smith', 'alice@blog.com', 'Tech writer and database enthusiast'),
  ('Bob Johnson', 'bob@blog.com', 'DevOps engineer with 10 years experience'),
  ('Charlie Davis', 'charlie@blog.com', 'Cloud architect and PostgreSQL expert');

-- Verify and see IDs
SELECT * FROM authors;

-- Insert categories
INSERT INTO categories (name, description) VALUES
  ('PostgreSQL', 'All about PostgreSQL database'),
  ('Docker', 'Containerization tutorials'),
  ('DevOps', 'DevOps practices and tools'),
  ('Kubernetes', 'Container orchestration');

-- Insert posts (using author and category IDs)
INSERT INTO posts (author_id, category_id, title, content, is_published) VALUES
  (1, 1, 'Getting Started with PostgreSQL', 'PostgreSQL is a powerful open-source database...', true),
  (1, 1, 'Database Indexing 101', 'Indexes help speed up queries by creating lookup structures...', true),
  (2, 2, 'Docker for DevOps', 'Docker containers provide isolated environments...', true),
  (2, 3, 'CI/CD Best Practices', 'Continuous integration and deployment are essential...', true),
  (3, 4, 'Kubernetes StatefulSets', 'StatefulSets are used for stateful applications...', true),
  (3, 1, 'PostgreSQL Replication', 'High availability requires proper replication setup...', false);

-- Insert comments
INSERT INTO comments (post_id, author_name, content) VALUES
  (1, 'Reader1', 'Great introduction! Very helpful.'),
  (1, 'Reader2', 'Can you cover advanced topics next?'),
  (2, 'Reader3', 'Excellent explanation of indexes!'),
  (3, 'Reader1', 'Docker has changed my workflow completely.'),
  (3, 'Reader4', 'What about Docker Compose?');

-- View inserted data
SELECT * FROM posts;
SELECT * FROM comments;

Step 4: Practice database navigation

-- List all databases
\l

-- Switch between databases
\c postgres
\c blog
\c workshop  -- If exists

-- Back to blog
\c blog

-- Check current connection
\conninfo
-- Output: You are connected to database "blog" as user "postgres" via socket

-- List all tables
\dt

-- Get table sizes
\dt+

Step 5: Query with JOINs

-- Simple JOIN - posts with author names
SELECT posts.title, authors.name AS author, posts.published_at
FROM posts
JOIN authors ON posts.author_id = authors.id
ORDER BY posts.published_at DESC;

-- JOIN with categories
SELECT 
  posts.title,
  authors.name AS author,
  categories.name AS category,
  posts.is_published
FROM posts
JOIN authors ON posts.author_id = authors.id
JOIN categories ON posts.category_id = categories.id;

-- LEFT JOIN to include posts without categories
SELECT 
  posts.title,
  categories.name AS category
FROM posts
LEFT JOIN categories ON posts.category_id = categories.id;

-- Complex query: posts with comment count
SELECT 
  posts.id,
  posts.title,
  authors.name AS author,
  COUNT(comments.id) AS comment_count
FROM posts
JOIN authors ON posts.author_id = authors.id
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id, posts.title, authors.name
ORDER BY comment_count DESC;

-- Subquery: authors with most posts
SELECT 
  authors.name,
  COUNT(posts.id) AS post_count
FROM authors
LEFT JOIN posts ON authors.id = posts.author_id
GROUP BY authors.name
ORDER BY post_count DESC;

Step 6: Add indexes

-- Check execution plan BEFORE indexes
EXPLAIN SELECT * FROM posts WHERE author_id = 1;
-- Shows Seq Scan

-- Create indexes
CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_posts_published_at ON posts(published_at);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- Create composite index for common query pattern
CREATE INDEX idx_posts_author_published ON posts(author_id, is_published);

-- List all indexes
\di

-- Check execution plan AFTER indexes
EXPLAIN SELECT * FROM posts WHERE author_id = 1;
-- Shows Index Scan (better!)

-- Analyze index usage
EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 1;

Step 7: Practice UPDATE and DELETE

-- Update single record
UPDATE posts 
SET views = views + 1 
WHERE id = 1;

-- Update multiple records
UPDATE posts 
SET is_published = true 
WHERE author_id = 3;

-- Verify changes
SELECT id, title, views, is_published FROM posts;

-- Conditional delete
DELETE FROM comments WHERE id = 5;

-- Delete with cascade (deleting author deletes their posts)
DELETE FROM authors WHERE id = 3;
-- This also deletes posts by author 3 due to ON DELETE CASCADE

Step 8: Test transactions

-- Start transaction
BEGIN;

-- Make changes
INSERT INTO authors (name, email) VALUES ('New Author', 'new@blog.com');
INSERT INTO posts (author_id, title, content) VALUES 
  (CURRVAL('authors_id_seq'), 'Test Post', 'This is a test');

-- Check data (visible in this transaction)
SELECT * FROM authors WHERE name = 'New Author';

-- Rollback (undo everything)
ROLLBACK;

-- Verify rollback worked
SELECT * FROM authors WHERE name = 'New Author';
-- Should return no rows

-- Successful transaction
BEGIN;
  INSERT INTO categories (name) VALUES ('Testing');
  UPDATE posts SET category_id = (SELECT id FROM categories WHERE name = 'Testing') 
  WHERE id = 1;
COMMIT;

-- Verify commit worked
SELECT * FROM posts WHERE id = 1;

Step 9: Export and backup practice

-- View table as CSV
\copy posts TO '/tmp/posts.csv' CSV HEADER;

-- Export specific query results
\copy (SELECT authors.name, COUNT(posts.id) FROM authors LEFT JOIN posts ON authors.id = posts.author_id GROUP BY authors.name) TO '/tmp/author_stats.csv' CSV HEADER;

Step 10: Cleanup (optional)

-- Switch to another database before dropping
\c postgres

-- Drop blog database
DROP DATABASE blog;

-- Verify it's gone
\l

🎯 Hands-On Challenges

Challenge 1: Extend the schema

Add a tags table (many-to-many relationship with posts)
Create a junction table post_tags
Insert sample tags and link them to posts

Challenge 2: Complex queries

Find authors who have never posted
Get the most commented post with author details
List categories with post count and total views

Challenge 3: Performance testing

Create 10,000 fake posts using generate_series()
Compare query speed with and without indexes
Use EXPLAIN ANALYZE to measure improvements

Challenge 4: Data integrity

Try to insert invalid data (violate constraints)
Test foreign key cascades
Test transaction rollbacks

Sample Solutions:

-- Challenge 1: Tags implementation
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

INSERT INTO tags (name) VALUES ('tutorial'), ('beginner'), ('advanced');

-- Challenge 2: Authors with no posts
SELECT authors.name, authors.email
FROM authors
LEFT JOIN posts ON authors.id = posts.author_id
WHERE posts.id IS NULL;

-- Challenge 3: Generate test data
INSERT INTO posts (author_id, category_id, title, content)
SELECT 
  (RANDOM() * 2 + 1)::INT,  -- Random author 1-3
  (RANDOM() * 3 + 1)::INT,  -- Random category 1-4
  'Test Post ' || generate_series,
  'Auto-generated content for testing'
FROM generate_series(1, 10000);

✅ Module 2 Complete

Key Takeaways:

Docker simplifies database deployment
psql is the primary CLI tool for PostgreSQL
Indexes dramatically improve query performance
Use EXPLAIN to analyze query execution
Transactions ensure data consistency

Next: Module 3 — Storage & Infrastructure Essentials!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Module 2 — Practical Foundations with PostgreSQL

Section 4: Running Databases Locally

17. Installing PostgreSQL (Optional)

18. Running PostgreSQL Using Docker

19. Connecting to PostgreSQL (CLI & GUI)

20. PostgreSQL Directory Structure & Data Files

21. Using psql & Running Basic Queries

22. Creating Tables, Users & Databases

23. Basic Indexing & Query Plans

24. Lab: Build Your First Database

🎯 Hands-On Challenges

✅ Module 2 Complete

FilesExpand file tree

module-02-postgresql-practical.md

Latest commit

History

module-02-postgresql-practical.md

File metadata and controls

Module 2 — Practical Foundations with PostgreSQL

Section 4: Running Databases Locally

17. Installing PostgreSQL (Optional)

18. Running PostgreSQL Using Docker

19. Connecting to PostgreSQL (CLI & GUI)

20. PostgreSQL Directory Structure & Data Files

21. Using psql & Running Basic Queries

22. Creating Tables, Users & Databases

23. Basic Indexing & Query Plans

24. Lab: Build Your First Database

🎯 Hands-On Challenges

✅ Module 2 Complete