courses.emultiskills.com, postgres database administration

PostgreSQL Database Administration ( Pg16-2024 )

PostgreSQL Database Administration

Buy $299.00 Free Preview

Course curriculum

1. Course Overview PostgreSQL
  FREE PREVIEW
1. Video on What is PostgreSQL and Top cloud PostgreSQL service providers
2. ( Presentation ) What is PostgreSQL and Top cloud PostgreSQL service providers
3. Assignment on What is PostgreSQL and Top cloud PostgreSQL service providers ?
1. Different Options to Deploy PostgreSQL
2. How to get started by launching a first VM in AWS cloud- PG16 ?
3. Step by step Installation of PostgreSQL-16 on ubuntu (latest)
4. How to Tune PostgreSQL on VM with pgtune / Cybertec PostgreSQL Configurator
5. PostgreSQL Architecture
6. Remote Connection in PostgreSQL - Connect with Pgadmin
7. Remote Connection in PostgreSQL-16 - Connect with another host like Ec2 through psql-16 cmd tool
8. Important PostgreSQL Performance Parameters with Details
9. ( Recording and Demo steps )How to setup a Virtual machine in AWS cloud for PostgreSQL - To be removed ( New one added for pg16)
10. ( Assignment )How to setup a Virtual machine in AWS cloud for PostgreSQL
11. How to deploy Postgresql managed instance as RDS@PostgreSQL-16 and connect via pgadmin
12. PostgreSQL as Managed (RDS) or IAAS (EC2 or VM)
13. Deploy RDS@PostgreSQL-16 & connect via psql from another host
14. Assignment on creating and launching postgres on Ec2
15. Different Configuration Files in PostgreSQL-16
16. What is postgresql.conf file in PostgreSQL-16 cluster?
17. What is pg_hba.conf file in postgresql-16 cluster ?
18. What is role and users in postgreSQL cluster?
19. What is cluster in Postgresql -16?
20. What is Schema in PostgreSQL?
21. WAL Files in PostgreSQL?
22. How to cleanup wal files and replication slot in pg
23. Password file in PostgreSQL
24. What is Tablespaces in PostgreSQL ?
25. What is Bloating in PostgreSQL
26. Vacuuming Fundamentals
27. Reindexing in postgresql
28. What is cluster table in PostgreSQL ?
29. pg_stat_statement in PostgreSQL ?
30. pgbench benchmarking tool for PostgreSQL on VM and RDS
31. How to recycle PostgreSQL service and when is it required ?
32. pgBadger - a fast PostgreSQL log analysis report
33. PGCollector - Complete Postgresql database analysis report
34. pg_stat_activity to analyze and perform diagnostics on active SQL queries
35. What is Pg_Repack in PostgreSQL ?
36. Deploy PostgreSQLin docker
37. Best Practices for Configuring PostgreSQL Logging
38. Autovaccum in PostgreSQL ?
39. How to Kill a Session in PostgreSQL?
40. Understanding the Public Schema and Search Path in PostgreSQL
41. What is MVCC in PostgreSQL?
42. Pros and Cons of Using Full Vacuum vs pg_repack
43. Understanding generate_series in PostgreSQL ?
44. How to Find Out Bloating in PostgreSQL and Preventive Measures ?
45. Understanding the pg_resetwal Command in PostgreSQL
1. ( Recording and Demo steps )Install PostgreSQL-13 on EC2 Instance in AWS Cloud - Leagacy
2. How to Install PostgreSQL-16 On ubuntu 22.04 manually
3. Assignment on Launching PostgreSQL 16 on ubuntu22.04 and connecting via Pgadmin
  FREE PREVIEW
