PostgreSQL Database Administration

What is Database, DB types & history of databases

Relational and Non-Relational Databases

PostgreSQL history, Community, Features matrix etc.

PostgreSQL installation and basic configuration.

Pagila sample database

GUI Tools(Pgadmin, Dbeaver, DbGrid etc.)

PSQL CLI and commands

PostgreSQL installation types (from repo and source code)

Recommended Linux package and configurations

Installation and configuration on RedHat

Installation and configuration on Ubuntu

Installation and configuration on Windows

Normal and immediate start stop methods of PostgreSQL

How to properly Uninstall PostgreSQL

Enabling Data checksum before and after installation

Changing DB Collate & Ctype before and after installation

Installation PostgreSQL from source code

Cloning database

Extensions that must be on every PostgreSQl DB

PostgreSQL Template DB yaradılması vəistifadəsi

Useful PostgreSQL functions for DB management

Creating DB objects

Tables, Constraints, Views, Sequences etc.

Materialized view and additional refresh methods

Client-server protocol

Transactionality and its implementation

Query processing and execution

Processes and memory structures

Background Processes

PostgreSQL memory structures

Storing data on disk and disk operations

System extensibility

Multiversion concurrency control (MVCC)

ACID (Atomicity, Concurrency, Isolation, Durability)

Isolation Levels (Read UnCommitted, Read Committed, Repeatable Read, Serializable)

Phenomena (Dirty Read, Non-repeatable Read, Phantom Read, Serialization anomaly, Lost Updates, Read Skew, Write Skew)

Locking

Routine tasks that require periodic execution

Vacuum and analysis

Table and index bloating

Full vacuum and rebuilding of indexes

Buffer cache overview

Replacement algorithm

WAL-Write-ahead log

Checkpoint

Processes related to the buffer cache and WAL

Databases and templates

Schemas and search path

Special schemas

System catalog

Tablespaces and directories

Heap Table internal structure

Data pages

Tuple structure

FILLFACTOR

Forks: data, visibility map, free space map

TOAST - The Oversized-Attribute Storage TechniqueAST

Numeric Data Types

Character/Binary Data Types

Date/Time Data Types

Enumerated Data Types

Range Data Types

Geometric Data Types

JSON Types (JSON & JSONB)

How NULL values are stored in PostgreSQL

OS tools

Database statistics

Server message log

Shell scripting and sending notifications with Telegram Bot

External monitoring systems (PerconaPMM, Zabbix, PgHero)

Roles and attributes, Roles Inheritance

Privileges

Grant and Revoke privileges

Object Ownership

Policies, Policy vs. Grant

Row-level security policies

Understanding ACLs

Configuring pg_hba.conf

Authentication Methods

pg ident Configuration

Authentication with Certificate

Security improvements in different PostgreSQL versions

Audit database with PG_Audit

Security Best practices

Logical backup (PG_DUMP, PG_RESTORE, PG_DUMPALL )

Plain format dump

Custom format dump

Directory format dump

Bulk data loading

Loading CSV data with COPY

Loading CSV data with FILE_FDW

Loading CSV data with PG_BULKLOAD

Loading CSV data with external GUI tools

Physical backup

Standalone Backup

Standalone HOT physical backup

PgBaseBackup

Creating Full and Incremental Backups

Recovery database

Point in Time Recovery(PITR)

Installation and configuration PgBackRest

Configuring Pgbackrest with Certificate

Barman(Full and Incremental Backup and Recovery)

Configuring Logging in PostgreSQL

Analyzing postgreSQL log with PgBadger

Installing and configuring centralized logging to SYSLOG və RSYSLOG

Replication purposes and types

Physical replication

Configuring replication with Streaming Replication method

Configuring replication with Replication Slots method

Configuring Delay Standby

Failover, monitoring , recreateding, dropping, checking replication

Configuring Logical replication

Monitoring COPY in logical replication

Row filtering in Logical replication tables

What is Connection Pool ?

Installation and configuration PgBouncer

Load balancing between primary and standby servers using HaProxy

Configuring healthcheck using KeepAlived

PostgreSQL versioning & Roadmap

Updating PostgreSQL to new minor version

Upgrading to major version using PG_UPGRADE

Upgrading to major version with zero downtime using PG_UPGRADE –LINK

B-TREE INDEX

HASH INDEX

GIN INDEX

RUM INDEX

BRIN INDEX

Unique Index

Partial Index

EXPLAIN

Index Scan

Bitmap Index Scan

Bitmap Heap Scan

Identifying table columns which need INDEX

Rebuild and Vacuum Indexes

Identifying unused indexes

Properly removing unused indexes

Checking indexes using AMCHECK Extension

Best practices to configure postgresql.conf for optimal performance

Identifying Slow Running Queries

Identifying Performance Issues reason with Slow Queries

Indexing recommendations

Index usage

Index Cache Hit Ratio

Parallel queries

Cache Hit Ratio

Importance of VACUUM for performance

Identifying BLOAT & solving bloat problem

Using different methods and extensions to reduce Bloat

PostgreSQL statistics

Migration from Oracle to PostgreSQL

OracleFDW

Ora2PG

Migration from Ms SQLServer to PostgreSQL

Migration from MySQL to PostgreSQL

NATIVE PARTITIONING

LIST PARTITION

RANGE PARTITION

HASH PARTITION

Multilevel mixed partitioning

Automated partitioning with PG_PARTMAN

Partition maintenance and useful scripts

Patroni installation and configuration

Adding new node to Patroni cluster

Removing node from Patroni cluster

Switchover on Patroni cluster

Change PostgreSQL configurations on Patroni

PG_AGENT

PG_CRON

PGAUDIT

PG_BUFFERCACHE

POSTGRES_DBA

PG_BACKGROUND

PGBADGER

PG_STAT_STATEMENTS

PG_QUALSTATS

PG_STAT_CACHE

pg_track_settings

POSTGIST

PG_PREWARM

PG_WAIT_SAMPLING

HYPOPG

PG PARTMAN

PG FREESPACEMAP

TABLE VERSION

POSTGRES_FDW

FILE_FDW

ORACLE_FDW

PGSTATTUPLE

PAGILA

PG_TRACK_SETTINGS

TABLEFUNC

PG_TRGM

INTARRAY

PG_NOTIFY

PG_TOP

EXTRA_WINDOW_FUNCTIONS

PG_HEXEDIT

PGMETRICS

SQLBENCH

PG_DUMPBINARY

PGroonga

PGTT

Pg_flame

PgHero

Pgtools

PostgreSQL Data Dictionary (pgdd)

Hardware BestPractices

OS Configuration BestPractices