Tag: PostgreSQL

Duration

21 hours (usually 3 days including breaks)

Requirements

• Experience with PostgreSQL
• Python programming experience

Audience

• Developers
• Administrators

Overview

PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS built for handling large data workloads. Python is a dynamic and flexible programming language that supports various databases, such as MySQL, Oracle, PostgreSQL, etc.

This instructor-led, live training (online or onsite) is aimed at developers and administrators who wish to use PostgreSQL with Python to build secure, reliable, and stable applications at scale.

By the end of this training, participants will be able to:

• Install, configure, and connect PostgreSQL with Python.
• Get a refresher on Python programming fundamentals.
• Understand the benefits of using PostgreSQL with Python.
• Learn how to perform basic SQL operations with Python.
• Learn how to use the advanced PostgreSQL features with Python.
• Implement data safety, security, monitoring, and optimization techniques.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Overview of PostgreSQL features and architecture
• Python programming fundamentals
• Advantages of Python PostgreSQL 

Getting Started

• Installing PostgreSQL
• Installing Python, Django or Flask
• Installing psycopg2
• Connecting to PostgreSQL with Python

Python and SQL Operations

• The basics of SQL language
• Database schema and tables
• Writing a Python query
• Creating tables
• Inserting data
• Manipulating data
• Using limit clause
• Joining tables
• Full-text search
• Working with SQLite
• Examples of Python applications

Advanced PostgreSQL with Python

• Table inheritance
• Nested transactions
• User-defined types
• VIEWS, HAVING, DISTINCT
• Working with dates and times
• Multi-version concurrency control (MVCC)
• Charts, graphs, and reports using matplotlib

Administration and Security

• PostgreSQL data safety
• Restricting access using whitelist
• Implementing replication
• Database migration
• Backup and restore
• Monitoring queries
• Performance tuning
• Preventing SQL injection attacks

Troubleshooting

Summary and Next Steps

Duration

21 hours (usually 3 days including breaks)

Requirements

• Basic knowledge in database administration
• Familiarity with SQL language

Audience

• Database administrators
• System administrators

Overview

PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS. It is built to handle large data workloads while maintaining stability, reliability, and optimal performance.

This instructor-led, live training (online or onsite) is aimed at database admins and sysadmins who wish to use PostgreSQL to manage large-scale data while optimizing the database systems for high availability and performance.

By the end of this training, participants will be able to:

• Install and configure PostgreSQL.
• Understand the features and architecture of PostgreSQL.
• Learn how to perform database operations with SQL.
• Perform database backup and recovery to prevent data loss.
• Configure the server for high availability.
• Learn the tools and techniques for high performance tuning.
• Explore the external tools for monitoring and observability.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Overview of PostgreSQL features and architecture
• Basics of database administration

Getting Started

• Installing PostgreSQL
• Configuring a pgAdmin 4 client
• Creating a database user
• Creating a database

Database Operations

• SQL syntax, functions, and operators
• Data definition and data types
• User-defined data types
• Table schemas
• Creating and modifying tables
• Manipulating data in tables
• Querying and filtering data
• Exploring constraints
• Conversion functions
• Joining multiple tables
• Combining queries
• Indexes and full-text search
• Concurrency control
• Regular expressions

Database Server Administration

• Security and encryption
• Access roles and permissions
• Backup and restore
• Upgrading PostgreSQL
• High availability
• Load balancing and replication
• Statistics and monitoring

High Performance Tuning

• Resource management
• Monitoring query performance
• Parameter tuning
• Index optimization
• Query optimization
• Scalability and partitioning

External Tools for Monitoring and Observability

• pgAdmin
• pgBadger
• pgbench
• node-exporter Prometheus, Grafana
• Zabbix

Troubleshooting

Summary and Next Steps

Duration

21 hours (usually 3 days including breaks)

Requirements

• An understanding of RDBMS
• Experience with SQL

Audience

• System administrators
• Developers

Overview

PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS. It is built to handle large data workloads while maintaining stability, reliability, and optimal performance.

This instructor-led, live training (online or onsite) is aimed at sysadmins and developers who wish to use PostgreSQL 13 to set up, build, and manage databases for high-performance applications.

