COMPREHENSIVE POSTGRESQL ADMINISTRATION ADVANCED LEVEL

COURSE DESCRIPTION

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. This class will give you guide to the Basic, Advance and Complete PostgreSQL Administration.

It will cover modules on PostgreSQL Introduction, System Architecture, Database Clusters, Configuration, Creating and Managing Databases, User Tools – Command Line Interface, GUI Tools, PostgreSQL Security, SQL Primer, Backup & Recovery, Routine Maintenance, Data Dictionary, Tablespaces, Performance Tuning, Pgpool, Transactions & Concurrency, Table Partitioning, Upgrading PostgreSQL, Data Loading / UnLoading and Database Replication.

COURSE OUTLINE

MODULE 1 – ROUTINE MAINTENANCE

• Database Maintenance
• Maintenance Tools
• Optimizer Statistics
• Data Fragmentation
• Routine Vacuuming
• Vacuuming Commands
• Preventing Transaction ID Wraparound Failures
• Vacuum Freeze
• The Visibility Map
• Vacuumdb
• Autovacuuming
• Per Table Thresholds
• Routine Reindexing
• CLUSTER

MODULE 2 – DATA DICTIONARY

• The System Catalog Schema
• System Information Tables
• System Information Functions
• System Administration Functions
• System Information Views 

MODULE 3 – TABLESPACES

• Tablespaces & Data Files
• pg_global 7 pg_default
• Advantage of Tablespaces
• Creating a Tablespace
• Changing Default Tablespace
• Altering Tablespaces
• Dropping Tablespaces

MODULE 4 – PERFORMANCE TUNING

• Hardware Configuration
• OS Configuration
• SQL Tuning
• Query Plan
• Advanced Performance Tuning Settings

MODULE 5 – PGPOOL

• Introduction to Pgpool
• Connection Pooling
• Replication
• Load Balancing

MODULE 6 – TRANSACTIONS & CONCURRENCY

• Transactions
• Concurrency & Transactions

MODULE 7 – TABLE PARTITIONING

• Partitioning
• Partitioning Methods
• Partitioning Methods
• Partitioning Setup
• Attach and Detach Partitions
• Partition Pruning
• Partitioned Table Limitations

MODULE 8 – UPGRADING POSTGRESQL

• Version Change and Upgrade
• Need to Upgrade
• Upgrade Plan
• Upgrade Using pg_upgrade
• Upgrading Best Practices

MODULE 9 – DATA LOADING / UNLOADING

• Loading flat files
• Import and export data using COPY
• Examples of COPY Command

MODULE 10 – DATABASE REPLICATION

• Configuring Streaming Replication
• Configuring Synchronous Replication
• Configuring Asynchronous Replication
• Logical Replication in PostgreSQL

Please contact us for schedules and for booking a private class.

Inquire Now