PostgreSQL Administration Training Course-Midrand, Sandton, Johannesburg, South Africa

PostgreSQL Administration Course Details

Instructor led training course on the administration of the PostgreSQL Relational Database Management System. You will be able to develop and maintain powerfull PostgreSQL servers.

Audience:

Database administrators, developers, IT support staff.

Pre-Requisites:

SQL course or equivalent knowledge.

Duration:

2 Days

Price:

$529.10 If you are currently resident in South Africa you will need to pay VAT at 14%. *Discount available

PC Hire:

$50 *Bring a PC

Note: information may change without notice.

Download Brochure:

About PostgreSQL Administration

PostgreSQL is a sophisticated open source relational database management system. PostgreSQL supports the structured query language (SQL) with a wide variety of data types and support for: triggers, a number of embedded programming languages, and full transactional processing.

Course Outline

Introduction toÂ PostgreSQL
- A Brief History of PostgreSQL
- Getting Started
- Installation
- Architectural Fundamentals
- Using the psql terminal based client
PostgreSQL SQL Language specifics
- Creating a Database
- Accessing a Database
- Creating a New Table
- Populating a Table With Rows
- Querying a Table
- Joins Between Tables
- Aggregate Functions
- Updates
- Deletions
Advanced SQL
- Views
- Foreign Keys
- Transactions
- Window Functions
- Inheritance
Indexes
- Multicolumn IndexesÂ
- Indexes and ORDER BY
- Combining Multiple Indexes
- Unique Indexes
- Indexes on Expressions
- Partial Indexes
- Setting up a Partial Index to Exclude Common Values
- Setting up a Partial Index to Exclude Uninteresting Values
- Setting up a Partial Unique Index
- Operator Classes and Operator Families
- Examining Index Usage
- Full Text SearchÂ Â
Transaction Isolation and Concurrency
- SQL Transaction Isolation Levels
- Read Committed Isolation Level
- Serializable Isolation Level
- Serializable Isolation versus True Serializability
- Explicit Locking
- Table-Level Locks
- Table-level lock modes
- Conflicting lock modes
- Row-Level Locks
- Deadlocks
- Advisory Locks
- Using EXPLAIN
Server Setup and Operation
- The PostgreSQL User Account
- Creating a Database Cluster
- Starting the Database Server
- Server Start-up Failures
- Client Connection Problems
- Shutting Down the Server
Server Configuration
- Setting Parameters
- File Locations
Security
- Preventing Server Spoofing
- Encryption Options
- Secure TCP/IP Connections with SSL
- Using client certificates
- SSL Server File Usage
- SSL Server File Usage
- Creating a Self-Signed Certificate
- Connections and Authentication
- Connection Settings
- Security and Authentication
  - The pg_hba.conf file
  - Username maps
  - Authentication methods
  - Trust authentication
  - Password authentication
  - GSSAPI authentication
  - SSPI authentication
  - Kerberos authentication
  - Ident-based authentication
  - Ident Authentication over TCP/IP
  - Ident Authentication over Local Sockets
  - LDAP authentication
  - Certificate authentication
  - PAM authentication
  - Authentication problems
  - Database Roles
  - Role Attributes
  - Privileges
  - Role MembershipÂ
Resource Consumption
- Memory
- Kernel Resource Usage
- Cost-Based Vacuum Delay
- Background Writer
- Asynchronous Behavior
- Write Ahead Log
- Settings
- Checkpoints
- Archiving
- Query Planning
- Planner Method Configuration
- Planner Cost Constants
- Genetic Query Optimizer
- Other Planner Options
Error Reporting and Logging
- Where To LogÂ
- When To Log
- Message severity levels
- What To Log
- Using CSV-Format Log Output
- Run-Time Statistics
- Query and Index Statistics Collector
- Statistics Monitoring
Vacuum
- Automatic Vacuuming
Backup and Restore
- SQL Dump
- Restoring the dump
- Using pg_dumpall
- Handling large databases
- Use compressed dumps.
- Use split.
- Use pg_dump's custom dump format.
- File System Level Backup
- Continuous Archiving and Point-In-Time Recovery (PITR)
- Setting up WAL archiving
- Making a Base Backup
- Recovering using a Continuous Archive Backup
- Recovery Settings
- Timelines
- Tips and Examples
- Standalone hot backups
- archive_command scripts
- Caveats
- Warm Standby Servers for High Availability
- Planning
- Implementation
- Failover
- Record-based Log Shipping
- Incrementally Updated Backups
- Migration Between Releases
Monitoring Database Activity
- Standard Unix Tools
- The Statistics Collector
- Statistics Collection Configuration
- Viewing Collected Statistics
- Viewing Locks
- Dynamic Tracing
- Compiling for Dynamic Tracing
- Built-in Probes
- Using Probes
- Defining New Probes
- Monitoring Disk Usage
- Determining Disk Usage
- Disk Full Failure

Delivery Method

Instructor Led classroom based training. Scheduled classes are normally held in Woodmead - near to Sandton in Johannesburg, Gauteng, South Africa. Stationary and textbook included. Refreshments, including 2 tea breaks and a cooked meal for lunch are provided for full time courses. Contact hours are between 9am to 4pm.

PostgreSQL Administration

TRAINING COURSE

RELATED COURSES

TRAINERS FOR COURSE

CUSTOMERS ON THIS COURSE