COURSE OUTLINE 

(THIS IS A 5 DAYs TRAINING PROGrAMME)

DAY 1

Introduction EDB PostgreSQL Advanced Server (EPAS):

• EDB PostgreSQL Platform

• Facts about PostgreSQL and EDB PostgreSQL Advanced Server 

• History of EDB PostgreSQL Advanced Server

• Major Features

• Features of EDB PostgreSQL Advanced Server Database

• General Database Limits

• Common Database Object Names

System Architecture:

• Architectural Summary

• Process and Memory Architecture 

• Utility Processes

• Connection Request-Response

• Disk Read Buffering

• Disk Write Buffering

• Background Writer Cleaning Scan 

• Commit and Checkpoint

• Statement Processing

• Physical Database Architecture

• Data Directory Layout

• Installation Directory Layout

• Page Layout

EDB PostgreSQL Advanced Server Installation:

 • OS User and Permissions

• Installation Options

• Installation of EDB PostgreSQL Advanced Server 

• StackBuilder Plus

• Setting Environmental Variables

Database Clusters:

• Database Clusters

• Creating a Database Cluster

• Starting and Stopping the Server (pg_ctl) 

• Connecting tthe Server Using psql

DAY 2

Configuration:

• Setting Server Parameters

• Access Control

• Connection Settings

• Security and Authentication Settings 

• Memory Settings

• Query Planner Settings 

• WAL Settings

• Log Management

• Auditing

• Background Writer Settings

• Statement Behavior

• Parallel Query Scan Settings 

• Parallel Maintenance Settings 

• Vacuum Cost Settings

• Autovacuum Settings

• Just-in-Time Compilation

• Configuration File Includes

Creating and Managing Databases:

• Object Hierarchy

• Creating Databases 

• Users and Roles

• Access Control

• Creating Schemas

• Schema Search Path 

• EDB Clone Schema

User Tools – Command Line Interfaces:

• Partitioning Benefits

• Introduction tedb-psql 

• Conventions

• Connecting tDatabase

• edb-psql Command Line Parameters 

• Entering psql Commands

• psql Meta-Commands

• psql SET Parameters

• Conditional Commands

• Information Commands

GUI Tools:

• Overview and Features of EDB PostgreSQL Enterprise Manager Client 

• Access EDB PostgreSQL Enterprise Manager Client

• Register and Connect ta Database Server

• General Database Administration

• Object Browser 

• View Data

• Query Tool

• Server Status

DAY 3

Security:

• Authentication and Authorization 

• Levels of Security

• pg_hba.conf File

• Row Level Security

• Data Redaction

• Object Ownership

• Application Access Parameters

• What is SQL/Protect 

• What is EDB*Wrap

SQL Primer:

• Data Types

• Structured Query Language (SQL) 

• DDL, DML and DCL Statements

• Transaction Control Statements

• Tables and Constraints

• View and Materialized Views

• Sequences

• Domains

• SQL Joins

• Using SQL Functions

• SQL Format Functions

• Quoting in PostgreSQL

• Indexes

Backup, Recovery and PITR:

• Backup Types

• Database SQL Dumps

• Restoring SQL Dumps

• Offline Physical Backups

• Continous Archiving

• Online Physical Backups Using pg_basebackup 

• Point-in-Time Recovery

• Recovery Settings

• Backup and Recovery Tool (BART)

Routine Maintenance Tasks:

• 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

DAY 4

Data Dictionary:

• The System Catalog Schema

• System Information Tables

• System Information Functions

• System Administration Functions 

• System Information Views

• Oracle-like Dictionaries

Moving Data Using COPY Command:

• Loading Flat files

• Import and export data using COPY 

• Examples of COPY Command

• Using COPY FREEZE for performance

EDB*Plus:

• EDB*Plus

• Installing EDB*Plus

• Starting EDB*Plus

• EDB*Plus Commands

Tablespaces:

• Tablespaces and Data Files 

• pg_global and pg_default 

• Advantages of Tablespaces 

• Creating Tablespaces

• Changing Default Tablespace 

• Usage Example

• Altering Tablespaces

• Dropping Tablespaces

DAY 5

Oracle Compatibility:

• Oracle Compatibility

• Oracle Compatibility Settings 

• Data Types

• Database Objects

• SPL

• Built-in Packages

• Catalog Objects

• OCL and ECPGPlus

Migrating Oracle Objects tEPAS:

• Oracle tEDB PostgreSQL Advanced Server Migration 

• Migration Process

• Assessing Migrations

• Database Migration Assessment Tool

• Database Migration

• EDB PostgreSQL Migration Toolkit 

• Installation and Setup

• Migration Demwith MTK

• Application Migration

• Deployment

Data Loader:

• Loading Flat Files intDatabase Tables 

• Edb*Loader