1. ( Recording and Demo steps ) Kernel parameters in PostgreSQL
2. Assignment for Kernel parameter settings.
1. ( Recording & Demo steps ) and Introduction to Postgres Default User.
2. Assignment on postgres service account
1. ( Recording ) PostgreSQL Architecture
2. ( Presentation ) Architecture of PostgreSQL
1. (Recording and Demo steps) Logical and Physical Architecture of PostgreSQL
2. ( Assignment )on Logical & Physical Architecture of PostgreSQL
1. (Recording and Demo steps )on Postgresql.conf file in PostgreSQL
2. ( Presentation ) PostgreSQL.conf file in PostgreSQL
3. ( Assignment ) What is a PostgreSQL and how to use Configuration Files
1. ( Recording and demo steps ) on pg_hba.conf file in postgres
2. Assignment on pg_hba.conf file in postgres
1. ( Recording and demo steps) on How to enable remote access to postgres server
1. ( Recording and demo steps) on PostgreSQL Installation on windows
1. (Recording and demo steps) on How to Stop & start Postgres service
2. Assignment on How to Stop & start Postgres service
1. ( Recording and demo steps ) on Cluster in PostgreSQL
1. ( recording and demo steps) on schema in Postgres
2. ( Presentation ) schema in Postgres
1. ( Recording and Demo steps ) on Tablespace in PostgreSQL
2. ( Presentation )Tablespace in postgresql
1. (Recording and demo steps on ) Autovaccum in postgres
2. (Presentation )Autovaccum in postgres
1. Video on Routine maintenance task in PostgreSQL
2. (Presentation )Routine maintenance task in PostgreSQL
1. ( Recoding and Demo steps) on Bloat in postgres
2. ( Presentation )Bloat in postgres
3. ( Assignment ) on Bloat in postgres
1. Routine Vacuuming- PostgreSQL
2. ( Presentation ) Routine Vacuuming- PostgreSQL
3. ( Assignment) Routine Vacuuming- PostgreSQL
1. Vacuuming_best_practices_postgresql
2. ( Assignment ) Vacuuming best practices postgresql
1. ( Video and demo steps ) on Max_connections , performance & connection pool in Postgres
2. (Assignment) on Max_connections , performance & connection pool in Postgres
1. Video on Pg_Repack in PostgreSQL
2. ( Presentation ) Pg_Repack in postgresql
3. (Assignment ) Pg_Repack in postgresql ( Online
1. 24 - (Video and demo steps ) on Password file in PostgreSQL
2. 24 - ( Presentation ) Password file in postgresql
3. 23-( Assignment )on Password file in PostgreSQL
1. ( Recording and demo steps) on WAL in PostgreSQL
2. (Assignment) on WALS in postgres
1. ( Recording and Demo steps )on PostgreSQL Connections monitoring and clean up idle connections
2. ( Assignment) PostgreSQL Connections monitoring and cleaning up idle connections ?
1. ( Recording and Demo steps) on Backup strategies in PostgreSQL
2. ( Assignment ) Different backup strategies in PostgreSQL
1. ( Recording and Demo steps ) on Backup & restore in Postgres
2. ( Presentation ) Backup & restore in Postgres
3. (Assignment ) on Backup & restore in Postgres
1. ( recording & Demo Steps ) on Pg_basebackup in postgresql
2. ( Assignment ) on Pg_basebackup in postgresql
1. (Recording & demo steps) on upgrade strategies in PostgreSQL
2. ( Presentation ) Upgrade strategies in postgresql
1. 31- Video on pg_upgrade in PostgreSQL
2. 31- ( presentation) pg_upgrade
1. 32- ( Demo steps ) Upgrade via backup and restore
1. 33 -( Demo steps ) Upgrade via Logical_Replication
1. 34- Video Streaming replication Postgres
1. 35 - (video and Demo steps ) How to import dvdrental sample database in postgres
2. 35 - (Assignment) Loading Sample dvdrental database
1. 36 - High speed backup and restore PostgreSQL
1. 37- Video on Does PostgreSQL release space when the database is dropped
1. 38- Video on Backup and restore on same host using pg_basebackup
1. 39-video on Continuous Archiving and Perform Point-In-Time-Recovery with PostgreSQL
1. 40 -Video log_statement in PostgreSQL
1. 41- Video on PostgreSQL replication monitoring
1. 42-Video on Introduction to PostgreSQL log
1. 43- Video How to Find slow ,long-running & Blocked Queries in postgresql
1. 44- Iaas vs Paas ( PostgreSQL)
1. 45-AWS Database Services
1. 46- Video on Move WAL to new Location
2. Assignment on moving Wal to new location in EC2 instance
1. Amazon Relational Database Service
1. 48 - Creating a PostgreSQL DB instance and connecting to a database
1. 49- How to recover postgres RDS admin username & password?
1. Strategies for migration of PostgreSQL to Amazon RDS@postgres
1. 51- Video Postgresql database migration from one host to another using pg_dumpall
1. ec2-postgresql
1. 53- Migrate Postgres to AWS RDS@postgres
1. 54 - Migrate postgres to RDS via DMS ( database migration services)
1. 55- video rds_superuser role in RDS@postgres
1. 56-Managing PostgreSQL database access in RDS
1. 57- DB parameter groups in postgres@RDS
2. DB parameter groups postgres@RDS
1. 58- Video on What is option group and parameter group in RDS?
1. 61- pg_archivecleanup -clean up PostgreSQL WAL archive files
1. WAL Archiving and how to configure and much more
1. Remove bloat from PostgreSQL@AWS_RDS with pg_repack/Vacuum
1. 70-PostgreSQL internal authentication
1. 71- Slow ,long-running & Blocked Queries in postgresql / pg_stat_activity in postgresql
1. 72- pg_stat_statements
1. 73 - How to Kill postgresql query
1. 74 -pgbench for PostgreSQL benchmark
1. 75- postgresqltuner
1. 76 - Daily backup schedule using pgdumpall
1. 77 - PGbadger (PostgreSQL Log Analysis tool)
  FREE PREVIEW
2. 77- Assignment on Installing and configuring and reading the pg_Badger report for log analysis
1. 78-PGBackRest (open source backup tool)
2. 78- Assignment on PGBackRest (open source Postgresql backup and restore tool)
1. 79 - pg_bouncer : Connection pooling in postgresql
2. 79 - Assignment on pgbouncer
1. 80 - (Video and demo steps ) regmgr
2. 80 -Assignment : ( repmgr ) Replication & Failover Management for Postgres
1. 81 - Barman
1. 82 - Deploy and connect 2 different version and postgres and connect to the instance separately
1. 83 - Compare schema on different hosts using pgadmin
1. 84 - Dbeaver for postgresql
1. 85- Compare schema objects in postgresql
1. 86 - Pg_cron in postgresql
1. 87 -Docker for postgresql
1. 88 - DB_LINK in Postgres
1. 89 - PostgreSQL In Azure
1. 90 - PostgreSQL In Azure VM
1. 91 - PostgreSQL In Azure managed instance single server
1. 92 - Azure Hyperscale (Citus) server group
1. 93 -PostgreSQL extensions in Azure Database for PostgreSQL - Single Server
1. 94-Create and manage read replicas in Azure Database for PostgreSQL - Single Server from the Azure portal
1. 95- DMS ( EC2 to RDS for PostgreSQL) Homogeneous Migration for PostgreSQL
1. 97- Streaming replication in postgres ( 2 EC2 postgresql-13) Failover Primary to replica failback replica to primary
1. 98- Replication slot in postgresql
1. wal segment vs slot
1. 100- Find the slow running queries in postgresql.
1. 101- Why postgres is not using indexes
1. 102-Sysbench Benchmark tool for PostgreSQL Performance testing
2. Required software's and sample database
1. 103- Video and notes : Reindex in PostgreSQL
2. 103 -Reindex assignment
1. Memory Parameters tuning for PostgreSQL.
1. 105- Video and steps on pgmetric
2. 105 -Assignment on pgmetric
1. 106-( Video and steps ) pg_collector for data collection and analysis
1. 12a -( Video and text ) Roles in Postgres
2. 12a -( Presentation ) Roles in Postgres
1. 107- ( Video) postgresql in google cloud
2. 107- ( PPT) postgresql in google cloud
1. 108(Video)- Launch postgresql instance in GCP
1. 109 -( Video) Google Cloud PostgreSQL users
1. 110 ( Video) Google Cloud PostgreSQL Replication in Cloud SQL
1. 111 (Video) Overview of the high availability configuration
1. 112( Video) Overview of backups in Cloud SQL for postgresql
1. 113 ( Video) Create a read replica in postgresql
1. 114 ( Video) importing and exporting data ( Theory)
1. 115 ( Video) Backup and restore data
1. 116 ( Video) Cloud SQL database disaster recovery
1. 117- Partitioning in PostgreSQL
2. 117- ( Video and Help notes ) Partitioning in PostgreSQL
1. 118 - ( Video and demo steps ) Password Authentication in postgresql
1. 119- ( Recording) What is postgres aurora cluster and most commonly asked questions
2. 119- What is postgres aurora cluster and most commonly asked questions
1. 120 - ( Recording and demo steps ) Creating a DB cluster and connecting to a database on an Aurora PostgreSQL DB cluster
1. (Recording and demo step ) Offline Migration from PostgreSQL on Ec2 or Onprem to Aurora Cluster
1. Slides (Migration strategies to Aurora cluster from another PostgreSQL instance)
2. Recording of Migration strategies to Aurora cluster from another PostgreSQL instance
3. Assignment on Migration strategies to Aurora cluster from another PostgreSQL instance
1. Assignment on Zero Downtime upgrade via pg_upgrade
1. Recording and demo steps
1. Generate lots of time series data for demo ?
2. pgpartman / pg_partman ( partition manager )
3. How to Partition an Existing Table using pgpartman in postgresql ?
1. Index-Only Scans and Covering Indexes
2. What is auto_explain (logging execution plans of slow statements automatically) ?
3. HypoPG (hypothetical indexes?)
4. Concurrent indexes postgres
1. What is timeseries database ?
2. Install TimescaleDB-15 on Ubuntu
3. Running TimescaleDb on Docker container
4. Configuring Timescale Database configuration file
5. Configuration of postgressql.conf in TimescaleDB via Manual way
1. Full Migration from One host to another on VM ( Pg13 tp P16 with downtime )
2. PostgreSQL Migration using pgcopydb (automates copying a PostgreSQL database to another server)
3. pgcopydb vs parallel pgdump & pgrestore( Migration one pg14/13 to pg16)
4. Migrating to RDS postgresql using copy command
5. Migration of postgresql-13 to postgresql-16 via pgloader
1. Performance tuning of parameters in postgresql 16 using pgtune tool
1. Upgrades in PostgreSQL
2. Options for Upgrading in PostgreSQL
3. In-Place and Out-of-Place Upgrades in PostgreSQL
4. pg-upgrade to upgrade PostgreSQL-12 to PostgreSQL-16
5. pg-upgrade to upgrade Pg-12 to Pg-16( lower major verions to higher version ) with --link option
6. Upgrading via pg_dumpall-upgrade Pg-12 to Pg-16 (VM)
7. Least Downtime -Upgrading via pg_dumpall ,upgrade Pg-12 to Pg-16 (VM)
8. Upgrading PostgreSQL via pg_dump from Pg12 to Pg16
9. How to Upgrade PostgreSQL using DMS from VM to RDS ?
10. Steps to upgrade using AWS DMS + ongoing changes
11. Quick Logical Backup/Restore using pgcopydb – PostgreSQL
12. Migrating PostgreSQL to RDS PostgreSQL using copy command
13. Steps to upgrade using AWS DMS + ongoing changes ( Pg@15 Ec2 to Pg@16 Ec2)
1. What is connection pooling in postgreSQL
2. Different options for pooling configuration in Postgresql and which one is better
3. What is Pgbouncer- Connection pooling ( Concept)
4. Step by step implementing Pgbouncer- Connection pooling ( Demo)
5. What is pgpool-11 connection pooling
6. pgpool-11 - Connection pooling -demo
7. What is Amazon RDS Proxy?
8. PostgreSQL Connections Monitoring and Clean Up Idle Connections
9. Max_connections and related parameters on performance & impact of connection pool in Postgres
1. Prepare rds oracle-Source ( Part 1/5)
1. What are Extensions in PostgreSQL?
2. Default Extensions in PostgreSQL
3. What is UUID,Universally Unique Identifier in PostgreSQL ?
4. Difference between uuid-ossp and sequences in PostgreSQL
5. What is pg_cron?
6. Understanding postgres_fdw
7. Using postgres_fdw to Access Data in External PostgreSQL Servers -16 (2023)
1. How to Backup PostgreSQL Running on VM and Managed Instance in AWS
2. Different Options for Postgresql cluster Backup Running on VM
3. Backup and Restore using pg_dump and psql ( concept only )
4. Backup and restore using pg_dump/psql from One host to another
5. Daily Refresh of DB from Prod to QA in PostgreSQL
6. Backup and Restore via pg_dumpall/psql from One Host to Another - Conceptual
7. Backup and restore via pg_dumpall/psql from one host to another
8. Backup and restore using pg_dump/psql from VM to RDS
9. Backup and restore using pg_dumpall/psql from VM to RDS
10. Backup and Restore using pgcopydb from One VM to another VM
11. Backup and Restore from VM to RDS PostgreSQL using copy command
12. How to use the pg_basebackup to take a full, filesystem-level backup of a database cluster and restore it?
13. Shell Script for Database Backup with 7 days Retention in PostgreSQL
14. Shell Script for full postgresql cluster Backup with 7 days Retention in PostgreSQL
15. Shell script to backup PostgreSQL cluster using pg_basebackup
16. Pgbackrest( Backup and restore via pgbackrest)
17. What is WAL-G in postgresql ?
1. Understanding Prometheus for PostgreSQL Monitoring
2. What is Prometheus ?
3. What is Grafana?
4. What is pg_exporter?
5. How to monitor your PostgreSQL database using Grafana, Prometheus & postgres_exporter ?
6. Understanding Grafana for PostgreSQL Monitoring
7. How to monitor PotgsreSQL using prometheus , Grafana and pg_exporter-Conceptual
1. What is CitusData in PostgreSQL?
2. How Install Citus on a VM ( Ec2 ) ?
3. Citusdata on Docker
1. Log-Shipping Standby Servers in PostgreSQL
2. Maintaining Similarity Between Primary and Standby Servers in PostgreSQL
3. Operation of a standby server in PostgreSQL ?
4. What is cascading replication in PostgreSQL ?
5. Upstream and Downstream in PostgreSQL Cascading Replication
6. How to Prepare the Primary for Standby Servers
7. Setting up Continuous Archiving in PostgreSQL
8. How to Set Up a Standby Server
9. 3 nodes streaming replication setup for Pg16 on Ubuntu22.04
10. Replication slots in streaming replication
11. Lecture: pg_rewind in PostgreSQL streaming Replication
12. Demo: Manual Promote standby in 3 node streaming repliction setup ( next one :pg_rewind in PostgreSQL streaming Replication)
13. Demo:pg_rewind in PostgreSQL streaming Replication)
1. Deploy Pg-17 in Ubuntu 22.04
2. New Features and Enhancements in PostgreSQL 17

