EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

EDB PostgreSQL Advanced Server Database Administration – Essentials

Course outline

5 days
Discuss this programme →

Duration

5 days

Introduction

EDB PostgreSQL Advanced Server Database Administration – Essentials is a five-day course that focuses on teaching individuals all of the essential techniques on how to design, implement and manage EDB PostgreSQL Advanced Server. Learn how to administer enterprise-scale data solutions using the different tools available in EPAS.

Upon course completion, you will have the essential skills required to install, configure, manage and maintain EDB PostgreSQL Advanced Server and will be prepared to manage large, highly available databases and their security requirements with confidence.

Pre-requisite

  • Working knowledge of relational databases
  • Basic knowledge of Linux

Course Outline

Introduction tEDB 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

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

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

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

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

Programme scope, delivery arrangements and suitability are confirmed during enquiry.