POSTGRESQL SERVER DATABASE TRAINING

POSTGRESQL SERVER DATABASE TRAINING MARC & ZED

5 DAYS | 35 HOURS TRAINING PROGRAMME

ONLINE OR FACE-TO-FACE TRAINING

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.


WHAT IS POSTGRESQL

PostgreSQL, often referred to as "Postgres," is a powerful open-source relational database management system (RDBMS) known for its advanced features, extensibility, and strong emphasis on standards compliance. It is widely used in various applications, ranging from small-scale projects to large enterprise-level systems.


Key Features of PostgreSQL:


Applications of PostgreSQL:


LEARNING OUTCOME

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


METHODOLOGY

This is a 5 days hands-on course. The trainer will explain how to make a database and maintain it using step-by-step practice. You will also be taught tips & tricks for good database management You will design your own database and also modify existing databases. You will get ample practise so that you can apply them in practical situations in database management.

 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 to 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 EDB 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 in Database Tables 

• Edb*Loader

 FOR PRICING AND BOOKING THIS COURSE, PLEASE E-MAIL US AT janice@marcnzed.com 

OR CALL +6012 451 4977 (MALAYSIA) OR  +65 9052 3859 (SINGAPORE)


Certificate

Upon successful completion of the course, participants will be awarded a verified digital certificate by Marc & Zed Training Singapore in collaboration with Marc & Zed SPACES Malaysia