Course Overview

Build the practical skills to install, configure, secure and maintain production PostgreSQL databases. Designed for database administrators and IT professionals responsible for PostgreSQL systems, this hands-on course covers architecture, security, performance tuning, routine maintenance, and backup and restore strategies for reliable, resilient PostgreSQL operations.

Course Prerequisites

Participants should have:

• Basic familiarity with relational database concepts.
• Comfort working on the command line; the course is Linux-based.
• No prior PostgreSQL experience is required.

Outline

Who Should Attend:

• Database administrators responsible for installing, securing and maintaining PostgreSQL systems.
• System administrators and IT professionals taking on PostgreSQL operational responsibilities.
• Developers who need a solid grounding in PostgreSQL administration to support the systems they build on.

Upon completion, you'll be able to install, configure, secure and maintain PostgreSQL systems with confidence, and handle routine operational tasks including backup, restore and performance tuning.

Outline

Day 1

• Introduction to PostgreSQL

• Installation

  • Quick Overview of methods of Installation.

• Postgresql – Client program / GUI Client.

• Postgresql Service /processes

• Architectural Fundamentals (Logical and Physical layout)

• Creating a Database (different options with command & utility)

• Accessing a Database & OIDs (Demo & Practical)

• Meta Commands & PgPLSQL Environment & Options

• Starting, stopping and finding status of postmaster

• Hands-on

• Physical Architectural

• Logical Architectural

• Exploring utilities in Postgresql (process and server / postmaster)

• Working with above said process related utilities

• Schemas in Postgresql

• Schema Search PATH

• Hands-on Exercise / implementation

• User Security

• Creating users

• Using and assigning user roles

• User authentication

• Hands-on Exercise

• Cluster Security

• Tablespace

• Built-in Table Space

• User Tablespaces

• Managing Tablespaces

Day 2

• Concurrency control

• Introduction

• Transaction Isolation (Levels with hands-on)

• Explicit Locking

• Hands-on Exercise / implementation

• Configuration files in postgresql

• Server configuration

• Connectivity configuration

• User auth. & privileges

  • Superuser
  • Creating normal users

• 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 3

• Data Dictionary

• System Catalog Schema

• System Information Tables

• System Information Functions

• Hands-on Exercise

• 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

• Continuous Archiving Concept

• Hands-on Exercise / implementation

Frequently asked questions