Cart 0
Training
Course Information
Course Name
Postgresql: Postgresql 13 Associate
Duration
5 Days
Overview
Audience Profile
Prerequisities
At Course Completion
Course Outline
Overview
Audience Profile
Prerequisities
At Course Completion
Course Outline
Course Outline
Module 1: Introduction and Architectural Overview
- EDB Postgres Platform
- History of PostgreSQL
- Major Features
- Architectural Overview
- General Database Limits
- Common Database Object Names
Module 2: System Architecture
- Architectural Summary
- Process & Memory Architecture
- Utility Processes
- Connection Request-Response
- Disk Read Buffering
- Disk Write Buffering
- Background Writer Cleaning Scan
- Commit & Checkpoint
- Statement Processing
- Physical Database Architecture
- Data Directory Layout
- Installation Directory Layout
- Page Layout
Module 3: PostgreSQL Installation
- OS User and Permissions
- Installation Options
- Installation of PostgreSQL
- Initializing Database Instance
- Database Cluster Defaults
- Starting Database Instance
- Connecting to a Database
- Setting Environmental Variables
Module 4: Database Clusters
- Database Clusters
- Creating a Database Cluster
- Starting and Stopping the Server (pg_ctl)
- Connect to the Server Using psql
Module 5: Configuration
- Server Parameter File – postgresql.conf
- Viewing and Changing Server Parameters
- Configuration Parameters – Security, Resources and WAL
- Configuration Parameters – Error Logging, Planner and Maintenance
- Viewing Compilation Settings
- Using File Includes
Module 6: Creating and Managing Databases
- Object Hierarchy
- Creating Databases
- Users and Roles
- Access Control
- Creating Schemas
- Schema Search Path
Module 7: User Tools - Command Line Interfaces
- Introduction to psql
- Conventions
- Connecting to Database
- psql Command Line Parameters
- Entering psql Commands
- psql Meta-Commands
- psql SET Parameters
- Conditional Commands
- Information Commands
Module 8: GUI Tools
- Introduction to pgAdmin 4
- Installing pgAdmin 4 on Linux
- Registering a server
- Viewing and Editing Data
- Query Tool
- Databases
- Languages
- Schemas
- Database Objects
- Maintenance
- Tablespaces
- Roles
Module 9: Security
- Authentication and Authorization
- Levels of Security
- conf File
- Row Level Security
- Object Ownership
- Application Access Parameters
Module 10: SQL Primer
- Data Types
- Structured Query Language (SQL)
- DDL, DML and DCL Statements
- Transaction Control Statements
- Tables and Constraints
- Views and Materialized Views
- Sequences
- Domains
- SQL Joins
- Using SQL Functions
- SQL Format Functions
- Quoting in PostgreSQL
- Indexes
Module 11: Backup and Recovery & Point-in Time Recovery
- Backup Types
- Database SQL Dumps
- Restoring SQL Dumps
- Offline Physical Backups
- Continuous Archiving
- Online Physical Backups Using pg_basebackup
- Verify Backups
- Point-in-time Recovery
- Recovery Settings
Module 12: Routine Maintenance Tasks
- Updating Optimizer Statistics
- Handling Data Fragmentation using Routine Vacuuming
- Preventing Transaction ID Wraparound Failures
- Automatic Maintenance using Autovacuum
- Re-indexing in Postgres
Module 13: Data Dictionary
- The System Catalog Schema
- System Information Tables
- System Information Functions
- System Administration Functions
- System Information Views
Module 14: Moving Data
- Loading flat files
- Import and export data using COPY
- Examples of COPY Command
- Using COPY FREEZE for performance
- Introduction to EDB*Loader for EDB Postgres Advanced Server
