Description
At the end of this course, you will know the advanced notions of administering a PostgreSQL database such as the fine configuration of an instance for better performance, the efficient management of connections and the use of scripts to facilitate operation.
Who is this training for ?
For whom ?
Database administrators and systems administrators.
Prerequisites
Good knowledge of PostgreSQL administration or equivalent knowledge
Training objectives
Training program
- PostgreSQL Overview
- Brief reminders about PostgreSQL administration.
- Managing multiple instances on the same machine.
- Creating and administering an instance
- Data directories.
- Transaction and activity logs.
- Installation of automatic tasks.
- Volume management.
- Using storage spaces.
- Defining the transaction log space.
- Loading data with pgLoader.
- Partitioning tables.
- Materialized views.
- Administering an instance.
- Using the system catalog.
- Tracking volumes.
- Connection tracking.
- Transaction tracking.
- Administrator contributions
- pgbench: installation, configuration and use.
- pg_stattuple: status of tables and indexes.
- pg_freespacemap: status of free spaces.
- pg_buffercache: memory status.
- pg_stat_statments: information on executed SQL statements.
- Performance and settings (reminders)
- Limit connections.
- Shared memory sizing.
- Sort and hash operations.
- Optimize data deletions.
- Optimize transaction log management.
- Refine auto-vacuum with thresholds.
- Practical work
- Supervision of an instance
- Activity statistics.
- PgBadger.
- Analysis of activity logs and Vacuum messages.
- Munin, presentation.
- Practical work.
- Advanced connection management
- Connection strings, connection attributes, multi-host connections.
- Pgbouncer.
- Installation and configuration of the pool manager.
- The use case.
- Definitions of connection pools.
- Practical work
- Supplements (overall vision)
- Defining replication and high availability.
- Warm Standby overview.
- Slony overview.
