Introduction to PostgreSQL for Developers and Administrators

SEE SCHEDULE

Duration: 5.0 days

Method: Instructor led with hands-on.

Price: $2695.00

Course Code: PS1000

Audience

Developers or Database Administrators who will be working with PostgreSQL.

Description

This Introduction to PostgreSQL for Developers and Administrators training class provides an introduction to the PostgreSQL database. The course targets both administrators and developers.

Objectives

Upon successful completion of this course, the student will be able to:

Understand the history of PostgreSQL.
Know where to find PostgreSQL documentation and resources.
Understand PostgreSQL Server Architecture.
Work with PostgreSQL functions.
Create your own functions.
Program with plpgsql.
Install and setup PostgreSQL securely.
Perform basic administration tasks such as user creation, cataloging and backing up.
Understand SLONY and PGPOOL.
Understand inheritance in PostgreSQL.
Partition tables.
Use tablespaces.
Perform database monitoring.
Understand the EXPLAIN Statement
Understand PITR
Tune the Postgresql Server

Prerequisites

A basic knowledge of relational databases and SQL.

Topics

I. Intro and Architecture
- History and Overview
- PostgreSQL Major Features
- PostgreSQL Limits
- PostgreSQL Architecture
- MVCC
- Write Ahead Logs
- The Background Writer
- PostgreSQL Data Types
II. Installation
- Overview
- Binary Installation
- Source Installation
- Initializing a PostgreSQL cluster
- Starting and stopping a PostgreSQL cluster
- Automatic startup/shutdown
III. Configuration
- Access Control
- Connection settings
- Security and Authentication settings
- Memory settings
- Kernel resource settings
- Log management
- Background writer settings
- Vacuum cost settings
- Autovacuum settings
IV. Intro to psql
- Command line parameters
- Meta commands
- Security
V. Managing PostgreSQL Databases
- PostgreSQL Clusters
- PostgreSQL Databases
- Tables, Joins and Aggregates
- PostgreSQL Indexes and Foreign Keys
VI. Roles and Security
- Schemas
- Tables, Views and Rules
- Users, Groups and Roles
- Tables and Sequences
- Object Security
VII. Tablespaces, Partitioning and Advanced Features
- Vacuum
- Tablespaces
- Inheritance
- Table Partitioning
- Windowing Functions
- Transactions
- Concurrency Control
- Functions and Operators
- Type conversion
- Full text search
VIII. Moving Data with PostgeSQL
- Basic DML
- COPY
- Other Tools
IX. Routine DBA Tasks and Best Practices
- Log Management
- Query analysis
- Routine Vacuuming
- Recovering disk space
- Managing Planner statistics
- REINDEX
X. Monitoring and Statistics
- Database Logs
- OS Process monitoring
- The PostgreSQL Statistics Collector
- Statistics Views
- Statistics Functions
XI. Performance Tuning
- OS Tuning
- Hardware configuration
- Transaction logs
- Tablespaces and Partitioning
- Checkpoint tuning
- Query tuning
XII. Backup and Recovery
- The pg_dump utility
- The pg_dumpall utility
- Recovery options
- Point In Time Revovery
XIII. PostgreSQL Upgrades
- The pg_upgrade utility
- RPM based upgrades
- Source based upgrades
- Replication based upgrades
- Rolling back
XIV. Replication and HA
- Streaming Replication
- Synchronous Replication
- Replication Slots
- Failover management
- Connection Poolers
- HA configurations
XV. Triggers
- Overview
- Triggers
XVI. Event Triggers
- Overview
- Event Triggers
XVII. RULEs
- Overview
- SELECT Views
- Updating a View
- Materialized Views
- Rules and privileges
XVIII. The PL/pgSQL Language
- Structure
- Declarations
- Expressions
- Statements
- Control Structures
- Cursors
- Errors and Messages
- Trigger Procedures
XIX. Other Procedural Languages
- PL/Perl
- PL/Python
- More...
XX. PostgreSQL Contribs
- Overview of additionally supplied modules