Duration
5 days
Overview
Participants taking this course of PostgreSQL will learn to effectively administer PostgreSQL.
Program Goals
- Learn configurations in postgresql
- Learn about Postgresql – Physical & Logical Architecture
- Learn about Concurrency Control, isolation & MVCC architecture.
- Learn to manage free space.
- Learn about security in PostgreSQL.
- Learn to take backup / restore.
Day 1
- Installation
- Architectural Fundamentals (Logical and Physical layout)
- Creating a Database
- Accessing a Database
- Creating a New Table
- Populating a Table With Rows
- Querying a Table
- Joins Between Tables
- Aggregate Functions
- Updates
- Deletions
Day 2
- Physical Architectural
- Logical Architectural
- Starting, stopping and finding status of postmaster
- Exploring utilities in Postgresql (process and server / postmaster)
- Working with above said process related utilities
- Schemas in Postgresql
- Hands-on Exercise / implementation
Day 3
- Concurrency control
Introduction
- Transaction Isolation (Levels with hands-on)
- Explicit Locking
- Hands-on Exercise / implementation
Day 4
- Configuration files in postgresql
- Server configuration
- Connectivity configuration
- User auth. & privileges
- Access Control
- Performance Tips
- Using EXPLAIN
- EXPLAIN ANALYZE
- Statistics
- Explicit JOIN Clauses
- Disable Autocommit
- Use COPY
- Remove Indexes
- Remove Foreign Key Constraints
- Run ANALYZE
- Hands-on Exercise / implementation
Day 5
- Routine Database Maintenance Tasks
- Routine Vacuuming
- Routine Reindexing
- Understand Auto-vacuuming
- Log File Maintenance
- Backup and Restore
- SQL Dump
- Pg_dump & pg_dumpall
- File System Level Backup
- Setting up WAL archiving
- Hands-on Exercise / implementation
Programme scope, delivery arrangements and suitability are confirmed during enquiry.