PostgreSQL Database Administration
Duration: 4 Days
Intended Audience
The course is for novice database adminisrators, as well as Web and system administrators who will be required to maintain and administer PostgreSQL database servers. Many of the principles and techniques covered on the course are also applicable to other Database Management Systems such as SQL Server and Oracle.
Course Overview
This is a comprehensive course on administering PostgreSQL databases on both Linux and Unix platforms. It covers the usual DBA duties and responsibilities such as installation, backup and recovery, security, physical database design and optimisation. In addition it overviews more advanced topics such as compiling PostgreSQL from sources, and importing and exporting data, authentication and encryption.
The course will cover the use of scripts for database administration and backup in a generic way. (i.e. knowledge of scripting languages such as Perl or Tcl, or of shells such as Bash or the Korn Shell will not be assumed). If needed (and if there is sufficient demand) this topic can be added as an extra (fourth) day of the course.
Key Skills
- PostgreSQL installation
- Specifying and enforcing a security policy
- Setting up accounts and conferring user priviledges
- Backup and recovery - planning and execution
- Optimisation
- Working with session encryption
- Compiling PostgreSQL from sources
Practical Work
About 40% of the course time will be allocated to practical exercises, and workshops which will include
- Estimation of data volumes, transaction processing loads, time to perform a backup and recovery.
- PostgreSQL installation under Linux/Unix
- Building and dropping indexes
- Implementing scripts (by studying and modifying a template script) for performing backups
- Carrying out a full database recovery
- Observing the effects of carrying out tuning and optimisation procedures
- Exploring the security mechanisms present in PostgreSQL
- PostgreSQL Database Replication
Course Contents
- Making data available
- Maintaining Database Integrity
- Data backup and recover
- Disaster recovery
- User support (helpdesk support)
- Determining, specifying and enforcing standards
- Database security
- Database/Table model
- Dedicated partitions
- Table Types, Table Locks, and Columns
- Indexes
- File Descriptors
- System memory
- Log Files
- RAID
- Stopping and starting PostgreSQL
- Intialising the file system
- Creating and Removing a Database
- Checking and Repairing Tables
- Devising and Implementing Backup and Recovery Plans
- Data export and data import
- Benchmarking
- Design optimisation
- Application level optimisation
- Query optimisation
- SQL statement optimisation
- Server configuration tuning
- PL/pgSQL stored procedures and triggers and optimisation
- PostgreSQL Permissions Scheme
- Managing users and groups
- Granting privileges
- Client authentication
- Devising and Enforcing a Security Policy
- Audit and security
- Operating System Security
- Network Security Strategy
- User Authentication Levels
- Data Encryption
- Encrypting sessions
- Backup Security
- Row Level Security
- SCRAM authentication
- pg_dump and restore options
- PITR Backup and Recovery
- Replication Concepts
- High Availability options
- Streaming Replication
- Managing clusters using repmgr
- Hot Standby
- Synchronous Replication
- File based Log Shipping
- Introduction to Slony and Londiste
- Connection Pooling
- Upgrading PostgreSQL
- Sharding and Scale Out