About this course

$299.00
303 lessons
55 hours of video content

Prerequires for PostgreSQL-16(2024) Administration

PostgreSQL Course Content

What is PostgreSQL and Top cloud PostgreSQL service providers( Pg16 -2024)

PostgreSQL database General topics ( Pg16 -2024)

How to Install Postgresql-16 on EC2 Instance on AWS - Manually

Kernel Parameters for PostgreSQL

Introduction to Postgres Default User.

Architecture of PostgreSQL

Logical and Physical Architecture of PostgreSQL

What is PostgreSQL.conf file in PostgreSQL

What is pg_hba.conf configuration file in PostgreSQL

How to enable remote access to postgres server

PostgreSQL Installation on windows

How to Stop & start Postgres service

Cluster in Postgres

Schema in Postgres

Tablespace in PostgreSQL

Autovaccum in postgres

Routine maintenance task in PostgreSQL

Bloat in postgres

Routine Vacuuming- PostgreSQL

Vacuuming_best_practices_postgresql

Max_connections , performance & connection pool in Postgres

Pg_Repack in postgresql

24 -Password file in PostgreSQL

WAL in PostgreSQL

PostgreSQL Connections monitoring and clean up idle connection/s

Backup strategies in PostgreSQL

Backup & restore in Postgres

pg_basebackup in PostgreSQL

