Course PostgreSQL Database - up1/training-courses GitHub Wiki

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