PostgreSQL DBA

PostgreSQL is commonly known as Postgres and it is also open source database. PostgreSQL is a powerful, open source object-relational database system. A database administrator’s (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.

Course Overview

Course Objective

What will you learn?

Who Should Learn

Who should go for this training?
The following professionals can go for this course:

Prerequisite

What are the pre-requisites for this Course?

Course Content

1. Introduction and Architectural Overview

History of PostgreSQL
Major Features
PPAS Features
Architectural Overview
General Database Limits
Common Database Object Name

3. Installation

OS User & Permissions
Installation Options
Installation of PostgreSQL
StackBuilder
Setting Environmental Variables

5. User Tools: Command Line Interfaces

Introduction to PSQL and EDB-PSQL
Conventions • Connecting to Database
PSQL Command Line Parameters
Entering PSQL Commands
PSQL Meta-Commands
PSQL SET Parameters
Information Commands

7. Log Management

Background Writer Settings
Statement Behavior
Configuration File Includes

9. GUI Tools - Intro to PGADMIN

Introduction to PEM Client
Registering a server
Viewing and Editing Data
Query Tool
Databases
Languages
Schemas
Database Objects
Maintenance
Tablespaces
Roles

11. PG/SQL

Data Types
Structured Query Language (SQL)
Tables and Constraints
Manipulating Data using INSERT,UPDATE and DELETE
Creating Other Database Objects: Sequences, Views and Domains
Table Partitioning
Partitioning
Partitioning Methods
When to Partition
Partitioning Setup
Partitioning Example
Partitioning and Constraint Exclusion
Caveats

13. Backup and Recovery & Point-in Time Recovery

Backup Types
SQL Dump Backups
SQL Dump Options
Handling Large Databases
Restoring SQL Dumps
Cluster Dump
Offline Backups
Recovering a Database using Offline

15. Routine Maintenance

Database Maintenance
Maintenance Tools
Optimizer Statistics
Data Fragmentation
Routine Vacuuming
Vacuuming Commands
Preventing Transaction ID Wraparound Failures
Vacuum Freeze
The Visibility Map

17. 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 PPAS

19. Performance Tuning

Performance Tuning – Overview
Performance Monitoring using PEM
A Tuning Technique
Operating System Considerations
Server Parameter Tuning
Memory Parameters
Temporary File Parameters
WAL Parameters

21. Connection Pooling

Connection Pooling Overview
pgpool-II – Features
Connection pooling
Replication
Load balance
Limiting exceeding connections
Automatic failover
pgpool II – Installation and Configuration
Configuring pcp.conf, pgpool.conf
Setting up pool_hba.conf for Client Authentication (HBA)
Starting/Stopping pgpool-II

23. Upgrading Best Practices

Version Change and Upgrade
Need to Upgrade
Upgrade Plan
Upgrade Using pg_upgrade
Upgrading Best Practices

2. PostgreSQL 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

4. Database Clusters

Database Cluster
Creating a Database Cluster
Starting and Stopping the Server (pg_ctl)
Connect to the Server Using psql

6. Configuration

Setting PostgreSQL Parameters
Access Control
Connection Settings
Security and Authentication Settings
Memory Settings
Query Planner Settings
WAL Settings

8. Creating and Managing Databases

Object Hierarchy
Creating Databases
Users Access Control
Creating Schemas
Schema Search Path

10. Security

Authentication and Authorization
Levels of Security
pg_hba.conf File
Object Ownership
Application Access Parameters
Protecting Against Injection Attacks with SQL/Protect
Source Code Protection for Functions

12. Introduction to various SQL functionalities:

Quoting
SQL Functions
Concatenation
Nested Queries
Joins
Materialized Views
Updatable Views o Indexes

14. Backups

Online Directory Backups
Continuous Archiving
Backup with Low-level API
How to use pg_basebackup for Online Backups
Point-In-Time Recovery Concepts
Recovery Example
Backup and Recovery Tool (BART)

16. Data Dictionary

The System Catalog Schema
System Information Tables
System Information Functions
System Administration Functions
System Information Views
Oracle-like Dictionaries

18. SQL Tuning

Statement Processing
Common Query Performance Issues
SQL Tuning Goals
SQL Tuning Steps
Identify slow queries
Review the query execution plan
Optimizer statistics and behavior
Restructure SQL statements
Indexes
Review Final Execution Plan

20. Streaming Replication

Data Replication
Data Replication in PostgreSQL
Sync or Async • Log-Shipping Standby Servers
Log-Shipping Architecture • Streaming Replication
Hot Streaming Architecture • Cascading Replication
Setup Replication Using Archive
Setup Streaming Replication
Prepare the Primary Server
Synchronous Streaming Replication Setup
Configure Authentication
Take a Full Backup of Primary Server
Setting up the Standby Server
Adding Cascading Replicated Standby Server
Monitoring Hot Standby
Recovery Control Functions

22. Extension Modules

What is Extension Modules
Installing Extension Modules
Module Index
Monitoring
Database Monitoring
Database Statistics
The Statistics Collector
Database Statistic Tables
Operating System Process Monitoring
Current Sessions and Locks
Log Slow Running Queries
Disk Usage
Postgres Enterprise Manager
PEM – Features
PEM – Architecture

Modes of Training

Classroom Training

Live interactive sessions delivered in our classroom by our expert trainers with real-time scenarios.

Online Training

Learn from anywhere over the internet, joining the live sessions delivered by our expert trainers.

Self-Pace Training

Learn through pre-recorded video sessions delivered by experts at your own pace and timing.

Frequently Asked Questions

Who are the trainers?

Our trainer is an OCP & OCM certified consultant and has a significant amount of experience in working with the technology, having 18yrs of experience.

How do I join the live sessions?

Once you get registered, our back-end team will share you the details to join the session live over an online portal which can be accessed through a browser.

What if I miss a class?

Each of our live sessions is recorded. In case if you miss any, you can request us to share the link to that particular session.

How will I execute the practical?

For practical execution, our trainer/technical team will provide server access details to the student

Will you help us in building our own Lab Setup in our local Desktop/Laptop?

Yes. We do provide the step-by-step document which you can follow and if required our technical team will assist you.

What is the difference between live online and pre-recorded video training?

Live-Online training is where you can have a live session with the trainer and clarify queries parallelly.

Pre-recorded sessions are the recorded videos that will be provided to you that you can see, listen, and learn anytime at ur feasible place. For doubts in the videos, you can mail the trainer regarding the same.

What if I have queries after completion of the class?

You can contact our support team, or just drop an email to help@sarinfotechindia.com with your queries.

Is the course material accessible after completion of the course?

The course material and recorded videos which are provided during the course period. You can download it anytime.

Any offer/discount that I can avail?

Visit our website regularly to check discounts offers from time to time. However, we provide a discount for single participants & special discounts for 2 or more participants.

Will I get the refund if I cancel my enrollment?

* If the request for cancelation is made within 2 days of enrolment for class, 100% refunded.

* If the request made after 2 days, then Refund is made after deduction of the administration fee.