Upgrade strategies in PostgreSQL

pg_upgrade in PostgreSQL

32- Upgrade via backup and restore

33 -Upgrade via Logical Replication

34 - Streaming replication in Postgres

35 - sample database postgres

36 - High speed backup and restore PostgreSQL

37- Does PostgreSQL release space when the database is dropped ?

38- Backup and restore on same host using pg_basebackup

39-Continuous Archiving and Perform Point-In-Time-Recovery with PostgreSQL

40 -log_statement in PostgreSQL

41- PostgreSQL replication monitoring

42-Introduction to PostgreSQL log

43-How to Find slow ,long-running & Blocked Queries in postgresql

44- Iaas vs Pass ( PostgreSQL)

45-AWS Database Services

46 - Move WAL to new Location

47-Amazon RDS / Amazon Relational Database Service ?

48 - Creating a PostgreSQL DB instance and connecting to a database

49 - How to recover postgres RDS admin username & password?

50-Strategies for migration of PostgreSQL to Amazon RDS@PostgreSQL

51- Postgresql migration from one host to another using pg_dumpall

52 -Launch postgresql in Ec2 as Iaas and import data

53- Migrate Postgres to AWS RDS@postgres

54 - Migrate postgres to RDS via DMS ( database migration services)

55- Maser user postgreSQl@RDS

56-Managing PostgreSQL database access in RDS

57- DB parameter groups in postgres@RDS

58- option group in RDS postgres@RDS

61- pg_archivecleanup - clean up PostgreSQL WAL archive files

62- What is WAL Archiving and how to configure and much more

64- Remove bloat from PostgreSQL@AWS_RDS with pg_repack/Vacuum

70-PostgreSQL internal authentication

71- pg_stat_activity in postgresql

72- pg_stat_statements

73 - How to Kill postgresql query

74 -pgbench for PostgreSQL benchmark

75- postgresqltuner

76 - Daily backup schedule using pgdumpall

77 - PGbadger (PostgreSQL Log Analysis tool)

78- PGBackRest (open source Postgresql backup tool)

79 - pg_bouncer : Connection pooling in postgresql

80- Repmgr (Replication & Failover Management for Postgres)

81 - Barman

82 - Deploy and connect 2 different version of postgres and connect to the instance separately

83 - Compare schema on different hosts using pgadmin

84 - Dbeaver for postgresql

85- Compare schema objects in postgresql

86 - Pg_cron in postgresql

87 -Docker for postgresql