Duration
PostgreSQL Essential 16
5 days
Introduction
History of PostgreSQL
Major Features
New Features of PostgreSQL
Multi Version Concurrency Control
Write-Ahead Logging
Architectural Overview
Limits
PostgreSQL System Architecture
Architectural Summary
Shared Memory
Inter-process Communications
Statement Processing
Utility Processes
Disk Read Buffering
Write Buffering
Background Writer Cleaning Scan
Commit & Checkpoint
Physical Database Architecture
Data Directory Layout
Installation Directory Layout
Page Layout
Installation
OS User & Permissions
Installation
Setting environment variables
Clusters
Creating a database cluster
Starting and Stopping the Server (pg_ctl)
Connect to the server using psql
Using SQL
Data Types
Tables
SQL Queries
Quoting
Using SQL Functions
Constraints
Dropping or Removing Database Objects
Views
Sequences
Indexes
Creating and Managing Databases
Object Hierarchy
Creating Databases
Creating Schemas
Schema Search Path
Roles
Users
Groups
Access Control
Introduction to PSQL
Introduction
Conventions
Connecting to PostgreSQL
PSQL Command Line Parameters
Entering PSQL Commands
PSQL Meta-Commands
PSQL SET Parameters
Information Commands
Configuration
Setting PostgreSQL Parameters
Access Control
Connection Settings
Security and Authentication Settings
Memory Settings
Query Planner Settings
WAL Settings
Log Management
Background Writer Settings
Statement Behavior
Vacuum Cost Settings
Autovacuum Settings
Security basics
Authentication
Authorization
Levels of security
pg_hba.conf file
Users
Object ownership
Access control
Application access parameters
Routine Maintenance
Explain and Explain Analyze
Table Statistics
Updating Planner Statistics
Vacuuming
Scheduling Auto Vacuum
Preventing Transaction ID Wraparound Failures
The Visibility Map
Routine Reindexing
Transactions and Concurrency
Transaction Definition
Effects of Concurrency on Transactions
Transaction Isolation Levels
Multi-Version Concurrency Control Overview (MVCC)
MVCC Example
Internal Identifiers
Transaction Wraparound
MVCC Maintenance
Performance Tuning
Hardware Configuration
OS Configuration
Server Parameter Tuning
Connection Settings
Memory Parameters
Memory settings for Planner
WAL Parameters
Explain Plan
Explain Example
Statistics Collection
Indexes
Examining Index Usage
Tips for Inserting Large Amount of Data
Some Notes About pg_dump
Non-Durable Settings
Monitoring
Database Monitoring
Database Statistics
The Statistics Collector
Database Statistic Tables
Operating System Process Monitoring
Current Sessions and Locks
Log Slow Running Queries
Disk Usage
Postgres Enterprise Manager
PEM - Features
PEM - Architecture
Backup and Recovery & Point-in Time Recovery
Backup Types
SQL Dump
Cluster Dump
Ofline Copy Backup
Continuous Archiving
pg_basebackup
Point-In Time Recovery
pg_upgrade
Replication & Failover
Database High Availability
Causes of Data Loss
Plan for Common Errors
Selection Criteria
High Availability Options
Hot Streaming Replication, Architecture and Setup
Streaming Replication Example
Slony-I Replication, Architecture and Setup
Switchovers and Failovers
Replication Limitations
Slony Replication Example
pgAdmin III
Registering a server
Viewing and Editing Data
Query Tool
Databases
Languages
Schemas
Domains
Functions
Sequences
Tables
Columns
Constraints
Indexes
Maintenance
Rules
Triggers
Types
Views
Tablespaces
Roles
Programme scope, delivery arrangements and suitability are confirmed during enquiry.