| Course reference: | POSTGRES-ADMIN |
|---|---|
| prerequisite: | Participants should have: Solid understanding of SQL fundamentals, including querying, data manipulation, and database design principles. Prior experience with relational databases and SQL syntax. Familiarity with Linux environments is beneficial but not mandatory. |
| Duration of Instructor-Present Course (in Days): | 3 |
Master the art of SQL query optimization with our PostgreSQL Developer Track. Designed for developers experienced in SQL and eager to harness PostgreSQL’s full potential, this intensive 3-day training equips you with advanced skills to write efficient and effective queries.
Upon completion, you’ll be proficient in crafting efficient SQL queries tailored to PostgreSQL, poised to drive performance and scalability in database applications.
Introduction to PostgreSQL
Installation
Postgresql – Client program / GUI Client.
Postgresql Service /processes
Architectural Fundamentals (Logical and Physical layout)
Creating a Database (different options with command & utility)
Accessing a Database & OIDs (Demo & Practical)
Meta Commands & PgPLSQL Environment & Options
Starting, stopping and finding status of postmaster
Hands-on
Physical Architectural
Logical Architectural
Exploring utilities in Postgresql (process and server / postmaster)
Working with above said process related utilities
Schemas in Postgresql
Schema Search PATH
Hands-on Exercise / implementation
User Security
Creating users
Using and assigning user roles
User authentication
Hands-on Exercise
Cluster Security
Tablespace
Built-in Table Space
User Tablespaces
Managing Tablespaces
Concurrency control
Introduction
Transaction Isolation (Levels with hands-on)
Explicit Locking
Hands-on Exercise / implementation
Configuration files in postgresql
Server configuration
Connectivity configuration
User auth. & privileges
Access Control
Performance Tips
Using EXPLAIN
EXPLAIN ANALYZE
Statistics
Explicit JOIN Clauses
Disable Autocommit
Use COPY
Remove Indexes
Remove Foreign Key Constraints
Run ANALYZE
Hands-on Exercise / implementation
Data Dictionary
System Catalog Schema
System Information Tables
System Information Functions
Hands-on Exercise
Routine Database Maintenance Tasks
Routine Vacuuming
Routine Reindexing
Understand Auto-vacuuming
Log File Maintenance
Backup and Restore
SQL Dump
Pg_dump & pg_dumpall
File System Level Backup
Setting up WAL archiving
Continuous Archiving Concept
Hands-on Exercise / implementation
