What you'll learn:
- Understand PostgreSQL Process and Memory Architecture.
- Install PostgreSQL on Windows and Linux.
- How to Setup Environment Variable on Windows and Linux.
- Automating Backup and DBA Maintenance Task
- PostgreSQL Configuration Files.
- Pg_basebackup (Full & Incremental)
- How to Create Database/User/Schema from utility and Psql commands.
- How to use PSQL command line interface to connect to PostgreSQL.
- CRUD operations on PostgreSQL.
- Table Inheritance in PostgreSQL.
- Partitioning in PostgreSQL using Table Inheritance.
- Copy table from/to PostgreSQL.
- How to Create/Move/Drop Tablespace.
- Logical (pg dump, pgdumpall), Physical backup (Offline, Online).
- Continuous Archiving and PITR.
- What is Explain plan and how to calculate cost of query.
- Maintenance (Updating planner statistics, Vacuum, Routine Reindex, Cluster).
- PostgreSQL Directory Structure.
- How to use Pgadmin 4 Interface.
- Uninstall PostgreSQL on Windows and Linux.
- How to Upgrade PostgreSQL
- Journey into Postgresql v13
- Postgresql v13 - New Features
- Let's Explore Postgresql v15
- Postgresql v15- New Features
- Postgresql v16- New Features
PostgreSQL is an advanced, enterprise class open source Object-Relational Database Management System (ORDBMS). Its is a relational database that supports both SQL (relational) and JSON (non-relational) querying.
PostgreSQL possesses robust feature sets including Multi-Version Concurrency Control (MVCC), point in time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backups, a refined query planner/optimizer, and write ahead logging. It supports international character sets, multi-byte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. PostgreSQL is highly scalable both in the quantity of data it can manage and in the number of concurrent users it can accommodate.
The course provides the learner with an extensive knowledge of PostgreSQL Database Administration with real time examples.
Topics covered are:
PostgreSQL Process and Memory Architecture
Installation of PostgreSQL on Windows and Linux
Setup PostgreSQL Environment Variables - Windows and Linux
PostgreSQL Storage Internals
Database Directory Layout
PostgreSQL Configuration Files
Cluster in PostgreSQL (Initdb,Start/Stop,Reload/Restart Cluster)
Createdb/Dropdb, Createuser/Dropuser Utility
Best Practices for User Management.
Create Schema and Schema Search Path
Pgpass File
Psqlrc.conf
Psql Interface Commands
Pg System Catalogs
Working with PostgreSQL Objects
CRUD Operations
MERGE AND MERGE RETURNING
Table Inheritance
Table Partitioning
Table Partitioning with Inheritance
Tablespace in PostgreSQL
Backup's, Continuous Archiving and PITR (Point-in-Time-Recovery)
Incremental Backups using Pg_basebackup.
Wal Summarizer Process
Explain plan and Query Execution Cost
Maintenance (Updating Planner Statistics, Vacuum, Routine Reindex & Log File Maintenance)
Best Practices for Routine Reindexing.
Data Fragmentation
Vacuum, Vacuum Full, Auto Vacuum Dameon
Best Practices for Vacuum Commands.
Automating Backup's and Maintenance Task.
Pgadmin 4
Upgrade PostgreSQL (Pg_Dumpall & Pg_Upgrade Utility)
Uninstall PostgreSQL on Windows and Linux
Journey into Postgresql v13
Postgresql v13 - New Features
Let's Explore Postgresql v15
Postgresql v15 - New Features
Tour of Postgresql v16
Postgresql v16 - New Features
