Comprehensive PostgreSQL Administration

postgres-training-post-banner

docker
mongoDB

PostgreSQL Training

Designed for DBAs, Architects, Application Developers, and IT Professionals, our training courses are created and delivered by experienced & Certified PostgreSQL experts who will show you how to quickly manage your database responsibilities with ease.

We offer training in 2 convenient formats:

  • Classroom – scheduled, open-enrolment public training batch at a specified city or location
  • On-Site – delivered on-premise at your company location for your team

HANDS-ON EXPERIENCE

Relevant and up-to date material and labs enable you to quickly put your new skills into practice in your own environment. All training includes modularized lessons, lecture, presentation copy, and real hands-on lab exercises.

COMPREHENSIVE POSTGRESQL ADMINISTRATION :

The COMPREHENSIVE POSTGRESQL ADMINISTRATION course includes basics of PostgreSQL Administration and certain key advanced concepts.This training will help you prepare for day-to-day Administrative and Management tasks to be performed as a PostgreSQL DBA; and slowly scale up to manage large and highly available databases, with ease and confidence. This 5-day course is designed for people with experience in database administration but are new to PostgreSQL.


Course Outline:

  • 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:

  • All attendees must attend this class in person. There is no remote access to the training.

Interested? Fill the Inquire Now form.





Just fill in your specifics and we will get in touch with you, with the course fees and other details.
captcha

OR
Write to us on training@ashnik.com.