PostgreSQL Administration and Performance Tuning

Level:

Intermediate

Date:

08.04 - 12.04.19

04.11 - 08.11.19

Duration:

5 days

Sign up for this course

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.

Content

Installing PostgreSQL

  • Installing PostgreSQL on Windows
  • Installing PostgreSQL on Linux
  • Compiling PostgreSQL source code
  • Performing upgrades
  • Solaris / AIX specifics
  • Creating database instances
  • Creating and dropping databases
  • Encoding and character sets
  • Adjusting kernel parameters

The PostgreSQL architecture

  • Overview
  • Postmaster
  • Backends
  • Using shared memory
  • Stats collector
  • Checkpoint subprocesses

PostgreSQL security

  • TCP vs. UNIX sockets
  • Managing pg_hba.conf
  • Encrypted database connections
  • Creating users / role
  • Access permissions
  • Central authentication

PostgreSQL performance tuning

  • Optimizing storage
  • Tablespaces
  • Adjusting storage parameters
  • Optimizing checkpoints
  • Optimizing the background writer process
  • Optimizer internals
  • Intelligent benchmarking
  • Understanding and using indexes
  • Partial indexes
  • Detecting unnecessary indexes
  • Finding slow queries
  • Using “explain”

Maintenance

  • VACUUM
  • Reclaiming storage
  • Preventing transaction wraparound
  • VACUUM FULL
  • VACUUM FREEZE
  • Rebuilding optimizer statistics

Monitoring

  • Creating log files
  • Monitoring queries
  • Collecting performance data
  • I/O caching and cache hit rates
  • I/O statistics

Backup and recovery

  • Performing backups
  • Disaster recovery
  • Point-In-Time-Recovery
  • Transaction log shipping
  • Asynchronous and synchronous replication
  • Creating standby systems
Sign up for this course