Learn to design and implement a complete enterprise database platform with high availability, monitoring, security, and automation for production environments.
Learn to design and implement a complete enterprise database platform with high availability, monitoring, security, and automation for production environments.
This capstone project brings together all the concepts you've learned throughout the Database Administration
course to build a complete enterprise-grade database platform. You'll design and implement a robust
database infrastructure that integrates high availability, monitoring, security, backup solutions, and
automation techniques.
By the end of this capstone project, you'll have created a production-ready database architecture
that can serve as a foundation for critical business applications with demanding requirements
for uptime, performance, and security.
This capstone project is divided into four major components, each representing a critical
aspect of enterprise database architecture:
Infrastructure:
Multi-region containerized deployment with proper networking and security
High Availability:
Replication, automated failover, and load balancing
Observability:
Monitoring, alerting, and performance dashboards
Automation:
Backup strategies, infrastructure as code, and CI/CD pipelines
Infrastructure
High Availability
Observability
Automation
A robust enterprise database infrastructure requires careful planning for deployment across
multiple regions to ensure resilience against regional outages, compliance with data residency
requirements, and optimal performance for globally distributed users.
Key Infrastructure Components
Containerized Database Deployment
Containerizing your PostgreSQL deployment provides consistency across environments,
simplified scaling, and faster deployment cycles. For enterprise deployments,
Kubernetes offers the orchestration capabilities needed to manage containerized databases
at scale.
Docker Containerization Best Practices
Use official PostgreSQL images or build custom images with clear versioning
Store data on persistent volumes outside the container
Set resource limits appropriate for database workloads
Configure appropriate health checks for database containers
Ensure proper secret management for database credentials
Example PostgreSQL Dockerfile for enterprise use:
Kubernetes StatefulSet for PostgreSQL
For orchestrating PostgreSQL on Kubernetes, a StatefulSet provides the necessary guarantees for stable network identities and persistent storage:
Network Architecture & Security
A secure network architecture is fundamental for protecting database systems in an enterprise
environment. This includes proper network segmentation, secure communication channels, and
defense in depth strategies.
Network Segmentation
Implement a multi-tier network architecture with database systems in a dedicated subnet
isolated from application tiers and public networks:
Place database servers in private subnets without public IP addresses
Use network ACLs and security groups to restrict traffic
Implement jump boxes or bastion hosts for administrative access
Deploy proxies or connection poolers in a middle tier for application access
Secure Communication
Ensure all database communications are encrypted:
Enable SSL/TLS for PostgreSQL connections
Use strong cipher suites and disable outdated protocols
Implement certificate validation for clients and servers
Rotate certificates regularly with minimal downtime
Example PostgreSQL SSL configuration:
AWS Network Architecture Example
Here's an example AWS architecture for a multi-region PostgreSQL deployment with proper
network security:
Connection Pooling & Load Balancing
Connection pooling and load balancing are essential components of an enterprise database
architecture. They improve performance, provide high availability, and optimize resource
utilization across database instances.
Connection Pooling with PgBouncer
PgBouncer is a lightweight connection pooler for PostgreSQL that reduces the overhead of
creating new connections to the database:
Maintains a pool of connections that can be reused by clients
Reduces the number of active connections to the database
Supports transaction, session, and statement pooling modes
Provides connection queuing to handle connection spikes
Example PgBouncer configuration:
Load Balancing with HAProxy
HAProxy can be used to distribute database connections across multiple PostgreSQL
instances based on their roles (primary or replicas):
Route write operations to the primary instance
Distribute read operations across replicas
Perform health checks to detect node failures
Provide failover capabilities
Example HAProxy configuration for PostgreSQL:
Kubernetes Service for PostgreSQL Load Balancing
In a Kubernetes environment, Services can provide basic load balancing:
1FROM postgres:14
2
3# Set environment variables for tuning
4ENV POSTGRES_USER=postgres \\
5 POSTGRES_PASSWORD_FILE=/run/secrets/postgres-password \\
6 PGDATA=/var/lib/postgresql/data/pgdata
7
8# Copy custom configuration
9COPY postgresql.conf /etc/postgresql/postgresql.conf
10COPY pg_hba.conf /etc/postgresql/pg_hba.conf
11
12# Add monitoring extensions
13RUN apt-get update && apt-get install -y --no-install-recommends \\
14 postgresql-contrib \\
15 postgresql-$PG_MAJOR-pgaudit \\
16 postgresql-$PG_MAJOR-pg-stat-statements
17
18# Copy initialization scripts
19COPY ./init-scripts/ /docker-entrypoint-initdb.d/
20
21# Create directories for WAL archiving
22RUN mkdir -p /var/lib/postgresql/archive && \\
23 chown -R postgres:postgres /var/lib/postgresql/archive
24
25# Set command to use custom config file
26CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]1apiVersion: apps/v1
2kind: StatefulSet
3metadata:
4 name: postgres
5spec:
6 serviceName: "postgres"
7 replicas: 1
8 selector:
9 matchLabels:
10 app: postgres
11 template:
12 metadata:
13 labels:
14 app: postgres
15 spec:
16 containers:
17 - name: postgres
18 image: postgres:14
19 ports:
20 - containerPort: 5432
21 name: postgres
22 env:
23 - name: POSTGRES_PASSWORD
24 valueFrom:
25 secretKeyRef:
26 name: postgres-secrets
27 key: password
28 - name: PGDATA
29 value: /var/lib/postgresql/data/pgdata
30 volumeMounts:
31 - name: postgres-data
32 mountPath: /var/lib/postgresql/data
33 - name: postgres-config
34 mountPath: /etc/postgresql
35 resources:
36 requests:
37 memory: "2Gi"
38 cpu: "1"
39 limits:
40 memory: "4Gi"
41 cpu: "2"
42 readinessProbe:
43 exec:
44 command:
45 - pg_isready
46 - -U
47 - postgres
48 initialDelaySeconds: 5
49 periodSeconds: 10
50 volumes:
51 - name: postgres-config
52 configMap:
53 name: postgres-config
54 volumeClaimTemplates:
55 - metadata:
56 name: postgres-data
57 spec:
58 accessModes: ["ReadWriteOnce"]
59 storageClassName: "fast"
60 resources:
61 requests:
62 storage: 100Gi1# PostgreSQL SSL configuration
2ssl = on
3ssl_cert_file = '/etc/ssl/certs/server.crt'
4ssl_key_file = '/etc/ssl/private/server.key'
5ssl_ca_file = '/etc/ssl/certs/ca.crt'
6ssl_prefer_server_ciphers = on
7ssl_min_protocol_version = 'TLSv1.2'1# Terraform configuration for VPC setup
2resource "aws_vpc" "database_vpc" {
3 cidr_block = "10.0.0.0/16"
4 enable_dns_support = true
5 enable_dns_hostnames = true
6 tags = {
7 Name = "database-vpc"
8 Environment = "production"
9 }
10}
11
12# Private subnet for databases
13resource "aws_subnet" "database_subnet" {
14 count = 3
15 vpc_id = aws_vpc.database_vpc.id
16 cidr_block = "10.0.\${count.index + 10}.0/24"
17 availability_zone = data.aws_availability_zones.available.names[count.index]
18 tags = {
19 Name = "database-subnet-\${count.index + 1}"
20 Environment = "production"
21 }
22}
23
24# Database security group
25resource "aws_security_group" "database_sg" {
26 name = "database-security-group"
27 description = "Security group for PostgreSQL databases"
28 vpc_id = aws_vpc.database_vpc.id
29
30 ingress {
31 from_port = 5432
32 to_port = 5432
33 protocol = "tcp"
34 security_groups = [aws_security_group.application_sg.id]
35 description = "Allow PostgreSQL access from application tier"
36 }
37
38 ingress {
39 from_port = 5432
40 to_port = 5432
41 protocol = "tcp"
42 self = true
43 description = "Allow PostgreSQL access between database nodes"
44 }
45
46 egress {
47 from_port = 0
48 to_port = 0
49 protocol = "-1"
50 cidr_blocks = ["0.0.0.0/0"]
51 description = "Allow all outbound traffic"
52 }
53
54 tags = {
55 Name = "database-sg"
56 Environment = "production"
57 }
58}1[databases]
2* = host=localhost port=5432 dbname=postgres
3
4[pgbouncer]
5listen_addr = *
6listen_port = 6432
7auth_type = md5
8auth_file = /etc/pgbouncer/userlist.txt
9pool_mode = transaction
10max_client_conn = 1000
11default_pool_size = 100
12reserve_pool_size = 5
13reserve_pool_timeout = 3
14server_reset_query = DISCARD ALL
15server_check_delay = 30
16server_check_query = select 1
17server_idle_timeout = 600
18client_idle_timeout = 0
19log_connections = 1
20log_disconnections = 1
21application_name_add_host = 1
22stats_period = 601global
2 maxconn 10000
3 log 127.0.0.1 local0 notice
4 user haproxy
5 group haproxy
6
7defaults
8 log global
9 mode tcp
10 retries 2
11 timeout connect 3s
12 timeout server 5m
13 timeout client 5m
14
15listen postgres-writes
16 bind *:5432
17 mode tcp
18 balance first
19 option httpchk GET /primary
20 http-check expect status 200
21 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
22 server postgres-1 postgres-1:5432 check port 8008
23 server postgres-2 postgres-2:5432 check port 8008 backup
24 server postgres-3 postgres-3:5432 check port 8008 backup
25
26listen postgres-reads
27 bind *:5433
28 mode tcp
29 balance roundrobin
30 option httpchk GET /replica
31 http-check expect status 200
32 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
33 server postgres-1 postgres-1:5432 check port 8008
34 server postgres-2 postgres-2:5432 check port 8008
35 server postgres-3 postgres-3:5432 check port 8008
36
37listen stats
38 bind *:7000
39 mode http
40 stats enable
41 stats uri /
42 stats realm Strictly\\ Private
43 stats auth admin:password1231apiVersion: v1
2kind: Service
3metadata:
4 name: postgres-primary
5 labels:
6 app: postgres
7 role: primary
8spec:
9 selector:
10 app: postgres
11 role: primary
12 ports:
13 - port: 5432
14 targetPort: 5432
15 type: ClusterIP
16---
17apiVersion: v1
18kind: Service
19metadata:
20 name: postgres-replicas
21 labels:
22 app: postgres
23 role: replica
24spec:
25 selector:
26 app: postgres
27 role: replica
28 ports:
29 - port: 5432
30 targetPort: 5432
31 type: ClusterIPHigh availability is critical for enterprise database systems that require minimal downtime
and continuous operation. This involves implementing robust replication, automated failover,
and properly designed load balancing to ensure service continuity even in the face of hardware
failures or maintenance operations.
Key High Availability Components
Streaming Replication & Patroni
PostgreSQL's streaming replication provides a fundamental building block for high availability,
allowing data to be replicated from a primary server to one or more standby servers in real-time.
Patroni enhances this by providing automated failover and cluster management capabilities.
PostgreSQL Streaming Replication Configuration
Configuration on the primary server (postgresql.conf):
Host-based authentication on the primary (pg_hba.conf):
Configuration on the standby server (recovery.conf or postgresql.auto.conf):
Patroni HA Cluster Setup
Patroni provides automated failover and cluster management on top of PostgreSQL replication.
It uses a distributed configuration store (etcd, Consul, or ZooKeeper) to maintain cluster state.
Example Patroni configuration (patroni.yml):
Patroni Kubernetes Deployment
Running Patroni on Kubernetes provides an excellent platform for high availability PostgreSQL:
Automated Failover Mechanisms
Automated failover is essential for minimizing downtime during database server failures.
A proper failover system should detect failures quickly, promote a standby to primary,
and reconfigure the system to maintain high availability.
Failover Process Overview
Failure Detection:
Continuously monitor the primary server through health checks
Ensure the failed primary cannot cause split-brain scenarios
Leader Election:
Select the most suitable standby for promotion
Standby Promotion:
Promote the selected standby to primary role
Reconfiguration:
Update connection routing and reconfigure remaining standbys
Reintegrate the failed node as a standby once it's available
Patroni Failover Configuration
Configure Patroni failover parameters for optimal performance in your environment:
Failover Testing
Regular testing of failover procedures is essential to ensure they work as expected in real failure scenarios:
Client-Side Failover Handling
Applications need to handle failover events gracefully. Here's an example of connection
configuration with libpq that supports failover:
Example Node.js code that handles PostgreSQL failover:
Cross-Region Disaster Recovery
Cross-region disaster recovery is essential for protecting against major outages affecting
an entire region. This approach ensures business continuity even in the face of large-scale
disasters.
Cross-Region Replication Approaches
Asynchronous Logical Replication:
Use PostgreSQL's logical replication to
replicate data across regions without waiting for acknowledgment, minimizing performance impact.
Delayed Standby:
Maintain a standby server with intentionally delayed
replication to protect against logical data corruption or erroneous changes.
Physical Replication with WAL Archiving:
Ship WAL files to remote regions
for point-in-time recovery capabilities.
Third-party Solutions:
Tools like pglogical or BDR (Bi-Directional Replication)
for more advanced replication topologies.
Logical Replication Configuration
Setting up logical replication between regions:
Disaster Recovery Testing
Regular disaster recovery testing is critical to ensure the system works when needed:
Recovery Time Objectives (RTO) and Recovery Point Objectives (RPO)
Define clear RTO and RPO for your cross-region disaster recovery:
Maximum acceptable time to restore service after a disaster (e.g., 15 minutes)
Maximum acceptable data loss measured in time (e.g., 5 minutes)
Cross-Region DR Checklist
Ensure standby databases in DR region are continuously synchronized
Maintain automation scripts for promotion and reconfiguration
Document clear procedures for determining when to trigger DR failover
Configure DNS and load balancers for quick redirection
Run quarterly DR tests to validate procedures
Monitor replication lag between regions continuously
Train team members on DR procedures
Document procedure for rebuilding original region after recovery
1# Replication settings on primary
2listen_addresses = '*'
3wal_level = replica
4max_wal_senders = 10
5max_replication_slots = 10
6wal_keep_size = 1GB
7hot_standby = on
8wal_log_hints = on
9synchronous_commit = on # For synchronous replication
10synchronous_standby_names = 'FIRST 1 (standby1, standby2)'1# Allow replication connections from standbys
2host replication replicator 10.0.0.0/16 scram-sha-256
3host replication replicator 192.168.1.0/24 scram-sha-2561# Recovery settings for standby
2primary_conninfo = 'host=primary port=5432 user=replicator password=secret application_name=standby1'
3restore_command = 'cp /path/to/archive/%f %p'
4recovery_target_timeline = 'latest'
5hot_standby = on1scope: postgres-cluster
2name: postgres1
3
4restapi:
5 listen: 0.0.0.0:8008
6 connect_address: 10.0.0.1:8008
7
8etcd:
9 host: 10.0.0.10:2379
10
11bootstrap:
12 dcs:
13 ttl: 30
14 loop_wait: 10
15 retry_timeout: 10
16 maximum_lag_on_failover: 1048576
17 postgresql:
18 use_pg_rewind: true
19 use_slots: true
20 parameters:
21 wal_level: replica
22 hot_standby: "on"
23 wal_keep_size: 1GB
24 max_wal_senders: 10
25 max_replication_slots: 10
26 wal_log_hints: "on"
27 initdb:
28 - encoding: UTF8
29 - data-checksums
30
31postgresql:
32 listen: 0.0.0.0:5432
33 connect_address: 10.0.0.1:5432
34 data_dir: /var/lib/postgresql/data
35 bin_dir: /usr/lib/postgresql/14/bin
36 pgpass: /tmp/pgpass
37 authentication:
38 replication:
39 username: replicator
40 password: replicatorpassword
41 superuser:
42 username: postgres
43 password: postgrespassword
44 parameters:
45 max_connections: 100
46 shared_buffers: 2GB
47 maintenance_work_mem: 128MB
48
49tags:
50 nofailover: false
51 noloadbalance: false
52 clonefrom: false
53 nosync: false1apiVersion: apps/v1
2kind: StatefulSet
3metadata:
4 name: patroni
5 labels:
6 app: patroni
7spec:
8 serviceName: patroni
9 replicas: 3
10 selector:
11 matchLabels:
12 app: patroni
13 template:
14 metadata:
15 labels:
16 app: patroni
17 spec:
18 containers:
19 - name: patroni
20 image: patroni/patroni:latest
21 env:
22 - name: PATRONI_KUBERNETES_POD_IP
23 valueFrom:
24 fieldRef:
25 fieldPath: status.podIP
26 - name: PATRONI_KUBERNETES_NAMESPACE
27 valueFrom:
28 fieldRef:
29 fieldPath: metadata.namespace
30 - name: PATRONI_KUBERNETES_LABELS
31 value: '{app: patroni}'
32 - name: PATRONI_SUPERUSER_USERNAME
33 value: postgres
34 - name: PATRONI_SUPERUSER_PASSWORD
35 valueFrom:
36 secretKeyRef:
37 name: patroni-secrets
38 key: superuser-password
39 - name: PATRONI_REPLICATION_USERNAME
40 value: replicator
41 - name: PATRONI_REPLICATION_PASSWORD
42 valueFrom:
43 secretKeyRef:
44 name: patroni-secrets
45 key: replication-password
46 - name: PATRONI_SCOPE
47 value: patroni-cluster
48 - name: PATRONI_NAME
49 valueFrom:
50 fieldRef:
51 fieldPath: metadata.name
52 ports:
53 - containerPort: 8008
54 name: patroni
55 - containerPort: 5432
56 name: postgresql
57 volumeMounts:
58 - name: data
59 mountPath: /data
60 resources:
61 requests:
62 cpu: 500m
63 memory: 1Gi
64 limits:
65 cpu: 1
66 memory: 2Gi
67 readinessProbe:
68 httpGet:
69 path: /readiness
70 port: 8008
71 initialDelaySeconds: 5
72 periodSeconds: 10
73 volumeClaimTemplates:
74 - metadata:
75 name: data
76 spec:
77 accessModes: [ "ReadWriteOnce" ]
78 resources:
79 requests:
80 storage: 50Gi1bootstrap:
2 dcs:
3 # Maximum time in seconds for leader election
4 ttl: 30
5 # Time between DCS checks
6 loop_wait: 10
7 # Time in seconds to wait for leader to recover
8 retry_timeout: 10
9 # Maximum allowed data lag for replica promotion
10 maximum_lag_on_failover: 1048576
11 # Maximum leader rotation tries before demoting unhealthy leader
12 master_start_timeout: 300
13 # Synchronous mode to prevent data loss
14 synchronous_mode: true
15 # Allow adding node as a sync standby even if others are not caught up
16 synchronous_mode_strict: false
17 # List of allowed standby names for sync replication
18 synchronous_node_count: 11#!/bin/bash
2# Script to test PostgreSQL failover with Patroni
3
4echo "Starting failover test..."
5echo "Current cluster state:"
6patronictl -c /etc/patroni/patroni.yml list
7
8# Get current primary
9PRIMARY=$(patronictl -c /etc/patroni/patroni.yml list -f json | jq -r '.[] | select(.Role == "Leader") | .Member')
10echo "Current primary is: $PRIMARY"
11
12# Simulate primary failure by stopping PostgreSQL
13echo "Simulating failure by stopping PostgreSQL on primary..."
14ssh $PRIMARY "sudo systemctl stop postgresql"
15
16# Wait for failover to occur
17echo "Waiting for failover to complete..."
18sleep 30
19
20# Check new cluster state
21echo "New cluster state after failover:"
22patronictl -c /etc/patroni/patroni.yml list
23
24# Get new primary
25NEW_PRIMARY=$(patronictl -c /etc/patroni/patroni.yml list -f json | jq -r '.[] | select(.Role == "Leader") | .Member')
26echo "New primary is: $NEW_PRIMARY"
27
28# Check data integrity
29echo "Verifying data integrity..."
30psql -h $NEW_PRIMARY -U postgres -c "SELECT count(*) FROM important_table;"
31
32# Restart old primary as standby
33echo "Recovering old primary as standby..."
34ssh $PRIMARY "sudo systemctl start postgresql"
35
36# Wait for recovery
37sleep 30
38
39# Final cluster state
40echo "Final cluster state after recovery:"
41patronictl -c /etc/patroni/patroni.yml list
42
43echo "Failover test completed."1# Connection string with multiple hosts for failover
2host=host1,host2,host3
3port=5432
4target_session_attrs=read-write # Connect only to nodes that allow writes
5fallback_application_name=myapp
6connect_timeout=3
7#options='-c statement_timeout=5000'1const { Pool } = require('pg');
2const retry = require('async-retry');
3
4// Configure a pool with multiple hosts for failover
5const pool = new Pool({
6 connectionString: 'postgresql://user:password@host1,host2,host3:5432/dbname?target_session_attrs=read-write',
7 max: 20,
8 idleTimeoutMillis: 30000,
9 connectionTimeoutMillis: 3000,
10});
11
12async function executeQueryWithRetry(query, params) {
13 return retry(async (bail, attempt) => {
14 try {
15 const client = await pool.connect();
16 try {
17 console.log(\`Executing query, attempt \${attempt}\`);
18 return await client.query(query, params);
19 } finally {
20 client.release();
21 }
22 } catch (err) {
23 if (err.code === 'ECONNREFUSED' || err.code === '57P01' || err.code === '08006') {
24 // Connection errors or server termination - can retry
25 console.error(\`Connection error: \${err.message}, retrying...\`);
26 throw err; // Throw to trigger retry
27 } else {
28 // Other errors may indicate query problems - don't retry
29 bail(err);
30 return;
31 }
32 }
33 }, {
34 retries: 5,
35 minTimeout: 100,
36 maxTimeout: 2000,
37 factor: 2,
38 onRetry: (err) => console.log(\`Retrying after error: \${err.message}\`),
39 });
40}1-- On primary server in region 1
2-- Create a publication for all tables
3CREATE PUBLICATION region1_pub FOR ALL TABLES;
4
5-- On standby server in region 2
6-- Create a subscription to the publication
7CREATE SUBSCRIPTION region2_sub
8 CONNECTION 'host=primary.region1 port=5432 dbname=mydb user=replication password=secret'
9 PUBLICATION region1_pub;
10
11-- Create a replication slot (on primary in region 1)
12SELECT pg_create_logical_replication_slot('region2_slot', 'pgoutput');
13
14-- Connect subscription to slot (on standby in region 2)
15ALTER SUBSCRIPTION region2_sub SET (slot_name = 'region2_slot');
16
17-- Monitor replication lag
18SELECT
19 slot_name,
20 confirmed_flush_lsn,
21 pg_current_wal_lsn(),
22 (pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_bytes
23FROM pg_replication_slots
24WHERE slot_type = 'logical';1#!/bin/bash
2# Cross-region disaster recovery test script
3
4LOG_FILE="/var/log/dr_test_$(date +%Y%m%d_%H%M%S).log"
5PRIMARY_REGION="us-west-2"
6DR_REGION="us-east-1"
7DB_NAME="production_db"
8TEST_TABLE="dr_test_table"
9
10log() {
11 echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE
12}
13
14log "Starting cross-region disaster recovery test"
15
16# 1. Create test data in primary region
17log "Creating test data in primary region..."
18psql -h primary.$PRIMARY_REGION -U postgres -d $DB_NAME -c "
19 CREATE TABLE IF NOT EXISTS $TEST_TABLE (id serial PRIMARY KEY, data text, created_at timestamp DEFAULT CURRENT_TIMESTAMP);
20 INSERT INTO $TEST_TABLE (data) VALUES ('DR test record');
21 SELECT pg_current_wal_lsn() AS current_lsn;
22" > /tmp/current_lsn.txt
23
24CURRENT_LSN=$(grep -oP '(?<=current_lsn \\| ).*(?= \\|)' /tmp/current_lsn.txt | tail -n1)
25log "Current WAL LSN in primary region: $CURRENT_LSN"
26
27# 2. Wait for replication to catch up
28log "Waiting for replication to catch up in DR region..."
29while true; do
30 STANDBY_LSN=$(psql -h standby.$DR_REGION -U postgres -d $DB_NAME -c "SELECT pg_last_wal_replay_lsn() AS replay_lsn;" -t | tr -d '[:space:]')
31 log "Standby replay LSN in DR region: $STANDBY_LSN"
32
33 if [[ "$STANDBY_LSN" > "$CURRENT_LSN" ]]; then
34 log "Replication caught up successfully"
35 break
36 fi
37
38 log "Waiting for replication to catch up..."
39 sleep 10
40done
41
42# 3. Verify data exists in DR region
43log "Verifying test data exists in DR region..."
44TEST_COUNT=$(psql -h standby.$DR_REGION -U postgres -d $DB_NAME -c "SELECT COUNT(*) FROM $TEST_TABLE WHERE data = 'DR test record';" -t | tr -d '[:space:]')
45
46if [[ "$TEST_COUNT" -gt 0 ]]; then
47 log "Data verified in DR region. Found $TEST_COUNT test records."
48else
49 log "ERROR: Test data not found in DR region!"
50 exit 1
51fi
52
53# 4. Simulate disaster in primary region
54log "Simulating disaster in primary region..."
55log "Assuming primary region is down..."
56
57# 5. Promote standby in DR region
58log "Promoting standby in DR region to primary..."
59psql -h standby.$DR_REGION -U postgres -d postgres -c "SELECT pg_promote();" || {
60 log "Manual promotion: Execute pg_ctl promote on standby server"
61 ssh $DR_REGION "pg_ctl promote -D /var/lib/postgresql/data"
62}
63
64# 6. Wait for promotion to complete
65log "Waiting for promotion to complete..."
66sleep 10
67
68# 7. Verify we can write to the new primary
69log "Verifying writes to new primary in DR region..."
70psql -h standby.$DR_REGION -U postgres -d $DB_NAME -c "
71 INSERT INTO $TEST_TABLE (data) VALUES ('DR recovery test record');
72 SELECT COUNT(*) FROM $TEST_TABLE;
73" > /tmp/dr_write_test.txt
74
75WRITE_SUCCESS=$(grep -c 'INSERT 0 1' /tmp/dr_write_test.txt)
76
77if [[ "$WRITE_SUCCESS" -gt 0 ]]; then
78 log "Successfully wrote to new primary in DR region"
79else
80 log "ERROR: Failed to write to new primary in DR region!"
81 exit 1
82fi
83
84# 8. Update connection parameters for applications
85log "In a real DR scenario, update load balancers and connection strings to point to new primary"
86
87# 9. Test complete
88log "Disaster recovery test completed successfully"
89log "Time to promote and verify: $(( $(date +%s) - $(date -d "$(head -n1 $LOG_FILE | cut -d'-' -f1-3)" +%s) )) seconds"
90log "In a real DR event, follow the full DR playbook for client redirection and rebuilding original region"
91
92# 10. Clean up test data
93log "Cleaning up test data..."
94psql -h standby.$DR_REGION -U postgres -d $DB_NAME -c "DELETE FROM $TEST_TABLE WHERE data LIKE 'DR%test record';"
95
96exit 0A robust monitoring and alerting system is essential for maintaining the health and performance
of an enterprise database platform. It provides visibility into system behavior, helps identify
issues before they become critical, and enables data-driven optimization.
Key Observability Components
Prometheus & Grafana Setup
Prometheus and Grafana form a powerful combination for monitoring PostgreSQL databases.
Prometheus collects and stores metrics, while Grafana provides visualization and alerting
capabilities.
PostgreSQL Exporter Configuration
The PostgreSQL Exporter queries the database and exports metrics for Prometheus to scrape:
Custom queries configuration for PostgreSQL Exporter (queries.yaml):
Prometheus Configuration
Configure Prometheus to scrape metrics from PostgreSQL Exporter:
Alert rules for PostgreSQL (postgres_rules.yml):
Grafana Dashboard
Create comprehensive Grafana dashboards to visualize PostgreSQL metrics.
Here's a sample dashboard JSON configuration (excerpt):
Query Performance Monitoring
Monitoring query performance is critical for identifying bottlenecks and optimizing database performance.
Tools like pg_stat_statements and pgBadger provide valuable insights into query execution.
pg_stat_statements Configuration
pg_stat_statements is a PostgreSQL extension that tracks execution statistics for all SQL statements:
Create the extension and query for slow queries:
pgBadger Log Analysis
pgBadger analyzes PostgreSQL log files to generate detailed reports of database activity:
Script to generate daily pgBadger reports:
Automated Query Performance Monitoring
A more comprehensive approach integrates query performance monitoring with your observability stack:
:200]}...\\`\\n*Avg Time:*
:.2f}s\\n*Calls:*
\\n*% of Total DB Time:*
:.2f}%"
}
})
try:
requests.post(SLACK_WEBHOOK, json=message)
except Exception as e:
logger.error(f"Error sending to Slack:
")
def send_to_prometheus(slow_queries):
"""Send metrics to Prometheus Pushgateway."""
if not PROMETHEUS_PUSHGATEWAY:
return
metrics = []
for query in slow_queries:
query_hash = hash(query['query']) % 1000000
metrics.extend([
f'pg_slow_query_calls
',
f'pg_slow_query_mean_time_seconds
',
f'pg_slow_query_total_time_seconds
',
f'pg_slow_query_percent_total
'
])
if metrics:
try:
data = '\\n'.join(metrics)
requests.post(
f"
/metrics/job/pg_query_monitor/instance/
",
data=data,
headers=
: 'text/plain'}
)
except Exception as e:
logger.error(f"Error sending to Prometheus:
")
def main():
"""Main monitoring loop."""
logger.info("Starting PostgreSQL query performance monitoring")
while True:
conn = connect_to_db()
if conn:
try:
slow_queries = get_slow_queries(conn)
if slow_queries:
logger.info(f"Found
slow queries")
send_to_slack(slow_queries)
send_to_prometheus(slow_queries)
else:
logger.info("No slow queries detected")
except Exception as e:
logger.error(f"Error monitoring queries:
")
finally:
conn.close()
logger.info(f"Sleeping for
seconds")
time.sleep(CHECK_INTERVAL)
if __name__ == "__main__":
main()`}
Alerting & Incident Response
A comprehensive alerting system and incident response plan are essential for ensuring timely responses
to database issues and maintaining high availability.
Alert Priority Levels
P1 - Critical:
Service is down or severely impacted; requires immediate attention at any time (24/7)
P2 - High:
Service is degraded with significant impact; requires attention during business hours
P3 - Medium:
Service is partially affected with moderate impact; requires attention within 24 hours
Minor issues with minimal impact; can be addressed during regular maintenance
Critical Database Alerts
Database Down
Instance not responding for >1 minute
Immediate failover, investigate root cause
Disk Space Critical
<5% free space
Clear logs, identify growth cause, add capacity
High Replication Lag
>5 minutes
Identify bottlenecks, adjust resource allocation
High Connection Count
>90% of max_connections
Identify connection leaks, implement pooling
Long-Running Transactions
Transaction >30 minutes
Identify and optimize or terminate transaction
High CPU Usage
>85% for >15 minutes
Identify resource-intensive queries, optimize
Slow Queries
Queries >10s execution time
Analyze execution plan, add indexes, optimize
Backup Failure
Failed backup job
Investigate cause, ensure next backup succeeds
Incident Response Playbook Template
Alerting Integration
Configure Prometheus Alertmanager to route alerts to appropriate channels:
1# Docker Compose configuration for PostgreSQL Exporter
2version: '3.8'
3
4services:
5 postgres-exporter:
6 image: prometheuscommunity/postgres-exporter:latest
7 container_name: postgres-exporter
8 restart: always
9 environment:
10 DATA_SOURCE_NAME: "postgresql://postgres:password@postgres:5432/postgres?sslmode=disable"
11 PG_EXPORTER_EXTEND_QUERY_PATH: "/etc/postgres_exporter/queries.yaml"
12 volumes:
13 - ./queries.yaml:/etc/postgres_exporter/queries.yaml
14 ports:
15 - "9187:9187"
16 depends_on:
17 - postgres
18 networks:
19 - monitoring-network1pg_replication:
2 query: "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag, CASE WHEN pg_is_in_recovery() THEN 1 ELSE 0 END as is_replica"
3 metrics:
4 - lag:
5 usage: "GAUGE"
6 description: "Replication lag in seconds"
7 - is_replica:
8 usage: "GAUGE"
9 description: "1 if server is a replica, 0 if primary"
10
11pg_stat_activity:
12 query: "SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY state"
13 metrics:
14 - state:
15 usage: "LABEL"
16 description: "Connection state"
17 - count:
18 usage: "GAUGE"
19 description: "Number of connections in this state"
20
21pg_database_size:
22 query: "SELECT datname, pg_database_size(datname) as size FROM pg_database"
23 metrics:
24 - datname:
25 usage: "LABEL"
26 description: "Database name"
27 - size:
28 usage: "GAUGE"
29 description: "Database size in bytes"
30
31pg_locks:
32 query: "SELECT mode, count(*) FROM pg_locks GROUP BY mode"
33 metrics:
34 - mode:
35 usage: "LABEL"
36 description: "Lock mode"
37 - count:
38 usage: "GAUGE"
39 description: "Number of locks in this mode"1global:
2 scrape_interval: 15s
3 evaluation_interval: 15s
4
5rule_files:
6 - "postgres_rules.yml"
7
8scrape_configs:
9 - job_name: 'prometheus'
10 static_configs:
11 - targets: ['localhost:9090']
12
13 - job_name: 'postgres'
14 static_configs:
15 - targets: ['postgres-exporter:9187']
16 labels:
17 instance: 'postgres-primary'
18 environment: 'production'
19
20 - job_name: 'node-exporter'
21 static_configs:
22 - targets: ['node-exporter:9100']
23
24 - job_name: 'patroni'
25 metrics_path: '/metrics'
26 static_configs:
27 - targets:
28 - 'patroni-1:8008'
29 - 'patroni-2:8008'
30 - 'patroni-3:8008'1groups:
2 - name: postgres_alerts
3 rules:
4 - alert: PostgresqlHighReplicationLag
5 expr: pg_replication_lag > 300
6 for: 5m
7 labels:
8 severity: critical
9 annotations:
10 summary: "High replication lag on {{ $labels.instance }}"
11 description: "Replication lag is {{ $value }} seconds on {{ $labels.instance }}"
12
13 - alert: PostgresqlDown
14 expr: pg_up == 0
15 for: 1m
16 labels:
17 severity: critical
18 annotations:
19 summary: "PostgreSQL instance {{ $labels.instance }} is down"
20 description: "PostgreSQL instance {{ $labels.instance }} is down"
21
22 - alert: PostgresqlLowDiskSpace
23 expr: node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql/data"} / node_filesystem_size_bytes{mountpoint="/var/lib/postgresql/data"} < 0.1
24 for: 5m
25 labels:
26 severity: critical
27 annotations:
28 summary: "Low disk space on {{ $labels.instance }}"
29 description: "Less than 10% disk space available on {{ $labels.instance }}"
30
31 - alert: PostgresqlTooManyConnections
32 expr: sum(pg_stat_activity_count) by (instance) > (pg_settings_max_connections * 0.8)
33 for: 5m
34 labels:
35 severity: warning
36 annotations:
37 summary: "Too many connections on {{ $labels.instance }}"
38 description: "More than 80% of connections are used on {{ $labels.instance }}"1{
2 "annotations": {
3 "list": [
4 {
5 "builtIn": 1,
6 "datasource": "-- Grafana --",
7 "enable": true,
8 "hide": true,
9 "iconColor": "rgba(0, 211, 255, 1)",
10 "name": "Annotations & Alerts",
11 "type": "dashboard"
12 }
13 ]
14 },
15 "editable": true,
16 "gnetId": null,
17 "graphTooltip": 0,
18 "id": 1,
19 "links": [],
20 "panels": [
21 {
22 "aliasColors": {},
23 "bars": false,
24 "dashLength": 10,
25 "dashes": false,
26 "datasource": "Prometheus",
27 "fieldConfig": {
28 "defaults": {
29 "links": []
30 },
31 "overrides": []
32 },
33 "fill": 1,
34 "fillGradient": 0,
35 "gridPos": {
36 "h": 8,
37 "w": 12,
38 "x": 0,
39 "y": 0
40 },
41 "hiddenSeries": false,
42 "id": 2,
43 "legend": {
44 "alignAsTable": true,
45 "avg": true,
46 "current": true,
47 "max": true,
48 "min": true,
49 "show": true,
50 "total": false,
51 "values": true
52 },
53 "lines": true,
54 "linewidth": 1,
55 "nullPointMode": "null",
56 "options": {
57 "alertThreshold": true
58 },
59 "percentage": false,
60 "pluginVersion": "7.5.5",
61 "pointradius": 2,
62 "points": false,
63 "renderer": "flot",
64 "seriesOverrides": [],
65 "spaceLength": 10,
66 "stack": false,
67 "steppedLine": false,
68 "targets": [
69 {
70 "expr": "pg_stat_database_tup_fetched{datname=~\"$database\"}",
71 "interval": "",
72 "legendFormat": "Rows fetched - {{instance}}",
73 "refId": "A"
74 },
75 {
76 "expr": "pg_stat_database_tup_inserted{datname=~\"$database\"}",
77 "interval": "",
78 "legendFormat": "Rows inserted - {{instance}}",
79 "refId": "B"
80 },
81 {
82 "expr": "pg_stat_database_tup_updated{datname=~\"$database\"}",
83 "interval": "",
84 "legendFormat": "Rows updated - {{instance}}",
85 "refId": "C"
86 },
87 {
88 "expr": "pg_stat_database_tup_deleted{datname=~\"$database\"}",
89 "interval": "",
90 "legendFormat": "Rows deleted - {{instance}}",
91 "refId": "D"
92 }
93 ],
94 "thresholds": [],
95 "timeFrom": null,
96 "timeRegions": [],
97 "timeShift": null,
98 "title": "Row Operations",
99 "tooltip": {
100 "shared": true,
101 "sort": 0,
102 "value_type": "individual"
103 },
104 "type": "graph",
105 "xaxis": {
106 "buckets": null,
107 "mode": "time",
108 "name": null,
109 "show": true,
110 "values": []
111 },
112 "yaxes": [
113 {
114 "format": "short",
115 "label": null,
116 "logBase": 1,
117 "max": null,
118 "min": null,
119 "show": true
120 },
121 {
122 "format": "short",
123 "label": null,
124 "logBase": 1,
125 "max": null,
126 "min": null,
127 "show": true
128 }
129 ],
130 "yaxis": {
131 "align": false,
132 "alignLevel": null
133 }
134 }
135 ],
136 "templating": {
137 "list": [
138 {
139 "allValue": null,
140 "current": {
141 "selected": false,
142 "text": "postgres",
143 "value": "postgres"
144 },
145 "datasource": "Prometheus",
146 "definition": "label_values(pg_database_size_bytes, datname)",
147 "description": null,
148 "error": null,
149 "hide": 0,
150 "includeAll": false,
151 "label": "Database",
152 "multi": false,
153 "name": "database",
154 "options": [],
155 "query": {
156 "query": "label_values(pg_database_size_bytes, datname)",
157 "refId": "StandardVariableQuery"
158 },
159 "refresh": 1,
160 "regex": "",
161 "skipUrlSync": false,
162 "sort": 0,
163 "tagValuesQuery": "",
164 "tagsQuery": "",
165 "type": "query",
166 "useTags": false
167 }
168 ]
169 }
170}1# Enable pg_stat_statements in postgresql.conf
2shared_preload_libraries = 'pg_stat_statements'
3pg_stat_statements.track = all
4pg_stat_statements.max = 10000
5pg_stat_statements.track_utility = on
6pg_stat_statements.save = on
7track_io_timing = on1-- Create the extension
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3
4-- Query for the top 10 most time-consuming queries
5SELECT
6 query,
7 calls,
8 total_exec_time / 1000 as total_exec_time_sec,
9 total_exec_time / calls / 1000 as avg_exec_time_sec,
10 rows / calls as avg_rows,
11 100 * total_exec_time / sum(total_exec_time) OVER() as percent_total
12FROM pg_stat_statements
13ORDER BY total_exec_time DESC
14LIMIT 10;
15
16-- Query for queries with high variance in execution time
17SELECT
18 query,
19 calls,
20 mean_exec_time / 1000 as mean_exec_time_sec,
21 stddev_exec_time / 1000 as stddev_exec_time_sec,
22 stddev_exec_time / mean_exec_time as variance_ratio
23FROM pg_stat_statements
24WHERE calls > 100
25 AND mean_exec_time > 1000000 -- 1 second
26ORDER BY variance_ratio DESC
27LIMIT 10;1# Configure PostgreSQL logging for pgBadger
2log_destination = 'csvlog'
3logging_collector = on
4log_directory = 'pg_log'
5log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
6log_rotation_age = 1d
7log_rotation_size = 100MB
8log_min_duration_statement = 100 # Log statements taking longer than 100ms
9log_checkpoints = on
10log_connections = on
11log_disconnections = on
12log_lock_waits = on
13log_temp_files = 0
14log_autovacuum_min_duration = 0 # Log all autovacuum operations1#!/bin/bash
2# Script to generate daily pgBadger reports
3
4# Configuration
5LOG_DIR="/var/log/postgresql"
6OUTPUT_DIR="/var/www/html/pgbadger"
7RETENTION_DAYS=30
8TODAY=$(date +%Y-%m-%d)
9YESTERDAY=$(date --date="1 day ago" +%Y-%m-%d)
10PGBADGER="/usr/local/bin/pgbadger"
11
12# Create output directory for today
13mkdir -p "$OUTPUT_DIR/$TODAY"
14
15# Generate report for yesterday's logs
16find "$LOG_DIR" -name "postgresql-$YESTERDAY*.log" -o -name "postgresql-$YESTERDAY*.csv" | sort | xargs $PGBADGER \
17 --prefix "%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h " \
18 --jobs 4 \
19 --format csv \
20 --outdir "$OUTPUT_DIR/$TODAY" \
21 --per-hour \
22 --top 20 \
23 --title "PostgreSQL Daily Report: $YESTERDAY"
24
25# Create latest link
26ln -sf "$OUTPUT_DIR/$TODAY" "$OUTPUT_DIR/latest"
27
28# Clean up old reports
29find "$OUTPUT_DIR" -maxdepth 1 -type d -name "20*" -mtime +$RETENTION_DAYS -exec rm -rf {} \\;
30
31echo "pgBadger report for $YESTERDAY generated in $OUTPUT_DIR/$TODAY/"1#!/usr/bin/env python3
2# query_performance_monitor.py - Monitors PostgreSQL query performance and sends alerts
3
4import psycopg2
5import psycopg2.extras
6import time
7import json
8import requests
9from datetime import datetime
10import logging
11import os
12
13# Configuration
14DB_CONFIG = {
15 'dbname': os.environ.get('DB_NAME', 'postgres'),
16 'user': os.environ.get('DB_USER', 'postgres'),
17 'password': os.environ.get('DB_PASSWORD', 'password'),
18 'host': os.environ.get('DB_HOST', 'localhost'),
19 'port': os.environ.get('DB_PORT', '5432'),
20}
21SLOW_QUERY_THRESHOLD = 1000 # milliseconds
22CHECK_INTERVAL = 300 # seconds
23SLACK_WEBHOOK = os.environ.get('SLACK_WEBHOOK', '')
24PROMETHEUS_PUSHGATEWAY = os.environ.get('PROMETHEUS_PUSHGATEWAY', 'http://localhost:9091')
25
26# Setup logging
27logging.basicConfig(
28 level=logging.INFO,
29 format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
30 handlers=[logging.StreamHandler()]
31)
32logger = logging.getLogger('query_monitor')
33
34def connect_to_db():
35 """Connect to the PostgreSQL database."""
36 try:
37 conn = psycopg2.connect(**DB_CONFIG)
38 return conn
39 except Exception as e:
40 logger.error(f"Error connecting to database: {e}")
41 return None
42
43def get_slow_queries(conn):
44 """Get slow queries from pg_stat_statements."""
45 cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
46 cursor.execute("""
47 SELECT
48 query,
49 calls,
50 total_exec_time / 1000 as total_exec_time_sec,
51 mean_exec_time / 1000 as mean_exec_time_sec,
52 rows / calls as avg_rows,
53 100 * total_exec_time / sum(total_exec_time) OVER() as percent_total
54 FROM pg_stat_statements
55 WHERE mean_exec_time > %s * 1000
56 ORDER BY total_exec_time DESC
57 LIMIT 10;
58 """, (SLOW_QUERY_THRESHOLD / 1000,))
59 return cursor.fetchall()
60
61def send_to_slack(slow_queries):
62 """Send slow query alert to Slack."""
63 if not SLACK_WEBHOOK:
64 return
65
66 if not slow_queries:
67 return
68
69 message = {
70 "blocks": [
71 {
72 "type": "header",
73 "text": {
74 "type": "plain_text",
75 "text": "🐢 Slow PostgreSQL Queries Detected",
76 "emoji": True
77 }
78 },
79 {
80 "type": "section",
81 "text": {
82 "type": "mrkdwn",
83 "text": f"*Database:* {DB_CONFIG['dbname']} on {DB_CONFIG['host']}\\n*Time:* {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
84 }
85 }
86 ]
87 }
88
89 for query in slow_queries:
90 message["blocks"].append({
91 "type": "section",
92 "text": {
93 "type": "mrkdwn",
94 "text": f"*Query:* \\`{query['query'][:200]}...\\`\\n*Avg Time:* {query['mean_exec_time_sec']:.2f}s\\n*Calls:* {query['calls']}\\n*% of Total DB Time:* {query['percent_total']:.2f}%"
95 }
96 })
97
98 try:
99 requests.post(SLACK_WEBHOOK, json=message)
100 except Exception as e:
101 logger.error(f"Error sending to Slack: {e}")
102
103def send_to_prometheus(slow_queries):
104 """Send metrics to Prometheus Pushgateway."""
105 if not PROMETHEUS_PUSHGATEWAY:
106 return
107
108 metrics = []
109
110 for query in slow_queries:
111 # Hash the query for a consistent label
112 query_hash = hash(query['query']) % 1000000
113
114 metrics.extend([
115 f'pg_slow_query_calls{{query_hash="{query_hash}"}} {query["calls"]}',
116 f'pg_slow_query_mean_time_seconds{{query_hash="{query_hash}"}} {query["mean_exec_time_sec"]}',
117 f'pg_slow_query_total_time_seconds{{query_hash="{query_hash}"}} {query["total_exec_time_sec"]}',
118 f'pg_slow_query_percent_total{{query_hash="{query_hash}"}} {query["percent_total"]}'
119 ])
120
121 if metrics:
122 try:
123 data = '\\n'.join(metrics)
124 requests.post(
125 f"{PROMETHEUS_PUSHGATEWAY}/metrics/job/pg_query_monitor/instance/{DB_CONFIG['host']}",
126 data=data,
127 headers={'Content-Type': 'text/plain'}
128 )
129 except Exception as e:
130 logger.error(f"Error sending to Prometheus: {e}")
131
132def main():
133 """Main monitoring loop."""
134 logger.info("Starting PostgreSQL query performance monitoring")
135
136 while True:
137 conn = connect_to_db()
138 if conn:
139 try:
140 slow_queries = get_slow_queries(conn)
141 if slow_queries:
142 logger.info(f"Found {len(slow_queries)} slow queries")
143 send_to_slack(slow_queries)
144 send_to_prometheus(slow_queries)
145 else:
146 logger.info("No slow queries detected")
147 except Exception as e:
148 logger.error(f"Error monitoring queries: {e}")
149 finally:
150 conn.close()
151
152 logger.info(f"Sleeping for {CHECK_INTERVAL} seconds")
153 time.sleep(CHECK_INTERVAL)
154
155if __name__ == "__main__":
156 main()1# Database Incident Response Playbook
2
3## Incident: [Type of Incident]
4
5### Initial Assessment
6
71. **Detect**: How was the incident detected? (Alert, user report, etc.)
82. **Impact**: What services are affected? How many users?
93. **Scope**: Is it isolated to one component or system-wide?
10
11### Response Team
12
13- **Incident Commander**: [Name/Role] - Coordinates response
14- **Technical Lead**: [Name/Role] - Directs technical remediation
15- **Communications Lead**: [Name/Role] - Handles stakeholder updates
16
17### Immediate Actions
18
191. **Containment**: Steps to prevent further impact
20 - [Specific steps for this type of incident]
21
222. **Mitigation**: Steps to restore service
23 - [Specific steps for this type of incident]
24
25### Investigation
26
271. **Root Cause Analysis**:
28 - Review logs, metrics, and recent changes
29 - Document findings and evidence
30
312. **Forensics**: Preserve relevant data
32 - [Specific data to collect]
33
34### Recovery
35
361. **Service Restoration**:
37 - [Step-by-step process]
38
392. **Verification**:
40 - [Tests to confirm successful recovery]
41
42### Post-Incident
43
441. **Documentation**:
45 - Complete incident report
46 - Update knowledge base
47
482. **Preventive Measures**:
49 - Implement measures to prevent recurrence
50
513. **Process Improvements**:
52 - Identify and implement response process improvements
53
54### Communication Templates
55
56#### Internal Update
57
58\`\`\`
59INCIDENT UPDATE: [Brief description]
60Status: [Active/Resolved]
61Impact: [Services affected]
62Actions: [What we're doing]
63ETA: [Estimated resolution time]
64Next update: [Time of next update]
65\`\`\`
66
67#### External Communication
68
69\`\`\`
70We are currently experiencing [brief description of issue] affecting [service].
71Our team is actively working to resolve this issue.
72We estimate resolution by [time] and will provide updates every [interval].
73\`\`\`1global:
2 resolve_timeout: 5m
3 smtp_smarthost: 'smtp.example.com:587'
4 smtp_from: '[email protected]'
5 smtp_auth_username: '[email protected]'
6 smtp_auth_password: 'password'
7
8templates:
9 - '/etc/alertmanager/template/*.tmpl'
10
11route:
12 group_by: ['alertname', 'instance', 'severity']
13 group_wait: 30s
14 group_interval: 5m
15 repeat_interval: 12h
16 receiver: 'team-db-email'
17 routes:
18 - match:
19 severity: critical
20 receiver: 'team-db-pager'
21 repeat_interval: 1h
22 - match:
23 severity: warning
24 receiver: 'team-db-slack'
25 repeat_interval: 3h
26
27inhibit_rules:
28 - source_match:
29 severity: 'critical'
30 target_match:
31 severity: 'warning'
32 equal: ['alertname', 'instance']
33
34receivers:
35- name: 'team-db-email'
36 email_configs:
37 - to: '[email protected]'
38 send_resolved: true
39
40- name: 'team-db-pager'
41 pagerduty_configs:
42 - service_key: '<pagerduty-service-key>'
43 send_resolved: true
44
45- name: 'team-db-slack'
46 slack_configs:
47 - api_url: 'https://hooks.slack.com/services/XXXX/YYYY/ZZZZ'
48 channel: '#db-alerts'
49 send_resolved: true
50 title: "{{ .GroupLabels.alertname }}"
51 text: "{{ range .Alerts }}{{ .Annotations.description }}\n{{ end }}"Automation is key to managing enterprise database platforms efficiently. By implementing
infrastructure as code, CI/CD pipelines, and automated operations, you can reduce human error,
increase deployment velocity, and maintain consistency across environments.
Key Automation Components
Backup & Recovery Automation
Automating backup and recovery procedures ensures consistent execution, reduces human error,
and enables quick response to data loss scenarios.
Comprehensive Backup Strategy
An effective backup strategy combines multiple approaches:
Full backups:
Complete database backups scheduled weekly
Incremental backups:
WAL archiving or logical backups scheduled daily
Continuous archiving:
Real-time WAL shipping to standby servers
Snapshot backups:
Storage-level snapshots for quick recovery
Automated Backup Script
A comprehensive backup script that handles full and incremental backups:
Automated Recovery Testing
Regularly testing recovery procedures ensures they work when needed:
Infrastructure as Code
Infrastructure as Code (IaC) allows you to define your database infrastructure in a
declarative manner, enabling consistent, version-controlled, and repeatable deployments.
Terraform for PostgreSQL Infrastructure
Using Terraform to provision a high-availability PostgreSQL cluster on AWS:
AWS CloudFormation for RDS PostgreSQL
Using AWS CloudFormation to provision a managed PostgreSQL database with Amazon RDS:
Kubernetes Operator for PostgreSQL
Using the Kubernetes Zalando Postgres Operator for containerized PostgreSQL deployments:
CI/CD Pipeline for Database Changes
Implementing CI/CD pipelines for database changes enables safe, automated, and
version-controlled database deployments.
Database Migration Tools
Tools like Flyway, Liquibase, or Sqitch provide version control for database schema changes:
Simple migration-based tool with SQL scripts
Liquibase:
More structured with support for XML, YAML, JSON formats
Language-agnostic, VCS-aware change management
Flyway Migration Structure
Example Flyway migration directory structure:
Example Flyway migration script:
GitHub Actions CI/CD Pipeline for Database Changes
A complete CI/CD pipeline for database migrations using GitHub Actions:
Database CI/CD Best Practices
Version control all changes:
Keep all database changes in version control alongside
application code.
Automated testing:
Test migrations against a clone of production data to catch
issues early.
Backward compatibility:
Design schema changes to be backward compatible when possible
to support zero-downtime deployments.
Pre-deployment backups:
Always create backups before applying migrations to production.
Small, incremental changes:
Break large changes into smaller, safer migrations.
Idempotent scripts:
Design migration scripts to be safely re-runnable.
Separate schema and data migrations:
Handle schema changes and data modifications
in separate steps.
Rollback plans:
Prepare rollback scripts for each migration when possible.
1#!/bin/bash
2# Enterprise PostgreSQL Backup Script
3# This script performs automated backups with validation and off-site replication
4
5# Configuration
6DB_NAME="production_db"
7DB_USER="postgres"
8BACKUP_DIR="/var/lib/postgresql/backups"
9RETENTION_DAYS=14
10S3_BUCKET="s3://company-database-backups"
11SLACK_WEBHOOK="https://hooks.slack.com/services/XXX/YYY/ZZZ"
12
13# Timestamp for backup naming
14TIMESTAMP=$(date +%Y%m%d_%H%M%S)
15BACKUP_PATH="$BACKUP_DIR/$DB_NAME-$TIMESTAMP"
16LOG_FILE="/var/log/postgresql/backups/backup_$TIMESTAMP.log"
17
18# Ensure backup directory exists
19mkdir -p "$BACKUP_DIR"
20mkdir -p "$(dirname "$LOG_FILE")"
21
22# Function to send Slack notification
23send_slack_notification() {
24 local status=$1
25 local message=$2
26
27 if [ "$status" = "success" ]; then
28 color="#36a64f"
29 title="Backup Successful"
30 else
31 color="#ff0000"
32 title="Backup Failed"
33 fi
34
35 curl -s -X POST -H 'Content-type: application/json' --data "{
36 \\"attachments\\": [
37 {
38 \\"title\\": \\"$title\\",
39 \\"text\\": \\"$message\\",
40 \\"color\\": \\"$color\\",
41 \\"fields\\": [
42 {
43 \\"title\\": \\"Database\\",
44 \\"value\\": \\"$DB_NAME\\",
45 \\"short\\": true
46 },
47 {
48 \\"title\\": \\"Timestamp\\",
49 \\"value\\": \\"$TIMESTAMP\\",
50 \\"short\\": true
51 }
52 ]
53 }
54 ]
55 }" "$SLACK_WEBHOOK" > /dev/null
56}
57
58# Function to check if today is the day for full backup (Sunday)
59is_full_backup_day() {
60 local day_of_week=$(date +%u)
61 [ "$day_of_week" = "7" ] # 7 = Sunday
62}
63
64log() {
65 echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
66}
67
68# Start backup process
69log "Starting backup process for $DB_NAME"
70
71# Determine backup type
72if is_full_backup_day; then
73 BACKUP_TYPE="full"
74 log "Performing full backup"
75else
76 BACKUP_TYPE="incremental"
77 log "Performing incremental backup"
78fi
79
80# Execute the appropriate backup command
81if [ "$BACKUP_TYPE" = "full" ]; then
82 # Full backup using pg_dump
83 log "Running pg_dump for full backup..."
84 pg_dump -U "$DB_USER" -d "$DB_NAME" -F c -f "$BACKUP_PATH.full.dump"
85 BACKUP_RESULT=$?
86else
87 # Incremental backup using pg_basebackup with WAL streaming
88 log "Running incremental backup..."
89 pg_basebackup -U "$DB_USER" -D "$BACKUP_PATH.incremental" -P -X stream
90 BACKUP_RESULT=$?
91
92 # Archive the incremental backup
93 log "Archiving incremental backup..."
94 tar -czf "$BACKUP_PATH.incremental.tar.gz" -C "$(dirname "$BACKUP_PATH.incremental")" "$(basename "$BACKUP_PATH.incremental")"
95 rm -rf "$BACKUP_PATH.incremental"
96fi
97
98# Check backup result
99if [ $BACKUP_RESULT -ne 0 ]; then
100 log "ERROR: Backup failed with exit code $BACKUP_RESULT"
101 send_slack_notification "failure" "Backup of $DB_NAME failed with exit code $BACKUP_RESULT. See log at $LOG_FILE for details."
102 exit 1
103fi
104
105# Verify backup integrity
106if [ "$BACKUP_TYPE" = "full" ]; then
107 log "Verifying backup integrity..."
108 pg_restore --list "$BACKUP_PATH.full.dump" > /dev/null
109 VERIFY_RESULT=$?
110
111 if [ $VERIFY_RESULT -ne 0 ]; then
112 log "ERROR: Backup verification failed with exit code $VERIFY_RESULT"
113 send_slack_notification "failure" "Backup verification of $DB_NAME failed. Backup may be corrupted."
114 exit 1
115 fi
116fi
117
118# Upload to S3 (off-site storage)
119log "Uploading backup to S3..."
120if [ "$BACKUP_TYPE" = "full" ]; then
121 aws s3 cp "$BACKUP_PATH.full.dump" "$S3_BUCKET/$DB_NAME/$TIMESTAMP/"
122else
123 aws s3 cp "$BACKUP_PATH.incremental.tar.gz" "$S3_BUCKET/$DB_NAME/$TIMESTAMP/"
124fi
125
126S3_RESULT=$?
127if [ $S3_RESULT -ne 0 ]; then
128 log "WARNING: S3 upload failed with exit code $S3_RESULT"
129 send_slack_notification "failure" "Backup of $DB_NAME completed, but S3 upload failed."
130else
131 log "S3 upload successful"
132fi
133
134# Cleanup old backups
135log "Cleaning up old backups..."
136find "$BACKUP_DIR" -name "$DB_NAME-*.dump" -o -name "$DB_NAME-*.tar.gz" -mtime +$RETENTION_DAYS -delete
137
138# Update backup catalog
139log "Updating backup catalog..."
140echo "$TIMESTAMP,$BACKUP_TYPE,$BACKUP_PATH" >> "$BACKUP_DIR/backup_catalog.csv"
141
142# Send success notification
143log "Backup completed successfully"
144BACKUP_SIZE=$(du -h "$BACKUP_PATH.$BACKUP_TYPE.dump" 2>/dev/null | cut -f1 || du -h "$BACKUP_PATH.$BACKUP_TYPE.tar.gz" | cut -f1)
145send_slack_notification "success" "Backup of $DB_NAME completed successfully. Type: $BACKUP_TYPE, Size: $BACKUP_SIZE"
146
147exit 01#!/bin/bash
2# Automated Recovery Testing Script
3# Tests restore functionality on a dedicated test server
4
5# Configuration
6TEST_SERVER="pg-test-restore"
7DB_NAME="production_db"
8BACKUP_DIR="/var/lib/postgresql/backups"
9TEST_DB_NAME="\${DB_NAME}_restore_test"
10SLACK_WEBHOOK="https://hooks.slack.com/services/XXX/YYY/ZZZ"
11LOG_FILE="/var/log/postgresql/recovery_test_$(date +%Y%m%d_%H%M%S).log"
12
13log() {
14 echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
15}
16
17# Function to send Slack notification
18send_slack_notification() {
19 local status=$1
20 local message=$2
21
22 if [ "$status" = "success" ]; then
23 color="#36a64f"
24 title="Recovery Test Successful"
25 else
26 color="#ff0000"
27 title="Recovery Test Failed"
28 fi
29
30 curl -s -X POST -H 'Content-type: application/json' --data "{
31 \\"attachments\\": [
32 {
33 \\"title\\": \\"$title\\",
34 \\"text\\": \\"$message\\",
35 \\"color\\": \\"$color\\"
36 }
37 ]
38 }" "$SLACK_WEBHOOK" > /dev/null
39}
40
41# Start test process
42log "Starting automated recovery test"
43
44# Get the latest backup from the catalog
45LATEST_BACKUP=$(tail -n1 "$BACKUP_DIR/backup_catalog.csv")
46if [ -z "$LATEST_BACKUP" ]; then
47 log "ERROR: No backup found in catalog"
48 send_slack_notification "failure" "Recovery test failed. No backup found in catalog."
49 exit 1
50fi
51
52# Parse backup information
53BACKUP_TIMESTAMP=$(echo $LATEST_BACKUP | cut -d',' -f1)
54BACKUP_TYPE=$(echo $LATEST_BACKUP | cut -d',' -f2)
55BACKUP_PATH=$(echo $LATEST_BACKUP | cut -d',' -f3)
56
57log "Testing recovery of backup from $BACKUP_TIMESTAMP (Type: $BACKUP_TYPE)"
58
59# Drop test database if it exists
60log "Preparing test environment..."
61ssh $TEST_SERVER "PGPASSWORD=postgres psql -U postgres -c 'DROP DATABASE IF EXISTS $TEST_DB_NAME WITH (FORCE);'"
62
63# Create fresh test database
64ssh $TEST_SERVER "PGPASSWORD=postgres psql -U postgres -c 'CREATE DATABASE $TEST_DB_NAME;'"
65if [ $? -ne 0 ]; then
66 log "ERROR: Failed to create test database"
67 send_slack_notification "failure" "Recovery test failed. Could not create test database."
68 exit 1
69fi
70
71# Copy backup to test server
72log "Copying backup to test server..."
73scp "$BACKUP_PATH.$BACKUP_TYPE.dump" $TEST_SERVER:/tmp/ || scp "$BACKUP_PATH.$BACKUP_TYPE.tar.gz" $TEST_SERVER:/tmp/
74
75# Perform recovery
76log "Performing recovery on test server..."
77if [ "$BACKUP_TYPE" = "full" ]; then
78 ssh $TEST_SERVER "pg_restore -U postgres -d $TEST_DB_NAME /tmp/$(basename $BACKUP_PATH.$BACKUP_TYPE.dump)"
79 RESTORE_RESULT=$?
80else
81 # For incremental, we need to extract and use pg_basebackup restore approach
82 ssh $TEST_SERVER "
83 mkdir -p /tmp/restore_test
84 tar -xzf /tmp/$(basename $BACKUP_PATH.$BACKUP_TYPE.tar.gz) -C /tmp/restore_test
85 # In a real environment, you would stop PostgreSQL, replace data dir, and restart
86 # For testing, we'll just verify the backup can be extracted
87 ls -la /tmp/restore_test
88 "
89 RESTORE_RESULT=$?
90fi
91
92# Verify recovery success
93if [ $RESTORE_RESULT -ne 0 ]; then
94 log "ERROR: Recovery test failed with exit code $RESTORE_RESULT"
95 send_slack_notification "failure" "Recovery test failed with exit code $RESTORE_RESULT."
96 exit 1
97fi
98
99# For full backups, perform data validation
100if [ "$BACKUP_TYPE" = "full" ]; then
101 log "Validating recovered data..."
102
103 # Check row counts in key tables
104 TABLE_COUNTS=$(ssh $TEST_SERVER "psql -U postgres -d $TEST_DB_NAME -c \"
105 SELECT 'users' as table_name, COUNT(*) as row_count FROM users
106 UNION ALL
107 SELECT 'orders' as table_name, COUNT(*) as row_count FROM orders
108 UNION ALL
109 SELECT 'products' as table_name, COUNT(*) as row_count FROM products;
110 \" -t")
111
112 log "Table counts in restored database:"
113 log "$TABLE_COUNTS"
114
115 # Additional validation queries could be added here
116fi
117
118# Cleanup
119log "Cleaning up test resources..."
120ssh $TEST_SERVER "
121 PGPASSWORD=postgres psql -U postgres -c 'DROP DATABASE $TEST_DB_NAME;'
122 rm -f /tmp/$(basename $BACKUP_PATH.$BACKUP_TYPE.dump) /tmp/$(basename $BACKUP_PATH.$BACKUP_TYPE.tar.gz)
123 rm -rf /tmp/restore_test
124"
125
126# Send success notification
127log "Recovery test completed successfully"
128send_slack_notification "success" "Recovery test for $DB_NAME backup from $BACKUP_TIMESTAMP completed successfully."
129
130exit 01# main.tf - Terraform configuration for PostgreSQL HA cluster on AWS
2
3provider "aws" {
4 region = var.aws_region
5}
6
7# VPC for database deployment
8module "vpc" {
9 source = "terraform-aws-modules/vpc/aws"
10 version = "3.14.0"
11
12 name = "postgres-ha-vpc"
13 cidr = "10.0.0.0/16"
14
15 azs = ["\${var.aws_region}a", "\${var.aws_region}b", "\${var.aws_region}c"]
16 private_subnets = ["10.0.1.0/24", "10.0.2.0/24", "10.0.3.0/24"]
17 public_subnets = ["10.0.101.0/24", "10.0.102.0/24", "10.0.103.0/24"]
18
19 enable_nat_gateway = true
20 single_nat_gateway = false
21 one_nat_gateway_per_az = true
22
23 tags = {
24 Environment = var.environment
25 Project = "database-ha"
26 }
27}
28
29# Security group for PostgreSQL
30resource "aws_security_group" "postgres" {
31 name = "postgres-ha-sg"
32 description = "Security group for PostgreSQL HA cluster"
33 vpc_id = module.vpc.vpc_id
34
35 ingress {
36 from_port = 5432
37 to_port = 5432
38 protocol = "tcp"
39 cidr_blocks = [module.vpc.vpc_cidr_block]
40 description = "PostgreSQL access from within VPC"
41 }
42
43 ingress {
44 from_port = 8008
45 to_port = 8008
46 protocol = "tcp"
47 cidr_blocks = [module.vpc.vpc_cidr_block]
48 description = "Patroni API access"
49 }
50
51 ingress {
52 from_port = 22
53 to_port = 22
54 protocol = "tcp"
55 cidr_blocks = var.admin_cidr_blocks
56 description = "SSH access from allowed IPs"
57 }
58
59 egress {
60 from_port = 0
61 to_port = 0
62 protocol = "-1"
63 cidr_blocks = ["0.0.0.0/0"]
64 description = "Allow all outbound traffic"
65 }
66
67 tags = {
68 Name = "postgres-ha-sg"
69 Environment = var.environment
70 }
71}
72
73# AWS EC2 Launch Template for PostgreSQL nodes
74resource "aws_launch_template" "postgres" {
75 name_prefix = "postgres-ha-"
76 image_id = var.ami_id
77 instance_type = var.instance_type
78 key_name = var.key_name
79
80 iam_instance_profile {
81 name = aws_iam_instance_profile.postgres.name
82 }
83
84 network_interfaces {
85 associate_public_ip_address = false
86 security_groups = [aws_security_group.postgres.id]
87 delete_on_termination = true
88 }
89
90 block_device_mappings {
91 device_name = "/dev/sda1"
92 ebs {
93 volume_size = 50
94 volume_type = "gp3"
95 delete_on_termination = true
96 encrypted = true
97 }
98 }
99
100 block_device_mappings {
101 device_name = "/dev/sdf"
102 ebs {
103 volume_size = var.postgres_data_volume_size
104 volume_type = "gp3"
105 delete_on_termination = false
106 encrypted = true
107 }
108 }
109
110 user_data = base64encode(templatefile("\${path.module}/scripts/init.sh", {
111 postgres_version = var.postgres_version
112 etcd_endpoints = join(",", aws_instance.etcd[*].private_ip)
113 pg_password = var.postgres_password
114 repl_password = var.replication_password
115 cluster_name = var.cluster_name
116 }))
117
118 tag_specifications {
119 resource_type = "instance"
120 tags = {
121 Name = "postgres-ha-node"
122 Environment = var.environment
123 Component = "database"
124 }
125 }
126
127 lifecycle {
128 create_before_destroy = true
129 }
130}
131
132# Auto Scaling Group for PostgreSQL cluster
133resource "aws_autoscaling_group" "postgres" {
134 name = "postgres-ha-asg"
135 min_size = var.postgres_node_count
136 max_size = var.postgres_node_count
137 desired_capacity = var.postgres_node_count
138 vpc_zone_identifier = module.vpc.private_subnets
139
140 launch_template {
141 id = aws_launch_template.postgres.id
142 version = "$Latest"
143 }
144
145 health_check_type = "EC2"
146 health_check_grace_period = 300
147 wait_for_capacity_timeout = "10m"
148
149 dynamic "tag" {
150 for_each = {
151 Name = "postgres-ha-node"
152 Environment = var.environment
153 Component = "database"
154 }
155 content {
156 key = tag.key
157 value = tag.value
158 propagate_at_launch = true
159 }
160 }
161
162 lifecycle {
163 create_before_destroy = true
164 }
165}
166
167# ETCD instances for Patroni cluster management
168resource "aws_instance" "etcd" {
169 count = 3
170 ami = var.ami_id
171 instance_type = "t3.small"
172 key_name = var.key_name
173 vpc_security_group_ids = [aws_security_group.etcd.id]
174 subnet_id = element(module.vpc.private_subnets, count.index)
175 iam_instance_profile = aws_iam_instance_profile.etcd.name
176
177 user_data = templatefile("\${path.module}/scripts/etcd-init.sh", {
178 etcd_version = "3.5.1"
179 cluster_name = "etcd-cluster"
180 node_name = "etcd-\${count.index}"
181 node_ip = "self.private_ip"
182 initial_cluster = join(",", [
183 for i in range(3) : "etcd-\${i}=http://\${aws_instance.etcd[i].private_ip}:2380"
184 ])
185 })
186
187 root_block_device {
188 volume_size = 20
189 volume_type = "gp3"
190 delete_on_termination = true
191 encrypted = true
192 }
193
194 tags = {
195 Name = "etcd-\${count.index}"
196 Environment = var.environment
197 Component = "etcd"
198 }
199}
200
201# Load Balancer for PostgreSQL read/write access
202resource "aws_lb" "postgres" {
203 name = "postgres-ha-lb"
204 internal = true
205 load_balancer_type = "network"
206 subnets = module.vpc.private_subnets
207
208 tags = {
209 Name = "postgres-ha-lb"
210 Environment = var.environment
211 }
212}
213
214# Target group for primary (write) connections
215resource "aws_lb_target_group" "postgres_primary" {
216 name = "postgres-primary-tg"
217 port = 5432
218 protocol = "TCP"
219 vpc_id = module.vpc.vpc_id
220
221 health_check {
222 port = 8008
223 protocol = "HTTP"
224 path = "/primary"
225 interval = 30
226 timeout = 5
227 healthy_threshold = 2
228 unhealthy_threshold = 2
229 }
230
231 tags = {
232 Name = "postgres-primary-tg"
233 Environment = var.environment
234 }
235}
236
237# Target group for replica (read) connections
238resource "aws_lb_target_group" "postgres_replicas" {
239 name = "postgres-replicas-tg"
240 port = 5432
241 protocol = "TCP"
242 vpc_id = module.vpc.vpc_id
243
244 health_check {
245 port = 8008
246 protocol = "HTTP"
247 path = "/replica"
248 interval = 30
249 timeout = 5
250 healthy_threshold = 2
251 unhealthy_threshold = 2
252 }
253
254 tags = {
255 Name = "postgres-replicas-tg"
256 Environment = var.environment
257 }
258}
259
260# Listener for primary (write) connections
261resource "aws_lb_listener" "postgres_primary" {
262 load_balancer_arn = aws_lb.postgres.arn
263 port = 5432
264 protocol = "TCP"
265
266 default_action {
267 type = "forward"
268 target_group_arn = aws_lb_target_group.postgres_primary.arn
269 }
270}
271
272# Listener for replica (read) connections
273resource "aws_lb_listener" "postgres_replicas" {
274 load_balancer_arn = aws_lb.postgres.arn
275 port = 5433
276 protocol = "TCP"
277
278 default_action {
279 type = "forward"
280 target_group_arn = aws_lb_target_group.postgres_replicas.arn
281 }
282}
283
284# SSM Parameter for PostgreSQL connection string
285resource "aws_ssm_parameter" "postgres_connection" {
286 name = "/\${var.environment}/database/postgres/connection_string"
287 description = "PostgreSQL connection string"
288 type = "SecureString"
289 value = "postgresql://postgres:\${var.postgres_password}@\${aws_lb.postgres.dns_name}:5432/postgres"
290
291 tags = {
292 Environment = var.environment
293 }
294}
295
296# Output values
297output "postgres_endpoint_primary" {
298 description = "PostgreSQL primary endpoint"
299 value = "\${aws_lb.postgres.dns_name}:5432"
300}
301
302output "postgres_endpoint_replicas" {
303 description = "PostgreSQL replicas endpoint"
304 value = "\${aws_lb.postgres.dns_name}:5433"
305}
306
307output "etcd_endpoints" {
308 description = "ETCD endpoints"
309 value = aws_instance.etcd[*].private_ip
310}1AWSTemplateFormatVersion: '2010-09-09'
2Description: 'PostgreSQL RDS Multi-AZ Deployment'
3
4Parameters:
5 Environment:
6 Description: Environment name
7 Type: String
8 Default: production
9 AllowedValues:
10 - development
11 - staging
12 - production
13
14 DBInstanceClass:
15 Description: Database instance type
16 Type: String
17 Default: db.t3.large
18 AllowedValues:
19 - db.t3.medium
20 - db.t3.large
21 - db.r5.large
22 - db.r5.xlarge
23 - db.r5.2xlarge
24
25 DBName:
26 Description: Database name
27 Type: String
28 Default: appdb
29 MinLength: 1
30 MaxLength: 64
31 AllowedPattern: '[a-zA-Z][a-zA-Z0-9_]*'
32
33 DBUsername:
34 Description: Database master username
35 Type: String
36 Default: postgres
37 MinLength: 1
38 MaxLength: 16
39 AllowedPattern: '[a-zA-Z][a-zA-Z0-9_]*'
40
41 DBPassword:
42 Description: Database master password
43 Type: String
44 NoEcho: true
45 MinLength: 8
46 MaxLength: 41
47 AllowedPattern: '[a-zA-Z0-9!@#$%^&*()_+=-]*'
48
49 DBAllocatedStorage:
50 Description: Database allocated storage in GB
51 Type: Number
52 Default: 100
53 MinValue: 20
54 MaxValue: 16384
55
56 DBBackupRetentionPeriod:
57 Description: Number of days to retain automated backups
58 Type: Number
59 Default: 7
60 MinValue: 0
61 MaxValue: 35
62
63 VpcId:
64 Description: ID of the VPC
65 Type: AWS::EC2::VPC::Id
66
67 DBSubnets:
68 Description: List of VPC subnet IDs for the DB subnet group
69 Type: List<AWS::EC2::Subnet::Id>
70
71 AllowedCIDR:
72 Description: CIDR block for database access
73 Type: String
74 Default: 10.0.0.0/16
75 AllowedPattern: '^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\\/([0-9]|[1-2][0-9]|3[0-2]))$'
76
77Resources:
78 DBSecurityGroup:
79 Type: AWS::EC2::SecurityGroup
80 Properties:
81 GroupDescription: Security group for PostgreSQL RDS
82 VpcId: !Ref VpcId
83 SecurityGroupIngress:
84 - IpProtocol: tcp
85 FromPort: 5432
86 ToPort: 5432
87 CidrIp: !Ref AllowedCIDR
88 Tags:
89 - Key: Name
90 Value: !Sub \${Environment}-postgres-sg
91 - Key: Environment
92 Value: !Ref Environment
93
94 DBSubnetGroup:
95 Type: AWS::RDS::DBSubnetGroup
96 Properties:
97 DBSubnetGroupDescription: Subnet group for PostgreSQL RDS
98 SubnetIds: !Ref DBSubnets
99 Tags:
100 - Key: Name
101 Value: !Sub \${Environment}-postgres-subnet-group
102 - Key: Environment
103 Value: !Ref Environment
104
105 DBParameterGroup:
106 Type: AWS::RDS::DBParameterGroup
107 Properties:
108 Description: Parameter group for PostgreSQL RDS
109 Family: postgres14
110 Parameters:
111 shared_buffers: '{DBInstanceClassMemory/32768}MB'
112 max_connections: '{DBInstanceClassMemory/9531392}'
113 effective_cache_size: '{DBInstanceClassMemory/16384}MB'
114 work_mem: '16MB'
115 maintenance_work_mem: '128MB'
116 random_page_cost: '1.1'
117 log_min_duration_statement: '1000'
118 log_connections: '1'
119 log_disconnections: '1'
120 log_lock_waits: '1'
121 log_temp_files: '0'
122 log_autovacuum_min_duration: '250'
123 autovacuum_max_workers: '5'
124 autovacuum_naptime: '20s'
125 autovacuum_vacuum_scale_factor: '0.05'
126 autovacuum_analyze_scale_factor: '0.025'
127 Tags:
128 - Key: Name
129 Value: !Sub \${Environment}-postgres-parameter-group
130 - Key: Environment
131 Value: !Ref Environment
132
133 DBClusterParameterGroup:
134 Type: AWS::RDS::DBClusterParameterGroup
135 Properties:
136 Description: Cluster parameter group for PostgreSQL RDS
137 Family: postgres14
138 Parameters:
139 track_commit_timestamp: '1'
140 shared_preload_libraries: 'pg_stat_statements,auto_explain'
141 auto_explain.log_min_duration: '5000'
142 Tags:
143 - Key: Name
144 Value: !Sub \${Environment}-postgres-cluster-parameter-group
145 - Key: Environment
146 Value: !Ref Environment
147
148 DBInstance:
149 Type: AWS::RDS::DBInstance
150 Properties:
151 DBInstanceIdentifier: !Sub \${Environment}-postgres
152 DBName: !Ref DBName
153 Engine: postgres
154 EngineVersion: '14.5'
155 DBInstanceClass: !Ref DBInstanceClass
156 AllocatedStorage: !Ref DBAllocatedStorage
157 StorageType: gp3
158 StorageEncrypted: true
159 MasterUsername: !Ref DBUsername
160 MasterUserPassword: !Ref DBPassword
161 DBParameterGroupName: !Ref DBParameterGroup
162 DBSubnetGroupName: !Ref DBSubnetGroup
163 VPCSecurityGroups:
164 - !Ref DBSecurityGroup
165 BackupRetentionPeriod: !Ref DBBackupRetentionPeriod
166 MultiAZ: true
167 AutoMinorVersionUpgrade: true
168 PubliclyAccessible: false
169 EnablePerformanceInsights: true
170 PerformanceInsightsRetentionPeriod: 7
171 MonitoringInterval: 60
172 EnableIAMDatabaseAuthentication: true
173 DeletionProtection: true
174 Tags:
175 - Key: Name
176 Value: !Sub \${Environment}-postgres
177 - Key: Environment
178 Value: !Ref Environment
179
180 DBReadReplica:
181 Type: AWS::RDS::DBInstance
182 Properties:
183 SourceDBInstanceIdentifier: !Ref DBInstance
184 DBInstanceIdentifier: !Sub \${Environment}-postgres-replica
185 DBInstanceClass: !Ref DBInstanceClass
186 Engine: postgres
187 PubliclyAccessible: false
188 VPCSecurityGroups:
189 - !Ref DBSecurityGroup
190 EnablePerformanceInsights: true
191 PerformanceInsightsRetentionPeriod: 7
192 MonitoringInterval: 60
193 Tags:
194 - Key: Name
195 Value: !Sub \${Environment}-postgres-replica
196 - Key: Environment
197 Value: !Ref Environment
198 - Key: Role
199 Value: read-replica
200
201 DatabaseAlarmCPUUtilization:
202 Type: AWS::CloudWatch::Alarm
203 Properties:
204 AlarmName: !Sub \${Environment}-postgres-high-cpu
205 AlarmDescription: Alarm if database CPU exceeds 80% for 5 minutes
206 MetricName: CPUUtilization
207 Namespace: AWS/RDS
208 Statistic: Average
209 Period: 300
210 EvaluationPeriods: 2
211 Threshold: 80
212 ComparisonOperator: GreaterThanThreshold
213 Dimensions:
214 - Name: DBInstanceIdentifier
215 Value: !Ref DBInstance
216 TreatMissingData: notBreaching
217
218 DatabaseAlarmFreeStorageSpace:
219 Type: AWS::CloudWatch::Alarm
220 Properties:
221 AlarmName: !Sub \${Environment}-postgres-low-storage
222 AlarmDescription: Alarm if free storage space drops below 10% of allocated storage
223 MetricName: FreeStorageSpace
224 Namespace: AWS/RDS
225 Statistic: Average
226 Period: 300
227 EvaluationPeriods: 2
228 Threshold: !Mul [ 0.1, !Mul [ !Ref DBAllocatedStorage, 1073741824 ] ]
229 ComparisonOperator: LessThanThreshold
230 Dimensions:
231 - Name: DBInstanceIdentifier
232 Value: !Ref DBInstance
233 TreatMissingData: breaching
234
235Outputs:
236 DBEndpoint:
237 Description: Connection endpoint for the PostgreSQL instance
238 Value: !GetAtt DBInstance.Endpoint.Address
239
240 DBReadEndpoint:
241 Description: Connection endpoint for the PostgreSQL read replica
242 Value: !GetAtt DBReadReplica.Endpoint.Address
243
244 DBPort:
245 Description: The port the database is listening on
246 Value: !GetAtt DBInstance.Endpoint.Port
247
248 DBName:
249 Description: Database name
250 Value: !Ref DBName
251
252 DBSecurityGroupId:
253 Description: ID of the database security group
254 Value: !Ref DBSecurityGroup1apiVersion: "acid.zalan.do/v1"
2kind: postgresql
3metadata:
4 name: postgres-cluster
5 namespace: database
6spec:
7 teamId: "platform"
8 volume:
9 size: 100Gi
10 storageClass: "ebs-gp3"
11 numberOfInstances: 3
12 users:
13 app_user: # application user
14 - superuser
15 - createdb
16 monitoring_user: # monitoring user
17 - pg_monitor
18 databases:
19 app_db: app_user # dbname: owner
20 postgresql:
21 version: "14"
22 parameters:
23 shared_buffers: "2GB"
24 max_connections: "200"
25 work_mem: "16MB"
26 max_worker_processes: "8"
27 max_parallel_workers_per_gather: "4"
28 max_parallel_workers: "8"
29 random_page_cost: "1.1"
30 effective_cache_size: "8GB"
31 log_min_duration_statement: "1000"
32 log_connections: "on"
33 log_disconnections: "on"
34 log_lock_waits: "on"
35 log_temp_files: "0"
36 log_autovacuum_min_duration: "250"
37 log_statement: "ddl"
38 patroni:
39 initdb:
40 encoding: "UTF8"
41 locale: "en_US.UTF-8"
42 data-checksums: "true"
43 pg_hba:
44 - host all all 0.0.0.0/0 md5
45 - host all app_user 10.0.0.0/16 md5
46 - host replication replication 10.0.0.0/16 md5
47 ttl: 30
48 loop_wait: 10
49 retry_timeout: 10
50 maximum_lag_on_failover: 1048576
51 resources:
52 requests:
53 cpu: 500m
54 memory: 2Gi
55 limits:
56 cpu: 2
57 memory: 4Gi
58 podAnnotations:
59 prometheus.io/scrape: "true"
60 prometheus.io/port: "9187"
61 sidecars:
62 - name: exporter
63 image: wrouesnel/postgres_exporter:v0.8.0
64 resources:
65 limits:
66 cpu: 100m
67 memory: 100Mi
68 requests:
69 cpu: 100m
70 memory: 100Mi
71 env:
72 - name: DATA_SOURCE_NAME
73 value: "postgresql://postgres:$(POSTGRES_PASSWORD)@localhost:5432/postgres?sslmode=disable"
74 podPriorityClassName: "high-priority"
75 enableMasterLoadBalancer: true
76 enableReplicaLoadBalancer: true
77 enableConnectionPooler: true
78 connectionPooler:
79 numberOfInstances: 2
80 mode: transaction
81 maxDBConnections: 100
82 resources:
83 requests:
84 cpu: 100m
85 memory: 100Mi
86 limits:
87 cpu: 500m
88 memory: 500Mi
89 maintenanceWindows:
90 - 01:00-06:00#Tuesday
91 - 01:00-06:00#Saturday1migrations/
2├── V1.0.0__initial_schema.sql
3├── V1.0.1__add_users_table.sql
4├── V1.1.0__add_orders_schema.sql
5├── V1.1.1__add_foreign_keys.sql
6└── R__views.sql # Repeatable migration1-- V1.1.0__add_orders_schema.sql
2CREATE SCHEMA IF NOT EXISTS orders;
3
4CREATE TABLE orders.orders (
5 id SERIAL PRIMARY KEY,
6 user_id INTEGER NOT NULL,
7 status VARCHAR(20) NOT NULL DEFAULT 'pending',
8 total_amount DECIMAL(10,2) NOT NULL,
9 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
10 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
11 CONSTRAINT status_values CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
12 CONSTRAINT total_amount_positive CHECK (total_amount > 0)
13);
14
15CREATE TABLE orders.order_items (
16 id SERIAL PRIMARY KEY,
17 order_id INTEGER NOT NULL,
18 product_id INTEGER NOT NULL,
19 quantity INTEGER NOT NULL,
20 unit_price DECIMAL(10,2) NOT NULL,
21 CONSTRAINT quantity_positive CHECK (quantity > 0),
22 CONSTRAINT unit_price_positive CHECK (unit_price > 0)
23);
24
25-- Create indexes for better performance
26CREATE INDEX idx_orders_user_id ON orders.orders(user_id);
27CREATE INDEX idx_orders_status ON orders.orders(status);
28CREATE INDEX idx_order_items_order_id ON orders.order_items(order_id);
29CREATE INDEX idx_order_items_product_id ON orders.order_items(product_id);
30
31-- Add audit triggers
32CREATE TRIGGER set_orders_updated_at
33BEFORE UPDATE ON orders.orders
34FOR EACH ROW
35EXECUTE FUNCTION public.set_updated_at();
36
37COMMENT ON TABLE orders.orders IS 'Stores customer orders';
38COMMENT ON TABLE orders.order_items IS 'Stores line items for each order';1# .github/workflows/database-migrations.yml
2name: Database Migrations CI/CD
3
4on:
5 push:
6 branches: [ main ]
7 paths:
8 - 'migrations/**'
9 pull_request:
10 branches: [ main ]
11 paths:
12 - 'migrations/**'
13
14env:
15 FLYWAY_VERSION: 8.5.7
16
17jobs:
18 validate:
19 name: Validate Migrations
20 runs-on: ubuntu-latest
21 steps:
22 - name: Checkout code
23 uses: actions/checkout@v3
24
25 - name: Set up Java
26 uses: actions/setup-java@v3
27 with:
28 distribution: 'temurin'
29 java-version: '17'
30
31 - name: Set up Flyway
32 run: |
33 wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/\${{ env.FLYWAY_VERSION }}/flyway-commandline-\${{ env.FLYWAY_VERSION }}-linux-x64.tar.gz | tar xvz
34 sudo ln -s $(pwd)/flyway-\${{ env.FLYWAY_VERSION }}/flyway /usr/local/bin/flyway
35
36 - name: Set up PostgreSQL
37 run: |
38 docker run -d --name postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -p 5432:5432 postgres:14
39 sleep 5 # Wait for PostgreSQL to start
40
41 - name: Validate SQL syntax
42 run: |
43 cd migrations
44 for file in *.sql; do
45 echo "Validating $file"
46 PGPASSWORD=postgres psql -h localhost -U postgres -d postgres -f "$file" -v ON_ERROR_STOP=1 --no-psqlrc -c ""
47 done
48
49 - name: Lint SQL files
50 run: |
51 pip install sqlfluff
52 sqlfluff lint migrations/ --dialect postgres
53
54 - name: Validate Flyway migrations
55 run: |
56 flyway -url=jdbc:postgresql://localhost:5432/postgres -user=postgres -password=postgres validate
57
58 test:
59 name: Test Migrations
60 needs: validate
61 runs-on: ubuntu-latest
62 steps:
63 - name: Checkout code
64 uses: actions/checkout@v3
65
66 - name: Set up Java
67 uses: actions/setup-java@v3
68 with:
69 distribution: 'temurin'
70 java-version: '17'
71
72 - name: Set up Flyway
73 run: |
74 wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/\${{ env.FLYWAY_VERSION }}/flyway-commandline-\${{ env.FLYWAY_VERSION }}-linux-x64.tar.gz | tar xvz
75 sudo ln -s $(pwd)/flyway-\${{ env.FLYWAY_VERSION }}/flyway /usr/local/bin/flyway
76
77 - name: Set up PostgreSQL
78 run: |
79 docker run -d --name postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -p 5432:5432 postgres:14
80 sleep 5 # Wait for PostgreSQL to start
81
82 - name: Run migrations
83 run: |
84 flyway -url=jdbc:postgresql://localhost:5432/postgres -user=postgres -password=postgres migrate
85
86 - name: Run database tests
87 run: |
88 cd tests
89 python -m pytest test_migrations.py -v
90
91 deploy-staging:
92 name: Deploy to Staging
93 needs: test
94 if: github.event_name == 'push' && github.ref == 'refs/heads/main'
95 runs-on: ubuntu-latest
96 environment: staging
97 steps:
98 - name: Checkout code
99 uses: actions/checkout@v3
100
101 - name: Set up Java
102 uses: actions/setup-java@v3
103 with:
104 distribution: 'temurin'
105 java-version: '17'
106
107 - name: Set up Flyway
108 run: |
109 wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/\${{ env.FLYWAY_VERSION }}/flyway-commandline-\${{ env.FLYWAY_VERSION }}-linux-x64.tar.gz | tar xvz
110 sudo ln -s $(pwd)/flyway-\${{ env.FLYWAY_VERSION }}/flyway /usr/local/bin/flyway
111
112 - name: Run info to check pending migrations
113 run: |
114 flyway -url=jdbc:postgresql://\${{ secrets.STAGING_DB_HOST }}:5432/\${{ secrets.STAGING_DB_NAME }} \
115 -user=\${{ secrets.STAGING_DB_USER }} \
116 -password=\${{ secrets.STAGING_DB_PASSWORD }} \
117 info
118
119 - name: Run migrations on staging
120 run: |
121 flyway -url=jdbc:postgresql://\${{ secrets.STAGING_DB_HOST }}:5432/\${{ secrets.STAGING_DB_NAME }} \
122 -user=\${{ secrets.STAGING_DB_USER }} \
123 -password=\${{ secrets.STAGING_DB_PASSWORD }} \
124 migrate
125
126 - name: Verify migrations
127 run: |
128 flyway -url=jdbc:postgresql://\${{ secrets.STAGING_DB_HOST }}:5432/\${{ secrets.STAGING_DB_NAME }} \
129 -user=\${{ secrets.STAGING_DB_USER }} \
130 -password=\${{ secrets.STAGING_DB_PASSWORD }} \
131 validate
132
133 - name: Notify staging deployment
134 uses: slackapi/slack-github-action@v1
135 with:
136 payload: |
137 {
138 "text": "Database migrations deployed to staging successfully! :rocket:",
139 "blocks": [
140 {
141 "type": "header",
142 "text": {
143 "type": "plain_text",
144 "text": "Database Migrations Deployed to Staging",
145 "emoji": true
146 }
147 },
148 {
149 "type": "section",
150 "text": {
151 "type": "mrkdwn",
152 "text": "*Commit:* \${{ github.sha }}\n*Author:* \${{ github.actor }}"
153 }
154 }
155 ]
156 }
157 env:
158 SLACK_WEBHOOK_URL: \${{ secrets.SLACK_WEBHOOK_URL }}
159
160 deploy-production:
161 name: Deploy to Production
162 needs: deploy-staging
163 runs-on: ubuntu-latest
164 environment:
165 name: production
166 url: https://github.com/\${{ github.repository }}/actions/runs/\${{ github.run_id }}
167 steps:
168 - name: Checkout code
169 uses: actions/checkout@v3
170
171 - name: Set up Java
172 uses: actions/setup-java@v3
173 with:
174 distribution: 'temurin'
175 java-version: '17'
176
177 - name: Set up Flyway
178 run: |
179 wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/\${{ env.FLYWAY_VERSION }}/flyway-commandline-\${{ env.FLYWAY_VERSION }}-linux-x64.tar.gz | tar xvz
180 sudo ln -s $(pwd)/flyway-\${{ env.FLYWAY_VERSION }}/flyway /usr/local/bin/flyway
181
182 - name: Run info to check pending migrations
183 run: |
184 flyway -url=jdbc:postgresql://\${{ secrets.PROD_DB_HOST }}:5432/\${{ secrets.PROD_DB_NAME }} \
185 -user=\${{ secrets.PROD_DB_USER }} \
186 -password=\${{ secrets.PROD_DB_PASSWORD }} \
187 info
188
189 - name: Create backup before migrations
190 run: |
191 PGPASSWORD=\${{ secrets.PROD_DB_PASSWORD }} pg_dump \
192 -h \${{ secrets.PROD_DB_HOST }} \
193 -U \${{ secrets.PROD_DB_USER }} \
194 -d \${{ secrets.PROD_DB_NAME }} \
195 -F c -v -f pre_migration_backup_$(date +%Y%m%d_%H%M%S).dump
196
197 - name: Run migrations on production
198 run: |
199 flyway -url=jdbc:postgresql://\${{ secrets.PROD_DB_HOST }}:5432/\${{ secrets.PROD_DB_NAME }} \
200 -user=\${{ secrets.PROD_DB_USER }} \
201 -password=\${{ secrets.PROD_DB_PASSWORD }} \
202 migrate
203
204 - name: Verify migrations
205 run: |
206 flyway -url=jdbc:postgresql://\${{ secrets.PROD_DB_HOST }}:5432/\${{ secrets.PROD_DB_NAME }} \
207 -user=\${{ secrets.PROD_DB_USER }} \
208 -password=\${{ secrets.PROD_DB_PASSWORD }} \
209 validate
210
211 - name: Upload backup artifact
212 uses: actions/upload-artifact@v3
213 with:
214 name: pre-migration-backup
215 path: pre_migration_backup_*.dump
216 retention-days: 7
217
218 - name: Notify production deployment
219 uses: slackapi/slack-github-action@v1
220 with:
221 payload: |
222 {
223 "text": "Database migrations deployed to production successfully! :tada:",
224 "blocks": [
225 {
226 "type": "header",
227 "text": {
228 "type": "plain_text",
229 "text": "Database Migrations Deployed to Production",
230 "emoji": true
231 }
232 },
233 {
234 "type": "section",
235 "text": {
236 "type": "mrkdwn",
237 "text": "*Commit:* \${{ github.sha }}\n*Author:* \${{ github.actor }}\n*Workflow:* <\${{ github.server_url }}/\${{ github.repository }}/actions/runs/\${{ github.run_id }}|View Workflow Run>"
238 }
239 }
240 ]
241 }
242 env:
243 SLACK_WEBHOOK_URL: \${{ secrets.SLACK_WEBHOOK_URL }}To create a complete enterprise database platform, you'll need to integrate all the components
discussed in this lesson. Here's a high-level architecture diagram of the complete solution:
ENTERPRISE DATABASE PLATFORM ┌───────────────────────────────────────────────────────────────────────────┐ │ │ │ ┌─────────────────────────────┐ ┌─────────────────────────────┐ │ │ │ REGION A (PRIMARY) │ │ REGION B (DR) │ │ │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ │ │ ┌─────────┐ ┌─────────┐ │ │ │ │ │ Primary │◄──┤ Replica │ │ │ │ Primary │◄──┤ Replica │ │ │ │ │ │ Node │ │ Node │ │ │ │ Node │ │ Node │ │ │ │ │ └────┬────┘ └────┬────┘ │ │ └────┬────┘ └────┬────┘ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ┌────▼─────────────▼────┐ │ │ ┌────▼─────────────▼────┐ │ │ │ │ │ Load Balancer │ │ │ │ Load Balancer │ │ │ │ │ └─────────────┬────────┘ │ │ └─────────────┬────────┘ │ │ │ │ │ │ │ │ │ │ │ └────────────────┼───────────┘ └────────────────┼───────────┘ │ │ │ │ │ │ ┌────────────────┼────────────────────────────────────┼───────────┐ │ │ │ │ Global Router │ │ │ │ └────────────────┼────────────────────────────────────┼───────────┘ │ │ │ │ │ │ ┌────────────────┼───────────┐ ┌────────────────┼───────────┐ │ │ │ │ │ │ │ │ │ │ │ ┌───────────▼────────┐ │ │ ┌───────────▼────────┐ │ │ │ │ │ Connection Pool │ │ │ │ Connection Pool │ │ │ │ │ └───────────┬────────┘ │ │ └───────────┬────────┘ │ │ │ │ │ │ │ │ │ │ │ │ ┌───────────▼────────┐ │ │ ┌───────────▼────────┐ │ │ │ │ │ Application │ │ │ │ Application │ │ │ │ │ │ Servers │ │ │ │ Servers │ │ │ │ │ └────────────────────┘ │ │ └────────────────────┘ │ │ │ │ │ │ │ │ │ └─────────────────────────────┘ └─────────────────────────────┘ │ │ │ └───────────────────────────────────────────────────────────────────────────┘ ┌───────────────────────────────────────────────────────────────────────────┐ │ │ │ ┌─────────────────────────────┐ ┌─────────────────────────────┐ │ │ │ Monitoring & Alerting │ │ Automation & CI/CD │ │ │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ │ │ ┌─────────┐ ┌─────────┐ │ │ │ │ │Prometheus│ │ Grafana │ │ │ │ GitHub │ │ Terraform│ │ │ │ │ └─────────┘ └─────────┘ │ │ │ Actions │ │ │ │ │ │ │ │ │ └─────────┘ └─────────┘ │ │ │ │ ┌─────────┐ ┌─────────┐ │ │ │ │ │ │ │ Alert │ │ PgBadger │ │ │ ┌─────────┐ ┌─────────┐ │ │ │ │ │ Manager │ │ │ │ │ │ Flyway │ │ Backup │ │ │ │ │ └─────────┘ └─────────┘ │ │ │ Migrations│ │ Scripts │ │ │ │ │ │ │ └─────────┘ └─────────┘ │ │ │ └─────────────────────────────┘ └─────────────────────────────┘ │ │ │ └───────────────────────────────────────────────────────────────────────────┘
To implement this enterprise database platform, follow these steps in order:
Infrastructure Setup:
Deploy network infrastructure using Terraform or CloudFormation
Set up containerized PostgreSQL instances with proper security
Configure connection pooling and load balancing
High Availability Configuration:
Set up Patroni cluster for automated failover
Configure replication between nodes
Implement and test failover procedures
Monitoring & Alerting:
Deploy Prometheus and Grafana
Configure PostgreSQL exporters for metrics collection
Set up alerts for critical database conditions
Create dashboards for performance monitoring
Backup & Recovery:
Implement automated backup scripts
Set up off-site backup storage
Create and test recovery procedures
Schedule regular recovery testing
CI/CD Pipeline:
Set up database migration framework
Configure CI/CD pipeline for database changes
Implement testing and validation steps
Create deployment process with safeguards
Disaster Recovery:
Set up cross-region replication
Configure DR testing procedures
Document DR runbooks and processes
Schedule regular DR testing
Documentation & Training:
Create comprehensive documentation
Develop operation runbooks
Train team members on platform operations
Establish on-call procedures
Now it's your turn to put these concepts into practice. In the editor page for this lesson,
you'll implement key components of the enterprise database platform, including:
Infrastructure as code for a multi-region PostgreSQL deployment
High-availability configuration using Patroni
Monitoring setup with Prometheus and Grafana
Automated backup and recovery scripts
CI/CD pipeline for database changes
This hands-on project will reinforce the concepts covered in this lesson and provide you with
practical experience in building enterprise-grade database platforms.
Your progress is saved in this browser, on this device only.