PostgreSQL Administration — A 5 day course
This course deals with the administration 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".
A sound understanding of Linux fundamentals, and of Structured Query Language (SELECT, INSERT, UPDATE, and CREATE TABLE statements in particular).
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 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.
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)