PostgreSQL Administration Bespoke Certificate for Arman Gabriel Lucilo

Introduction

PostgresSQL Setup and Configuration

Overview of PostgreSQL Architecture and Advanced Features

PostgreSQL Administration

Connecting to the Database

• Overview of PgAdmin III
• Configuring the Server
• Setting up and Managing Users
• Overview of Database Object Types
• Setting Permissions & Security
• Querying the Database
• Monitoring the Database
• Ensuring Atomicity and Isolation
• Backing up and Restoring PostreSQL
• Migrating Data to Another System
• Working with PostreSQL Logs

Performance Optimization

• Reducing I/O with HOT
• Locking & Concurrency
• Evaluating the Design of the Database
• Optimizing SQL Queries with EXPLAIN
• Inspecting the Workload
• Benchmarking and Profiling
• Optimzing Joins
• Using Indexes Efficiently

PostgreSQL Replication

• Overview of PostgreSQL Replication
• High Availability in PostgreSQL
• Streaming Replication
• Managing Clusters Using repmgr
• Running Queries on Database in Hot Standby
• Synchronous Replication
• File based Log Shipping
• Trigger-based Logical Replication with Slony & Londiste
• Connection Pooling
• Upgrading PostgreSQL
• Sharding and Scaling PostgreSQL

Query Tuning

• Logging in PostgreSQL
• Query Plans
• Statistics
• SQL Best Practices
• Indexes
• Table Partitioning

Performance Tuning

• Operating System Best Practices
• Server Parameter Tuning
• Extension – pg_stat_statements
• Demo and Labs

Benchmarking

• Database Benchmarking
• Pgbench
• Reading the Results
• Demo and Labs

The SQL Language

• SQL Syntax
• Data Definition
• Data Manipulation
• Queries
• Data Types
• Functions and Operators
• Type Conversion
• Indexes
• Full Text Search
• Concurrency Control
• Performance Tips

Server Administration

• Installation from Source Code
• Installation from Source Code on Windows
• Server Setup and Operation
• Server Configuration
• Client Authentication
• Database Roles
• Managing Databases
• Localization
• Routine Database Maintenance Tasks
• Backup and Restore
• High Availability, Load Balancing, and Replication
• Recovery Configuration
• Monitoring Database Activity
• Monitoring Disk Usage
• Reliability and the Write-Ahead Log
• Regression Tests

Client Interfaces

• libpq - C Library
• Large Objects
• ECPG - Embedded SQL in C
• The Information Schema

Server Programming

• Extending SQL
• Triggers
• The Rule System
• Procedural Languages
• PL/pgSQL - SQL Procedural Language
• PL/Tcl - Tcl Procedural Language
• PL/Perl - Perl Procedural Language
• PL/Python - Python Procedural Language
• Server Programming Interface

Internals

• Overview of PostgreSQL Internals
• System Catalogs
• Frontend/Backend Protocol
• PostgreSQL Coding Conventions
• Native Language Support
• Writing A Procedural Language Handler
• Writing A Foreign Data Wrapper
• Genetic Query Optimizer
• Index Access Method Interface Definition
• GiST Indexes
• GIN Indexes
• Database Physical Storage
• BKI Backend Interface
• How the Planner Uses Statistics