POSTGRESQL DBA

PostgreSQL System Architecture
  •    System Architecture

  •    Architectural Summary

  •    Process Architecture

  •    Database Clusters

  •    PostgreSQL Terminology

  •    Physical Database Architecture

  •    Data File Architecture

  •    Database Server

  •    Installation Using

  •    PostgreSQL Binary Windows

  •    Installation from Source

  •    Creating a database cluster

  •    Starting and Stopping the Server (Other)

  •    Lab Exercise – Installation

  •    Install PostgreSQL from source

  •    Create a database cluster

  •    Start the database server

  •    Connect to the server using psql

  •    Stop the database server

Configuration
  •    Setting PostgreSQL Parameters

  •    Access Control

  •    Connection Settings

  •    Security and Authentication Settings

  •    Configure client/server connectivity

  •    Memory Settings

  •    Free Space Settings

  •    Kernel Resource Settings

  •    Log Management

  •    Background Writer Settings

  •    Vacuum Cost Settings

Create and Managing PostgreSQL Database and Security
  •    USERS

  •    GROUPS

  •    ROLES

  •    Object Ownership

  •    Schemas and Search Paths

  •    Lab to create USERS, SCHEMAS, ROLES, Alter SEARCH_PATH

  •    GRANT and REVOKE privileges

  •    Levels of security: pg_hba.conf, schemas and users and table level

  •    pg_hba.conf

Moving Data with PostgreSQL and Backup and Recovery
  •    COPY

  •    Perform a Logical Backup Using PG_DUMP

  •    Perfomr Entire Cluster Backup using PG_DUMPALL

  •    Filesystem Level Backup (Hot and Cold)

  •    Server To Server Backup Using BART and PG_ADMIN Tools

  •    PITR – Point in Time Recovery

  •    pg_start_backup and pg_stop_backup

  •    Creating a base backup

  •    Archive Backup Local and Standby

  •    Recovery file parameters

  •    Lab to test PITR

Routine Database Maintenance Tasks
  •    Routine Vacuuming

  •    Recovering Disk Space

  •    Vacuum Full

  •    Updating Planner Statistics

  •    Preventing Transaction ID Wraparound Failures

  •    Routine Reindexing

Performance Tuning
  •    Explain and Explain Analyze

  •    Forcing Query Plan Selection

  •    Indexing

  •    Log Management

  •    Clustering

PostgreSQL Partitioning and Table spaces
  •    Partition Methods

  •    Partition Setup

  •    Partition Table Explain Plan

  •    Table space Management

High Availability and Replication
  •    Hot Standby / Warm Standby

  •    Why Use Replication?

  •    Hot Streaming Replication

  •    Replication Limitations and Monitoring log shipping

  •    Replication Configuration and check Logs

Connection Pooling
  •    Why Connection Pooling?

  •    Pgpool-II setup

  •    Pgbouncer setup

Advance Monitoring Techniques
  •    Importance of Monitoring

  •    PGFouine/pg_badger – Log Analyzer

Advance Monitering Tools(community and EDB)
  •    Monitering the Database using Pg_admin and pgbadger

  •    Pem server(Enterprise Manager). Handle more tha 10 server one window

  •    Pg_client, pg_agent

  •    Ora2pg(migration)-oracle to postgres Migration

  •    Edb Migration Tool Kit