Advanced PostgreSQL Administration and Performance

Explore Next-Level PostgreSQL Administrative Tasks, Performance Tuning, Monitoring, Security, Replication and More

Course Code: TTDB7023

Skill Level: Intermediate and Beyond

Duration: 3 Days

Course Overview

Geared for experienced database administrators and system administrators, Advanced PostgresSQL Administration and Perfomance is a comprehensive, hands-on course that explores core administrative tasks, performance tuning, monitoring, replication and other advanced topics. This course builds on the Foundations of Postgres Administration course prepares DBAs to manage large, highly available databases and their related security requirements with confidence.

This “skills-centric” course is about 50% hands-on lab and 50% lecture. Working within in a hands-on learning environment, guided by our expert team, attendees will explore:

The PostgreSQL architecture
PostgreSQL indexing and performance
Transactions and locking
Storage management
WAL: The PostgreSQL transaction log
Memory management
PostgreSQL replication
Database backups
PostgreSQL security
PostgreSQL monitoring and logging

Need different skills or topics? If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate. We offer additional PostgreSQL, database / DBA, scripting, SQL, programming, sysadmin, design and security courses which may be blended with this course for a track that best suits your needs.

Course Objectives

This “skills-centric” course is about 50% hands-on lab and 50% lecture. Working within in a hands-on learning environment, guided by our expert team, attendees will explore:

The PostgreSQL architecture
PostgreSQL indexing and performance
Transactions and locking
Storage management
WAL: The PostgreSQL transaction log
Memory management
PostgreSQL replication
Database backups
PostgreSQL security
PostgreSQL monitoring and logging

Course Prerequisites

This course is geared for experienced database administrators and sysadmins and IT Professionals who want to enhance their Postgres administration skills. Attendees should have proficiency in fundamental Postgres DBA functions

Take Before:

TTDB7020 PostgreSQL Server Administration Fundamentals – 3 days

Course Agenda

The PostgreSQL architecture

Overview of PostgreSQL
Postmaster and its child processes
Backend connections
Using shared memory
Stats collector
Checkpoint subprocesses

PostgreSQL indexing and performance

Types of PostgreSQL indexes
Understanding the PostgreSQL optimizer
EXPLAIN and EXPLAIN ANALYZE
Reading execution plans
Detecting slow queries
Find missing indexes
Full text search
GIS indexing and geospatial search

Transactions and locking

The PostgreSQL transactional model
Understanding MVCC (MultiVersion Concurrency Control)
FOR UPDATE vs. FOR SHARE vs. LOCK TABLE
Managing transactional integrity
Using advisory locks
Understanding deadlocks
Making use of transaction isolation levels
Detecting locking problems

Storage management

Using tablespaces
VACUUM and VACUUM FULL
Understanding pg_squeeze
Preventing table bloat
Improving UPDATE performance
HOT-UPDATE and FILLFACTOR
PostgreSQL partitioning

WAL: The PostgreSQL transaction log

How PostgreSQL writes data to disk
Improving checkpoint performance (postgresql.conf)
Speeding up bulk loads
Asynchronous COMMIT
Unlogged tables

Memory management

Adjusting shared_buffers
Improving work_mem and maintenance_work_mem
Understanding additional memory parameters

PostgreSQL replication

Setting up streaming replication
Synchronous vs. asynchronous replication
Automated failover (Patroni)
Managing service IPs
Handling replication conflicts
Using pg_rewind manually
Logical replication

Database backups

Utilizing pg_dump and pg_restore
Binary backups (pg_basebackups)
Point-In-Time-Recovery (PITR)
Backup tools (pgbackrest)

PostgreSQL security

TCP vs. UNIX sockets
Managing pg_hba.conf
Central authentication
Encrypted database connections
Creating users / roles
Access permissions (GRANT / REVOKE)
Defining default priviledges
PostgreSQL on disk encryption
Row Level Security (RLS)
Security barrier, leakproof functions, etc

