Course description
This PostgreSQL training class teaches the configuration, security management, administration, backup and restore, and optimization of PostgreSQL databases.
Course structure
Installation and data files
- PostgreSQL versions
- Download and installation
- Initial database initialization
- Database engine startup
- Starting and stopping of database engine
- Resolving startup failures
Configuration and data files
- psql client
- Connecting to database
- Common connection problems
- Configuration files
- main conf
- Physical storage and file layout
- Tablespaces
- Roles & privileges
- Roles and attributes
- Client authentication
- File conf
- Postgresql identity mapping
- Databases, schemas
Basic operations
- Working with search_paths
- Purpose of information_schema
- Database inspection
- customizing psql
- Magic psql commangs
- psql configuration files
- Automating autentication with .pgpass and environmenta variables
Monitoring & maintenance
- PostgreSQL logging behaviour
- Statistics tables
- Monitoring
- database activity
- client connections
- slow queries
- locks
- Vacuum and autovacuum
Transaction and WALs
- How transactions works
- Transaction isolations levels
- Write Ahead Logs
- Transaction implementation
Backup and restore
- PostgreSQL internal backup_mode
- Filesystem backup
- PostgreSQL backups
- Replication-based backups
- Point In Time Restore
Tips and tricks
- Basic parameters tuning
- Customizing PostgreSQL client
- Replication types overview
