SAINORA

HomeBlogsPostgreSQL Database Administration

PostgreSQL Database Administration

Introduction

  • Introduction and Overview of PostgreSQL
  • PostgreSQL System Architecture
  • Architectural Summary
  • Process Architecture
  • Database Clusters
  • PostgreSQL Terminology
  • Physical Database Architecture
  • Data File Architecture
  • Installation
  • Installation using
  • PostgreSQL Binary Windows
  • Installation from Source
  • Creating a database cluster
  • Starting and Stopping the Server (Windows)
  • 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
  • Personality Development and Enhancement Positive Thinking, Effective Communication Presentation Skills
  • Leadership and Team Work
  • Inter-Personal Skills, Resume Building Group Discussions
  • Interview Etiquettes

Configuration

  • CSetting 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
  • Autovacuum Settings

Security

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

Create and Managing PostgreSQL Database

  • USERS
  • GROUPS
  • ROLES
  • Object Ownership
  • Access control
  • Application Access
  • Schemas and Search Paths
  • Lab to create USERS, SCHEMAS, ROLES, Alter SEARCH_PATH
  • GRANT and REVOKE privilege

Moving Data with PostgreSQL

  • Backup & Recovery
  • pg_dump
  • pg_restore
  • pg_dumpall
  • File System Backup - copying DATA folder
  • pg_start_backup and pg_stop_backup
  • PITR - Point in Time Recovery
  • Creating a base backup
  • Archive command
  • 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 Rows

PostgreSQL Partitioning and Table spaces

  • Partition Methods
  • Partition Setup
  • Partition Table Explain Plan
  • Table space Management

High Availability & Replication

  • Warm Standby
  • Why Use Replication
  • Replication
  • Replication Limitations
  • Replication Configuration

Connection Pooling

  • Why Connection Pooling
  • Pgpool-II setup
  • Pgbouncer setup

Advance Monitoring Technique

  • Importance of Monitoring
  • PGFouine - Log Analyzer