By the end of this training, participants will be able to:

• Install and configure PostgreSQL 13.
• Understand the features, architecture, and fundamentals of operating, managing, and maintaining databases with PostgreSQL 13.
• Learn how to configure Postgres database tables and schemas.
• Learn how to perform data manipulations and queries.
• Explore the different programming interfaces (client and server) to fully utilize and extend functionalities in PostgreSQL 13.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• PostgreSQL vs MySQL vs Oracle
• Overview of PostgreSQL 13 features and architecture

Getting Started

• Setting up a database server
• Installing PostgreSQL 13
• Creating and accessing a database
• SQL syntax, commands, and functions

Configuring a Postgres Database

• Data definition, default values, and constraints
• Setting up and modifying tables
• Managing privileges and security policies
• Creating database schemas
• Implementing table inheritance and partitioning
• Adding, updating, and querying data
• Data types, functions, and operators
• Using indexes and running full-text search
• Understanding concurrency control

Managing a Database Server

• Upgrading a PostgreSQL cluster
• Securing the server and encrypting data
• Configuring server parameters
• Enabling client authentication
• Setting up access roles and permissions
• Managing multiple databases
• Performing database maintenance tasks
• Backing up and restoring data
• Optimizing database performance and reliability
• Monitoring database activity and disk usage
• Implementing logical replication
• Running regression tests

Client-Side Programming

• Using the libpq C library   and large object interface
• Executing embedded SQL statements
• Exploring the information schema
• Other PostgreSQL client applications

Server-Side Programming

• Extending the SQL query language
• Writing event trigger functions
• Implementing rule systems
• Using procedural languages (PLs)
• Exploring TCL, Perl, and Python PLs
• Executing server programming interfaces
• Examples of logical decoding
• Tracking replication progress

Troubleshooting

Summary and Next Steps

Duration

7 hours (usually 1 day including breaks)

Requirements

It is recommended to take this training only if you have a prior knowledge of PostgreSQL.

Overview

This training is designed for Database Administrators and Architects who want to learn Streaming Replication in PostgreSQL and how pgpool-II can be used to achieve automatic failovers and setup a highly available PostgreSQL Database Cluster.

Course Outline

• Introduction
• Streaming Replication
  • Streaming Replication
  • Sync or Async
  • Cascaded Replication
  • Transaction Log Archiving 
  • Base Backups
  • Setup Streaming
  • Monitoring Streaming
  • Handling Conflicts
• High Availability Setup
  • Connection Pooling and HA setup using pgpool-II
    • Installation
    • Configuration
    • Pool Setup
    • HA Setup
  • Demos and Labs

Duration

7 hours (usually 1 day including breaks)

Requirements

• Experience with database management systems
• Familiarity with cloud computing

Audience

• Database administrators
• Cloud architects

Overview

On-premise, Oracle database management systems can be costly and difficult to maintain for mid and large-scale organizations. Migrating Oracle databases to Azure SQL (also known as Microsoft Azure SQL Database, Microsoft Azure SQL or AzureSQL) or PostgreSQL (also known as Postgres or Postgre SQL) is a practical solution for growing businesses to address database management, maintenance, and scalability at an optimum cost.

This instructor-led, live training (online or onsite) is aimed at database administrators and cloud architects who wish to migrate Oracle databases to Azure SQL or PostgreSQL.

By the end of this training, participants will be able to:

• Compare Azure SQL with PostgreSQL to choose the database that best meets business objectives and needs.
• Prepare databases to move definitions, data, and procedures from Oracle to AzureSQL or PostgreSQL database system.
• Perform post-migration steps to test, optimize, update, and maintain a migrated database.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Overview of Oracle Migration to Azure SQL or PostgreSQL

Designing a Proof of Concept Solution

Comparing Azure SQL Database With PostgreSQL Database

Configuring SQL Server Instances

Setting up Oracle Express Edition

Preparing the Database for Migration

Migrating SQL Server to Azure SQL

Deploying Post Upgrade Enhancements

Migrating Oracle Database to SQL Server 2017

