Database Operations for DevOps

🎯 Mục tiêu: Thực hiện full backup bằng pg_dump, kiểm tra tính toàn vẹn bằng pg_restore --list, restore vào DB mới và xác nhận dữ liệu khớp.

🧰 Công cụ / nền tảng: Docker Desktop, PostgreSQL 16 client (psql, pg_dump, pg_restore).

📦 Chuẩn bị: Docker chạy. Cài PostgreSQL client tools trên WSL2: sudo apt-get install -y postgresql-client.

mkdir -p ~/db-backup-lab && cd ~/db-backup-lab

▶️ Các bước:

Bước 1 — Khởi động PostgreSQL và tạo dữ liệu mẫu

# Chạy PostgreSQL production (port 5432)
docker run -d --name pg-prod \
  -e POSTGRES_DB=appdb \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=secret123 \
  -p 5432:5432 \
  postgres:16-alpine

# Chờ PostgreSQL sẵn sàng
sleep 3
until docker exec pg-prod pg_isready -U appuser -d appdb; do sleep 1; done

# Tạo schema và dữ liệu mẫu
docker exec -i pg-prod psql -U appuser -d appdb << 'SQL'
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  customer   VARCHAR(100) NOT NULL,
  amount     NUMERIC(10,2) NOT NULL,
  status     VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO orders (customer, amount, status) VALUES
  ('Alice Nguyen',  1250000, 'completed'),
  ('Bob Tran',       890000, 'completed'),
  ('Carol Le',      3100000, 'pending'),
  ('David Pham',     450000, 'cancelled'),
  ('Eve Hoang',     2200000, 'completed');

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  stock INT DEFAULT 0
);

INSERT INTO products (name, price, stock) VALUES
  ('Laptop Pro 14',  28000000, 15),
  ('Phone Max X',    18500000, 42),
  ('Tablet Air',     12000000, 8);

SELECT 'Tables created, rows: ' || COUNT(*) FROM orders;
SQL

Bước 2 — Backup với pg_dump (custom format)

# Custom format (-Fc): compressed, parallel-restore capable
pg_dump \
  -h localhost -p 5432 \
  -U appuser \
  -d appdb \
  -Fc \
  --verbose \
  -f appdb_$(date +%Y%m%d_%H%M%S).backup

# Kiểm tra file backup
ls -lh *.backup
echo ""

# Xem nội dung backup (table of contents)
pg_restore --list appdb_*.backup | head -30

Bước 3 — Backup chỉ schema (không data)

# Schema-only backup (hữu ích cho DR planning)
pg_dump \
  -h localhost -p 5432 -U appuser -d appdb \
  --schema-only \
  -f appdb_schema_$(date +%Y%m%d).sql

echo "Schema-only backup:"
wc -l appdb_schema_*.sql

# Plain SQL format (human-readable, dễ review)
pg_dump \
  -h localhost -p 5432 -U appuser -d appdb \
  -Fp --no-owner --no-privileges \
  -f appdb_plain.sql
echo "Plain SQL backup size: $(du -h appdb_plain.sql | cut -f1)"

Bước 4 — Restore vào database mới và verify

# Khởi động PostgreSQL restore target (port 5433)
docker run -d --name pg-restore \
  -e POSTGRES_DB=appdb_restored \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=secret123 \
  -p 5433:5432 \
  postgres:16-alpine

sleep 3
until docker exec pg-restore pg_isready -U appuser; do sleep 1; done

# Restore từ custom format backup
BACKUP_FILE=$(ls appdb_*.backup | head -1)
pg_restore \
  -h localhost -p 5433 \
  -U appuser \
  -d appdb_restored \
  --verbose \
  --no-owner \
  "$BACKUP_FILE"

echo ""
echo "=== RESTORE VERIFICATION ==="

# So sánh row counts giữa source và target
echo "--- Source (port 5432) ---"
psql -h localhost -p 5432 -U appuser -d appdb \
  -c "SELECT 'orders' as table, COUNT(*) FROM orders UNION ALL SELECT 'products', COUNT(*) FROM products;"

