PostgreSQL Database Administration

Descrizione del corso

Questo corso completa le conoscenze per evadere i compiti quotidiani di un DBA, partendo dalle operazioni di base ad alcune tecniche intermedie.&Consigliato per amministratori di database e amministratori di sistema.

Programma

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