Skip to main content
Get a Quote
Course Enquiry
Contact Us

NOTICE

We are open for classroom training with strict Covid-19 Level 1 protocols in place.

Details

PostgreSQL is the world's most advanced open-source database, which means no strange licence restrictions, unexpected costs, or lengthy deployment times. That's why companies such as Netflix, Uber and Instagram all run PostgreSQL. Now you can learn to install, administer, secure and manage your PostgreSQL database with this comprehensive course.

This course covers all major topics related to PostgreSQL. In addition to basic administrative tasks, we deal with performance tuning, monitoring, replication and other important topics.

During the workshop, you will learn first-hand what to pay attention to and how to achieve your goals. You will receive a broad technical foundation from which to expertly manage your database.

Delivery Methods

Delivery Method Duration Price (excl. VAT)
Fulltime 5 Days R 34,000.00
Webinar 5 Days R 34,000.00

Discounts Available

Brochure:

Download Brochure


Information may change without notice.

Audience

This course is especially suitable for database administrators (Linux / Windows / Solaris / Mac OS X) and sysadmis. We will deal with administration and performance tuning related topics.

Pre-Requisites

  • LINUX FOR POSTGRESQL DBAS
  • SQL

Course Outline / Curriculum

DAY 1:

Installing PostgreSQL

  • Installing PostgreSQL on Windows (if required)
  • Installing PostgreSQL on Linux
  • Running PostgreSQL as container (Docker)
  • Creating database instances
  • Creating and dropping databases
  • Encoding and character sets
  • Adjusting kernel parameters

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

 

DAY 2:

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

Upgrading PostgreSQL

  •     pg_upgrade at work

 

DAY 3:

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)

 

DAY 4:

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

 

DAY 5:

Parallel queries

  •     Configuring parallel queries
  •     How the optimizer handles parallelism
  •     Performance considerations

Server side code

  •     Optimizing PL/pgSQL procedures
  •     Finding bottlenecks in serverside code
  •     Monitoring procedures
  •     Deploying PostgreSQL extensions

Foreign Data Wrappers

  •     Accessing remote data
  •     Connecting to Oracle
  •     Connecting to PostgreSQL
  •     Migrating data to PostgreSQL
  •     Implications for the optimizer

Schedule Dates and Booking

There are currently no scheduled dates.

Please note that this course needs a minimum of 6 delegates to schedule a course. You can choose to be added to the waiting list by clicking the button below and we will contact you when we have enough delegates interested. Should we not get enough delegates, we will refund or credit your paid booking.

Add me to the waiting list

Should you need this course urgently, the following options are available:

  1. Pay for 6 delegates (whether you have them or not) and we will schedule the course as soon as possible.
  2. If you have fewer delegates and cannot pay for 6, we can negotiate a shortened course where some of the time will be spent in blended learning - watching videos and doing tutorials and exercises with some contact time with the trainer. We would want to discuss what your core needs are so that we cover those aspects. You need to have paid for 3 delegates at least.