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 availablePC Hire:
$50 *Bring a PCNote: 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.
Scheduled Dates
There are currently no scheduled dates.