Foundations of PostgreSQL Administration – PGSQL

edbTrainingBanner

edbTrainingBannerM

Foundations of PostgreSQL Administration – 12 Oct to 14 Oct 2020

Learn the essential details of PostgreSQL Administration including architecture, configuration, maintenance, monitoring, backup, recovery, and data movement. The course introduces you to enterprise class tools for PostgreSQL administration and will prep you to handle real time production environments. It’s designed for professionals who have experience in database administration, but are new to PostgreSQL.

This training program provides the expertise to perform your duties as a PostgreSQL DBA and is the foundation for the advanced courses that follow in the DBA track.


Course Outline (3 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 a Database
  • Setting Environmental Variables
  • Database Clusters:
  • Database Clusters
  • Creating a Database Cluster
  • Starting and Stopping the Server (pg_ctl)
  • Connect to the 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 to psql
  • Conventions
  • Connecting to Database
  • psql Command Line Parameters
  • Entering psql Commands
  • psql Meta-Commands
  • psql SET Parameters
  • Conditional Commands
  • Information Commands
  • GUI Tools:
  • Introduction to pgAdmin 4
  • Installing pgAdmin 4 on Linux
  • Registering a server
  • Viewing and Editing Data
  • Query Tool
  • Databases
  • Languages
  • Schemas
  • Database Objects
  • Maintenance
  • Tablespaces
  • Roles
  • Introduction to PEM Web Client
  • Security:
  • Authentication and Authorization
  • Levels of Security
  • pg_hba.conf File
  • Row Level Security
  • Object Ownership
  • Application Access Parameters
  • SQL Primer:
  • Data Types
  • Structured Query Language (SQL)
  • DDL, DML and DCL Statements
  • Transaction Control Statements
  • Tables and Constraints
  • View 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
  • Continous Archiving
  • Online Physical Backups Using pg_basebackup
  • Point-in-Time Recovery
  • Recovery Settings
  • Routine Maintenance Tasks:
  • 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 to EDB*Loader for EDB PostgreSQL Advanced Server

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

  • 3 full day Training
  • Delivered online by our certified Postgres trainer

Keen to attend? Fill the Inquire Now form.





Fill the form and we’ll get back with training fees and more details.

Other Training Courses