PostgreSQL Database Administration | Ingress Academy
For individuals For business Jobs & hiring
Career Paths Courses Why Us Instructors Blog
az en ru
Portal

Database Management

PostgreSQL Database Administration

Learn to install, configure, and manage PostgreSQL database systems effectively. Topics include backup and recovery, performance tuning, security, and SQL query optimization. Suitable for database administrators seeking practical expertise. Experienced trainers provide practical lab sessions and real-world use cases.

Intermediate On-site 16 weeks 64 hours

About this course

Learn to install, configure, and manage PostgreSQL database systems effectively. Topics include backup and recovery, performance tuning, security, and SQL query optimization. Suitable for database administrators seeking practical expertise. Experienced trainers provide practical lab sessions and real-world use cases.

Advantages

🧑‍🏫

Practical teaching

In addition to class hours, you will practice the topics covered with your instructor and mentor dur

🤝

Mentors

The knowledge and skills you learn at the academy will be further strengthened with the mentor syste

📋

Academic transcript

Assignments and projects are checked by the instructor, and your knowledge and skills are determined

Training program

Download program
  1. 1 What is Database, DB types & history of databases
  2. 2 Relational and Non-Relational Databases
  3. 3 PostgreSQL history, Community, Features matrix etc.
  4. 4 PostgreSQL installation and basic configuration.
  5. 5 Pagila sample database
  6. 6 GUI Tools(Pgadmin, Dbeaver, DbGrid etc.)
  7. 7 PSQL CLI and commands
  1. 1 PostgreSQL installation types (from repo and source code)
  2. 2 Recommended Linux package and configurations
  3. 3 Installation and configuration on RedHat
  4. 4 Installation and configuration on Ubuntu
  5. 5 Installation and configuration on Windows
  6. 6 Normal and immediate start stop methods of PostgreSQL
  7. 7 How to properly Uninstall PostgreSQL
  8. 8 Enabling Data checksum before and after installation
  9. 9 Changing DB Collate & Ctype before and after installation
  10. 10 Installation PostgreSQL from source code
  11. 11 Cloning database
  12. 12 Extensions that must be on every PostgreSQl DB
  13. 13 PostgreSQL Template DB yaradılması vəistifadəsi
  14. 14 Useful PostgreSQL functions for DB management
  15. 15 Creating DB objects
  16. 16 Tables, Constraints, Views, Sequences etc.
  17. 17 Materialized view and additional refresh methods
  1. 1 Client-server protocol
  2. 2 Transactionality and its implementation
  3. 3 Query processing and execution
  4. 4 Processes and memory structures
  5. 5 Background Processes
  6. 6 PostgreSQL memory structures
  7. 7 Storing data on disk and disk operations
  8. 8 System extensibility
  1. 1 Multiversion concurrency control (MVCC)
  2. 2 ACID (Atomicity, Concurrency, Isolation, Durability)
  3. 3 Isolation Levels (Read UnCommitted, Read Committed, Repeatable Read, Serializable)
  4. 4 Phenomena (Dirty Read, Non-repeatable Read, Phantom Read, Serialization anomaly, Lost Updates, Read Skew, Write Skew)
  5. 5 Locking
  1. 1 Routine tasks that require periodic execution
  2. 2 Vacuum and analysis
  3. 3 Table and index bloating
  4. 4 Full vacuum and rebuilding of indexes
  1. 1 Buffer cache overview
  2. 2 Replacement algorithm
  3. 3 WAL-Write-ahead log
  4. 4 Checkpoint
  5. 5 Processes related to the buffer cache and WAL
  1. 1 Databases and templates
  2. 2 Schemas and search path
  3. 3 Special schemas
  4. 4 System catalog
  1. 1 Tablespaces and directories
  2. 2 Heap Table internal structure
  3. 3 Data pages
  4. 4 Tuple structure
  5. 5 FILLFACTOR
  6. 6 Forks: data, visibility map, free space map
  7. 7 TOAST - The Oversized-Attribute Storage TechniqueAST
  1. 1 Numeric Data Types
  2. 2 Character/Binary Data Types
  3. 3 Date/Time Data Types
  4. 4 Enumerated Data Types
  5. 5 Range Data Types
  6. 6 Geometric Data Types
  7. 7 JSON Types (JSON & JSONB)
  8. 8 How NULL values are stored in PostgreSQL
  1. 1 OS tools
  2. 2 Database statistics
  3. 3 Server message log
  4. 4 Shell scripting and sending notifications with Telegram Bot
  5. 5 External monitoring systems (PerconaPMM, Zabbix, PgHero)
  1. 1 Roles and attributes, Roles Inheritance
  2. 2 Privileges
  3. 3 Grant and Revoke privileges
  4. 4 Object Ownership
  5. 5 Policies, Policy vs. Grant
  6. 6 Row-level security policies
  7. 7 Understanding ACLs
  8. 8 Configuring pg_hba.conf
  9. 9 Authentication Methods
  10. 10 pg ident Configuration
  11. 11 Authentication with Certificate
  12. 12 Security improvements in different PostgreSQL versions
  13. 13 Audit database with PG_Audit
  14. 14 Security Best practices
  1. 1 Logical backup (PG_DUMP, PG_RESTORE, PG_DUMPALL )
  2. 2 Plain format dump
  3. 3 Custom format dump
  4. 4 Directory format dump
  5. 5 Bulk data loading
  6. 6 Loading CSV data with COPY
  7. 7 Loading CSV data with FILE_FDW
  8. 8 Loading CSV data with PG_BULKLOAD
  9. 9 Loading CSV data with external GUI tools
  10. 10 Physical backup
  11. 11 Standalone Backup
  12. 12 Standalone HOT physical backup
  13. 13 PgBaseBackup
  14. 14 Creating Full and Incremental Backups
  15. 15 Recovery database
  16. 16 Point in Time Recovery(PITR)
  17. 17 Installation and configuration PgBackRest
  18. 18 Configuring Pgbackrest with Certificate
  19. 19 Barman(Full and Incremental Backup and Recovery)
  1. 1 Configuring Logging in PostgreSQL
  2. 2 Analyzing postgreSQL log with PgBadger
  3. 3 Installing and configuring centralized logging to SYSLOG və RSYSLOG
  1. 1 Replication purposes and types
  2. 2 Physical replication
  3. 3 Configuring replication with Streaming Replication method
  4. 4 Configuring replication with Replication Slots method
  5. 5 Configuring Delay Standby
  6. 6 Failover, monitoring , recreateding, dropping, checking replication
  7. 7 Configuring Logical replication
  8. 8 Monitoring COPY in logical replication
  9. 9 Row filtering in Logical replication tables
  1. 1 What is Connection Pool ?
  2. 2 Installation and configuration PgBouncer
  3. 3 Load balancing between primary and standby servers using HaProxy
  4. 4 Configuring healthcheck using KeepAlived
  1. 1 PostgreSQL versioning & Roadmap
  2. 2 Updating PostgreSQL to new minor version
  3. 3 Upgrading to major version using PG_UPGRADE
  4. 4 Upgrading to major version with zero downtime using PG_UPGRADE –LINK
  1. 1 B-TREE INDEX
  2. 2 HASH INDEX
  3. 3 GIN INDEX
  4. 4 RUM INDEX
  5. 5 BRIN INDEX
  6. 6 Unique Index
  7. 7 Partial Index
  8. 8 EXPLAIN
  9. 9 Index Scan
  10. 10 Bitmap Index Scan
  11. 11 Bitmap Heap Scan
  12. 12 Identifying table columns which need INDEX
  13. 13 Rebuild and Vacuum Indexes
  14. 14 Identifying unused indexes
  15. 15 Properly removing unused indexes
  16. 16 Checking indexes using AMCHECK Extension
  1. 1 Best practices to configure postgresql.conf for optimal performance
  2. 2 Identifying Slow Running Queries
  3. 3 Identifying Performance Issues reason with Slow Queries
  4. 4 Indexing recommendations
  5. 5 Index usage
  6. 6 Index Cache Hit Ratio
  7. 7 Parallel queries
  8. 8 Cache Hit Ratio
  9. 9 Importance of VACUUM for performance
  10. 10 Identifying BLOAT & solving bloat problem
  11. 11 Using different methods and extensions to reduce Bloat
  12. 12 PostgreSQL statistics
  1. 1 Migration from Oracle to PostgreSQL
  2. 2 OracleFDW
  3. 3 Ora2PG
  4. 4 Migration from Ms SQLServer to PostgreSQL
  5. 5 Migration from MySQL to PostgreSQL
  1. 1 NATIVE PARTITIONING
  2. 2 LIST PARTITION
  3. 3 RANGE PARTITION
  4. 4 HASH PARTITION
  5. 5 Multilevel mixed partitioning
  6. 6 Automated partitioning with PG_PARTMAN
  7. 7 Partition maintenance and useful scripts
  1. 1 Patroni installation and configuration
  2. 2 Adding new node to Patroni cluster
  3. 3 Removing node from Patroni cluster
  4. 4 Switchover on Patroni cluster
  5. 5 Change PostgreSQL configurations on Patroni
  1. 1 PG_AGENT
  2. 2 PG_CRON
  3. 3 PGAUDIT
  4. 4 PG_BUFFERCACHE
  5. 5 POSTGRES_DBA
  6. 6 PG_BACKGROUND
  7. 7 PGBADGER
  8. 8 PG_STAT_STATEMENTS
  9. 9 PG_QUALSTATS
  10. 10 PG_STAT_CACHE
  11. 11 pg_track_settings
  12. 12 POSTGIST
  13. 13 PG_PREWARM
  14. 14 PG_WAIT_SAMPLING
  15. 15 HYPOPG
  16. 16 PG PARTMAN
  17. 17 PG FREESPACEMAP
  18. 18 TABLE VERSION
  19. 19 POSTGRES_FDW
  20. 20 FILE_FDW
  21. 21 ORACLE_FDW
  22. 22 PGSTATTUPLE
  23. 23 PAGILA
  24. 24 PG_TRACK_SETTINGS
  25. 25 TABLEFUNC
  26. 26 PG_TRGM
  27. 27 INTARRAY
  28. 28 PG_NOTIFY
  29. 29 PG_TOP
  30. 30 EXTRA_WINDOW_FUNCTIONS
  31. 31 PG_HEXEDIT
  32. 32 PGMETRICS
  33. 33 SQLBENCH
  34. 34 PG_DUMPBINARY
  35. 35 PGroonga
  36. 36 PGTT
  37. 37 Pg_flame
  38. 38 PgHero
  39. 39 Pgtools
  40. 40 PostgreSQL Data Dictionary (pgdd)
  1. 1 Hardware BestPractices
  2. 2 OS Configuration BestPractices

Instructors

Alisahib Gadimli

Alisahib Gadimli

Senior Postgresql Database Administrator

Goldenpay

 Zaur Hajili

Zaur Hajili

Senior DBA

Azercell