This training course takes the database administrator beyond the basics covered in the Essentials class. You will learn how to best fulfill the more advanced DBA responsibilities including database tuning and benchmarking, monitoring, setting up high availability and replication, and more. The concepts and architecture that support these more in-depth topics, along with the options available to carry them out, are covered in detail. This includes how to make the best use of tools available to more efficiently perform these tasks.
EPAS Advanced Database Administration gives you a unique opportunity to get trained and certified on advanced database administration for EPAS.
- Introduction:
- EDB PostgreSQL Platform
- Facts about PostgreSQL and EDB PostgreSQL Advanced Server
- Major Features
- Features of EDB PostgreSQL Advanced Server Database
- General Database Limits
- Common Database Object Names
- Prepare a Sample Database
- Database Monitoring:
- Database Monitoring
- Database Statistics
- The Statistics Collector
- Database Statistic Tables
- PostgreSQL Enterprise Manager (PEM)
- PEM – Features
- PEM – Architecture
- SQL Tuning:
- Statement Processing
- Common Query Performance Issues
- SQL Tuning Goals
- SQL Tuning Steps
- Identify Slow queries
- Review the query execution plan
- Optimizer statistics and behavior
- TABLESAMPLE clause
- Optimizer Hints
- Restructure SQL statements
- Indexes
- Performance Tuning:
- Performance Tuning – Overview
- Performance Monitoring using PEM
- Operating System Considerations
- Server Parameter Tuning
- Loading a Table into Memory
- Best Practices for Inserting Large Amount of Data
- Non – Durable Settings
- Tuning PostgreSQL Using PEM
- Performance Benchmarking:
- Database Benchmarking
- Benchmarking Tools
- Pgbench
- Preparing a database for pgbench
- Running a pgbench test
- Default Script
- Extensions:
- What are Extension modules?
- Installing Extension Modules
- Add Extension to a Database
- Extensions List
- Table Partitioning:
- Partitioning Benefits
- Partitioning Methods and Syntax
- Partition Pruning
- Adding New Partitioned Table
- Partitioning an Existing Table
- Moving and Dropping a Partition
- Handling Stray Values
- Information Views
- Connection Pooling:
- Connection Pooling Overview
- pgpool-II – Features
- pgpool II – Installation and Configuration
- Starting/Stopping pgpool-II
- pgbouncer – Features
- pgbouncer – Installation and Configuration
- Starting/Stopping pgbouncer
- Cloning:
- Database Cloning
- Creating a Clone Database
- How to Clone a Database using pg_dump
- EDB Clone Schema
- Setup EDB Clone Schema
- Install EDB Clone Schema
- Clone the schema
- High Availability:
- Data Replication
- Data Replication in PostgreSQL
- Streaming Replication and Architecture
- Synchronous, Asynchronous and Cascaded Replication Options
- Logical Replication
- Setup Streaming Replication
- Setup Cascaded Replication
- Monitoring and Recovery Control
- High Availability Setup Using EDB Failover Manager
- Database Security:
- Data Security Requirements
- Protection Plan
- PostgreSQL Security Options
- Added Security in EDB PostgreSQL Advanced Server
- Host Based Access Control
- SSL, Passwords, LDAP
- Authorized Access
- Row Level Security
- Data Encryption
- Best Practices
- Upgrading Best Practices:
- Version Change and Upgrade
- Need to Upgrade
- 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
- All attendees must attend this class in person. There is no remote access to the training.
Grab your seat! Fill the form to know course fees and details!
Other Training Courses
Designed for DBAs, Solution Architects, Application Developers, and IT Professionals; the Postgres Training Courses are…
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…
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…