Migrating the Application to Azure SQL

Migrating SQL Server to PostgreSQL

Preparing Oracle Database Migration to PostgreSQL

Migrating the Database and Application to PostgreSQL

Performing Post-Migration Tests and Optimization

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

It is recommended to attend this training only if you have a prior knowledge of PostgreSQL or have attended PostgreSQL Essentials Training

Overview

This training is designed for Database Administrators and Architects who want to learn the tools and techniques for implementing Backups, High Availability and Database Security in PostgreSQL.
You will also learn how to find slow queries, monitor database performance and tune PostgreSQL for Performance.

Course Outline

• Introduction
  • Architectural Overview
  • Lab Machine Setup
     
• Performance Monitoring and Tuning
  • Monitoring Slow Queries
  • SQL Tuning
  • Table Partitioning
  • Operating System Best Practices
  • Parameter Tuning
  • Extensions
  • Benchmarking
  • Monitoring Best Practices 
  • Demos and Labs
     
• Database Backups and Restores
  • Logical or SQL Dump Backups
  • Restoring SQL Dumps
  • Physical Backups – Offline
  • Restoring Offline Backups
  • Physical Backups – Online
  • Write Ahead Log(WAL) Archiving
  • Recovery configuration
  • Point in Time Recovery using WAL Archives
     
• High Availability
  • Transaction Log Archiving 
  • Streaming Replication
  • Connection Pooling and HA setup using pgpool-II
  • Installation
  • Configuration
  • pgpool Setup
  • HA Setup
  • Demos and Labs
     
• Database Security
  • Users and Groups
  • Access Control
  • Row Level Security 
  • Host Based Access Control
  • Data Encryption using pgcrypto
  • Demos and Labs
     
• Database Upgrades
  • PostgreSQL Versioning
  • Plan for Upgrade
  • Upgrade using pg_dump
  • Upgrade using pg_upgrade
  • In Place or Not-in Place Upgrade
  • Post Upgrade Tasks

Duration

14 hours (usually 2 days including breaks)

Requirements

It is recommended to take this training only if you have a prior knowledge of PostgreSQL.

Overview

This training is designed for Database Administrators and Architects who want to tune PostgreSQL Database for Performance. Learn how to log slow workload and find the possible pain points in a query. This topic also covers the most important parameters that must be tuned for optimal performance. 

Course Outline

• Introduction
• Query Tuning
  • Logging in PostgreSQL
  • Query Plans
  • Statistics
  • SQL Best Practices
  • Indexes
  • Table Partitioning
• Performance Tuning
  • Operating System Best Practices
  • Server Parameter Tuning
  • Extension – pg_stat_statements
  • Demo and Labs
• Benchmarking
  • Database Benchmarking
  • Pgbench
  • Reading the Results
  • Demo and Labs

Duration

35 hours (usually 5 days including breaks)

Requirements

• Experience with SQL
• Experience with the Linux command line
• Experience with basic UNIX shell programming

Audience

• Database administrators
• Systems administrators
• Developers with database experience

Overview

PostgreSQL (or Postgres) is one of the most popular open-source relational databases.

In this instructor-led, live training (onsite or remote), participants will learn the general and advanced aspects of PostgreSQL, including administration, performance turning, advanced development, and replication as they step through a series of hands-on, live-lab exercises involving setup, configuration, testing, deployment and troubleshooting.

By the end of this training, participants will be able to:

• Use advanced administration techniques to configure and manage a PostgreSQL server.
• Optimize the database and queries for maximum performance.
• Replicate and scale a PostgreSQL server.

Format of the Course

• Part lecture, part discussion, exercises and heavy hands-on practice

Note

• This course is based PostreSQL 9 but can be adapted for a different version.
• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

PostgresSQL Setup and Configuration

Overview of PostgreSQL Architecture and Advanced Features

PostgreSQL Administration

• Connecting to the Database
• Overview of PgAdmin III
• Configuring the Server
• Setting up and Managing Users
• Overview of Database Object Types
• Setting Permissions & Security
• Querying the Database
• Monitoring the Database
• Ensuring Atomicity and Isolation
• Backing up and Restoring PostreSQL
• Migrating Data to Another System
• Working with PostreSQL Logs

