KEMBAR78
PostgreSQL Syllabus | PDF | Postgre Sql | Databases
Scribd
Upload
511 views6 pages

PostgreSQL Syllabus

This document outlines an intensive 40-hour PostgreSQL administration training offered by Aalok Institute. The training covers topics such as PostgreSQL installation, configuration, security, backup and recovery, performance tuning, replication, and monitoring. It is divided into 18 parts that include hands-on lessons and practical exercises on topics such as database architecture, installation, configuration, security basics, backup strategies, routine maintenance, and monitoring. The goal is to provide comprehensive training to help administrators effectively manage and optimize PostgreSQL databases.

Uploaded by

vinodsalunkhevnd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
511 views6 pages

PostgreSQL Syllabus

This document outlines an intensive 40-hour PostgreSQL administration training offered by Aalok Institute. The training covers topics such as PostgreSQL installation, configuration, security, backup and recovery, performance tuning, replication, and monitoring. It is divided into 18 parts that include hands-on lessons and practical exercises on topics such as database architecture, installation, configuration, security basics, backup strategies, routine maintenance, and monitoring. The goal is to provide comprehensive training to help administrators effectively manage and optimize PostgreSQL databases.

Uploaded by

vinodsalunkhevnd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

AALOK INSTITUTE

PostgreSQL Administration Training

Hands-on Training on PostgreSQL


Duration: 40Hrs

Part 1: Introduction

 History of PostgreSQL
 Major Features
 New Features of PostgreSQL
 Multi Version Concurrency Control
 Write-Ahead Logging
 Architectural Overview
 Limits

Part 2: PostgreSQL System Architecture

 Architectural Summary
 Shared Memory
 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

Part 3: 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

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com
AALOK INSTITUTE

 Connect to the server using PGAdmin

Part 4: 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

Part 5: Creating and Managing Databases,Schemas,Users,Tablespaces

 Object Hierarchy
 Creating Databases
 Creating Schemas
 Schema Search Path
 Roles, Users & Groups
 Access Control

Part 6: Postgres Data Dictionary

 The System Catalog Schema


 System Information views/tables
 System Information Functions

Part 7:

 Registering a server
 Query Tool
 Databases
 Schemas
 Indexes

Part 8: Security Basics

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com
AALOK INSTITUTE

 Authentication
 Authorization
 Levels of security
 pg_hba.conf file
 Users
 Object ownership
 Access control
 Application access parameters

Part 9: Backup and Recovery & Point-in Time Recovery

 Backup Types
 pg_dump
 pg_dumpall
 pg_restore
 Cluster Dump
 Offline Copy Backup
 Continuous Archiving
 pg_basebackup
 Point-In Time Recovery
 BARMAN

Part 10: 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

Part 11: Moving Data

 The COPY Command


 Examples

Part 12: Transactions and Concurrency

 Transaction Definition

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com
AALOK INSTITUTE

 Effects of Concurrency on
 Transactions
 Transaction Isolation Levels
 Multi-Version Concurrency Control
 Overview (MVCC)
 MVCC Example
 Internal Identifiers
 Transaction Wraparound
 MVCC Maintenance
 MVCC Demo

Part 13: 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

Part 14: 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
 repmgr Installation and Configuration
 Switchover Using repmgr
 Promote using repmgr

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com
AALOK INSTITUTE

 Automatic Failover using repmgrd


 Troubleshooting Replication Lag

Part 15: Table Partitioning

 Partitioning
 Partitioning Methods
 When to Partition
 Partitioning Setup
 Partitioning Example
 Partitioning and Constraint
 Exclusion
 Caveats

Part 16: Different Upgrade Methods

 pg_dump/pg_restore
 Upgrading PostgreSQL Database
 Migration from Oracle to Postgres

Part 17: Database Monitoring

 Database Statistics
 The Statistics Collector
 Database Statistic Tables
 Operating System Process Monitoring
 Current Sessions and Locks
 Log Slow Running Queries
 Disk Usage

Part 18: Other Tools – All Tools and Topics covered with Practical’s

 pgbadger
 pgadmin
 pgbouncer
 pgpool
 repmgr
 ora2pg
 BARMAN
 pg_backrest

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com
AALOK INSTITUTE

For Any Clarification please feel free to contact @ +91 9867328291.

Cheers,
Ambasa S. Ladwa
Ph# +91 9867328291
Ambasa.Ladwa@aalokinstitute.com

Head Office : No. 602, 6th Floor, G Square Business Park, Plot No-25 & 26, Sector No-30,Landmark Opposite Sanpada Station, Vashi, Navi
Mumbai – 400705.
Tel.: +022-4131 8291 | +022-4923 8291 Mobile/Whatsapp : +91 86550 15955
Email: training@aalokinstitute.com | Website: www.aalokinstitute.com

You might also like