echo "--- Restored (port 5433) ---"
psql -h localhost -p 5433 -U appuser -d appdb_restored \
  -c "SELECT 'orders' as table, COUNT(*) FROM orders UNION ALL SELECT 'products', COUNT(*) FROM products;"

# Checksum verify (sum of amounts)
ORIG=$(psql -h localhost -p 5432 -U appuser -d appdb -tAc "SELECT SUM(amount)::text FROM orders;")
REST=$(psql -h localhost -p 5433 -U appuser -d appdb_restored -tAc "SELECT SUM(amount)::text FROM orders;")
[ "$ORIG" = "$REST" ] && echo "✅ CHECKSUM MATCH: $ORIG" || echo "❌ CHECKSUM MISMATCH: orig=$ORIG restored=$REST"

✅ Kết quả mong đợi: Backup file ~10–20KB (compressed). pg_restore --list hiện đủ tables/sequences. Row count và checksum khớp giữa source và restored. Output: ✅ CHECKSUM MATCH.

🧹 Cleanup: docker stop pg-prod pg-restore && docker rm pg-prod pg-restore && cd ~ && rm -rf ~/db-backup-lab

🎯 Mục tiêu: Tạo migration pipeline với Flyway: V1 tạo schema, V2 thêm column, V3 thêm index. Chạy flyway migrate, verify history, và thử flyway info để xem trạng thái.

🧰 Công cụ / nền tảng: Docker (chạy Flyway và PostgreSQL), psql.

📦 Chuẩn bị: Docker chạy. Tạo workspace.

mkdir -p ~/flyway-lab/sql && cd ~/flyway-lab

# Khởi động PostgreSQL
docker run -d --name pg-flyway \
  -e POSTGRES_DB=shopdb \
  -e POSTGRES_USER=shopuser \
  -e POSTGRES_PASSWORD=flyway123 \
  -p 5432:5432 \
  postgres:16-alpine

sleep 3
until docker exec pg-flyway pg_isready -U shopuser; do sleep 1; done
echo "PostgreSQL ready"

▶️ Các bước:

Bước 1 — Tạo migration scripts

# V1: Initial schema
cat > sql/V1__create_initial_schema.sql << 'SQL'
-- V1: Create initial e-commerce schema
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) UNIQUE NOT NULL,
    username   VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    price       NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    category_id INT REFERENCES categories(id),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO categories (name, slug) VALUES
  ('Electronics', 'electronics'),
  ('Clothing', 'clothing'),
  ('Books', 'books');
SQL

# V2: Add inventory tracking
cat > sql/V2__add_inventory_and_orders.sql << 'SQL'
-- V2: Add inventory column to products (safe: nullable with default)
ALTER TABLE products
  ADD COLUMN IF NOT EXISTS stock_qty  INT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS is_active  BOOLEAN DEFAULT true;

-- Add orders table
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INT REFERENCES users(id),
    total      NUMERIC(12,2) NOT NULL,
    status     VARCHAR(20) DEFAULT 'pending'
               CHECK (status IN ('pending','paid','shipped','cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id         SERIAL PRIMARY KEY,
    order_id   INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id),
    qty        INT NOT NULL CHECK (qty > 0),
    unit_price NUMERIC(12,2) NOT NULL
);
SQL

# V3: Performance indexes
cat > sql/V3__add_performance_indexes.sql << 'SQL'
-- V3: Add indexes for common query patterns
-- Using CONCURRENTLY to avoid table lock in production
-- NOTE: CONCURRENTLY cannot run inside a transaction;
--       Flyway runs each migration in a transaction by default.
--       For production with live traffic, run these manually with CONCURRENTLY.
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active   ON products(is_active) WHERE is_active = true;
CREATE INDEX idx_orders_user_id    ON orders(user_id);
CREATE INDEX idx_orders_status     ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- Repeatable migration: keeps views up-to-date
SQL

# R__active_products_view.sql: repeatable (re-runs when checksum changes)
cat > sql/R__active_products_view.sql << 'SQL'
-- Repeatable: active products view (updated with every schema change)
CREATE OR REPLACE VIEW v_active_products AS
SELECT
    p.id,
    p.name,
    p.price,
    p.stock_qty,
    c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.is_active = true;
SQL

ls -la sql/

Bước 2 — Chạy Flyway migrate

# Flyway via Docker — mount sql directory
FLYWAY_URL="jdbc:postgresql://host.docker.internal:5432/shopdb"

docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  flyway/flyway:10 \
  -url="$FLYWAY_URL" \
  -user=shopuser \
  -password=flyway123 \
  migrate

Bước 3 — Xem migration history và status

FLYWAY_URL="jdbc:postgresql://host.docker.internal:5432/shopdb"

# Xem trạng thái tất cả migrations
docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  flyway/flyway:10 \
  -url="$FLYWAY_URL" \
  -user=shopuser \
  -password=flyway123 \
  info

# Verify schema history trong PostgreSQL
psql -h localhost -p 5432 -U shopuser -d shopdb \
  -c "SELECT version, description, type, installed_on, success FROM flyway_schema_history ORDER BY installed_rank;"

# Kiểm tra tables và view
psql -h localhost -p 5432 -U shopuser -d shopdb \
  -c "\dt" \
  -c "\dv" \
  -c "SELECT * FROM v_active_products LIMIT 5;"

Bước 4 — Simulate thêm migration mới

# V4: Add discount feature (safe migration)
cat > sql/V4__add_discount_feature.sql << 'SQL'
-- V4: Add discount support (nullable = backward-compatible)
ALTER TABLE products
  ADD COLUMN IF NOT EXISTS discount_pct NUMERIC(5,2) DEFAULT 0
    CHECK (discount_pct >= 0 AND discount_pct <= 100);

CREATE TABLE discount_codes (
    id          SERIAL PRIMARY KEY,
    code        VARCHAR(50) UNIQUE NOT NULL,
    discount    NUMERIC(5,2) NOT NULL,
    valid_until DATE,
    is_active   BOOLEAN DEFAULT true
);
SQL

FLYWAY_URL="jdbc:postgresql://host.docker.internal:5432/shopdb"

# Migrate lại — Flyway chỉ chạy V4 (V1/V2/V3/R đã có checksum)
docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  flyway/flyway:10 \
  -url="$FLYWAY_URL" \
  -user=shopuser -password=flyway123 \
  migrate

# Xem info sau migrate V4
docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  flyway/flyway:10 \
  -url="$FLYWAY_URL" \
  -user=shopuser -password=flyway123 \
  info

✅ Kết quả mong đợi: flyway migrate output: Successfully applied 4 migrations. flyway info hiển thị V1–V4 status = Success, R__ = Success. \dt liệt kê 5 tables + \dv hiển thị v_active_products. flyway_schema_history có 5 rows với success=true.

🧹 Cleanup: docker stop pg-flyway && docker rm pg-flyway && cd ~ && rm -rf ~/flyway-lab

🎯 Mục tiêu: Triển khai PostgreSQL với credentials được inject qua Docker Secrets (Swarm mode) và environment variable (standalone), kiểm tra không lộ password trong docker inspect hay process list.

🧰 Công cụ / nền tảng: Docker (Swarm mode), bash, psql.

📦 Chuẩn bị: Docker chạy. docker swarm init để enable Swarm mode.

mkdir -p ~/secrets-lab && cd ~/secrets-lab

# Khởi tạo Docker Swarm (nếu chưa)
docker swarm init 2>/dev/null || echo "Swarm already initialized"

▶️ Các bước:

Bước 1 — Tạo Docker Secrets

# Tạo secrets (giá trị được lưu encrypted trong Swarm raft store)
echo "supersecure_db_password_2026!" | docker secret create pg_password -
echo "appuser" | docker secret create pg_user -
echo "productiondb" | docker secret create pg_dbname -

