What Will Learn?
Part 1: Introduction (time required – 25-30 Min)
- History of PostgreSQL
- Major Features
- New Features of PostgreSQL
- Multi-Version ConcurrencyControl
- Write-AheadLogging
- ArchitecturalOverview
- Limits
Part 2: PostgreSQL System Architecture (time require – 1:00 – 1:30 hours)
- Architectural Summary
- Shared Memory
- Statement Processing
- Utility Processes
- Disk Read Buffering
- Write Buffering
- Background Writer CleaningScan
- Commit & Checkpoint
- Physical DatabaseArchitecture
- Data Directory Layout
- Installation Directory Layout
- Page Layout
Curriculum
- 1 Section
- 16 Lessons
- 10 Weeks
Expand all sectionsCollapse all sections
- 16
- 1.1Installation (Time Required – 1:30 Hours)
- 1.2Configuration (time require – 2 – 2:30 hours) Setting PostgreSQLParameters Access Control Connection Settings Security and authentication settings Memory Settings Query Planner Settings WAL Settings Log Management Background Writer Settings StatementBehavior Vacuum Cost Settings Auto vacuum SettingsCopy
- 1.3Creating and Managing Databases, Schemas, Users, and Tablespaces (time require – 30-45 Min) Object Hierarchy Creating Databases Creating Schemas Schema Search Path Roles, Users &Groups Access ControlCopy
- 1.4Postgres Data Dictionary (time require – 15-20 Min) The System Catalog Schema System Informationviews/tables System InformationFunctionsCopy
- 1.5(time require – 15-20 Min) Registering a server Query Tool Databases Schemas IndexesCopy
- 1.6Security Basics (time require – 30-50 Min) Authentication Authorization Levels of security pg_hba.conf file Users Object ownership Access control Application access parametersCopy
- 1.7Backup and Recovery & Point-in-Time Recovery (time require – 2:00 hours) Backup Types pg_dump pg_dumpall pg_restore Cluster Dump Offline Copy Backup Continuous Archiving pg backrest pg_basebackup Point-In TimeRecovery BARMANCopy
- 1.8Routine Maintenance (time require – 30 Mins) Explain and Analyze Table Statistics Updating Planner Statistics Vacuuming Scheduling Auto Vacuum Preventing Transaction ID WraparoundFailures The Visibility Map Routine ReindexingCopy
- 1.9Moving Data (time require – 15 Mins) The COPY Command ExamplesCopy
- 1.10Transactions and Concurrency (time require – 45 Min) Transaction Definition Effects of Concurrency on Transactions Transaction Isolation Levels Multi-Version Concurrency Control Overview (MVCC) MVCC Example Internal Identifiers Transaction Wraparound MVCC Maintenance MVCC DemoCopy
- 1.11Performance Tuning (time require – 2 hours) Hardware Configuration OS Configuration Server parameter tuning Connection Settings MemoryParameters Memory settings for planner WAL Parameters Explain Plan Explain Example Statistics Collection Indexes Examining Index Usage Tips for Inserting Large Amounts of Data Some Notes About pg_dump Non-Durable SettingsCopy
- 1.12Replication & Failover (time require – 3 hours) 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 Repair installation and Configuration Switchover Using repmgr Promote using repmgr Automatic Failover using repmgrd Troubleshooting ReplicationLagCopy
- 1.13Table Partitioning (time require – 45 Min) Partitioning Partitioning Methods When to Partition Partitioning Setup Partitioning Example Partitioning and Constraint Exclusion CaveatsCopy
- 1.14Different Upgrade Methods (time require – 45 Min) pg_dump/pg_restore Upgrading PostgreSQL Database Migration from Oracle to PostgresCopy
- 1.15Database Monitoring (time require – 45 Min) Database Statistics The Statistics Collector Database Statistic Tables Operating System ProcessMonitoring Current Sessions and Locks Log running queries Disk UsageCopy
- 1.16Other Tools – All Tools and Topics Covered with Practicals pg badger (time require – 30 Min) PGA admin (time require – 25 Min) pg bouncer (time require – 30 Min) pg pool (time require – 30 Min) repair (time require – 1 hour) ora2pg BARMAN pg_backrestCopy
