Home About Courses Schedule Services Webinars Contact Search

Introduction to PostgreSQL for Developers and Administrators

SEE SCHEDULE

Duration: 5 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