Introduction to PostgreSQL Administration Course:

This course provides essential skills for managing PostgreSQL databases, covering installation, configuration, security, backup/recovery, and performance tuning. It is suitable for database administrators, developers, and IT professionals looking to specialize in PostgreSQL.

Prerequisites:

1. Operating System Fundamentals: Basic knowledge of Linux/Unix systems and command-line interface.

2.Basic Database Concepts: Understanding of database functions and basic SQL.

3.Networking Basics: Familiarity with IP addresses, ports, and firewalls.

4.Familiarity with Other Databases (Optional): Experience with relational databases like MySQL or Oracle.

Table of Content

1. Introduction
1.1 History of PostgreSQL
1.2 Major Features
1.3 New Features of PostgreSQL 9.2
1.4 Multi Version Concurrency Control
1.5 Write-Ahead Logging
1.6 Architectural Overview
1.7 Limits

2. PostgreSQL System Architecture
2.1 Architectural Summary
2.2 Shared Memory
2.3 Inter-process Communications
2.4 Statement Processing
2.5 Utility Processes
2.6 Disk Read Buffering
2.7 Write Buffering
2.8 Background Writer Cleaning Scan
2.9 Commit & Checkpoint
2.10 Physical Database Architecture
2.11 Data Directory Layout
2.12 Installation Directory Layout
2.13 Page Layout

3. Installation
3.1 OS User & Permissions
3.2 Installation
3.3 Setting environment variables
3.4 Clusters
3.5 Creating a database cluster
3.6 Starting and Stopping the Server (pg_ctl)
3.7 Connect to the server using psql

4. Configuration
4.1 Setting PostgreSQL Parameters
4.2 Access Control
4.3 Connection Settings
4.4 Security and Authentication Settings
4.5 Memory Settings
4.6 Query Planner Settings
4.7 WAL Settings
4.8 Log Management
4.9 Background Writer Settings
4.10 Statement Behavior
4.11 Vacuum Cost Settings
4.12 Autovacuum Settings

5. Creating and Managing Databases
5.1 Object Hierarchy
5.2 Creating Databases
5.3 Creating Schemas
5.4 Schema Search Path
5.5 Roles
5.6 Users
5.7 Groups
5.8 Access Control

6. Postgres Data Dictionary
6.1 The System Catalog Schema
6.2 System Information views/tables
6.3 System Information Functions

7. Introduction to PSQL
7.1 Introduction
7.2 Conventions
7.3 Connecting to PostgreSQL
7.4 PSQL Command Line Parameters
7.5 Entering PSQL Commands
7.6 PSQL Meta-Commands
7.7 PSQL SET Parameters
7.8 Information Commands

8. pgAdmin III
8.1 Registering a server
8.2 Viewing and Editing Data
8.3 Query Tool
8.4 Databases
8.5 Languages
8.6 Schemas
8.7 Domains
8.8 Functions
8.9 Sequences
8.10 Tables
8.11 Columns
8.12 Constraints
8.13 Indexes
8.14 Maintenance
8.15 Rules
8.16 Triggers
8.17 Types
8.18 Views
8.19 Table Space

9. Security Basics
9.1 Authentication
9.2 Authorization
9.3 Levels of security
9.4 pg_hba.conf file
9.5 Users
9.6 Object ownership
9.7 Access control
9.8 Application access parameters

10. SQL
10.1 Data Types
10.2 Tables
10.3 SQL Queries
10.4 Quoting
10.5 Using SQL Functions
10.6 Constraints
10.7 Dropping or Removing Database Objects
10.8 Views
10.9 Sequences
10.10 Indexes

11. Backup and Recovery & Point-in-Time Recovery
11.1 Backup Types
11.2 SQL Dump
11.3 Cluster Dump
11.4 Offline Copy Backup
11.5 Continuous Archiving
11.6 pg_basebackup
11.7 Point-In-Time Recovery
11.8 pg_upgrade

12. Routine Maintenance
12.1 Explain and Explain Analyze
12.2 Table Statistics
12.3 Updating Planner Statistics
12.4 Vacuuming
12.5 Scheduling Auto Vacuum
12.6 Preventing Transaction ID Wraparound Failures
12.7 The Visibility Map
12.8 Routine Reindexing

13. Moving Data
13.1 The COPY Command
13.2 Examples