Comprehensive PostgreSQL Administration

Postgres-Banner-Final-Web-Nov

postgres-training-mob-banner

Comprehensive PostgreSQL Administration

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.

Course Outline (5 day Training)

  • 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
  • PostgreSQL Installation:
  • OS User and Permissions
  • Installation Options
  • Installation of PostgreSQL
  • Initializing Database Instance
  • Database Cluster Defaults
  • Starting Database Instance
  • Connecting to Database
  • Setting Environmental Variables
  • 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
Read more

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!





Fill the form to get COURSE FEES and details.
 yes I understand and acknowledge the Ashnik Privacy Policy

Other Training Courses