PostgreSQL – Administration

Stalwart Learning Online training on PostgreSQL Administration training takes participants through the Postgres Architecture, Installation, other administrations tasks, security, migration and best practices to ensure that you have a solid foundation in managing PostgreSQL in your organisation. It is a comprehensive program extremely popular in our corporate clients. 

5 Days

Basic Database knowledge can be an advantage.

PostgreSQL  DBA-Administration

  • PostgreSQL System Architecture (Logical and physical layout)
  • Installation
  • Configuration
  • Creating and Managing Databases
  • Starting, stopping and finding status of postmaster
  • Exploring utilities in Postgresql (process and server / postmaster)

ADMINISTRATION

  • pgAdmin
  • Schemas in Postgresql
  • DML operation effect & working in Postgresql
  • Updates
  • Deletions
  • Transactions and Concurrency
    • Transactions
    • Transaction Isolation
  • Routine Maintenance
  • Managing Free Space
  • Tuning free space map settings, postgresql.conf
  • How to maintain data base object (VACUM, ANALYZE, auto-vacuuming, REINDEX).

SECURITY

  • Security Basics
  • Security Concerns in PostgreSql (Login Roles).
  • Server Start up Variables (Configuration Variables)
  • Configuration files in postgresql
  • Server configuration
  • Connectivity configuration
  • User auth. & privileges
  • Access Control

ADMINISTRATION

  • Administrating Tablespace
  • Creating & Managing Indexes
  • Understanding Indexes
  • Postgres Data Dictionary
  • Query Optimization, plan executions
  • Understanding the EXPLAIN statement
  • Importing data to pgsql tables
  • Exporting from pgsql tables to text files
  • Best Practices
    • Server startup paramaters & options (pg_ctl)
    • Understanding useful Commands like
  • SHOW
  • SET CONSTRAINTS (setting user/role parameters)
  • SET ROLE / ALTER ROLE

MIGRATION

  • Backup and Recovery & Point-in Time Recovery
  • Backup and Disaster Recovery

o   pg_dump

o   pg_dumpall

o   pg_restore

o   pg_ctl,

o   oid2name (understand concept of oids and relevance of it)

o   psql,

o   postgres (postmaster),

o   vacuumdb,

o   reindexdb,

o   createdb,

o   dump,

o   dumpall, etc.

  • Using utilities
  • Monitoring
  • Introduction to PostgrSQL pg_stat views and tables, using psql and pgadmin