Projects
Assignments
Overview
PostgreSQL training teaches attendees how to build, query, program stored procedures in PL/pgSQL, and administer the popular, open source PostgreSQL database platform.
Objectives
At the end of PostgreSQL training course, participants will be able to
- Find PostgreSQL documentation and resources
- Work with PostgreSQL functions and create own functions
- Program with PL/pgSQL
- Install and setup PostgreSQL securely
- Work with basic administration tasks such as user creation, cataloging, and backing up
- Use SLONY and PGPOOL
- Work with inheritance in PostgreSQL
- Use Tablespaces, EXPLAIN Statement, PITR and monitor the database
- Tune the PostgreSQL Server
Prerequisites
Participants must have a solid understanding of basic relational database principles and SQL SELECT statements prior to this training.
Course Outline
Introduction and Architectureedforce2021-06-28T12:39:11+05:30
- History and Overview
- PostgreSQL Major Features
- PostgreSQL Limits
- PostgreSQL Architecture
- MVCC
- Write Ahead Logs
- The Background Writer
- PostgreSQL Data Types
Installationedforce2021-06-28T12:41:58+05:30
- Overview
- Binary Installation
- Source Installation
- Initializing a PostgreSQL cluster
- Starting and stopping a PostgreSQL cluster
- Automatic startup/shutdown
Configurationedforce2021-06-28T12:42:05+05:30
- Access Control
- Connection settings
- Security and Authentication settings
- Memory settings
- Kernel resource settings
- Log management
- Background writer settings
- Vacuum cost settings
- Autovacuum settings
Managing PostgreSQL Databasesedforce2021-06-28T12:42:19+05:30
- PostgreSQL Clusters
- PostgreSQL Databases
- Tables, Joins and Aggregates
- PostgreSQL Indexes and Foreign Keys
Roles and Securityedforce2021-06-28T12:42:28+05:30
- Schemas
- Tables, Views and Rules
- Users, Groups and Roles
- Tables and Sequences
- Object Security
Tablespaces, Partitioning, and Advanced Featuresedforce2021-06-28T12:42:36+05:30
- Vacuum
- Tablespaces
- Inheritance
- Table Partitioning
- Windowing Functions
- Transactions
- Concurrency Control
- Functions and Operators
- Type conversion
- Full text search
Routine DBA Tasks and Best Practicesedforce2021-06-28T12:42:52+05:30
- Log Management
- Query analysis
- Routine Vacuuming
- Recovering disk space
- Managing Planner statistics
- REINDEX
Monitoring and Statisticsedforce2021-06-28T12:45:28+05:30
- Database Logs
- OS Process monitoring
- The PostgreSQL Statistics Collector
- Statistics Views
- Statistics Functions
Performance Tuningedforce2021-06-28T12:45:35+05:30
- OS Tuning
- Hardware configuration
- Transaction logs
- Tablespaces and Partitioning
- Checkpoint tuning
- Query tuning
Backup and Recoveryedforce2021-06-28T12:45:42+05:30
- The pg_dump utility
- The pg_dumpall utility
- Recovery options
- Point In Time Recovery
PostgreSQL Upgradesedforce2021-06-28T12:45:50+05:30
- The pg_upgrade utility
- RPM based upgrades
- Source based upgrades
- Replication based upgrades
- Rolling back
Replication and HAedforce2021-06-28T12:45:55+05:30
- Streaming Replication
- Synchronous Replication
- Replication Slots
- Failover management
- Connection Poolers
- HA configurations
RULEsedforce2021-06-28T12:46:18+05:30
- Overview
- SELECT Views
- Updating a View
- Materialized Views
- Rules and privileges
The PL/pgSQL Languageedforce2021-06-28T12:47:27+05:30
- Structure
- Declarations
- Expressions
- Statements
- Control Structures
- Cursors
- Errors and Messages
- Trigger Procedures
