Course Objectives

  • Understand the PostgreSQL architecture
  • Installing PostgreSQL
  • Create databases
  • Get used to the command line interface: psql
  • Understand backup and restore procedures in PostgreSQL
  • Understand extensions in PostgreSQL
  • Create a hot standby database
  • Understand the optimizer, statistics and vacuum
  • Understand monitoring tools
  • Upgrades in PostgreSQL

Training Method

  • Recording of Presentation with numerous exercises, case studies, and live demos
  • Discussions forums and ask a question.
  • Hands on Lab assignments
  • Handouts for reference
  • Doubt clearing sessions by sharing screen one on one.

Target Audience

  • Database administrators (DBA) as well as network, system and application administrators responsible for or supporting PostgreSQL databases

PostgreSQL Course Topics

  1. PostgreSQL Introduction and Features

  • Introduction and History PostgreSQL
  • PostgreSQL Features
    • ACID compliant
    • MVCC
    • Write ahead logging
    • Point in time recovery
    • Standby server and high availability
    • Streaming replication
    • Procedural languages
    • Partitioning
    • Cost based optimizer
    • Multi platform support
    • Tablespaces
    • Triggers
    • Views
    • Constraint enforcement
    • Extension system

  1. PostgreSQL Architecture

  • Understanding the Architecture
  • PostgreSQL Architecture
  • Shared Memory
  • Shared Buffer
  • WAL Buffer
  • PostgreSQL Process Types
  • Postmaster Process
  • Backend Process
  • Client Process
  • Database Structure

 

  1. Installing PostgreSQL

  • Installing PostgreSQL using RPMs on CentOS
  • Initializing a PostgreSQL cluster using initdb
  • Starting a PostgreSQL cluster using pg_ctl
  • The core binaries
    • postgres
    • pg_ctl
    • initdb
    • psql
    • pg_dump
    • pg_restore
    • pg_controldata
    • pg_resetxlog
  • Wrappers and contributed modules
    • The create and drop utilities
    • clusterdb
    • reindexdb
    • vacuumdb
    • vacuumlo

  1. Getting Started with PostgreSQL

  • Running Tests
  • Clusters in PostgreSQL
  • Databases in PostgreSQL
  • Create User Database
  • Shutting down a PostgreSQL cluster using different shutdown modes
  • Identifying a PostgreSQL data directory and its contents
  • Moving pg_wal to another location
  • Running the psql client and some psql shortcuts
  • Running a SQL server using psql
  • Getting a list of databases
  • Finding the database's size
  • Connecting to a database
  • Getting the list of schemas in a database
  • Getting the list of tables
  • Describing a table
  • SQLs behind the shortcuts
  • Locating the Postgres configuration file
  • Modifying the location of a postgresql.conf file in PostgreSQL
  • Modifying the postgresql.auto.conf file in PostgreSQL
  • Enable archiving in PostgreSQL

  1. The logical layout of PostgreSQL

  • The connection .
  • Databases .
  • Tables .
    • Logged tables .
    • Unlogged tables .
    • Temporary tables .
    • Foreign tables .
  • Table inheritance
  • Indices
    • b-tree
    • hash
    • GiST
    • GIN
  • Views
  • Tablespaces
  • Transactions
    • Snapshot exports

  1. The Physical Layout

  • Data files .
    • Free space map .
    • Visibility map .
    • Initialisation fork .
    • pg_class
  • Pages
  • Tuples
  • TOAST
  • Tablespaces
  • MVCC

  1. PostgreSQL Cluster Management

  • Creating and dropping databases
  • Locating a database and a table on the file system
  • Creating a schema in PostgreSQL
  • Checking table and index sizes in PostgreSQL
  • Create User Tablespace
  • How to Change Tablespace Location
  • What is Vacuum
  • Creating a user in PostgreSQL
  • Dropping a user in PostgreSQL
  • Assigning and revoking a privilege to/from a user or a role
  • Creating a group role for role-based segregation
  • MVCC implementation in PostgreSQL

  1. PostgreSQL Data Integrity

  • Primary keys
  • Unique keys
  • Foreign keys
  • Check constraints
  • Not null

  1. Backup and Recovery in PostgreSQL

  • Backing up and restoring a database using pg_dump and pg_restore
  • The RPM package for CentOS/Red Hat distributions
  • Backing up and restoring one or more tables using pg_dump and pg_restore
  • Backing up and restoring globals or an entire cluster using pg_dumpall and psql
  • Parallel backup and restore using pg_dump and pg_restore
  • Backing up a database cluster using pg_basebackup
  • Restoring a backup taken using pg basebackup
  • Installing pgBackRest on CentOS/RedHat OS
  • Backing up a database cluster using pgBackRest
  • Performance tips
    • Avoid remote backups
    • Skip replicated tables
    • Check for slow cpu cores
    • Check for the available locks
  • Backup validation
  • Restoring a backup taken using pgBackRest
    • The plain format and binary format
  • Restore performances
    • shared buffers
    • wal level
    • fsync
    • checkpoint segments, checkpoint timeout
    • autovacuum
    • max connections
    • port and listen addresses
    • maintenance work memory

  1. Advanced Replication Techniques in PostgreSQL

  • Setting up streaming replication in PostgreSQL
  • Adding a delayed standby for faster point-in-time recovery
  • Promoting a standby to a master
  • Adding a cascaded streaming replica
  • Promoting a standby in a replication cluster with multiple standby servers
  • Using pg_rewind to re-synchronize a demoted master
  • Enabling synchronous streaming replication
  • Setting up logical replication in PostgreSQL

  1. High Availability and Automatic Failover

  • Automatic failover using Patroni
  • Enabling distributed consensus using etcd
  • Avoiding split-brain using Watchdog/softdog
  • Installing Patroni along with its Python dependencies
  • Creating a Patroni configuration file
  • Starting Patroni as a service using systemd
  • Initializing a PostgreSQL primary database using Patroni
  • Adding a standby to a Patroni cluster
  • Performing a manual switchover using Patroni

  1. Connection Pooling and Load Balancing

  • Installing pgBouncer on a Linux server
  • Creating a pgBouncer configuration file
  • Configuring the pool settings on pgBouncer
  • Starting and stopping the pgBouncer service
  • Installing HAProxy on Linux servers
  • Using xinetd to detect a primary or a standby
  • Creating an HAProxy configuration file
  • Starting and stopping the HAProxy service
  • Building a robust HA cluster using Patroni, pgBouncer, and HAProxy

  1. Securing Through Authentication

  • Securing client connections using the pg_hba.conf file
  • Categories in the pg_hba.conf file
  • Performing authorization using roles and privileges
  • Setting up row-level security
  • Configuring encryption of data over the wire using SSL
  • Enabling certificate authentication using SSL
  • Auditing PostgreSQL through logging
  • Auditing PostgreSQL using pgaudit
  • Setting up object-level auditing using pgaudit

  1. Logging and Analyzing PostgreSQL Servers

  • Setting up slow query logging in PostgreSQL
  • Logging runtime execution plans in PostgreSQL using auto_explain
  • Global level
  • Session level
  • Logging locks, waits, and temp in PostgreSQL
  • Logging autovacuum and analyzing activity in PostgreSQL
  • Generating a pgBadger report
  • Configuring pg_stat_statements as an extension
  • Query analysis using pg_stat_statements
  • Getting the kernel-level statistics of a query using pg_stat_kcache

  1. PostgreSQL Services Monitoring

  • Installation of Grafana and its dependencies
  • Prometheus as a data source on the monitoring server
  • Configuring Node Exporter on Postgres servers to monitor
  • operating system metrics
  • Adding metrics being collected using node_exporter to Prometheus
  • Collecting PostgreSQL metrics using postgres_exporter
  • Adding metrics exposed by postgres_exporter to Prometheus
  • Importing a dashboard for monitoring Linux metrics
  • How to import a dashboard for monitoring Postgres metrics
  • Adding custom queries to postgres_exporter

  1. PostgreSQL Performance Tuning

  • Installing and creating pg_repack to rebuild objects online
  • Installing pg_repack on CentOS
  • Installing pg_repack on Ubuntu
  • How to rebuild a table online using pg_repack
  • How to rebuild indexes of a table online using pg_repack
  • Rebuilding all the indexes of a table
  • Rebuilding a specific index
  • Moving a table or an index to another tablespace online
  • Warming up the cache using pg_prewarm
  • How to tune a function or a stored procedure using plprofiler
  • Capturing statements that require tuning using pg_stat_statements
  • Viewing the execution plans using EXPLAIN in PostgreSQL

  1. PostgreSQL Upgrades and Patches

  • Finding the difference between a major and minor release in PostgreSQL
  • What is an obsolete version?
  • Technical requirements
  • Major version upgrade to PostgreSQL using pg_dumpall
  • Major version upgrade to PostgreSQL using pg_dump and pg_restore
  • Major version upgrade to PostgreSQL using pg_upgrade with downtime
  • Major version upgrade to PostgreSQL using pg_upgrade with
  • hard links for seamless downtime
  • Installing the pglogical extension to upgrade older versions to PostgreSQL
  • Upgrading to PostgreSQL using the pglogical extension
  • Upgrading to PostgreSQL using logical replication and logical decoding
  • Updating the minor version of PostgreSQL

  1. PostgreSQL Maintenance

  • VACUUM
    • vacuum freeze table age
    • vacuum freeze min age
    • vacuum multixact freeze table age
    • vacuum multixact freeze min age
    • vacuum defer cleanup age
    • vacuum cost delay
    • vacuum cost limit
    • vacuum cost page hit
    • vacuum cost page miss
    • vacuum cost page dirty
  • ANALYZE
  • REINDEX
  • VACUUM FULL and CLUSTER
  • The autovacuum
    • autovacuum
    • autovacuum max workers
    • autovacuum naptime
    • autovacuum vacuum scale factor
    • autovacuum vacuum threshold
    • autovacuum analyze scale factor
    • autovacuum analyze threshold
    • autovacuum freeze max age
    • autovacuum multixact freeze max age
    • autovacuum vacuum cost delay
    • autovacuum vacuum cost limit

  1. Things to know before starting

  • SQL is your friend
  • The design comes first
  • Clean coding
    • The identifier’s name
    • Self explaining schema
    • Query formatting
  • Get DBA advice