I. Overview:

PostgreSQL is the most advanced and most sophisticated Open Source database available.

This 4-day course is a fast-paced dive into PostgreSQL. Class sessions cover basic administration topics as well as more advanced techniques including backup and recovery, PITR, and high-availability configurations. Work with hands-on lab exercises that solidify PostgreSQL administration best practices.

II. Duration: 32 hours (4 days)

III. Objectives:

The objective of this course is to give participants hands-on practical experience in administrating PostgreSQL Databases in a proficient way (comprising among other tasks: account user access settings/privileges, transaction and concurrency control, managing table spaces, creating and managing directories and clusters as well as recovery/backup strategies). This training will give team members an understanding of core concepts and a working competency in the administration of PostgreSQL DBMS through lectures and tailored lab assignments.

IV. Intended Audience:

Database Administrator, Programmers, Developers and Open Source Technology Specialists.

V. Prerequisites:

• Basic understanding of database concept would help
• Basic understanding of SQL would help.

VI. Course outlines:

1. Installing PostgreSQL Server and Client

• Comparison of installation methods
• Demonstration of installation
• Hands-on installation exercise

2. Administration Tools and Options

• Psql options
• Pg_env.sh
• Other PostgreSQL command line utilities

3. Configuring a PostgreSQL Cluster

• Review components of a PostgreSQL cluster
• Initialize and run a cluster

4. SQL in PostgreSQL

• DDL
• DML
• Running queries in psql
• PostgreSQL data types
• Creating and using views

5. Cluster Security

• Enabling remote connections to cluster
• Host-based authentication (pg_hba.conf)

6. User Security

• Creating users
• Using and assigning user roles
• User authentication

7. Backup and Recovery Methods

• Selecting an effective backup approach
• Logical backup
• Physical backup

8. PostgreSQL Optimization

• VACUUM
• EXPLAIN

9. High Availability Configuration and Operation

• Setting up streaming replication
• Master-Standby replication
• Triggering failover
• Logical replication (New for PostgreSQL 10!)

10. Upgrading PostgreSQL

• Minor version upgrades
• Major version upgrades
• Pg_upgrade utility

11. Continuous Archiving and Point-in-Time Recovery (PITR)

• Setting up WAL archiving
• Running a base backup
• Recovery from a continuous archive