# Verify secrets created (giá trị KHÔNG được hiển thị)
docker secret ls
echo "Secret values are encrypted — cannot be read back after creation"

Bước 2 — Deploy PostgreSQL service với Secrets

cat > docker-compose-secrets.yml << 'EOF'
version: "3.8"
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER_FILE: /run/secrets/pg_user
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
      POSTGRES_DB_FILE: /run/secrets/pg_dbname
    secrets:
      - pg_password
      - pg_user
      - pg_dbname
    ports:
      - "5434:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

secrets:
  pg_password:
    external: true
  pg_user:
    external: true
  pg_dbname:
    external: true

volumes:
  pgdata:
EOF

docker stack deploy -c docker-compose-secrets.yml pg-secure
sleep 5
docker stack services pg-secure

Bước 3 — Verify secrets không lộ trong process/inspect

# Đọc password từ secret file bên trong container
CONTAINER=$(docker ps --format '{{.Names}}' | grep pg-secure | head -1)
echo "Container: $CONTAINER"

# Secret được mount vào /run/secrets/ — chỉ process trong container mới đọc được
docker exec "$CONTAINER" cat /run/secrets/pg_password
# Output: supersecure_db_password_2026!

# Kiểm tra: password KHÔNG xuất hiện trong environment variables
echo "--- Environment variables (should NOT contain password) ---"
docker exec "$CONTAINER" env | grep -i "password\|POSTGRES" | grep -v "_FILE"

# Kiểm tra: docker inspect không lộ actual password
echo "--- docker inspect env check ---"
docker inspect "$CONTAINER" | python3 -c "
import sys,json
data=json.load(sys.stdin)
env=data[0].get('Config',{}).get('Env',[])
for e in env:
    if 'password' in e.lower():
        print('LEAKED:', e)
    elif 'POSTGRES' in e:
        print('OK (no plaintext):', e)
" 2>/dev/null || echo "(inspect check skipped)"

Bước 4 — Standalone: .env file pattern (không commit)

# Pattern cho development (non-Swarm)
cat > .env.example << 'EOF'
# .env.example — commit this (no real values)
DB_HOST=localhost
DB_PORT=5432
DB_NAME=appdb
DB_USER=appuser
DB_PASSWORD=CHANGE_ME
DB_POOL_MIN=2
DB_POOL_MAX=10
EOF

cat > .env << 'EOF'
DB_HOST=localhost
DB_PORT=5434
DB_NAME=productiondb
DB_USER=appuser
DB_PASSWORD=supersecure_db_password_2026!
EOF

# .gitignore — CRITICAL
cat > .gitignore << 'EOF'
.env
*.key
*.pem
*_password*
secrets/
EOF

echo "=== .gitignore check ==="
git check-ignore -v .env 2>/dev/null && echo "✅ .env is git-ignored" || echo "⚠️  .env not in repo yet, .gitignore is set"

# Test connection từ env file
source .env
PGPASSWORD="$DB_PASSWORD" psql \
  -h "$DB_HOST" -p "$DB_PORT" \
  -U "$DB_USER" -d "$DB_NAME" \
  -c "SELECT current_database(), current_user, NOW();" 2>/dev/null || \
  echo "(Connection test — service may still be starting)"

✅ Kết quả mong đợi: docker secret ls liệt kê 3 secrets. docker exec ... env KHÔNG hiển thị POSTGRES_PASSWORD=... (chỉ thấy POSTGRES_PASSWORD_FILE). .env bị git-ignore. Secret file readable inside container.

🧹 Cleanup: docker stack rm pg-secure && docker secret rm pg_password pg_user pg_dbname && docker swarm leave --force && cd ~ && rm -rf ~/secrets-lab

🎯 Mục tiêu: Thiết lập PostgreSQL streaming replication với 1 primary + 1 read replica, kiểm tra replication lag, verify data đồng bộ và test read-only enforcement trên replica.

