About the Postgres Administrators course

We do not price our training by person, instead you can flexibly change the amount of hours of the training, and content covering will be adjusted to the pace of your team.

The training covers the following topics:

• Server Installation / Configuration
• Hardware choice / Evaluation
• Performance test (benchmarking)
• Database desing, supported objects
• Referential Integrity
• Mainteance
• Backups
• Flight view of the most common monitoring metrics

The recommended duration for this training is between 14 and 20 hours, including laboratories, with a flexible topic list. We focus on what it matters for the day to day work and consistent knowledge around the Postgres ecosystem.

We also accept questions about specific problems in your infrastructure, and we’ll assist you through its troubleshooting.

The temaries aren’t fixed, and you can request to dig into specific topics more than others; think about our trainings more like an structured-AMA (Ask Me Anything).

Temary

Initial Installation / Configuration

• Installation and Setup
  • Available Versions
  • Optional: Source compilation
  • Packagind system
  • Upgrading
• Hardware
  • CPUs and Memory
  • Storage Hardware, FS, partitioning. RAID, LVM, etc.
  • Cloud Database-related Resources planning and available services.

Desing Aspects

• The relational model. Strengths and Weakness.
• Tables, Data types and other objects.
• Index and access methods types.
• Referential Integrity (Foreign Keys)
• Views: hiding implementation complexities

Backup systems

• pgbackrest
• Other backup solutions such as barman can be also included.

Monitoring

• Most common metrics for Postgres.
• Server analysis:
  • OS tools for resource monitoring (sysstat, *top, etc)
  • PostgreSQL Tools
  • System tables and Views
  • External Tools (prometheus, elastic stack, etc.)
  • Logs Analysis. pgbadger
• Query sampling analysis: pg_stat_statements
• Log analysis
• Backups

Performance Analysis

• OS level analysis
  • CPU usage
  • Memory
  • Cache usage and invalidation by the dirty cleaner
• Using Explain to know the execution plan
• “Logging in” Slow queries
• Tweaking Postgres configuration
  • Server memory
  • Backend memory
  • Connections
  • WAL configuration
  • Checkpoint and helpers
• All about indexing:
  • Effective use of indices
  • Partial Indices
  • Insufficient Indices
  • When is it “too many indexes” a problem?
  • Index Bloat
  • Clustering
  • Evaluating the effectiveness of the indices
  • The pg_stat_* catalog tables
• Vacuum strategies
• Checkpoints
• Bulk Loading

Security

• The pg_hba.conf file
• SQL privileges
• The Security Invoker / Definer Clause
• Connections via SSL