PostgreSQL DBA Training

Part 1: Introduction about PostgreSQL & PostgreSQL DBA Training

• Why PostgreSQL?
• PostgreSQL in DevOps Culture
• PostgreSQL in Cloud 
• PostgreSQL in Big Data & Analytics
• Course overview & what you will learn?
• After completing the course, what are the job opportunities?

Part 2: Built your Sandbox, Practice setup using Linux & Virtualization or Docker

• Overview of Linux for PostgreSQL
• Linux commands for DBA
• Install & setup up Virtual box
• Install Linux on Virtual box
• Virtual box networking to use putty or ssh
• Docker overview 
• Persistence volume for Databases
• Install PostgreSQL on docker
• Connect Database on docker environment
• Networking Basics for DBA 

 Part 3: Installation of PostgreSQL

• PostgreSQL Version Types 
• Installation on windows
• Installation on Linux
• Install PostgreSQL in Docker
• Installation of PostgreSQL on AWS EC2
• AWS PostgreSQL RDS
• Using EDB Using Stack Builder

Part 4: Database connection

• Linux firewall
• Ports
• Pg_hba.cog
• LDAP Auth
• OS Authentication

Part 4: PostgreSQL Admin tools

• pgadmin
• Dbeaver
• Omnidb

Part 3:  Architecture of PostgreSQL Database Engine

• Client Process & Application Connection
• Server process
  • Postmaster

• Back-end processes   
  • Background Writer 
  • Checkpoint
  • Auto vacuum Launcher
  • WAL Writer
  • Stats Collector
  • Log collector
  • Archiver

• Memory Architecture
  • Local Memory Area
    • Work_Mem
    • Maintenance_Work_mem
    • Temp_buffers

• Shared Memory Area
  • Shared buffer
  • Wall Buffer
  • Commit log

• Logical Structure of Database
  • PG/SQL
  • Database Cluster
  • Database
  • Tables
  • Indexes 
  • View 
  • Function
  • Sequences
  • Table spaces

• Table Partitioning
  • Why Partition
  • Partition types
    • List partition
    • Range partition
    • Hash partition
    • Multilevel partition 

Part 4: Configuration overview

• Database files & Directory Layout   
  • Base
  • Global
  • Pg_commit_ts
  • Pg_clog
  • pg_logical

• Database Config files   
  • PostgreSQL.conf
  • pg_hba.conf   
  • pg_ident.conf   
  • postgresql.conf   
  • postgresql.auto.conf
  • Postmaster.opts
   
• Linux environment variables for PostgreSQL
  • PGHOST 
  • PGHOSTADDR
  • PGPORT 
  • PGDATABASE
  • PGUSER
  • PGPASSWORD 
  • PGPASSFILE
  • PGSERVICE
  • PGSERVICEFILE 
  • PGOPTIONS

Part 5: PostgreSQL Database & Objects   

• Database creation
• Schema creation
• User creation
• Group creation

Part 6: Database Start / Stop

• Pg_ctl
• Service start/stop Linux
• Statue of the database server
• Stop fast
• Stop immediate
• Reload

Part 7: Database Catalog Tables

• pg_aggregate
• pg_am
• pg_amop
• pg_amproc
• pg_attrdef
• pg_attribute
• pg_authid
• pg_auth_members
• pg_cast
• pg_class
• pg_constraint
• pg_collation
• pg_conversion
• pg_database
• pg_db_role_setting
• pg_default_acl
• pg_depend
• pg_description
• pg_enum
• pg_extension
• pg_foreign_data_wrapper
• pg_foreign_server
• pg_foreign_table
• pg_index
• pg_inherits
• pg_language
• pg_largeobject
• pg_largeobject_metadata
• pg_namespace
• pg_opclass
• pg_operator
• pg_opfamily
• pg_pltemplate
• pg_proc
• pg_rewrite
• pg_seclabel
• pg_shdepend
• pg_shdescription
• pg_statistic
• pg_tablespace
• pg_trigger
• pg_ts_config
• pg_ts_config_map
• pg_ts_dict
• pg_ts_parser
• pg_ts_template
• pg_type
• pg_user_mapping
• System Views
• pg_available_extensions
• pg_available_extension_versions
• pg_cursors
• pg_group
• pg_indexes
• pg_locks
• pg_prepared_statements
• pg_prepared_xacts
• pg_roles
• pg_rules
• pg_seclabels
• pg_settings
• pg_shadow
• pg_stats
• pg_tables
• pg_timezone_abbrevs
• pg_timezone_names
• pg_user
• pg_user_mappings
• pg_views

Part 7:  Managing Storage for PostgreSQL

• Table space
  
• Storage volumes
• Iscsi

Part 8: Backup & Recovery  

• Logical backup 
• Physical backup
• Pg_dump
• pg_dumpall
• Pg_base backup
• Point in time recovery
• OS level backup
  • Tar backup
  • ISCI level backup
  • Storage level backup

Part 9: PostgreSQL Database Maintenance

• Autocaccum
• Page Corruption
• Auto Freeze
• Pg_repack 
• Pg_reorg
• Preventing transaction ID Wraparound Failures 
• Table bloating
• Index Bloating 
• Planner Stats update
• Re-indexing

Part 10: Monitoring 

• Cpu usage
• Page and Swapping 
• System load
• Disk space monitoring 
• Network monitoring

Part 11: Monitoring tools installation and configuration

• Installation of Prometheus & Grafana 
• Prometheus exporters for PostgreSQL
• PostgreSQL Enterprise Manager (PEM) & Pem Agent install
• Nagios overview for PostgreSQL monitoring
• AWS Cloud watch overview

Part 12: Data load 

• .csv load to tables
• Data copy using PostgreSQL_fdw
• Copy freeze

Part 13: Replication

• Types of replications
• Advantages of Replication
• Replication Configuration
• In house to AWS RDS replication 
• Hot standby 
• Streaming replication
• Failover 
• Swiftcover 

Part 14: Upgrade 

• Database version upgrade
• Database upgrade 
• Database upgrade best particles 
• Database upgrade planning
• Database upgrade prerequisites 

Part 15: Database Connection pooling 

• Pg_pool
• Pg_bouncer

Part 16:  Migration

• Database migration using ora2pg
• Server migration
• PostgreSQL Migration best practices

Part 17: Performance Tuning and Optimization

• Server parameter tuning & Query tuning  
  • max_connections           
  • password_encryption       
  • Resource Usage           
  • shared_buffers           
  • work_mem           
  • maintenance_work_mem       
  • effective_io_concurrency   
  • Write-Ahead Log           
  • wal_compression           
  • wal_log_hints           
  • wal_buffers           
  • checkpoint_timeout       
  • checkpoint_completion_target   
  • max_wal_size           
  • archive_mode           
  • archive_command

Part 18:  PostgreSQL DBA Daily Tasks & Automation

• Backup automation using Shell script
• Database log query monitoring
• Killing Session using pg_terminate_backend
• Application DB performance monitoring
• Finding session lock
• Running Analyze for latest stats

Part 20:  Php or python project on PostgreSQL Database

• Database Connection to Application
• CURD operation using code
• Basic connection configuration and Monitoring