Introduction to PostgreSQL for Developers and Administrators

  3.6 out of 5 - Read Testimonials

The primary objective of this course is to provide an introduction to the PostgreSQL database. The course targets both administrators and developers.

Goals
  1. Learn about the history of PostgreSQL.
  2. Learn where to find PostgreSQL documentation and resources.
  3. Understand PostgreSQL Server Architecture.
  4. Learn about different popular PostgreSQL clients.
  5. Learn to work with PostgreSQL functions.
  6. Learn to create your own functions.
  7. Learn to program with plpgsql.
  8. Learn to install and setup PostgreSQL securely.
  9. Learn basic administration tasks such as user creation, cataloging and backing up.
  10. Learn about SLONY and PGPOOL.
  11. Understand inheritance in PostgreSQL.
  12. Learn to partition tables.
  13. Learn to use Tablespaces.
  14. Learn about database monitoring.
  15. Understand the EXPLAIN Statement
  16. Learn about PITR
  17. Learn to tune the Postgresql Server
Outline
  1. Intro and Architecture
    1. History and Overview
    2. PostgreSQL Major Features
    3. PostgreSQL Limits
    4. PostgreSQL Architecture
    5. MVCC
    6. Write Ahead Logs
    7. The Background Writer
    8. PostgreSQL Data Types
  2. Installation
    1. Overview
    2. Binary Installation
    3. Source Installation
    4. Initializing a PostgreSQL cluster
    5. Starting and stopping a PostgreSQL cluster
    6. Automatic startup/shutdown
    7. Lab
  3. Configuration
    1. Access Control
    2. Connection settings
    3. Security and Authentication settings
    4. Memory settings
    5. Kernel resource settings
    6. Log management
    7. Background writer settings
    8. Vacuum cost settings
    9. Autovacuum settings
    10. Lab
  4. Intro to psql
    1. Command line parameters
    2. Meta commands
    3. Security
    4. Lab
  5. Managing PostgreSQL Databases
    1. PostgreSQL Clusters
    2. PostgreSQL Databases
    3. Tables, Joins and Aggregates
    4. PostgreSQL Indexes and Foreign Keys
    5. Lab
  6. Roles and Security
    1. Schemas
    2. Tables, Views and Rules
    3. Users, Groups and Roles
    4. Tables and Sequences
    5. Object Security
    6. Lab
  7. Tablespaces, Partitioning and Advanced Features
    1. Vacuum
    2. Tablespaces
    3. Inheritance
    4. Table Partitioning
    5. Windowing Functions
    6. Transactions
    7. Concurrency Control
    8. Functions and Operators
    9. Type conversion
    10. Full text search
    11. Lab
  8. Moving Data with PostgeSQL
    1. Basic DML
    2. COPY
    3. Other Tools
    4. Lab
  9. Routine DBA Tasks and Best Practices
    1. Log Management
    2. Query analysis
    3. Routine Vacuuming
    4. Recovering disk space
    5. Managing Planner statistics
    6. REINDEX
    7. Lab
  10. Monitoring and Statistics
    1. Database Logs
    2. OS Process monitoring
    3. The PostgreSQL Statistics Collector
    4. Statistics Views
    5. Statistics Functions
    6. Lab
  11. Performance Tuning
    1. OS Tuning
    2. Hardware configuration
    3. Transaction logs
    4. Tablespaces and Partitioning
    5. Checkpoint tuning
    6. Query tuning
    7. Lab
  12. Backup and Recovery
    1. The pg_dump utility
    2. The pg_dumpall utility
    3. Recovery options
    4. Point In Time Revovery
    5. Lab
  13. PostgreSQL Upgrades
    1. The pg_upgrade utility
    2. RPM based upgrades
    3. Source based upgrades
    4. Replication based upgrades
    5. Rolling back
    6. Lab
  14. Replication and HA
    1. Streaming Replication
    2. Synchronous Replication
    3. Replication Slots
    4. Failover management
    5. Connection Poolers
    6. HA configurations
    7. Lab
  15. Triggers
    1. Overview
    2. Triggers
    3. Lab
  16. Event Triggers
    1. Overview
    2. Event Triggers
    3. Lab
  17. RULEs
    1. Overview
    2. SELECT Views
    3. Updating a View
    4. Materialized Views
    5. Rules and privileges
    6. Lab
  18. The PL/pgSQL Language
    1. Structure
    2. Declarations
    3. Expressions
    4. Statements
    5. Control Structures
    6. Cursors
    7. Errors and Messages
    8. Trigger Procedures
  19. Other Procedural Languages
    1. PL/Perl
    2. PL/Python
    3. More...
  20. PostgreSQL Contribs
    1. Overview of additionally supplied modules
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this PostgreSQL class:

  • Oracle SQL as taught in our Introduction to Oracle SQL and Advanced Oracle SQL classes.
Preparing for Class

Training for your Team

Length: 5 Days
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors

What people say about our training

Exceeded my expectations.The instructor did a great job reviewing what we had previously learned and reinforcing important concepts and tips. The pace was good. The course book was easy to follow and did a great job adding context to the steps we were performing to learn.
Greg Wagner
Sargent & Lundy, LLC
I was a SharePoint novice, but this class really opened my eyes to the possibilities of Designer.
Jane Roegner
Federal Emergency Management Agency (FEMA)
Our trainer is engaging, professional and a fountain of relevant knowledge as it relates to real world corporate software development.
Edmund Jimenez
Apollo Group
I couldn't have been more pleased with the online instructor-led HTML5 Web Developers course. The instructor was extremely well-versed in the subject matter, with plenty of real life experience that he readily shared. The course manual was well written and easy to follow. Overall it was a very enjoyable experience and I would definitely take another course with Webucator.
Jack Gambardella
Barrons Educational Series

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

60,501

Students who have taken Instructor-led Training

11,680

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

7.17

Students rated our Introduction to PostgreSQL for Developers and Administrators trainers 7.17 out of 10 based on 11 reviews

Contact Us or call 1-877-932-8228