Opleiding: PostgreSQL Administration

PostgreSQL Intro

The course PostgreSQL Administration starts with a discussion of the installation options and the tools pgAdmin and psql. Also the main features of PostgreSQL are covered such as table inheritance, sophisticated locking, nested transactions and asynchronous replication.

PostgreSQL Architecture

Subsequently the PostgreSQL Architecture and the processes that play a role in it, such as the Postmaster Daemon Process, Background and Backend Processes and Client Processes, are treated. Tablespaces and vacuum are also covered.

Server and Database Objects

Attention is also paid to the most commonly used server and database objects that PostgreSQL provides. It is important to get to know these objects, such as Server Service and Database Object, in order to be able to use their functionality.

Backup and Restore

Then it's time for the PostgreSQL backup tools pg_dump and pg_dumpall. Point-in-Time Database Restoration and Setting up WAL archiving are then explained.

Indexes

The use of indices in PostgreSQL and the differences between the indices B-Tree, Hash, GiSY, GIN and BRIN is also part of the course PostgreSQL Administration.

Roles and Security

Securing a PostgreSQL Server is of course of great importance. In that respect access permissions and client authentication control are covered and Data Encryption and the pg_crypto module as well.

Clustering

Finally attention is paid to High Availability and Load Balancing by placing multiple PostgreSQL servers in a cluster.

Audience PostgreSQL Administration Course

The course PostgreSQL Administration is intended for persons who need to administer, monitor and support PostgreSQL databases and servers.

Prerequisites Course PostgreSQL Administration

To join the course PostgreSQL Administration knowledge of the SQL query language and databases is needed.

Realization Training PostgreSQL Administration

The subject matter is treated on the basis of presentations. Demos are used to clarify the theory and exercises are used to bring the theory into practice. The course times are from 9.30 up and to 16.30.

Certification Course PostgreSQL Administration

Participants receive an official certificate PostgreSQL Administration after successful completion of the course.

Modules

Module 1 : PostgreSQL Intro

• What is PostgreSQL?
• PostgreSQL Features
• User-defined types
• Table inheritance
• Sophisticated locking mechanism
• Foreign key referential integrity
• Nested Transactions and Savepoints
• Multi-version Concurrency Control
• Asynchronous replication
• Install and Connect to PostgreSQL
• pgAdmin Application and psql Shell

Module 2 : PostgreSQL Architecture

• Shared Memory
• Shared and WAL Buffer
• PostgreSQL Process Types
• Postmaster Daemon Process
• Background Processes
• Backend Processes
• Client Processes
• Database Structure
• Create User Database
• What are Tablespaces?
• What is Vacuum?

Module 3 : Server and Database Objects

• Server Service
• Database Object
• Table Object and Schema
• PostgreSQL Tablespaces
• pg_default and pg_global
• View as Virtual Tables
• Functions and Operators
• Server Configuration
• Logging Parameters
• Memory Parameters
• WAL Parameters

Module 4 : Backup and Restore

• PostgreSQL Backup Tools
• pg_dump and pg_dumpall
• Point-in-Time Database Restoration
• Setting up WAL archiving
• Data and/or Structure
• pg_dump Parameters
• Backup Object Definitions
• pg_restore and psql
• Ignore and Stop on Errors
• pg_store Parameters
• Restore only Structure

Module 5 : Indexes

• What are Indexes?
• Index Types
• B-Tree, Hash, GiSY
• GIN and BRIN
• Index Differences
• Create and Drop Index
• List indexes
• Unique Index
• Index on Expression
• Partial index and Reindex
• Multicolumn Indexes

Module 6 : Database Management

• Options to Create Databases
• Modify Databases
• Rename Databases
• Change Owner and Tablespace
• Change Session Defaults
• Delete Databases
• Check Activity with pg_stat_activity
• Copy a Database
• Using pg_dump
• Get Database Object Sizes
• Using pg_size_pretty

Module 7 : Role Management

• Access Permissions
• pg_hba.conf file
• Creating PostgreSQL Roles
• Privileges and Restrictions
• superuser
• Login Privilege
• pg_roles System Catalog
• Role Attributes
• Groups
• Role Membership
• User Role and Group Inheritance

Module 8 : Securing PostgreSQL

• Client Authentication Control
• Rule Specification
• Server Configuration
• Changing Parameters
• Role Strategies
• Super User Management
• Data Encryption
• One and Two Way Encryption
• pg_crypto
• Logging
• pg_stat_statements Module

Module 9 : Cluster Management

• High Availability Cluster
• Performing Replication
• Primary Server
• Promoting Standby Server
• Load Balancing
• HAProxy Configuration
• Xinetd Setup
• HAProxy in ClusterControl
• Reslaving Standby Server
• Chained Replication
• Keepalived