Course PostgreSQL Database

1. PostgreSQL Server Administration

• 3 days

Introduction and Architecture

• Introduction to Database Management System
• Architecture overview
  • processes
  • memory
  • storage layout
• Data management with PostgreSQL
  • databases
  • schemas
  • tablespaces
• PostgreSQL toolset and system catalogues
  • Command-line tools
    • psql
    • pg_database
    • pg_dump
    • pg_restore
    • pg_upgrade
    • vacuumdb / analyze
    • pgbench
  • User Interface tools
    • PgAdmin

Installation and Configuration

• Installing PostgreSQL
  • Standalone
  • Master-slave
  • Cluster with HA (High Availability)
• Configuration files
  • postgresql.conf
  • pg_hba.conf
  • pg_ident.conf
• Adjusting core parameters
  • memory
  • logging
  • connections
• Upgrading and migrating PostgreSQL installations
• Essential configuration and tuning
  • Max connection
  • Shared buffers
  • Max memory
  • Effective cache size
  • Checkpoint timeout
  • WAL buffers

Security Management

• User roles, groups, and privileges
• Authentication methods: trust, password, MD5, SCRAM, SSL
• Row-Level Security and data access policies

Backup and Recovery

• Logical backups with pg_dump and pg_restore
• Physical backups using pg_basebackup
• Incremental backups and WAL archiving
• Point-in-Time Recovery (PITR) and restoration procedures

Monitoring and Maintenance

• Understanding the autovacuum process
• Routine maintenance and log analysis
• Using PgBadger for performance and error tracking
• Monitoring system
  • Slow query monitoring
  • Working with Prometheus and Grafana

2. PostgreSQL Replication and High Availability

• 2 days

PostgreSQL Replication

• Overview of PostgreSQL Replication
• High Availability in PostgreSQL
• Streaming Replication
  • Sync or Async
  • Cascaded Replication
  • Transaction Log Archiving
  • Base Backups
  • Setup Streaming
  • Monitoring Streaming
  • Handling Conflicts
• Managing Clusters Using repmgr
• Running Queries on Database in Hot Standby
• Synchronous Replication
• File based Log Shipping
• Trigger-based Logical Replication with Slony & Londiste
• Connection Pooling
• Upgrading PostgreSQL
• Sharding and Scaling PostgreSQL

High Availability Setup using pgpool-II

• Architecture of pgpool-II
• Connection Pooling and HA setup using pgpool-II
• Installation
• Configuration
• Pool Setup
• HA Setup
• Monitoring
• Performance testing and tuning