🧰 Công cụ / nền tảng: Docker Compose, psql.

📦 Chuẩn bị: Docker Compose v2 (docker compose version). Tạo workspace.

mkdir -p ~/replication-lab/{primary-conf,replica-conf} && cd ~/replication-lab

▶️ Các bước:

Bước 1 — Tạo cấu hình và docker-compose

# Primary: postgresql.conf additions
cat > primary-conf/postgresql.conf << 'EOF'
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64
synchronous_commit = on
listen_addresses = '*'
EOF

# Primary: pg_hba.conf — allow replication user
cat > primary-conf/pg_hba.conf << 'EOF'
local   all             all                                     trust
host    all             all             0.0.0.0/0               md5
host    replication     replicator      0.0.0.0/0               md5
EOF

# Replica init script
cat > replica-conf/init-replica.sh << 'EOF'
#!/bin/bash
# Wait for primary
until pg_isready -h pg-primary -U postgres; do sleep 1; done

# If data directory empty, run pg_basebackup
if [ ! -f "$PGDATA/PG_VERSION" ]; then
  PGPASSWORD=reppassword pg_basebackup \
    -h pg-primary -U replicator \
    -D "$PGDATA" \
    -Fp -Xs -P -R
  echo "pg_basebackup complete"
fi
EOF
chmod +x replica-conf/init-replica.sh

# Docker Compose
cat > docker-compose.yml << 'EOF'
version: "3.8"
services:
  pg-primary:
    image: postgres:16-alpine
    container_name: pg-primary
    environment:
      POSTGRES_DB: shopdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
    volumes:
      - primary-data:/var/lib/postgresql/data
      - ./primary-conf/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./primary-conf/pg_hba.conf:/etc/postgresql/pg_hba.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
                      -c hba_file=/etc/postgresql/pg_hba.conf
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 10

  pg-replica:
    image: postgres:16-alpine
    container_name: pg-replica
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
    volumes:
      - replica-data:/var/lib/postgresql/data
    ports:
      - "5433:5432"
    depends_on:
      pg-primary:
        condition: service_healthy

volumes:
  primary-data:
  replica-data:
EOF

docker compose up -d pg-primary
sleep 5

Bước 2 — Tạo replication user trên primary

# Tạo replication user và sample data
docker exec pg-primary psql -U postgres -d shopdb << 'SQL'
-- Create replication user
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'reppassword';

