PostgreSQL Administration
=====
—–
Course Id : DATA-PGRS
Duration : 48 Hours
Overview
—–
* PostgreSQL is an open source object-relational database system and its commonly known as Postgres
* A DBA’s primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed
* In this course you will learn how to build, query, administer, backup and secure PostgreSQL
Pre-Requisites
——-
* Participants should have the knowledge about SQL and Database concepts
* Knowledge of Linux environment is preferred
Training Objectives
——
* How to install and setup PostgreSQL
* Understand PostgreSQL Architecture
* How to create databases and database structures
* How to compile source code
* How to Perform backup and disaster recovery
* Work with inheritance, partitioning, and Tablespaces
* How to Manage free space
* How to Secure PostgreSQL
* How to create high availability and high performance solutions
Course Structure
—–
* We provide more focus on hands-on in our technical courses (typically 80% hands-on/20% theory)
* Students get the capability to apply the material they learn to real-world problems
Materials Provided
—–
* PDF of slides and hands-on exercises
* Access to instance with lab environment
Software Requirements
—–
* Any current internet browser
Hardware Requirements
—–
* Processor: 1.2 GHz
* RAM: 512 MB
* Disk space: 1 GB
* Network Connection with low latency (<250ms) to Internet
Course Outline
## Daywise course outline
—–
## Day 1
—–
* Unit 1 : PostgreSQL Introduction and Architecture
* Unit 2 : PostgreSQL Data Types
* Unit 3 : PostgreSQL Installation
* Unit 4 : PostgreSQL Configuration
## Day 2
—–
* Unit 5 : Introduction to psql
* Unit 6 : Functions and Operators
* Unit 7 : Managing PostgreSQL Databases
* Unit 8 : PostgreSQL Roles and Security
## Day 3
—–
* Unit 9 : Moving Data with PostgreSQL
* Unit 10 : Tablespaces, Inheritance and Data Partitioning
* Unit 11 : VACUUM
* Unit 12 : Transactions and Concurrency Control
## Day 4
—–
* Unit 13 : Routine DBA Tasks and Best Practices
* Unit 14 : Monitoring and Statistics
* Unit 15 : PostgreSQL Tools Overview
* Unit 16 : PostgreSQL Performance Tuning
## Day 5
—–
* Unit 17 : PostgreSQL Backup and Recovery
* Unit 18 : PostgreSQL Upgrade Methods
* Unit 19 : PostgreSQL Streaming Replication
* Unit 20 : SLONY
## Day 6
—–
* Unit 21 : PostgreSQL High Availability
* Unit 22 : PostgreSQL and AWS
* Unit 23 : PostgreSQL RDS Overview
* Unit 24 : PostgreSQL Redshift Overview
* Unit 25 : The PostgreSQL Contribs
## Detailed course outline
—–
Unit 1 : PostgreSQL Introduction and Architecture
—–
* Introduction and History
* PostgreSQL Major Features
* PostgreSQL Architecture Overview
Unit 2 : PostgreSQL Data Types
—–
Unit 3 : PostgreSQL Installation
—–
* Platforms
* Binary Installation
* Source Installation
* Binary vs. Source – Pros and cons
* Initializing a PostgreSQL Cluster
* Starting and Stopping a PostgreSQL Cluster
* Automatic Startup / Shutdown
* Common Issues and Troubleshooting
Unit 4 : PostgreSQL Configuration
—–
* Access Control
* The postgresql.conf file
* Common Issues and Troubleshooting
Unit 5 : Introduction to psql
—–
* Command line parameters
* Meta Commands
* SET Commands
* psql Security
Unit 6 : Functions and Operators
—–
Unit 7 : Managing PostgreSQL Databases
—–
* Creating PostgreSQL Databases
* Creating Schemas
* Creating Tables
* Altering Tables
* SELECT and Joins
* Indexes and Foreign Keys
Unit 8 : PostgreSQL Roles and Security
—–
* Views
* Rules
* Users, Groups and Roles
* Sequences
* Object Security
Unit 9 : Moving Data with PostgreSQL
—–
* Basic DML
* COPY
* Other Tools
Unit 10 : Tablespaces, Inheritance and Data Partitioning
—–
* Tablespaces
* Inheritance
* PostgreSQL Data Partitioning
Unit 11 : VACUUM
—–
* Routine Vacuuming
* Benefits of Vacuuming
* Recovering Disk Space
* Updating Planner Statistics
* Transaction ID Wraparound Failure
Unit 12 : Transactions and Concurrency Control
—–
* Transactions
* Concurrency
Unit 13 : Routine DBA Tasks and Best Practices
—–
* Log Management
* Query Analysis
* Routine Vacuuming
* Recovering Disk Space
* Managing Planner Statistics
* REINDEX
Unit 14 : Monitoring and Statistics
—–
* Database Logs
* OS Process Monitoring
* The PostgreSQL Statistics Collector
* Statistics Views
* Statistics Functions
Unit 15 : PostgreSQL Tools Overview
—–
* PG Badger
* PG Bouncer
* PG Pool
* PGCLUU
* PG Admin
* PG Modeler
* MySQL Workbench
* pgbench
* Consistent State PTS
Unit 16 : PostgreSQL Performance Tuning
—–
* OS Tuning
* HW Configuration
* Transaction Logs
* Tablespaces and Partitioning
* Checkpoint Tuning
* Query Tuning
Unit 17 : PostgreSQL Backup and Recovery
—–
* pg_dump
* pg_dumpall
* Recovery Options
* Restore via a List File
* Point In Time Recovery (PITR) Based Backup
* PITR Based Recovery
Unit 18 : PostgreSQL Upgrade Methods
—–
* Minor Version Upgrades
* pg_upgrade
* RPM Based Upgrade
* Source Based Upgrade
* SLONY Based Upgrade
Unit 19 : PostgreSQL Streaming Replication
—–
* Overview
* Configuration
* Base Backup
* Recovery.conf
* Initializing Streaming Replication
* Standby Conflicts
* Monitoring
* Standby Promotion
* Cascading Replication
* WAL Shipping
* Replication Slots
* Synchronous Replication
Unit 20 : SLONY
* Overview
* Configuration and Setup
* Monitoring
* Executing DDL
* Adding Tables to Replication
* Switchover
* Failover
Unit 21 : PostgreSQL High Availability
—–
* Overview
* Replication Type Selection
* Connection Poolers
* Heartbeat Monitoring
* Failing Over
* Failing Back
Unit 22 : PostgreSQL and AWS
—–
Unit 23 : PostgreSQL RDS Overview
—–
Unit 24 : PostgreSQL Redshift Overview
—–
Unit 25 : The PostgreSQL Contribs
—–