PostgreSQL DBRE Modernization Lab

Hands-on Database Reliability Engineering and PostgreSQL modernization lab focused on observability, migration strategies, infrastructure as code, schema lifecycle, recovery, and analytical workload separation.

Overview

This project simulates the modernization of a transactional PostgreSQL environment while exploring practical responsibilities commonly found in Database Reliability Engineering, Data Platform Engineering, and Data Engineering roles.

The lab reproduces operational scenarios and connects them into a single modernization workflow.

It covers:

• PostgreSQL performance troubleshooting
• Query and connection observability
• Long-running query detection
• Blocking and lock analysis
• Database health automation with Python
• Full Load and incremental migration strategies
• CDC concepts
• PostgreSQL WAL and logical replication concepts
• Migration validation
• Flyway schema versioning
• Terraform Infrastructure as Code
• AWS DMS architecture
• Amazon RDS modernization concepts
• Backup and restore validation
• Point-in-Time Recovery concepts
• OLTP and OLAP workload separation
• Dimensional modeling
• Engineering trade-offs and operational runbooks

Architecture

Application / Workload
        |
        v
PostgreSQL Source (OLTP)
        |
        +--> Python Observability
        |
        +--> Full Load / Incremental / CDC concepts
                         |
                         v
                   Migration Layer
                         |
                         v
                  PostgreSQL Target
                         |
                         v
                  Analytical Model
                Dimensions + Facts

Supporting layers:

• Terraform -> Infrastructure as Code
• Flyway -> Schema lifecycle
• Python -> Monitoring and validation
• AWS DMS -> Migration architecture
• PostgreSQL -> Runtime and workload observability

Detailed documentation:

• Architecture
• Migration Strategy
• Observability
• Troubleshooting
• Disaster Recovery

Technology Stack

Database

• PostgreSQL 16
• SQL
• pg_stat_activity
• pg_stat_statements
• pg_stat_user_tables
• pg_locks
• pg_blocking_pids()
• EXPLAIN (ANALYZE, BUFFERS)

Automation

• Python
• Psycopg
• python-dotenv

Infrastructure

• Docker
• Docker Compose
• Terraform
• Terraform Modules

Database Lifecycle

• Flyway

AWS Architecture Concepts

• Amazon RDS for PostgreSQL
• AWS Database Migration Service
• Full Load
• Change Data Capture
• PostgreSQL WAL
• Logical Replication
• CloudWatch concepts

Analytics

• Dimensional modeling
• Fact tables
• Dimension tables
• OLTP vs OLAP workload separation

Repository Structure

postgresql-dbre-modernization-lab/
├── .env.example
├── .gitignore
├── LICENSE
├── README.md
├── requirements.txt
├── docker-compose.yml
├── dms/
├── docs/
├── infrastructure/
├── migrations/
├── scripts/
└── sql/

Getting Started

Requirements

• Docker Desktop
• Python 3
• Terraform
• Git

Clone

git clone <repository-url>
cd postgresql-dbre-modernization-lab

Python environment

python -m venv .venv
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt

Environment configuration

Create .env based on .env.example.

Never commit .env.

Start PostgreSQL

docker compose up -d

Expected:

• Source PostgreSQL -> localhost:5432
• Target PostgreSQL -> localhost:5433

Reliability Scenarios

Long-Running Queries

scripts/monitor_long_queries.py detects queries above a configurable threshold using pg_stat_activity.

Blocking Sessions

scripts/monitor_blocking.py identifies blocked sessions, blockers, transaction duration, and SQL involved.

Connection Pressure

scripts/open_connections.py creates concurrent connections and scripts/monitor_connections.py inspects them.

Database Health Check

scripts/db_health_check.py checks connection count, long-running queries, blocking sessions, dead tuples, and table sizes.

Query Performance Analysis

The lab uses pg_stat_statements for cumulative workload analysis and:

EXPLAIN (ANALYZE, BUFFERS)

to inspect execution plans, row estimates, scans, joins, sorting, cache activity, and storage reads.

Migration Lab

The project simulates Full Load from source PostgreSQL to target PostgreSQL and validates consistency using scripts/validate_migration.py.

Validation includes:

• row counts
• sums
• min timestamps
• max timestamps

Incremental Loading

The lab includes a simplified high-water-mark strategy based on last_order_id.

This captures new records but not arbitrary updates or deletes to older rows.

Change Data Capture

True CDC captures INSERT, UPDATE, and DELETE.

For PostgreSQL, production CDC commonly relies on WAL and logical replication.

The Python replication examples here are intentionally simplified to demonstrate checkpoints, lag, synchronization, and resumability.

AWS DMS Architecture

PostgreSQL Source
    |
    v
Source Endpoint
    |
    v
DMS Replication Task
    |
    | Full Load + CDC
    v
Target Endpoint
    |
    v
Amazon RDS PostgreSQL

Infrastructure as Code

Terraform examples are under infrastructure/.

• infrastructure/rds/ -> reusable RDS module and configuration
• infrastructure/dms/ -> DMS replication architecture
• infrastructure/examples/terraform-state/ -> state and drift lab

Schema Versioning

Flyway migrations are under migrations/.

Concepts:

• migration ordering
• schema history
• baseline
• checksums
• backward-compatible changes
• expand-and-contract migrations

Analytical Workload Separation

Transactional model:

• customers
• orders
• payments

Analytical model:

• dim_customer
• dim_date
• fact_orders

This demonstrates why OLAP workloads should not automatically compete with OLTP workloads.

Backup and Recovery

The lab includes:

• pg_dump
• pg_restore
• restore validation
• PITR concepts
• WAL
• RPO
• RTO

A backup strategy is only useful if the restore process has been tested.

Engineering Trade-Offs

Decision	Option A	Option B
Migration	pg_dump / pg_restore	AWS DMS
Availability	Multi-AZ	Read Replica
Analytics	PostgreSQL Replica	Redshift
Hosting	RDS	PostgreSQL on EC2
Query Performance	Indexing	Write overhead
Scaling	Scale-up	Query/application tuning
Data Movement	Incremental polling	CDC
Modeling	Normalized OLTP	Dimensional OLAP

The correct choice depends on workload, reliability requirements, operational complexity, cost, and recovery objectives.

Security Considerations

Production environments should consider:

• private networking
• least privilege
• TLS in transit
• encryption at rest
• secure secret management
• backup retention
• auditing
• controlled infrastructure changes
• deletion protection
• monitoring

Secrets are intentionally excluded from this repository.

Key Lessons

• A successful pipeline does not guarantee correct data.
• Scaling infrastructure should not replace root-cause analysis.
• Database connections are a managed resource.
• A query can have high cumulative impact even if each execution is fast.
• Replication is different from analytical modeling.
• Full Load alone does not provide low-downtime synchronization.
• A migration is incomplete without independent validation.
• Backups should be tested through restore procedures.
• Destructive Terraform plans must be reviewed before apply.
• Observability should combine infrastructure and database-level signals.

Disclaimer

This repository is an educational and portfolio laboratory.

AWS infrastructure definitions are provided for architecture and Infrastructure as Code practice and should be reviewed and adapted before production use.

No production credentials are included.

Author

João Lucas Oliveira

Analytics Engineer / Data Engineer

Focused on Data Engineering, Analytics Engineering, Database Reliability, and Data Platform Engineering.