PostgreSQL Administration Training (PGS502)

This PostgreSQL Server Administration course provides a comprehensive guide to installing, configuring, and managing PostgreSQL servers and clusters. Ideal for database administrators and developers, the course covers essential topics such as backup and recovery, security configurations, performance optimization, and high availability setup.

The course begins with Installing PostgreSQL Server and Client, where you’ll compare different installation methods and participate in a hands-on installation exercise. This section ensures you are equipped with practical knowledge on setting up PostgreSQL servers.

Next, the Administration Tools and Options module introduces key PostgreSQL command line utilities, including psql and other administrative tools, enhancing your command-line proficiency for efficient server management.

In Configuring a PostgreSQL Cluster, you will review the components of a PostgreSQL cluster and learn how to initialize and run a cluster. This module provides the foundational skills required to manage PostgreSQL clusters effectively.

SQL in PostgreSQL delves into the core SQL operations, covering Data Definition Language (DDL), Data Manipulation Language (DML), and the use of views within PostgreSQL. You’ll also explore various PostgreSQL data types and practice running queries in psql.

The Cluster Security module focuses on securing your PostgreSQL clusters. Learn to enable remote connections and configure host-based authentication using pg_hba.conf to protect your data from unauthorized access.

In User Security, you will master creating users, assigning roles, and managing user authentication, equipping you with the skills needed to control access within your PostgreSQL environment.

The Backup and Recovery Methods section provides insights into selecting effective backup strategies, including logical and offline physical backups, ensuring that you can maintain data integrity and availability under all circumstances.

In PostgreSQL Optimization, you will learn to optimize queries using the EXPLAIN command and configure system logs for better performance monitoring and troubleshooting.

The High Availability Configuration and Operation module covers setting up streaming replication and configuring master-standby replication to ensure your databases remain available even in the event of server failures.

With Upgrading PostgreSQL, you will explore the process of performing minor and major version upgrades using the pg_upgrade utility, helping you keep your PostgreSQL environment up-to-date and secure.

Logical Replication teaches you to set up logical replication, including publisher/subscriber configurations, enabling real-time data replication across different PostgreSQL instances.

Finally, Continuous Archiving and Point-in-Time Recovery (PITR) equips you with the skills to set up WAL (Write-Ahead Logging) archiving, conduct online physical backups, and recover data from continuous archives, ensuring that your data can be restored to any point in time.

By the end of this course, you will have developed advanced skills in PostgreSQL administration, including managing high availability, implementing security measures, and optimizing database performance, making you proficient in running robust PostgreSQL environments.

1. Installing PostgreSQL Server and Client
   1. Comparison of installation methods
   2. Demonstration of installation
   3. Hands-on installation exercise
2. Administration Tools and Options
   1. psql options
   2. Other PostgreSQL command line utilities
3. Configuring a PostgreSQL Cluster
   1. Review components of a PostgreSQL cluster
   2. Initialize and run a cluster
4. SQL in PostgreSQL
   1. DDL
   2. DML
   3. Running queries in psql
   4. PostgreSQL data types
   5. Creating and using views
5. Cluster Security
   1. Enabling remote connections to cluster
   2. Host-based authentication (pg_hba.conf)
6. User Security
   1. Creating users
   2. Using and assigning user roles
   3. User authentication
7. Backup and Recovery Methods
   1. Selecting an effective backup approach
   2. Logical backup
   3. Offline Physical backup
8. PostgreSQL Optimization
   1. EXPLAIN
   2. Configuring PostgreSQL system logs
9. High Availability Configuration and Operation
   1. Setting up streaming replication
   2. Master-Standby replication
10. Upgrading PostgreSQL
    1. Minor version upgrades
    2. Major version upgrades
    3. pg_upgrade utility
11. Logical Replication
    1. Setting up logical replication
    2. Publisher/Subscriber configuration
12. Continuous Archiving and Point-in-Time Recovery (PITR)
    1. Setting up WAL archiving
    2. Running an online physical backup
    3. Recovery from a continuous archive

Each student receives a comprehensive set of materials, including course notes and all class examples.

Experience in the following is required for this PostgreSQL class: