PostgreSQL training

The PostgreSQL training aims to provide the delegate with the knowledge to be able to Administer the data held in a PostgreSQL database and to progress the SQL skills beyond the basics. 

By the end of this course..

you should be able to understand the concepts of PostgreSQL Admin and can run your production show on your own.

For more details mail us at : postgrestraining@gmail.com

Module - 1 : PostgreSQL Administration

  • Why PostgreSQL
  • ACID Properties
  • PostgreSQL Database Limits
  • How a connection is established in PostgreSQL
  • PostgreSQL Architecture
  • Architecture components
  • Background processes
  • Memory components
  • OS User & Permissions.
  • Installation Options.
  • Installation of PostgreSQL
  • Setting Environmental Variables.
  • Installing Extensions
  • Uninstallation
  • Database Cluster
  • Creating a Database Cluster
  • Starting and Stopping the Server (pg_ctl)
  • Connect to the Server Using psql
  • Post Installation Steps
  • Physical Database Architecture
  • Data Directory Layout
  • Installation Directory Layout
  • Page Layout
  • WAL files layout
  • Contents of Base directory including _vm,_fsm and Toast Table
  • Setting up postgresql.conf & pg_hba.conf
  • Understanding important PostgreSQL Parameters
  • understanding pg_hba.conf
  • Log management.
  • Creating Databases
  • Creating Tablespaces
  • Working with Tablespaces
  • Creating Users
  • Creating Roles
  • Creating Schemas
  • Schema search path
  • Logical backup using pg_dump
  • Logical backup using pg_dumpall
  • Continuous Archiving
  • Hot backups
  • How to use pg_basebackup for Online Backups
  • Point-In-Time Recovery Concepts
  • Recovery Example

Activities

  • task 1 – Backup and Restore in different machine

  • task 2 – Backup and Restore in different machine + apply archivelogs

  • task 3 – Backup and Restore in different machine + apply archivelogs + until my fav archive

  • task 4 – Backup and Restore in different machine + apply archivelogs + wait for next archive to come

  • task 5 – Backup and Restore in different machine + apply archivelogs + start streaming wals

  • task 6 – Backup and Restore in different machine + apply archivelogs + start streaming wals

  • task 7 : Synchronous replication

  • task 8 : Switchover and Failover

 

  • Database Maintenance.
  • Maintenance Tools.
  • Optimizer Statistics.
  • Data Fragmentation.
  • Routine Vacuuming.
  • Vacuuming Commands.
  • Preventing Transaction ID Wraparound Failures.
  • Vacuum Freeze.
  • Database monitoring.
  • The System Catalog Schema
  • System Information Tables
  • Loading flat files
  • Import and export data using COPY
  • Examples of COPY Command
  • Version Change and Upgrade
  • Need to Upgrade
  • Upgrade Plan
  • Upgrade from v15 to v16 Using pg_upgrade
  • Upgrading Best Practices
  • Grafana
  • pgAdmin
  • Accordion ContentPerformance Tuning – Overview
  • SQL Tuning
  • Database Tuning
  • Operating System Tuning
  • Tools for Tuning
  • Performance Monitoring using Grafana
  • Operating System Considerations
  • Server Parameter Tuning
  • Memory Parameters
  • Temporary File Parameters
  • WAL Parameters
  • Reading Explain Plan
  • Data Replication
  • Data Replication in PostgreSQL
  • Log-Shipping Architecture
  • Hot Streaming Architecture
  • Setup Replication Using Archive
  • Setup Streaming Replication
  • Prepare the Primary Server
  • Synchronous Streaming Replication Setup
  • Configure Authentication
  • Take a Full Backup of Primary Server
  • Setting up the Standby Server
  • Monitoring Hot Standby
  • Recovery Control Functions
  • Logical replication
  • Interview Questions
  • What’s Next

Module - 2* : AWS RDS/Aurora and Tools

Module – 1: Set up the environment manually

Module – 2: Set up the environment

  • Create VPC using CloudFormation template
  • Create Security groups using CloudFormation template
  • Create DBSubnet Group using CloudFormation template
  • Create RDS Oracle using CloudFormation template
  • Create Aurora PostgreSQL using CloudFormation template
  • Add aurora read replica to aurora using CloudFormation template

Module – 3: Working with Aurora instance

  • Create cluster parameter group
  • Create instance parameter group
  • Modify a parameter in cluster parameter group
  • Apply cluster parameter group and instance parameter group to cluster
  • Reboot the cluster (instance)

Module – 4: Preparing DMS

  • Create keypairs (download .ppk)
  • Create EC2 instance
  • Login to EC2 instance and Configure AWSCLI
  • Install and Setup environment (aws configure)
  • Install Oracle 19c client
  • Install PostgreSQL

Module 5 – DMS

  • Load hr schema to Oracle RDS
  • Prepare hr schema and RDS instance for CDC
  • Use SCT to migrate the metadata from RDS oracle to Aurora PostgreSQL
  • Create DB subnet groups in DMS page
  • Create Replication instance
  • Create source and target endpoints
  • Create task and ensure the replication is working
  • Create and verify oracle_fdw extension
  • Patroni for HA
  • PG-POOL for load balancing
  • pgBackRest for Backups
  • pgBouncer for Connection caching
  • Introduction to Terraform
  • Introduction to Python 3.9 for PostgreSQL
  • pgvector extension 

For more details or to join the course, write to : postgrestraining@gmail.com