POSTGRESQL FUNDAMENTALS TO INTERMEDIATE

COURSE DESCRIPTION

PostgreSQL is a powerful, open-source object-relational database system. It has about two (2) decades of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

This class will give you guide to the Basic, Advance and Complete PostgreSQL Administration.
It will cover modules on PostgreSQL Introduction, System Architecture, Database Clusters, Configuration, Creating and Managing Databases, User Tools – Command Line Interface, GUI Tools, PostgreSQL Security, SQL Primer, Backup & Recovery, Routine Maintenance, Data Dictionary, Tablespaces, Performance Tuning, Pgpool, Transactions & Concurrency, Table Partitioning, Upgrading PostgreSQL, Data Loading / Unloading and Database Replication.

PREREQUISITES

• Basic and/or prior experience with database administration will be an advantage
• Familiarity with database concepts and terminologies.
• Familiarity with Linux operating systems is recommended but not required

TARGET AUDIENCE

This course is designed for:
• Database Administrators (DBAs) looking to build, develop, and broaden their PostgreSQL knowledge.
• System Administrators who will be responsible for managing PostgreSQL databases.
• IT Professionals seeking to build and enhance their skills in database management.
• Developers and Engineers who work with PostgreSQL and require foundational to interdmediate knowledge for optimizing and securing databases.
• Anyone interested in gaining knowledge on the functionalities of PostgreSQL to support from small, medium to enterprise-level database environments.

COURSE OUTLINE

Module 1 – PostgreSQL Introduction
• History of PostgreSQL
• Major Features
• Architectural Overview
• General Database Limits
• Common Database Object Names

Module 2- System Architecture
• Architectural Summary
• Process and Memory Architecture
• Utility Processes
• Connection Requests-Response
• Buffering
• Background Writer Cleaning
• Commit and Checkpoint
• Statement Processing
• Physical Database Architecture
• Data Directory Layout
• Installation Directory Layout
• Page Layout

Module 3 – Database Clusters
• Introduction
• Creating a Database Cluster
• Starting and Stopping PostgreSQL Server
• Connecting the Server Using psql

Module 4 – Configuration
• Setting PostgreSQL Parameters
• Access Control
• Connection Settings
• Security & 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
• Configuration Files

Module 5 – Creating & Managing Databases
• Object Hierarchy
• Creating Databases
• Users and Roles
• Access Control
• Creating Schemas
• Schema Search Path

Module 6 – User Tools – Command Line Interface
• Introduction to Psql
• Connecting to Databases
• PSql command Line Parameters
• Entering psql commands
• Psql Meta Commands
• Psql set Parameters
• Conditional Commands
• Information Commands

Module 7 – GUI Tools
• Introduction to Pgadmin
• Installing Pgadmin
• Registering a Server
• Viewing & Editing Data
• Query Tool
• Databases
• Languages
• Schemas
• Database Objects
• Maintenance
• Tablespaces
• Roles

Module 8 – PostgreSQL Security
• Authentication & Authorization
• Levels of Security
• Pg_hba.conf file
• Row Level Security
• Object Ownership
• Host Based Access Control
• Pgcrypto Module
• Application access parameters

Module 9 – 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
• Indexes

Module 10 – Backup & 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

Module 11 – Routine Maintenance
• Database Maintenance
• Maintenance Tools
• Optimizer Statistics
• Data Fragmentation
• Routine Vacuuming
• Vacuuming Commands
• The Visibility Map
• Vacuumdb
• Autovacuuming
• Per Table Thresholds
• Routine Reindexing
• CLUSTER

Module 12 – Data Dictionary
• The System Catalog Schema
• System Information Tables
• System Information Functions
• System Administration Functions
• System Information Views

Module 13 – Tablespaces
• Tablespaces & Data Files
• pg_global 7 pg_default
• Advantage of Tablespaces
• Creating a Tablespace
• Changing Default Tablespace
• Altering Tablespaces
• Dropping Tablespaces

Module 14 – Performance Tuning
• Hardware Configuration
• OS Configuration
• SQL Tuning
• Query Plan
• Advanced Performance Tuning Settings

Module 15 – Pgpool
• Introduction to Pgpool
• Connection Pooling
• Replication
• Load Balancing
Module 16 – Transactions & Concurrency
• Transactions
• Concurrency & Transactions
• Concurrency & Transactions

Module 17 – Table Partitioning
• Partitioning
• Partitioning Methods
• Partitioning Methods
• Partitioning Setup
• Attach and Detach Partitions
• Partition Pruning
• Partitioned Table Limitations

Module 18 – Upgrading PostgreSQL
• Version Change and Upgrade
• Need to Upgrade
• Upgrade Plan
• Upgrade Using pg_upgrade
• Upgrading Best Practices

Module 19 – Data Loading / UnLoading
• Loading flat files
• Import and export data using COPY
• Examples of COPY Command

Module 20 – Database Replication
• Configuring Streaming Replication
• Configuring Synchronous Replication
• Configuring Asynchronous Replication
• Logical Replication in PostgreSQL

Module 21 – Columnar Tables and Storage in PostgreSQL
• Columnar Storage Introduction
• Citus Installation
• Citus Installation on Multiple Nodes
• Creating Distributed Tables
• Creating Distributed Tables with Colocation
• Creating Reference Tables
• Creating Tables with Columnar Storage
• Operations on Columnar Tables

Please contact us for schedules and for booking a private class.

Inquire Now