(Click Category to List Courses)

38 - IT-D Information Technology - Databases and Related Technologies


IT-D 255B - PostgreSQL Architecture and Advanced Features (7 Days)

Code Start Date Duration Venue
IT-D 255B 29 August 2022 7 Days Istanbul Registration Form Link
IT-D 255B 24 October 2022 7 Days Istanbul Registration Form Link
IT-D 255B 19 December 2022 7 Days Istanbul Registration Form Link
Please contact us for fees

 

Course Description

PostgreSQL is one of the most advanced open-source platforms available today. Multiple large-scale organizations such as Cisco and Apple use the tool for their web-based and mobile-based application development. It is managed by an open-source community that is dedicated to improving the platform to keep it relevant to today's time. Another focus for the community of developers is security; they keep evolving PostgreSQL and updating their security measures to keep up with the threats of today. This course will go over the basics of the PostgreSQL. It will cover topics ranging from installations to performance monitoring. 

Course Objectives

  • Understanding the main aspects of how Postgres could be configured
  • Understanding the basic security concepts
  • Getting to know about database backups and restores
  • Learning how to perform database upgrades
  • Discussing performance monitoring and tuning

Who Should Attend?

  • A database administrators (DBA)
  • IT Professionals

Course Details/Schedule

Day 1

  • PostgreSQL Administration
  • Connecting to the Database
  • Overview of PgAdmin III
  • Configuring the Server
  • Setting up and Managing Users
  • Overview of Database Object Types
  • Setting Permissions & Security
  • Querying the Database

Day 2

  • Monitoring the Database
  • Ensuring Atomicity and Isolation
  • Backing up and Restoring PostreSQL
  • Migrating Data to Another System
  • Working with PostreSQL Logs

Day 3

  • Database Backups and Restores
  • Logical or SQL Dump Backups
  • Restoring SQL Dumps
  • Physical Backups - Offline
  • Restoring Offline Backups
  • Physical Backups - Online
  • Write Ahead Log(WAL) Archiving
  • Recovery configuration
  • Point in Time Recovery using WAL Archives

Day 4

  • Database Security
  • Users and Groups
  • Access Control
  • Row Level Security 
  • Host Based Access Control
  • Data Encryption using pgcrypto
  • Demos and Labs

Day 5

  • Database Upgrades
  • PostgreSQL Versioning
  • Plan for Upgrade
  • Upgrade using pg_dump
  • Upgrade using pg_upgrade
  • In Place or Not-in Place Upgrade
  • Post Upgrade Tasks

Day 6

  • PostgreSQL Replication
  • Overview of PostgreSQL Replication
  • High Availability in PostgreSQL
  • Streaming Replication
  • Managing Clusters Using repmgr
  • Running Queries on Database in Hot Standby
  • Synchronous Replication
  • File based Log Shipping
  • Trigger-based Logical Replication with Slony & Londiste
  • Connection Pooling
  • Upgrading PostgreSQL
  • Sharding and Scaling PostgreSQL

Day 7

  • Performance Monitoring and Tuning
  • Monitoring Slow Queries
  • SQL Tuning
  • Table Partitioning
  • Operating System Best Practices
  • Parameter Tuning
  • Extensions
  • Benchmarking
  • Monitoring Best Practices 
  • Demos and Labs