PostgreSQL Database Administration (DBA)

Introduction and Architectural Overview

• History of PostgreSQL
• Major Features
• Architectural Overview
• Common Database Object Name

PostgreSQL System Architecture

• Architectural Summary
• Process & Memory Architecture
• Utility Processes
• Connection Request-Response
• Disk Read Buffering Disk
• Write Buffering
• Background Writer Cleaning Scan
• Commit & Checkpoint
• Statement Processing
• Physical Database Architecture
• Data Directory Layout
• Installation Directory Layout
• Page Layout

Installation

• OS User & Permissions
• Installation Options
• Installation of PostgreSQL

Database Clusters

• Database Cluster
• Creating a Database Cluster
• Starting and Stopping the Server (pg_ctl)
• Connect to the Server Using psql

Configuration

• Setting PostgreSQL Parameters
• Access Control
• Connection Settings
• Security and Authentication Settings
• Memory Settings
• Query Planner Settings
• WAL Settings

Creating and Managing Databases

• Object Hierarchy
• Creating Databases
• Users
• Access Control
• Creating Schemas
• Schema Search Path

Security

• Authentication and Authorization
• Levels of Security
• pg_hba.conf File
• Object Ownership
• Application Access Parameters

SQL:

• Data Types
• Structured Query Language (SQL)
• Tables and Constraints
• Manipulating Data using INSERT,UPDATE and DELETE
• Creating Other Database Objects: Sequences, Views and Domains
• Overview of Table Partitioning

Backup and Recovery & Point-in Time Recovery

• Backup Types
• SQL Dump Backups
• SQL Dump Options
• Handling Large Databases
• Restoring SQL Dumps
• Cluster Dump
• Offline Backups
• Recovering a Database using Offline

Routine Maintenance

• Database Maintenance
• Maintenance Tools
• Optimizer Statistics
• Data Fragmentation
• Routine Vacuuming
• Vacuuming Commands

Data Dictionary

• The System Catalog Schema
• System Information Tables
• System Information Functions
• System Administration Functions
• System Information Views

Moving Data

• Loading flat files
• Import and export data using COPY
• Examples of COPY Command

SQL Tuning

• Statement Processing
• Common Query Performance Issues
• SQL Tuning Goals
• SQL Tuning Steps
• Identify slow queries
• Review the query execution plan
• Optimizer statistics and behavior
• Restructure SQL statements
• Indexes
• Review Final Execution Plan

Performance Tuning

• Performance Tuning - Overview
• Performance Monitoring using PEM
• A Tuning Technique
• Operating System Considerations
• Server Parameter Tuning
• Memory Parameters
• Temporary File Parameters
• WAL Parameters

Streaming Replication

• Data Replication
• Data Replication in PostgreSQL
• Sync or Async
• Log-Shipping Standby Servers
• Log-Shipping Architecture
• Streaming Replication
• Hot Streaming Architecture
• Cascading Replication
• Setup Replication Using Archive
• Setup Streaming Replication
• Prepare the Primary Server
• Synchronous Streaming Replication Setup
• Configure Authentication
• Take a Full Backup of Primary Server
• Setting up the Standby Server
• Adding Cascading Replicated Standby Server
• Monitoring Hot Standby

Upgrade

• Version Change and Upgrade
• Need to Upgrade
• Upgrade Plan
• Upgrade Using pg_upgrade

GUI Tools:

• Overview of ORAp2G Tool
• PGADMIN4
• PgBouncer for Connection Pooler
• PgBackRest for Backups