Content
- Introduction and Architectural Overview
1.1 EDB Portfolio
1.2 History of PostgreSQL
1.3 Major Features
1.4 Architectural Overview
1.5 General Database Limits
1.6 Common Database Object Names - System Architecture
2.1 Architectural Summary
2.2 Process and Memory Architecture
2.3 Utility Processes
2.4 Connection Request-Response
2.5 Disk Read Buffering
2.6 Disk Write Buffering
2.7 Background Writer Cleaning Scan
2.8 Commit and Checkpoint
2.9 Statement Processing
2.10 Physical Database Architecture
2.11 Data Directory Layout
2.12 Installation Directory Layout
2.13 Page Layout - PostgreSQL Installation
3.1 Deployment Options
3.2 OS User and Permissions
3.3 Package Installation
3.4 Installation Example and Practice Labs
3.5 Setting Environmental Variables - User Tools – Command Line Interfaces
4.1 Introduction to psql
4.2 Connecting to Database
4.3 psql Command Line Parameters
4.4psql Meta-Commands
5.4 Conditional and Information Commands - Database Clusters
5.1 Database Clusters
5.2 Creating a Database Cluster
5.3 Starting and Stopping the Server (pg_ctl)
5.4 Connecting to the Server Using psql - Configuration
6.1 Server Parameter File – postgresql.conf
6.2 Viewing and Changing Server Parameters
6.3 Configuration Parameters – Security, Resources and WAL
6.4 Configuration Parameters – Error Logging, Planner and Maintenance
6.5 Viewing Compilation Settings
6.6 Using File Includes - Data Dictionary
7.1 The System Catalog Schema
7.2 System Information Tables and Views
7.3 System Information and Administration Functions - Creating and Managing Databases
8.1 Object Hierarchy
8.2 Users and Roles
8.3 Tablespaces
8.4 Databases
8.5 Access Control
8.6 Creating Schemas
8.7 Schema Search Path - Database Security
9.1 Database Security Requirements and Protection Plan
9.2 Levels of Security in Postgres
9.3 Access Control using pg_hba.conf
9.4 Introduction to Row Level Security
9.5 Data Encryption
9.6 General Security Recommendations - Monitoring and Admin Tools
10.1 Overview and Features of pgAdmin
10.2 Access pgAdmin
10.3 Register and Connect to a Database Server
10.4 General Database Administration
10.5 Object Browser – View Data, Query Tool, Server Status
10.6 Overview of Postgres Enterprise Manager - SQL Primer
11.1 Data Types
11.2 Structured Query Language
11.3 DDL, DML and DCL Statements
11.4 Transaction Control Statements
11.5 Tables and Constraints
11.6 Views and Materialized Views
11.7 Sequences
11.8 Domains
11.9 SQL Joins and Functions
11.10 Explain Plans
11.11Quoting in PostgreSQL
11.12 Indexes - Backup, Recovery and PITR
12.1 Backup Types
12.2 Database SQL Dumps
12.3 Restoring SQL Dumps
12.4 Offline Physical Backups
12.5 Continuous Archiving
12.6 Online Physical Backups Using pg_basebackup
12.7 Point-in-time Recovery
12.8 Recovery Settings
12.9 Backup Tools – Barman and pgBackRest - Routine Maintenance Tasks
13.1 Updating Optimizer Statistics
13.2 Handling Data Fragmentation using Routine Vacuuming
13.3 Preventing Transaction ID Wraparound Failures
13.4 Automatic Maintenance using Autovacuum
13.5 Re-indexing in Postgres - Moving Data Using Copy Command
14.1 Loading Flat Files
14.2 Import and Export Data Using Copy
14.3 Examples of Copy Command
14.4 Using Copy Freeze for Performance - Replication and High Availability Tools
15.1 Data Replication
15.2 Data Replication in Postgres
15.3 Streaming Replication and Architecture
15.4 Synchronous, Asynchronous and Cascaded Replication
15.5 Setup Streaming Replication
15.6 Logical Replication Architecture
15.7 Overview: EDB Postgres Distributed, EDB Failover Manager, Replication Server and Replication Manager (repmgr)
