PostgreSQL Administration and Performance Tuning
Audience
This course is especially suitable for database administrators (Linux / Windows / Solaris / Mac OS X) and sysadmis. We will deal with administration and performance tuning related topics.
Content
Installing PostgreSQL
- Installing PostgreSQL on Windows
- Installing PostgreSQL on Linux
- Compiling PostgreSQL source code
- Performing upgrades
- Solaris / AIX specifics
- Creating database instances
- Creating and dropping databases
- Encoding and character sets
- Adjusting kernel parameters
The PostgreSQL architecture
- Overview
- Postmaster
- Backends
- Using shared memory
- Stats collector
- Checkpoint subprocesses
PostgreSQL security
- TCP vs. UNIX sockets
- Managing pg_hba.conf
- Encrypted database connections
- Creating users / role
- Access permissions
- Central authentication
PostgreSQL performance tuning
- Optimizing storage
- Tablespaces
- Adjusting storage parameters
- Optimizing checkpoints
- Optimizing the background writer process
- Optimizer internals
- Intelligent benchmarking
- Understanding and using indexes
- Partial indexes
- Detecting unnecessary indexes
- Finding slow queries
- Using “explain”
Maintenance
- VACUUM
- Reclaiming storage
- Preventing transaction wraparound
- VACUUM FULL
- VACUUM FREEZE
- Rebuilding optimizer statistics
Monitoring
- Creating log files
- Monitoring queries
- Collecting performance data
- I/O caching and cache hit rates
- I/O statistics
Backup and recovery
- Performing backups
- Disaster recovery
- Point-In-Time-Recovery
- Transaction log shipping
- Asynchronous and synchronous replication
- Creating standby systems
Other courses that might interest you
We offer these services
High availability with Patroni
Patroni – Protects your database Patroni is an Open Source Cluster-Technology, which takes care of automatic failover and high availability of your PostgreSQL database. Furthermore, Patroni is simple – the Patroni-Clusters are very easy to set up and to use. Ensure high availability of your database with Patroni! Cybertec’s Patroni-Extension “vipmanager” ensures an automatic IP […]
PostgreSQL Product- and 3rd Level Support
Are you using PostgreSQL as database backend for your product? Then we have the ideal solution to save you and your customers from database problems. PostgreSQL is increasingly being used as database for software and hardware solutions. Once they are sold to the customer, you are often left with the question how to offer a […]
PostgreSQL performance tuning
Is your PostgreSQL database slow? Do you see high latency, many slow query entries and are customers already complaining? If you are looking for a performance tuning because you simply want a fast, reliable database, you are on the right website. PostgreSQL Storage Tuning One part of performance tuning is storage tuning. A good storage […]