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
- Introduzione a PostgreSQL
- Caratteristiche principali
- PostgreSQL Architecture Overview
- Installazione di PostgreSQL
- Installazione dei pacchetti
- Inizializzazione database
- Creazione di un database di prova
- Amministrazione
- File di configurazione
- Creazione e rimozione di un database
- Security fundamentals
- 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
- 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
- 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
- Performance tuning
- Key parameters influencing performance
- Performance and reliabilità balancing
- Performance problems diagnosing
- Statistics
- The PostgreSQL Statistics Collector
- Statistics Views
- Statistics Functions
- 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
- 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
- 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
- Upgrade Methods
- Minor Version Upgrades
- pg_upgrade
- RPM Based Upgrade
- Source Based Upgrade
- SLONY Based Upgrade
