Postgresql database Administration

1-0-1 Training on PostgreSQL Database Administration

This course is meant for advnaced level for PostgreSQL database Administration.

Buy $1,999.00 Free Preview

Course curriculum

1. Prerequisites to perform the hands on and prepare for interview?
  FREE PREVIEW
2. Assignment on Prerequisites for the hands on course
1. echo command in linux
2. cat command in Linux with examples
3. Find command in Linux
4. grep command in Linux
5. vi editor in linux
6. telnet command in linux
7. chmod and chown in linux
8. sed command in linux
9. sudo command in linux
10. Monitor Log Files in Real Time: tail unix command
11. Basic Linux Commands to Check Hardware and System Info
12. Getting started : Install First PostgreSQL-16 on Ec2 Instance
13. How to recycle PostgreSQL service and when is it required ?
14. Launch RDS for postgesql 15 and connect via ec2 and pgadmin to postgresql
15. Launch Aurora -PostgeSQL 16 and connect via Ec2 and pgadmin to Aurora -PostgreSQL
16. Service account of PostgreSQL on an EC2 Instance
17. PostgreSQL.conf file in PostgreSQL ?
18. What is pg_hba.conf file ?
19. What is cluster in postgresql ?
20. Roles in PostgreSQL ?
21. Schema in PostgreSQL
22. Tablespaces in PostgreSQL
23. WAL Files in PostgreSQL?
24. Vacuuming Fundamentals
25. cluster table in postgresql
26. Troubleshooting issues with Vacumming in postgresql
27. How To Install PostgreSQL 16 on Ubuntu
1. Bloating in postgresql
2. Reindexing in postgresql
3. Password file in PostgreSQL
1. Install postgres 14 on Ec2 Ubuntu and connect with pgadmin running on your laptop
2. How to setup and troubleshoot Remote connectivity issue of postgresql running on 2 Ec2 instances ?
3. How to Remotely connect with postgres managed instance RDS@postgresql
4. How to Remotely connect with postgres managed instance Aurora@postgresql with pgadmin running on local system?
5. Remote connectivity of postges running on docker with pgadmin running on local system
1. 3 Node Streaming Replication in PostgresQL-14
2. repmgr : Postgresql Replication Manager for PostgreSQL Cluster
3. How to promote a standby server with repmgr ?
4. Repmgr failover and change of roles in replication ( 1 Primary and 2 standby )
5. Switchover with repmgr
6. Logical Replication
7. pglogical extension setup for Logical replication
8. Migrating PostgreSQL from EC2 to RDS using pglogical replication
9. PostgreSQL Streaming Replication With Slots
10. Replicate tables in RDS ( 2 instances) for PostgreSQL using logical replication
11. Streaming replication - pg16 3 nodes
1. Pgbackrest( Backup and restore)
2. Backup Postgres using pgBackRest running on dedicated host
3. Replication setup using using pgBackRest running on dedicated host & postgres on separate host
4. pgloader
5. Barman backup and restore via streaming protocol
6. Archiving in postgresql
7. Archiving WAL and Perform Point in Time Recovery in postgresql
8. Backup and Restore a PostgreSQL with pg_basebackup with Tablespaces
1. pg-upgrade to upgrade postgres12 to postgres15
2. pg-upgrade to upgrade postgres12 to postgres15( lower major verions to higher version ) with --link option
3. Upgrade PostgreSQL from EC2 to RDS using pglogical replication
4. Upgrade RDS@ PostgreSQL @Aurora PostgreSQL database using the pglogical ?
5. Upgrading Data via pg_dumpall upgrade postgres12 to postgres15 (vm)
6. Steps to upgrade using AWS DMS
7. Steps to upgrade using AWS DMS + ongoing changes
1. sysbench benchmarking tool
2. pgbench benchmarking tool for postgres on VM and RDS
1. Migration of PostgreSQL running on VM to RDS via pg_dump and pg_restore
2. Migrating PostgreSQL Databases to Amazon RDS using logical replication ( publisher and subscriber model.)
3. Migrating PostgreSQL Databases using pgcopydb
4. Migrating to RDS postgresql using copy command
5. Migrate PostgreSQL from Local to RDS postgres using pgadmin
6. Troubleshooting RDS/PostgreSQL on VM Connectivity issue
1. pgpool-11 Load balancer and connection pooling
2. pgbouncer Load balancing
1. Migrate PostgreSQL database using export and import to Azure postgresql
2. Migrate PostgreSQL database using pg_dump and pg_restore to Azure postgresql
1. Assignment on PostgreSQL-16 on Ubuntu22.04 Ec2
2. Assignment on pg_hba.conf file
3. Assignment on Aurora for PostgreSQL + Ec2
4. Assignment on Postgresql.conf
5. Assignment on PostgreSQL 16 and 13 on Ubuntu 22.04
6. Assignment on benchmarking Ec2 and RDS
7. delete wal files in postgresql
8. High speed backup and restore using pgcopydb
9. copy command to export subset of data
10. (Upgrade to higher major version pgupgrade without link
11. Pg_upgrade --link option ( 12 to 17 on ubuntu 22.04)
12. upgrade using pg_dumpall ( 13 to 16) on disk and in memory
13. Migration of PostgreSQL running on VM to RDS via pg_dump and pg_restore
14. Cleanup existing postgresql binaries on Ubuntu 22.04
15. 3 Node repmgr setup on ubuntu 22.04
16. Repmgr with ssh configuration
17. Data directory change
18. PostgreSQL-16 Replication & Automated Failover by Repmgr ( 4 Nodes with 3 data &1 Witness )
19. manual Vcauum tall tables in a schema
20. reindex all tables across all schemas
21. Check Statistics and Analyze Tables
22. script to check the size of all memory-related parameters in Amazon Aurora
23. No Demo : best practices for writing efficient PostgreSQL queries
24. Missing Indexes
25. pg_resetwal Command in PostgreSQL
26. Understanding the work_mem Command in PostgreSQL
27. Understanding checkpoint_timeout and checkpoint_completion_target in PostgreSQL
28. Mandatory Parameters to Improve PostgreSQL Performance
29. Understanding maintenance_work_mem in PostgreSQL
30. Improving Vacuuming in PostgreSQL and Demonstrating its Impact on Usage
31. pg_cron
1. pg_stat_statement in postgresql
2. pg_stat_activity to analyze and perform diagnostics on active SQL queries
3. pgBadger - a fast PostgreSQL log analysis report
4. PGCollector - Complete Postgresql database analysis report
5. What is auto_explain in postgresql
6. Download tools
7. pgCluu :PostgreSQL performance monitoring and auditing tool.
8. Impact of missing index in postgreSQL
9. Getting started with explain analyze
10. pg_index_watch
11. Hypothetical or virtual index in postgres
1. Parameter group in RDS PostgreSQL
1. Setup 2 Node streaming replication on pg15 Manually via replication slot
2. Setup 2 Node streaming replication on pg15 Manually via replication slot ( Bind with repmgr)
3. Setup 3 Node )2+1) streaming replication on pg15 Manually via replication slot ( Bind with repmgr)
4. Postgresql-15 Replication & Automatd Failover by Repmgr ( 4 Nodes with 3 data &1 Witness )
5. repmgr standby register - 4 Nodes ( 3 Data +1 Witness )
1. What is Barman (Backup and Recovery Manager)
1. Install Pg-17 on ubuntu 24.04
2. Restore Scenarios
1. Extensions
2. Best practices for New server configuration

About this course

$1,999.00
128 lessons
24 hours of video content

1-0-1 Training on PostgreSQL Database Administration

Course curriculum

Prerequisites for hands on demo

Section-1 Basic Linux for PostgreSQL Admins

General Topics

Remote Connection to postgresql ?

Replication in PostgreSQL

Backup and Restore in PostgreSQL

Upgrade PostgreSQL version

Postgresql benchmarking

Migration to RDS / Postgresql Aurora

Connection Pooling and Load balancer

Migrate PostgreSQL on VM to target Azure Database for PostgreSQL

Assignments

Performance Tuning in PostgreSQL

parameter file in postgresql

Replication Via repmgr

Barman (Backup and Recovery Manager)

Pg17

Extensions

About this course