PostgreSQL monitoring and logging

Managing log files
Handling log rotation
Understanding PostgreSQL system tables
Using pg_stat_statements
Inspecting performance vitals
Checking stored procedure performance
Making use of pgwatch2 monitoring

Course Materials

Student Materials: Each participant will receive a digital Student Guide and/or Course Notes, code samples, software tutorials, step-by-step written lab instructions (as applicable), diagrams and related reference materials and resource links. Students will also receive the project files (or code, if applicable) and solutions required for the hands-on work.

Hands-On Setup Made Simple! Our dedicated tech team will work with you to ensure our ‘easy-access’ cloud-based course environment, or local installation, is accessible, fully-tested and verified as ready to go well in advance of the course start date, ensuring a smooth start to class and effective learning experience for all participants. In some cases we can also help you install this course locally if preferred. Please inquire for details and options.

Every-Course Extras = High-Value & Long-Term Learning Support! All Public Schedule courses include our unique EveryCourse Extras package (Course Recordings, Live Instructor Follow-on Support, Free *Live* Course Refresh Re-Takes, early access to Special Offers, Free Courses & more). Please inquire for details.

Raise the bar for advancing technology skills

Attend a Class!

Live scheduled classes are listed below or browse our full course catalog anytime

Special Offers

We regulary offer discounts for individuals, groups and corporate teams. Contact us

Custom Team Training

Check out custom training solutions planned around your unique needs and skills.

EveryCourse Extras

Exclusive materials, ongoing support and a free live course refresh with every class.

Mix, Match & Master!
2FOR1: Two Courses, One Price!
Enroll in *any* two public courses (for 2023 *OR* 2024 dates!) by December 31, for one price! Learn something new, or share the promo!

Click for Details & Additional Offers

Learn. Explore. Advance!

Extend your training investment! Recorded sessions, free re-sits and after course support included with Every Course

Gain the skills you need with less time in the classroom with our short course, live-online hands-on events

Trivera QuickSkills: Free Courses and Webinars

Training on us! Keep your skills current with free live events, courses & webinars

Trivera AfterCourse: Coaching and Support

Expert level after-training support to help organizations put new training skills into practice on the job

The voices of our customers speak volumes

Excellent detail about the Spring framework which directly relates to my role as a senior engineer. I found the content and style to be very enjoyable, and would encourage me to ask questions where previously I might not have paid much attention, eg. pom version numbers / spring starters being imported. The focus on the different styles of unit testing was also excellent and I would plan to incorporate in day to day work.

Edward K

Excellent training. Even though it was online, I did not have any issues understanding or getting the answers for the questions I had. The lab exercises are interesting to apply what was learnt. Overall very good training. Thank you!

Anitha

Instructor was beyond phenomenal. He had an in depth knowledge of the course material and always ensured that everyone understood what was being discussed. I really enjoyed the mix of theory and practical examples of each part of the course.

Cillian M

The instructor was clearly knowledgeable about Java and programming in general. He was very attentive to every student's individual needs and constantly encouraged questions, concerns, student issues to be raised and addressed each one thoroughly.

Mark M

Both the instructor and the course material were very thorough - the course material has many helpful references - the labs were well laid out, making it easy to learn from and get done on time

Steve C.

Best programming class I've ever been in, head and shoulders above any thing I took in college as part of a computer science degree. The instructor was thorough and very detailed, he was very good abut explaining all possible scenarios a situation would apply to. The way the albs were structured and the class was conducted was very good for interaction and hand on learning.

Danish J

A very good balance of teaching time to lab time. I've done other courses that I found to be much lighter on content and heavy on labs to waste time. This was a refreshing change of pace.

Adam G

Great job overall. Great communicator and explained complex concepts in a simple, easier to understand format.

Jeremy G

First time in my life I have attended remote class and I find it great. Personally I don't feel there is any area which require improvement!

Rishi M

Special Offers
Limited Offer for most courses.

SAVE 50%

Learn More