Open
Request a Info

PostgreSQL

HomeTironation  > PostgreSQL

PostgreSQL

Overview

What Will Learn?

Introduction

• Introduction to NoSQL (1 hour)
• What Is NoSQL?
• Why NoSQL databases are required. • Types of NoSQL Database
• NoSQL vs SQL Comparison
• ACID & BASE Property
• CAP Theorem
• Benefits of NoSQL databases
• Installation
• Start and Stop the MongoDB process

Curriculum

Introduction (Time Require - 25-30 Min)

  • History of PostgreSQL
  • Major Features
  • New Features of PostgreSQL
  • Multi Version Concurrency Control
  • Write-Ahead Logging
  • Architectural Overview
  • Limits

 

PostgreSQL System Architecture (Time Require - 1:00 - 1:30 hours)

  • Architectural Summary
  • Shared Memory
  • Statement Processing
  • Utility Processes
  • Disk Read Buffering
  • Write Buffering
  • Background Writer CleaningScan
  • Commit &Checkpoint
  • Physical DatabaseArchitecture
  • Data Directory Layout
  • Installation Directory Layout
  • Page Layout

Installation (Time Require - 1:30 Hours)

  • OS User &Permissions
  • Installation
  • Setting environment variables
  • Clusters
  • Creating a database cluster
  • Starting and Stopping the Server
  • (pg_ctl)
  • Connect to the server using psql
  • Connect to the server using PG Admin

Configuration (Time Require - 2 - 2:30 hours)

  • Setting PostgreSQL Parameters
  • Access Control
  • Connection Settings
  • Security and Authentication
  • Settings
  • Memory Settings
  • Query Planner Settings
  • WAL Settings
  • Log Management
  • Background Writer Settings
  • Statement Behavior
  • Vacuum Cost Settings
  • Autovacuum Settings

Creating and Managing Databases, Schemas, Users, Tablespaces (Time Require - 30-45 Min)

  • Object Hierarchy
  • Creating Databases
  • Creating Schemas
  • Schema Search Path
  • Roles, Users &Groups
  • Access Control

Postgres Data Dictionary (Time Require - 15-20 Min)

  • The System Catalog Schema
  • System Information views/tables
  • System Information Functions

(Time Require - 15-20 Min)

  • Registering a server
  • Query Tool
  • Databases
  • Schemas
  • Indexes

Security Basics (Time Require - 30-50 Min)

  • Authentication
  • Authorization
  • Levels of security
  • Pg_hba.conf file
  • Users
  • Object ownership
  • Access control
  • Application access parameters

Backup and Recovery & Point-in Time Recovery (Time Require - 2:00 hours)

  • Backup Types
  • Pg_dump
  • Pg_dumpall
  • Pg_restore
  • Cluster Dump
  • Offline Copy Backup
  • Continuous Archiving
  • Pgbackrest
  • Pg_basebackup
  • Point-In Time Recovery
  • BARMAN

Routine Maintenance (Time Require - 30 Mins)

  • Explain and Explain Analyze
  • Table Statistics
  • Updating Planner Statistics
  • Vacuuming
  • Scheduling Auto Vacuum
  • Preventing Transaction ID Wrap around  Failures
  • The Visibility Map
  • Routine Reindexing

Moving Data (Time Require - 15 Mins)

  • The COPY Command

  • Examples

Transactions and Concurrency (Time Require - 45 Min)

  • Transaction Definition
  • Effects of Concurrency on
  • Transactions
  • Transaction Isolation Levels
  • Multi-Version Concurrency Control
  • Overview (MVCC)
  • MVCC Example
  • Internal Identifiers
  • Transaction Wraparound
  • MVCC Maintenance
  • MVCC Demo

Performance Tuning (Time Require - 2 hours)

  • Hardware Configuration
  • OS Configuration
  • Server Parameter Tuning
  • Connection Settings
  • Memory Parameters
  • Memory settings for Planner
  • WAL Parameters
  • Explain Plan
  • Explain Example
  • Statistics Collection
  • Indexes
  • Examining Index Usage
  • Tips for Inserting Large Amount of Data
  • Some Notes About pg_dump Non-Durable Settings

Replication & Failover (Time Require - 3 hours)

  • Database High Availability
  • Causes of Data Loss
  • Plan for Common Errors
  • Selection Criteria
  • High Availability Options
  • Hot Streaming Replication, Architecture and Setup
  • Streaming Replication Example
  • Repmgr Installation and Configuration
  • Switchover Using repmgr
  • Promote using repmgr
  • Automatic Failover using repmgrd
  • Troubleshooting Replication Lag

Table Partitioning (Time Require - 45 Min)

  • Partitioning
  • Partitioning Methods
  • When to Partition
  • Partitioning Setup
  • Partitioning Example
  • Partitioning and Constraint
  • Exclusion
  • Caveats

Different Upgrade Methods (Time Require - 45 Min)

  • Pg_dump/pg_restore
  • Upgrading PostgreSQL Database
  • Migration from Oracle to Postgres

Database Monitoring (Time Require - 45 Min)

  • Database Statistics
  • The Statistics Collector
  • Database Statistic Tables
  • Operating System Process Monitoring
  • Current Sessions and Locks
  • Log Slow Running Queries
  • Disk Usage

Other Tools – All Tools and Topics covered with Practical’s

  • Pgbadger (time require – 30 Min)
  • Pgadmin (time require – 25 Min)
  • Pgbouncer (time require – 30 Min)
  • Pgpool (time require – 30 Min)
  • Repmgr (time require – 1 hour)
  • ora2pg
  • BARMAN
  • Pg_backrest
Call Now Button