Designed for DBAs, Solution Architects, Application Developers, and IT Professionals; the Postgres Training Courses are delivered by certified PostgreSQL experts who will coach you to manage your database responsibilities with ease.
The 5-day PAID training gets you certified on Comprehensive PostgreSQL Administration
Course includes basics of PostgreSQL Administration and some key advanced concepts. The training will enable you to handle day-to-day Administrative and Management tasks as a PostgreSQL DBA. And, scale up to manage large and highly available databases with ease and confidence. This 5-day course is designed for professionals with experience in database administration but are new to the PostgreSQL technology.
- Introduction and Architectural Overview:
- EDB PostgreSQL Platform
- History of PostgreSQL
- Major Features
- EDB PostgreSQL Advanced Server Features
- Architectural Overview
- General Database Limits
- Common Database Object Names
- System Architecture:
- Architectural Summary
- Process & Memory Architecture
- Utility Processes
- Connection Request-Response
- Disk Read Buffering
- Disk Write Buffering
- Background Writer Cleaning Scan
- Commit & Checkpoint
- Statement Processing
- Physical Database Architecture
- Data Directory Layout
- Installation Directory Layout
- Page Layout
- Introduction and Architectural Overview:
- EDB PostgreSQL Platform
- History of PostgreSQL
- Major Features
- EDB PostgreSQL Advanced Server Features
- Architectural Overview
- General Database Limits
- Common Database Object Names
- Database Clusters:
- Database Clusters
- Creating a Database Cluster
- Starting and Stopping the Server (pg_ctl)
- Connect tthe Server Using psql
- 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
- Parallel Query Scan Settings
- Parallel Maintenance Settings
- Vacuum Cost Settings
- Autovacuum Settings
- Just-in-Time Compilation
- Configuration File Includes
- Creating and Managing Databases:
- Object Hierarchy
- Creating Databases
- Users and Roles
- Access Control
- Creating Schemas
- Schema Search Path
- User Tools – Command Line Interfaces:
- Introduction tpsql
- Conventions
- Connecting tDatabase
- psql Command Line Parameters
- Entering psql Commands
- psql Meta-Commands
- psql SET Parameters
- Conditional Commands
- Information Commands
- GUI Tools:
- Introduction tpgAdmin 4
- Installing pgAdmin 4 on Linux
- Registering a server
- Viewing and Editing Data
- Query Tool
- Databases
- Languages
- Schemas
- Database Objects
- Maintenance
- Tablespaces
- Roles
- Introduction tPEM Web Client
- Security:
- Authentication and Authorization
- Levels of Security
- pg_hba.conf File
- Row Level Security
- Object Ownership
- Application Access Parameters
- Protecting Against Injection Attacks with SQL/Protect
- Source Code Protection for Functions
- SQL Primer:
- Data Types
- Structured Query Language (SQL)
- DDL, DML and DCL Statements
- Transaction Control Statements
- Tables and Constraints
- Views and Materialized Views
- Sequences
- Domains
- SQL Joins
- Using SQL Functions
- SQL Format Functions
- Quoting in PostgreSQL
- Indexes
- Backup and Recovery & Point-in Time Recovery:
- Backup Types
- Database SQL Dumps
- Restoring SQL Dumps
- Offline Physical Backups
- Continuous Archiving
- Online Physical Backups Using pg_basebackup
- Point-in-time Recovery
- Recovery Settings
- Routine Maintenance:
- Database Maintenance
- Maintenance Tools
- Optimizer Statistics
- Data Fragmentation
- Routine Vacuuming
- Vacuuming Commands
- Preventing Transaction ID Wraparound Failures
- Vacuum Freeze
- The Visibility Map
- Vacuumdb
- Autovacuuming
- Per Table Thresholds
- Routine Reindexing
- CLUSTER
- Data Dictionary:
- The System Catalog Schema
- System Information Tables
- System Information Functions
- System Administration Functions
- System Information Views
- Moving Data:
- Loading flat files
- Import and export data using COPY
- Examples of COPY Command
- Using COPY FREEZE for performance
- Introduction tEDB*Loader for EDB PostgreSQL Advanced Server
- Database Monitoring:
- Database Monitoring
- Database Statistics
- The Statistics Collector
- Database Statistic Tables
- PostgreSQL Enterprise Manager (PEM)
- PEM – Features
- PEM – Architecture
- Monitoring Utilities
- Statement Processing
- Common Query Performance Issues
- SQL Tuning Goals
- SQL Tuning Steps
- Identify slow queries
- Review the query execution plan
- Optimizer statistics and behavior
- Restructure SQL statements
- Indexes
- Performance Tuning:
- Performance Tuning – Overview
- Performance Monitoring using PEM
- A Tuning Technique
- Operating System Considerations
- Server Parameter Tuning
- Loading a Table intMemory
- Best Practices for Inserting Large Amount of Data
- Non-Durable Settings
- Tuning PostgreSQL Using PEM
- High Availability:
- Data Replication
- Data Replication in PostgreSQL
- Streaming Replication and Architecture
- Synchronous, Asynchronous and Cascading Replication Options
- Logical Replication
- Setup Streaming Replication
- Setup Cascaded Replication
- Monitoring and Recovery Control
- High Availability Setup Using EDB Failover Manager
- Connection Pooling:
- Connection Pooling Overview
- pgpool-II – Features
- pgpool II – Installation and Configuration
- Starting/Stopping pgpool-II
- Table Partitioning:
- Partitioning
- Partitioning Methods
- When tPartition
- Partitioning Methods
- Partitioning Setup
- Attach and Detach Partitions
- Partition Pruning
- Partitioned Table Limitations
- Extension Modules:
- What are Extension modules?
- Installing Extension Modules
- Add Extension ta Database
- Extensions List
- Upgrading Best Practices:
- Version Change and Upgrade
- Need tUpgrade
- Upgrade Plan
- Upgrade Using pg_upgrade
- Upgrading Best Practices
Training Pre-requisites
- Basic experience in database administration and knowledge of RDBMS concepts
- Basic experience with the use of the Linux operating system is helpful
Delivery Approach
- 5-day full training
- Delivered online by our certified Postgres Trainer
Grab your seat! Fill the form to know course fees and details!
Other Training Courses
Learn the essential details of PostgreSQL Administration including architecture, configuration, maintenance, monitoring, backup…
EDB PostgreSQL Advanced Server Database Administration – Essentials is a five-day course that focuses on teaching…
This training course takes the database administrator beyond the basics covered in the Essentials class. You will learn how to best…
Designed for DBAs, Architects, Application Developers, and IT Professionals, our training courses are created…
This 2-day course covers the suite of EDB tools designed for large scale and mission critical enterprise performance, availability…