Overview
PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS. It is built to handle large data workloads while maintaining stability, reliability, and optimal performance.
This instructor-led, live training (online or onsite) is aimed at sysadmins and developers who wish to use PostgreSQL 13 to set up, build, and manage databases for high-performance applications.
By the end of this training, participants will be able to:
- Install and configure PostgreSQL 13.
- Understand the features, architecture, and fundamentals of operating, managing, and maintaining databases with PostgreSQL 13.
- Learn how to configure Postgres database tables and schemas.
- Learn how to perform data manipulations and queries.
- Explore the different programming interfaces (client and server) to fully utilize and extend functionalities in PostgreSQL 13.
Format of the Course
- Interactive lecture and discussion.
- Lots of exercises and practice.
- Hands-on implementation in a live-lab environment.
Course Customization Options
- To request a customized training for this course, please contact us to arrange.
Requirements
- An understanding of RDBMS
- Experience with SQL
Audience
- System administrators
- Developers
Course Outline
Introduction
- PostgreSQL vs MySQL vs Oracle
- Overview of PostgreSQL 13 features and architecture
Getting Started
- Setting up a database server
- Installing PostgreSQL 13
- Creating and accessing a database
- SQL syntax, commands, and functions
Configuring a Postgres Database
- Data definition, default values, and constraints
- Setting up and modifying tables
- Managing privileges and security policies
- Creating database schemas
- Implementing table inheritance and partitioning
- Adding, updating, and querying data
- Data types, functions, and operators
- Using indexes and running full-text search
- Understanding concurrency control
Managing a Database Server
- Upgrading a PostgreSQL cluster
- Securing the server and encrypting data
- Configuring server parameters
- Enabling client authentication
- Setting up access roles and permissions
- Managing multiple databases
- Performing database maintenance tasks
- Backing up and restoring data
- Optimizing database performance and reliability
- Monitoring database activity and disk usage
- Implementing logical replication
- Running regression tests
Client-Side Programming
- Using the libpq C library and large object interface
- Executing embedded SQL statements
- Exploring the information schema
- Other PostgreSQL client applications
Server-Side Programming
- Extending the SQL query language
- Writing event trigger functions
- Implementing rule systems
- Using procedural languages (PLs)
- Exploring TCL, Perl, and Python PLs
- Executing server programming interfaces
- Examples of logical decoding
- Tracking replication progress
Troubleshooting
Summary and Next Steps