Performance Optimization

• Reducing I/O with HOT
• Locking & Concurrency
• Evaluating the Design of the Database
• Optimizing SQL Queries with EXPLAIN
• Inspecting the Workload
• Benchmarking and Profiling
• Optimzing Joins
• Using Indexes Efficiently

PostgreSQL Replication

• Overview of PostgreSQL Replication
• High Availability in PostgreSQL
• Streaming Replication
• Managing Clusters Using repmgr
• Running Queries on Database in Hot Standby
• Synchronous Replication
• File based Log Shipping
• Trigger-based Logical Replication with Slony & Londiste
• Connection Pooling
• Upgrading PostgreSQL
• Sharding and Scaling PostgreSQL

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

There are no specific requirements needed to attend this course. It is recommended to start with this training if you have never taken a formal training of PostgreSQL

Overview

This training is designed for Database Administrators and Architects who want to learn PostgreSQL from basics.

You will learn PostgreSQL architecture, installation, designing as well as how to perform backup and recovery databases in PostgreSQL. 

Course Outline

• Introduction
• System Architecture and Internals 
  • Process Architecture
  • Memory Internals
  • Storage Architecture 

• Installations
  • Downloading PostgreSQL 
  • Preparing System for Installation
  • Install PostgreSQL 
  • Create, Start, Stop a Database Server
  • Viewing Information 

• PSQL Tool
  • Connecting to a Database
  • Executing SQL
  • psql Meta Commands

• PGAdmin4
  • Connecting to a Database
  • Basic Tasks using GUI

• Database Designing
  • Database Users
  • Creating and Managing Databases
  • Schemas
  • Tables
  • Data Types
  • Sequences
  • Views
  • Materialized Views

• Database Backups 
  • Logical or SQL Dump Backups
  • Physical Backups – Offline
  • Physical Backups – Online
  • Archiving

• Restoring Backups
  • Restoring SQL Dumps
  • Restoring Offline Backups
  • Point in Time Recovery
  • Partial Restore

• Proactive Maintenance 
  • Database Statistics
  • Data Fragmentation and Vacuuming
  • Data Re-organization
  • Automatic Maintenance Configuration 
  • Re-indexing 

• System Parameters
  • Parameter File
  • Changing Server Parameters
  • Resource Parameters
  • Planner Parameters
  • Logging Parameters
  • Transaction Logging Parameters

Duration

14 hours (usually 2 days including breaks)

Requirements

A working knowledge of SQL

Overview

This course provides programmatic interaction with PostgreSQL databases. Learn techniques, syntaxes and structures needed to develop quality applications using PostgreSQL backend. This training also covers SQL Tuning covering best practices for writing efficient SQL.

Target audience includes developers who want to use or extend PostgreSQL, as well as database architects.

Course Outline

Introduction to PostgreSQL

• A Brief History of PostgreSQL
• Features
• Internals Summary
• Limits and Terminology

Installation and Configuration

• Pre-requisites
• Installation from Packages and Creating Database
• Installation from Source Code
• Client Installation
• Starting and Stopping a Database Server
• Environment Setup

The SQL Language

• SQL Syntax
• Data Definition
• Data Manipulation
• Queries
• Data Types
• JSON
• Functions and Operators
• Type Conversion
• Indexes

Transactions and Concurrency

• Transactions and Isolation
• Multi-Version Concurrency Control

Client Interfaces

• Command Line Interface – psql
• Graphical Interface – pgadmin4

Server Programming

• Extending SQL
• Triggers
• The Rule System
• Procedural Languages
• PL/pgSQL – SQL Procedural Language
• Error Handling
• Cursors

Foreign Data Wrappers

• Extension in PostgreSQL
• Adding FDW in a Database
• postgres_fdw
• file_fdw
• Other FDWs

SQL Tuning

• Logging in PostgreSQL
• Query Plans
• Optimizing Queries
• Statistics
• Planner Parameters
• Parallel Query Scans
• SQL Best Practices
• Indexes
• Table Partitioning