PostgreSQL Administration

PostgreSQL - Configuration and Installation

• Host based access control
• secure access via OpenSSL

psql client usageIndex 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)