Course Content:
- Introduction and Overview of PostgreSQL
- PostgreSQL System Architecture
- Architectural Summary
- Process Architecture
- Database Clusters
- PostgreSQL Terminology
- Physical Database Architecture
- Data File Architecture
- Installation
- Installation using
- PostgreSQL Binary Windows
- Installation from Source
- Creating a database cluster
- Starting and Stopping the Server (Windows)
- Starting and Stopping the Server (Other)
- Lab Exercise – Installation
- Install PostgreSQL from source
- Create a database cluster
- Start the database server
- Connect to the server using psql
- Stop the database server
Configuration
- CSetting PostgreSQL Parameters
- Access Control
- Connection Settings
- Security and Authentication Settings
- Configure client/server connectivity
- Memory Settings
- Free Space Settings
- Kernel Resource Settings
- Log Management
- Background Writer Settings
- Vacuum Cost Settings
- Autovacuum Settings
Security
- Levels of security: pg_hba.conf, schemas and users and table level
- pg_hba.conf
Create and Managing PostgreSQL Database
- USERS
- GROUPS
- ROLES
- Object Ownership
- Accesss control
- Application Access
- Schemas and Search Paths
- Lab to create USERS, SCHEMAS, ROLES, Alter SEARCH_PATH
- GRANT and REVOKE privilege
Moving Data with PostgreSQLCOPY
- Backup & Recovery
- pg_dump
- pg_restore
- pg_dumpall
- File System Backup – copying DATA folder
- pg_start_backup and pg_stop_backup
- PITR – Point in Time Recovery
- Creating a base backup
- Archive command
- Recovery file parameters
- Lab to test PITR
Routine Database Maintenance Tasks
- Routine Vacuuming
- Recovering Disk Space
- Vacuum Full
- Updating Planner Statistics
- Preventing Transaction ID Wraparound Failures
- Routine Reindexing
Performance Tuning
- Explain and Explain Analyze
- Forcing Query Plan Selection
- Indexing
- Log Management
- Clustering Rows
- Partition Methods
- Partition Setup
- Partition Table Explain Plan
- Table space Management
High Availability & Replication
- Warm Standby
- Why Use Replication
- Replication
- Replication Limitations
- Replication Configuration
Connection Pooling
- Why Connection Pooling
- Pgpool-II setup
- Pgbouncer setup
Advance Monitoring Techniques
- Importance of Monitoring
- PGFouine – Log Analyzer
For more details:
RVR Towers, 4th Floor, No.1, AA Block,
1st Street, 3rd Main Road,
Annanagar, Chennai 600 040.
Landmark: Annanagar Roundtana,
Behind Nalli Silks Showroom,
Phone: 044-43486300 / 45082474/75
