PostgreSQL Training Overview
Accelebrate's Introduction to PostgreSQL for Developers and Administrators training teaches attendees how to build, query, program stored procedures in PL/pgSQL, and administer the popular, open source PostgreSQL database platform.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Find PostgreSQL documentation and resources
- Work with PostgreSQL functions
- Create your own functions
- Program with PL/pgSQL
- Install and setup PostgreSQL securely
- Work with basic administration tasks such as user creation, cataloging, and backing up
- Use Advanced replication mechanisms and implement Connection Pooling
- Work with inheritance in PostgreSQL
- Partition tables
- Use Tablespaces
- Monitor the database
- Use the EXPLAIN Statement
- Use PITR
- Tune the PostgreSQL Server
- Get a detailed understanding of MVCC and Autovacuum in PostgreSQL
Prerequisites
All PostgreSQL training students must have a solid understanding of basic relational database principles and SQL SELECT statements prior to this training.
Outline
Expand All | Collapse All
Introduction and Architecture
- History and Overview
- PostgreSQL Major Features
- PostgreSQL Limits
- PostgreSQL Architecture
- MVCC
- Write Ahead Logs
- The Background Writer
- PostgreSQL Data Types
Installation
- Overview
- Binary Installation
- Source Installation
- Initializing a PostgreSQL cluster
- Starting and stopping a PostgreSQL cluster
- Automatic startup/shutdown
Configuration
- Access Control
- Connection settings
- Security and Authentication settings
- Memory settings
- Kernel resource settings
- Log management
- Background writer settings
- Vacuum cost settings
- Autovacuum settings
Intro to psql
- Command line parameters
- Meta commands
- Security
Managing PostgreSQL Databases
- PostgreSQL Clusters
- PostgreSQL Databases
- Tables, Joins and Aggregates
- PostgreSQL Indexes and Foreign Keys
Roles and Security
- Schemas
- Tables, Views and Rules
- Users, Groups and Roles
- Tables and Sequences
- Object Security
Tablespaces, Partitioning, and Advanced Features
- Vacuum
- Tablespaces
- Inheritance
- Table Partitioning
- Windowing Functions
- Transactions
- Concurrency Control
- Functions and Operators
- Type conversion
- Full text search
Moving Data with PostgreSQL
Routine DBA Tasks and Best Practices
- Log Management
- Query analysis
- Routine Vacuuming
- Recovering disk space
- Managing Planner statistics
- REINDEX
Monitoring and Statistics
- Database Logs
- OS Process monitoring
- The PostgreSQL Statistics Collector
- Statistics Views
- Statistics Functions
Performance Tuning
- OS Tuning
- Hardware configuration
- Transaction logs
- Tablespaces and Partitioning
- Checkpoint tuning
- Query tuning
Backup and Recovery
- The pg_dump utility
- The pg_dumpall utility
- Restore using pg_restore and psql
- The pg_basebackup utility
- Restoring a physical backup.
- Point In Time Recovery
PostgreSQL Upgrades
- Major Version Upgrades
- Using pg_upgrade
- Using logical backup/restore
- Using logical backup/restore
- Minor Version Upgrades
- Upgrading PostgreSQL packages
- Source based upgrades
- Rolling back
Replication and HA
- Streaming Replication
- Synchronous Replication
- Replication Slots
- Failover management
- Connection Poolers
- HA configurations
- Cascaded Streaming Replication
- Logical Replication
RULEs
- Overview
- SELECT Views
- Updating a View
- Materialized Views
- Rules and privileges
The PL/pgSQL Language
- Structure
- Declarations
- Expressions
- Statements
- Control Structures
- Cursors
- Errors and Messages
- Trigger Procedures
Other Procedural Languages
PostgreSQL Contribs
- Overview of additionally supplied modules
Conclusion
Training Materials
All students receive comprehensive courseware.
Software Requirements
- Operating system of your choice
- An ssh terminal or client (such as PuTTY) for connection to lab servers provided by Accelebrate
- Optional: pgAdmin 4
- Sample databases and lab files that Accelebrate provides
