PostgreSQL Database Administration

Descrizione del corso

This course provides the knowledge to perform the typical functions of a database, starting from basic operations and some intermediate techniques. The course is recommended for database and system administrators.

Programma

1. Introduction to PostgreSQL
o Main features
o PostgreSQL Architecture Overview
o Installing PostgreSQL
o Installing packages
o Initializing the database
o Creating a sample database
2. Administration
o Configuration files
o Creating and removing a database
o Security fundamentals
3. Maintenance
o Critical parameters
o Kernel shared memory settings
o Database restart modes
o Processes started by PostgreSQL
o postmaster
o bgwriter
o stats collector
o autovacuum
o statistics and_ANALYSE_command
o clients processes (backends)
o Data Integrity guarantees
o WAL files
o _fsync_settings
o Write-through and write-back caches
o Routine Maintenance
4. Backup and restore
o When to use backup and restore
o pg_dump
o pg_dumpall
o Restore
o Restore via a List File
o Restoring data from a copy, including partial recover
5. Advanced backup techniques
o PostgreSQL operation log working
o WAL logs archiving (log shipping)
o _pg_basebackup_ program
o _pg_receivexlog_program
o Parallel mode for_pg_dump_
o Point In Time Recovery
o Backup at the file system’s level
6. Performance tuning
o Key parameters influencing performance
o Performance and reliability balancing
o Performance problems diagnosing
7. Statistics
o The PostgreSQL Statistics Collector
o Statistics Views
o Statistics Functions
8. Replication
o Hot standby
o Streaming replication
o Monitoring replication stage
o Replication parameters tuning
o Cascading Replication
o Synchronous replication
o Inner workings
o The balance of benefits and costs
o External replication systems
9. High availability
o Replication setting for HA
o Master-slave automated promotion
o STONITH problem (Shoot The Other Node In The Head)
o Security guarantees for HA systems
o Estimating the risk of loosing data in case of failure
o External HA systems for PostgreSQL
o Heartbeat Monitoring
o Failing Over
o Failing Back
10. Monitoring database work
o System tools
o OS Process Monitoring
o vmstat, top, iostat
o strace, systemtap
o System perspectives
o Monitoring the bgwriter process
o Troubleshooting deadlocks
o Preventing transaction wraparound
o Dedicated monitoring tools
o _check_postgres.pl_script
o Log parsers: pgFouine, _pg_badger_
o Automatic errors notification: _tail_n_mail_
o Avoiding auto freezing and page corruptions
o Avoiding transaction wraparound
o Removing old prepared transactions
o Maintaining indexes
o View locks
o Monitoring, backup correctness verification
11. Upgrade Methods
o Minor Version Upgrades
o pg_upgrade
o RPM Based Upgrade
o Source Based Upgrade
o SLONY Based Upgrade