PostgreSQL Administration — A 5-Day Course

 Closed In-House Course

Course Overview

This course deals with the administrationg and performance tuning of PostgreSQL databases. It also covers use of specialised PostgreSQL modules such as replication, connection pooling and full text searching.

Attendees are expected to have a basic working knowledge of SQL.

This is a hands on course with the emphasis on thinking and problem solving via exercises and group "mini projects".

Suitable For

• A sound understanding of Linux fundamentals, and of Structured Query Language (SELECT, INSERT, UPDATE, and CREATE TABLE statements in particular).

Scheduled and On-site Courses

Courses in this subject are scheduled on an 'ad-hoc' basis. We can arrange a course at our Carshalton centre or on customer site for any client wishing to send two or more delegates on the same course.

For courses at the Carshalton Centre the fee will be:

• £3500 + VAT for the first two delegates
• £1650 + VAT for the third delegate
• £1200 + VAT for the fourth delegate
• £400 + VAT for each additional delegate to a maximum of eight

On-site course fees are as above + expenses recharged at cost.

For a 1:1 course in Carshalton the fee is £2750 + VAT. For a 1:1 on-site course the fee is £2750 + VAT + expenses recharged at cost.

If there are any dates showing below this line, a course has been scheduled and places for individuals are available at £1750. Otherwise, no courses are currently scheduled and the above fees apply.

Publicly scheduled dates, locations, and prices

Course Contents

PostgreSQL - Configuration and Installation

• Host based access control
• secure access via OpenSSL

psql client usage

Index Creation and Management

• Index creation
• Indexes based expressions
• Partial indexes

Understanding Transactions and Concurrency

Defining and Managing database privileges

• Overview of PostgreSQL roles
• Defining User Roles
• Assigning users to roles
• Troubleshooting and Understanding role access

Fine grained Access control using GRANT

Structure and Organisation of PostgreSQL databases

• PostgreSQL storage
• table space usage and management
• PostgreSQL log directories
• PostgreSQL directory structures
• PostgreSQL clusters - setup and management

Design, implementation and management of PostgreSQL tablespaces and databases

PostgreSQL Schemas - overview of the key concepts

PostgreSQL logging - principles and uses

Backup and recovery (pg_dump, pg_dumpall, file system backups)

Point-in-time recovery (PITR)

• overview of PostgreSQL Write-ahead logs
• Carrying out a PITR backup
• Carrying out a PITR recovery

Overview of the PostgreSQL query optimizer

• Use of explain and explain analyze to optimize queries and indexes

Server - performance tuning and monitoring

Overview of PostgreSQL modules and add-ons

• Connection pooling (pgpool)
• Full Text indexing (tsearch2)
• Database replication (Slony)