-- Sample data for replication test
CREATE TABLE metrics (
  id         SERIAL PRIMARY KEY,
  service    VARCHAR(50),
  value      NUMERIC(10,3),
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO metrics (service, value) VALUES
  ('api', 99.95), ('db', 100.0), ('cache', 98.7);

SELECT 'Primary ready. Rows: ' || COUNT(*) FROM metrics;
SQL

Bước 3 — Setup replica với pg_basebackup

# Chạy pg_basebackup từ primary để seed replica
docker run --rm \
  --network replication-lab_default \
  -e PGPASSWORD=reppassword \
  postgres:16-alpine \
  pg_basebackup \
    -h pg-primary -U replicator \
    -D /tmp/replica-seed \
    -Fp -Xs -P -R \
    -v 2>&1 | tail -5

# Khởi động replica với data đã seed
# (Trong môi trường lab đơn giản: dùng pg_basebackup kết hợp volume)
# Start replica container
docker compose up -d pg-replica
sleep 5

Bước 4 — Verify replication và kiểm tra

# Kiểm tra replication status trên primary
echo "=== Replication Status (Primary) ==="
docker exec pg-primary psql -U postgres -x \
  -c "SELECT pid, usename, application_name, client_addr, state, sent_lsn, write_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag
      FROM pg_stat_replication;" 2>/dev/null || \
  echo "(Replica not yet connected — see note below)"

# Write test trên primary
docker exec pg-primary psql -U postgres -d shopdb \
  -c "INSERT INTO metrics (service, value) VALUES ('worker', 97.2);"

sleep 1

# Verify data đồng bộ replica
echo "=== Data on Primary ==="
docker exec pg-primary psql -U postgres -d shopdb \
  -c "SELECT COUNT(*), MAX(recorded_at) FROM metrics;"

echo "=== Data on Replica ==="
docker exec pg-replica psql -U postgres -d shopdb \
  -c "SELECT COUNT(*), MAX(recorded_at) FROM metrics;" 2>/dev/null || \
  echo "(Replica may need pg_basebackup setup first)"

# Test: replica is read-only (cannot write)
echo "=== Read-only enforcement test ==="
docker exec pg-replica psql -U postgres -d shopdb \
  -c "INSERT INTO metrics (service, value) VALUES ('hack', 0);" 2>&1 | \
  grep -q "read-only" && echo "✅ Replica correctly rejects writes" || \
  echo "(Replica may not be in standby mode yet)"

✅ Kết quả mong đợi: pg_stat_replication hiển thị replica với state=streaming. Data INSERT trên primary xuất hiện trên replica sau <1s. Replica từ chối INSERT với lỗi cannot execute INSERT in a read-only transaction.

🧹 Cleanup: docker compose down -v && cd ~ && rm -rf ~/replication-lab

🎯 Mục tiêu: Thực hiện rename column name → full_name trên bảng đang có traffic mà không downtime, áp dụng expand-contract pattern qua 3 Flyway migrations, verify backward compatibility ở từng phase.

🧰 Công cụ / nền tảng: Docker, psql, Flyway CLI (Docker).

📦 Chuẩn bị: Docker chạy. Tạo workspace.

mkdir -p ~/expand-contract-lab/sql && cd ~/expand-contract-lab

# Start fresh PostgreSQL
docker run -d --name pg-ec \
  -e POSTGRES_DB=userdb \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=admin123 \
  -p 5432:5432 \
  postgres:16-alpine

sleep 3
until docker exec pg-ec pg_isready -U admin; do sleep 1; done

# Create initial state (simulating existing production table)
docker exec -i pg-ec psql -U admin -d userdb << 'SQL'
CREATE TABLE customers (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,    -- COLUMN TO RENAME
  email      VARCHAR(255) UNIQUE NOT NULL,
  tier       VARCHAR(20) DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO customers (name, email, tier) VALUES
  ('Nguyen Van A', '[email protected]', 'pro'),
  ('Tran Thi B',   '[email protected]', 'free'),
  ('Le Van C',     '[email protected]', 'enterprise');

-- Simulate app v1 reading "name" column
CREATE VIEW v_customer_display AS
  SELECT id, name, email, tier FROM customers;

SELECT 'Initial state: ' || COUNT(*) || ' customers' FROM customers;
SQL

▶️ Các bước:

Phase 1 — EXPAND: Thêm column mới, cả 2 tồn tại song song

cat > sql/V1__initial_customers.sql << 'SQL'
-- Placeholder: table already exists in this lab
-- In real scenario, this would be the original migration
SELECT 1;
SQL

cat > sql/V2__expand_add_full_name_column.sql << 'SQL'
-- PHASE 1: EXPAND
-- Add new column (nullable, no data loss risk)
ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS full_name VARCHAR(200);

-- Backfill existing data: copy name -> full_name
-- Use batches to avoid long lock (here table is small, single UPDATE OK)
UPDATE customers SET full_name = name WHERE full_name IS NULL;

-- Add NOT NULL constraint after backfill
ALTER TABLE customers
  ALTER COLUMN full_name SET NOT NULL;

-- Keep BOTH columns: app v1 reads "name", app v2 reads "full_name"
-- Add trigger: keep both columns in sync during transition
CREATE OR REPLACE FUNCTION sync_customer_name()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    -- If app writes to "name", sync to "full_name"
    IF NEW.name IS DISTINCT FROM OLD.name THEN
      NEW.full_name := NEW.name;
    END IF;
    -- If app writes to "full_name", sync to "name"
    IF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
      NEW.name := NEW.full_name;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_customer_name
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION sync_customer_name();

COMMENT ON COLUMN customers.full_name IS
  'New canonical name column. Replaces "name" after app v3 deploy.';
SQL

# Run V2 migration
FLYWAY_URL="jdbc:postgresql://host.docker.internal:5432/userdb"
docker run --rm -v $(pwd)/sql:/flyway/sql flyway/flyway:10 \
  -url="$FLYWAY_URL" -user=admin -password=admin123 migrate

echo "=== After Phase 1 (EXPAND) ==="
docker exec pg-ec psql -U admin -d userdb \
  -c "\d customers" \
  -c "SELECT id, name, full_name FROM customers;"

Phase 2 — Verify backward compat: app v1 vẫn dùng được

echo "=== App v1 compatibility test (reads old 'name' column) ==="
docker exec pg-ec psql -U admin -d userdb \
  -c "SELECT id, name, email FROM customers;" \
  -c "SELECT * FROM v_customer_display;"

echo ""
echo "=== Test sync trigger: write to 'name', 'full_name' should sync ==="
docker exec pg-ec psql -U admin -d userdb \
  -c "UPDATE customers SET name = 'Nguyen Van A Updated' WHERE id = 1;"

docker exec pg-ec psql -U admin -d userdb \
  -c "SELECT id, name, full_name FROM customers WHERE id = 1;"
# Both columns should show "Nguyen Van A Updated"

echo ""
echo "=== Test sync trigger: write to 'full_name', 'name' should sync ==="
docker exec pg-ec psql -U admin -d userdb \
  -c "UPDATE customers SET full_name = 'Tran Thi B New Name' WHERE id = 2;"

docker exec pg-ec psql -U admin -d userdb \
  -c "SELECT id, name, full_name FROM customers WHERE id = 2;"

Phase 3 — CONTRACT: Xóa column cũ sau khi app v3 deployed

cat > sql/V3__contract_drop_old_name_column.sql << 'SQL'
-- PHASE 3: CONTRACT
-- Pre-condition: App v3 deployed, no longer reads "name" column
-- Verify no active queries use "name" column before running this migration

-- Drop sync trigger first
DROP TRIGGER IF EXISTS trg_sync_customer_name ON customers;
DROP FUNCTION IF EXISTS sync_customer_name();

-- Update view to use new column
CREATE OR REPLACE VIEW v_customer_display AS
  SELECT id, full_name AS name, email, tier FROM customers;
  -- Note: view still exposes "name" alias for compatibility if needed

-- Drop old column (POINT OF NO RETURN)
ALTER TABLE customers DROP COLUMN name;

COMMENT ON TABLE customers IS
  'Migration V3 complete: "name" column removed, "full_name" is canonical.';
SQL

# Run V3 migration
FLYWAY_URL="jdbc:postgresql://host.docker.internal:5432/userdb"
docker run --rm -v $(pwd)/sql:/flyway/sql flyway/flyway:10 \
  -url="$FLYWAY_URL" -user=admin -password=admin123 migrate

echo "=== After Phase 3 (CONTRACT) ==="
docker exec pg-ec psql -U admin -d userdb \
  -c "\d customers" \
  -c "SELECT id, full_name, email FROM customers;"

# Verify migration history
docker run --rm -v $(pwd)/sql:/flyway/sql flyway/flyway:10 \
  -url="$FLYWAY_URL" -user=admin -password=admin123 info

✅ Kết quả mong đợi: Sau Phase 1: bảng có cả name lẫn full_name, trigger đồng bộ hai chiều. Sau Phase 2: UPDATE vào name → full_name tự cập nhật và ngược lại. Sau Phase 3: \d customers chỉ còn full_name, không còn name. flyway info: V1–V3 status = Success. Không có downtime trong cả 3 phase.

🧹 Cleanup: docker stop pg-ec && docker rm pg-ec && cd ~ && rm -rf ~/expand-contract-lab