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.
The 3-day paid training gets you certified on Foundations of PostgreSQL Administration
- 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
- 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
- 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
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-day full training
- Delivered online by our certified Postgres trainer
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…
EDB PostgreSQL Advanced Server Database Administration – Essentials is a five-day course that focuses on teaching…
This training course takes the database administrator beyond the basics covered in the Essentials class. You will learn how to best…
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…