Foundations of PostgreSQL Administration – PGSQL

postgres-training-post-banner

docker
mongoDB

Overview

In this course you will learn the essential details of PostgreSQL Administration including architecture, configuration, maintenance, monitoring, backup, recovery, and data movement. It will also introduce you to enterprise class tools for PostgreSQL administration and will prepare you to handle real time, production environments. It is designed for people with experience in database administration who are new to PostgreSQL.
This class conveys the knowledge you will need to perform your duties as a PostgreSQL DBA, and is the foundation for the advanced courses that follow in the DBA track.


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

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

Interested? Fill the Inquire Now form.

OR
Write to us on training@ashnik.com.