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:

1. Data Integrity and ACID Compliance: PostgreSQL ensures data integrity through support for ACID (Atomicity, Consistency, Isolation, Durability) properties, making it reliable for critical and transactional applications.

2. Extensibility: PostgreSQL allows users to define custom data types, functions, and extensions, making it highly adaptable to specific project needs.

3. Advanced Data Types: In addition to standard data types, PostgreSQL supports complex data types such as arrays, JSON, hstore, and geometric data types, enabling the storage and manipulation of diverse data.

4. Full Text Search: PostgreSQL offers robust full-text search capabilities, allowing efficient searching and indexing of textual data.

5. Geospatial Support: It includes native support for geospatial data and provides functionalities for spatial queries and data visualization.

6. Concurrent Operations: PostgreSQL is designed to handle concurrent read and write operations effectively, providing good performance even under heavy workloads.

7. Extensive Indexing: PostgreSQL supports various index types, including B-tree, hash, and generalized search trees, optimizing query performance.

8. Join Optimization: The query planner of PostgreSQL optimizes joins and subqueries to improve query execution speed.

9. Foreign Data Wrappers (FDW): FDW allows PostgreSQL to interact with remote data sources, enabling seamless integration with other databases or data systems.

10. Replication and High Availability: PostgreSQL offers built-in replication and high-availability solutions like streaming replication and logical replication to ensure data redundancy and fault tolerance.

11. Security Features: PostgreSQL includes robust security mechanisms, supporting SSL encryption, role-based access control (RBAC), and row-level security to protect data.

12. Community Support and Active Development: PostgreSQL has a large and active community of developers and users, ensuring continuous improvement and regular updates.

Applications of PostgreSQL:

1. Web applications and content management systems

2. Geographic information systems (GIS)

3. Business intelligence and data analytics

4. E-commerce platforms

5. Social media applications

6. Financial systems and banking applications

7. Online gaming and mobile applications

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.

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