PostgreSQL

Introduction

• What are Relational Databases?
• RDBMS Benefits and Limitations
• PostgreSQL vs Other RDBMS
• PostgreSQL vs NoSQL Databases

Basic RDBMS Concept

Object Model

• Databases
• Tables
• Rows
• Columns
• Schemas
• Data Types

Relational Model

• Domains
• Attributes
• Tuples
• Relations
• Constraints
• NULL

High Level Database Concepts

• ACID
• MVCC
• Transactions
• Write-ahead Log
• Query Processing

Installation and Setup

• Query Processing
• Package Managers
• Using Docker

Managing Postgres

• Using `systemd`
• Using `pg_ctl`
• Using `pg_ctlcluster`

Installation and Setup

• Connect using `psql`
• Deployment in Cloud

Learn SQL Concepts

DDL Queries

• For Schemas 
• For Tables
• Data Types

DML Queries

• Querying Data

• Modifying Data
• Filtering Data
• Joining Tables
• Import / Export using `COPY`

Advanced Topics

• Transactions
• CTE
• Subqueries
• Lateral Join
• Grouping S
• et Operations

Configuring PostgreSQL

• Reporting Logging and Statistics
• Following postgres.conf configurations and more

• Resources Usage
• Write-ahead Log
• Vacuums
•  Replication
• Query Planner
• Checkpoints Background Writer

• Adding Extra Extension

Postgres Security Concepts

• Authentication Models
• Roles
• pg_hba.conf
• SSL Settings

Postgres Security Concepts

• Default Privileges
• Grant / Revoke
• Object Priviliges
• Authentication Models
•  Roles
• pg_hba.conf
• SSL Settings
• Advanced Topics
• Row-Level
• Security SELinu

Develop Infrastructure Skills

• Resource Usage and Provisioning, Capacity Planning
• Kubernetes Deployment

• Simple Stateful
• Setup
• Helm Operators

• Cluster Management

• Patroni Patroni
• Alternatives
• Load Balancing & Service Discovery
• HAProxy
•  KeepAlived
• Consul
•  Etcd
• Upgrade Procedures

• Using `pg_upgrade`
• Using Logical Replication

• Monitoring

• Prometheus
• Zabbix
• check_pgactivity
• temBoard
• check_pgbackrest

• Backup / Recovery Tools

• 3rd Party

• Barman
• pgbackrest
• pg
• _probackup
• WAL-G

Builtin Tools

• pg_dump
• pg_dumpall
• pg_restore
• pg_basebackup
• Backup Validation Procedures

Learn to Automate Routines

• Shell Scripts
• Any Programming
• Terraform
• Language

Configuration Management

• Ansible
• Salt
• Chef
• Puppet

Application Skills

• Migrations
• Practical Patterns and Antipatterns
• liquibase, sqitch, Bytebase, ora2pg etc
• Migration Tools
• Bulk Loading and Processing Data
• Data Partitioning
• Data Normalization / Normal Forms
• Queues
• Practical Patterns and Antipatterns
• Skytools PGQ

Advanced Topics

• Low Level Internals
• Processes and memory architecture
• Vacuum Processing
• Bu!er Management
• Lock Management
• Physical Storage and File Layout
• System Catalog

Fine Grained Tuning

• Per-user, Per-Database
• Settings Storage Parameters
• Workload-dependant tuning: OLTP, OLAP, HTAP

Advanced SQL

• PL/pgSQL
• Procedures and Functions
• Triggers
• Triggers
• Aggregate and window functions

Troubleshooting Techniques

SQL Optimization Techniques

• Schema Design Patterns / Anti-patterns
• SQL Query Patterns / Anti-patterns

Get Involved in Development

• Mailing Lists
• Reviewing Patches
• Writing Patches

• B-Tree
• Hash
• GiST
• SP-GiST
• GIN
• BRIN

Get Involved in Development

• Mailing Lists
• Reviewing Patches
• Writing Patches

Troubleshooting Techniques

Operating System Tools

• Top
• sysstat
• iotop

Profiling Tools

• Gdb
• Strace
• Ebpf
• perf-tools
• Core Dumps

Log Analysis

• PgBadger
• PgCluu
• awk
• grep
• sed

Postgres System Views

• pg_stat_activity
• pg_stat_statements

Postgres Tools

Pgcenter

Query Analysis

• EXPLAIN
• Depesz
• PEV2
• Tenser
• explain.dalibo.com

Troubleshooting Methods

• USE
• RED
• Golden
• Signals

دانش و مهارت استفاده از کامپیوتر و اینترنت