OTHER DATABASES

PostgreSQL DBA Training


Topics

Chapter 1 : Introduction & Installation
  • Database Basics and Database Types
  • Database Basics and Database Types
  • PostgreSQL : Advantages, Usage
  • PostgreSQL as a Open Source
  • PostgreSQL : History and Versions
  • PostgreSQL : Real-time Usage
  • PostgreSQL installation on LINUX OS;
  • bin,data and PATH Concepts
  • Command Line Tools
  • Post Installation : Environment Variables, Path,bin and PGDATA Variables
Chapter 2 : SQL Basics, Linux Installation
  • Languages
  • Constarints
  • Operators
  • Functions
  • Set operator
  • Joins
  • Subquery
  • Views
  • Sequence
  • Index
Chapter 3 : Server Architecture
  • PostgreSQL Server Architecture;
  • Server Process and Client Process; Postgres Program : Internal Process;
  • Postmaster Process and forks; Background Process, Connections; Utility Processes: Bgwriter, WAL;
  • Checkpointer and StatsCollector; Log Writer, Autovacuum Utilities; Memory Segments and Usage; Shared
  • Buffer and WAL Buffer; Dirty Data and background writer; Background Process, Backend Process;
Chapter 4 : Cluster Creation
  • initdb utility introduction
  • Creating cluster using initdb
  • Changing port number of new cluster
Chapter 5 : Database Creation and Maintanence
  • Default Databases in Postgres; postgres, template0, template1;
  • Database creation from bash
  • Database creation from DB level
Chapter 6 : Schema Creation and Maintanence
  • Default schema-public
  • Creating schema
  • Creating table inside schema
  • Setting default schema
  • Moving ables across schema
Chapter 7 : Tablespace Creation and Maintanence
  • Tablespaces, Real-time Advantages;
  • pg_default, pg_global tablespaces;
  • Create, Modify tablespace
  • Create tables inside tablespace
Chapter 8 : User, Roles, Privileges
  • User Creation
  • Assigning privileges to user
  • Dropping user
  • Assigning privileges to role
  • Assigning role to user
  • Privileges
Chapter 9 : Performance Tuning
  • Transactions and Lock Management;
  • Table Level Locks, Row Level Locks;
  • Query Blocking and Deadlocks;
  • Lock Queue, Lock Management Options;
  • Page Locks, Shared and Exclusive Locks;
  • pg_stat_activity;
  • pg_cancel_backend,pg_terminate_backend;
  • Vacuum
  • Vacuum full
  • Freeze
  • Analyze
  • Partition
  • Pg_badger - log analysis tool
Chapter 10 : Job Scheduling
  • PG_cron introduction
  • Installing pg_cron
  • Parameters for pg_cron
  • Scheduling job
Chapter 11 : Access other database
  • DBlink
  • Foreign data wrapper
Chapter 12 : Backup & Restore - 1
  • Backups: Types, Levels in Postgres;
  • Logical and Physical Backups;
  • Backup Tools: pg_dump, pg_dumpall;
  • Online Backups, Offline Backups;
  • Logical Backups : Single Table; Multiple Tables, Single Database; Logical Backup Options, Usage;
  • pg_backrest - third party tool for backup
Chapter 13 : Backup & Restore - 2
  • wal_level and archive_mode Options;
  • archive_command with File Formats WAL Archival Process;
  • Point In Time Recovery (PITR);
  • pg_switch_wal, pg_basebackup Utilities;
  • Physical Backups, Continous Archival;
Chapter 14 : Upgrade
  • pg_upgrade
  • checking consistency
  • delete old cluster
Chapter 15 : Migration
  • ora2pg introduction
  • Downloading files
  • Setting parameters
  • Migrating data
Chapter 16 : HA-DR with Postgres
  • PostgreSQL Replication, Advantages;
  • wal sender, wal receiver,startup;
  • pg_hba.conf Entires for repl host;
  • pg_basebackup
  • Single Master Replication;
  • recovery.conf file & configurations;
  • primary_conninfo & standby_mode;
  • Replication Setup Validations;
  • pg_stat_replication
Chapter 17: Connection Pooling
  • Pg_bouncer introduction
  • installing pg_bouncer
  • configuring pg_bouncer

Next Course

RELATED VIDEOS

Example of Embedding YouTube Video inside Bootstrap Modal

Enrolled Students

8565

Education Awards

120

Certified Teachers

193

Library and Labs

150