Overview
PostgreSQL training teaches attendees how to build, query, program stored procedures in PL/pgSQL, and administer the popular, open source PostgreSQL database platform.
Objectives
At the end of PostgreSQL training course, participants will be able to
Prerequisites
Participants must have a solid understanding of basic relational database principles and SQL SELECT statements prior to this training.
Course Outline
- History and Overview
- PostgreSQL Major Features
- PostgreSQL Limits
- PostgreSQL Architecture
- MVCC
- Write Ahead Logs
- The Background Writer
- PostgreSQL Data Types
- Overview
- Binary Installation
- Source Installation
- Initializing a PostgreSQL cluster
- Starting and stopping a PostgreSQL cluster
- Automatic startup/shutdown
- Access Control
- Connection settings
- Security and Authentication settings
- Memory settings
- Kernel resource settings
- Log management
- Background writer settings
- Vacuum cost settings
- Autovacuum settings
- PostgreSQL Clusters
- PostgreSQL Databases
- Tables, Joins and Aggregates
- PostgreSQL Indexes and Foreign Keys
- Schemas
- Tables, Views and Rules
- Users, Groups and Roles
- Tables and Sequences
- Object Security
- Vacuum
- Tablespaces
- Inheritance
- Table Partitioning
- Windowing Functions
- Transactions
- Concurrency Control
- Functions and Operators
- Type conversion
- Full text search
- Log Management
- Query analysis
- Routine Vacuuming
- Recovering disk space
- Managing Planner statistics
- REINDEX
- Database Logs
- OS Process monitoring
- The PostgreSQL Statistics Collector
- Statistics Views
- Statistics Functions
- OS Tuning
- Hardware configuration
- Transaction logs
- Tablespaces and Partitioning
- Checkpoint tuning
- Query tuning
- The pg_dump utility
- The pg_dumpall utility
- Recovery options
- Point In Time Recovery
- The pg_upgrade utility
- RPM based upgrades
- Source based upgrades
- Replication based upgrades
- Rolling back
- Streaming Replication
- Synchronous Replication
- Replication Slots
- Failover management
- Connection Poolers
- HA configurations
- Overview
- SELECT Views
- Updating a View
- Materialized Views
- Rules and privileges
- Structure
- Declarations
- Expressions
- Statements
- Control Structures
- Cursors
- Errors and Messages
- Trigger Procedures