Installing POSTGRE SQL

• 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
• Using PSQL Tool

Performance Tuning

• Explain and Explain Analyze
• Forcing Query Plan Selection
• Helping the Optimizer
• Using Views
• Creating Rules with Views
• Indexing
• Constraints
• Clustering Rows

Backup & Recovery

• Log Management
• Routine Vacuuming
• Recovering Disk Space
• Vacuum Full
• Updating Planner Statistics
• Preventing Transaction ID Wraparound
• Failures
• Routine Reindexing
• Dump & Restore
• 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

Partitioning and Tablespaces

• Partition Methods
• Partition Setup
• Table space Management
• Monitoring & Statistics
• Database Logs
• OS Process Monitoring
• Statistics Collector
• Statistics Parameters

Security

• Levels of security: pg_conf, schemas and users and table level.
• USERS; GROUPS; ROLES
• Object Ownership
• Access control
• Application Access
• pg_hba.conf
• Schemas and Search Paths
• Lab to create USERS, SCHEMAS, ROLES,
• Alter SEARCH_PATH, GRANT and REVOKE
• privileges