PostgreSQL for Jobseekers: Introduction to PostgreSQL administration for modern DBAs

Cover
Title Page
Copyright Page
Dedication
About the Author
About the Reviewer
Acknowledgements
Preface
Contents
1. Introduction to Opensource Database - PostgreSQL
   Introduction
   Structure
   Objectives
   Open-source introduction
      The origin of free software
      The open source concept
      The open source nowadays
   A brief history of PostgreSQL
      The POSTGRES project
      Postgres95
      PostgreSQL
         PostgreSQL versions key features
         PostgreSQL in stats on a single image
   PostgreSQL release cycle
      The current impact of PostgreSQL on the market
   Companies that use PostgreSQL
      Companies that help in enhancing PostgreSQL
         Advantages of PostgreSQL
      Distributed architecture with PostgreSQL
      StatefulSet with PostgreSQL
   Conclusion
   Bibliography
2. Getting PostgreSQL to work
   Introduction
   Structure
   Objectives
   Source code installation
      Short version of source code installation
      Pre-requisites
      Downloading the source
   Installation procedure
      Verifying directory structure
      Adding postgres user
      Creating data directory
   Initializing PostgreSQL
      Validating the data directory
      Start PostgreSQL database
      Verify postgres process is running
   Binary installation
      Create repository configuration
      Import the repository signing key
      Update the package list
   Installing PostgreSQL
   Conclusion
   Bibliography
3. Modern Options to get PostgreSQL
   Introduction
   Structure
   Objectives
   Other ways to get PostgreSQL
      On-premise, virtualization, containers, and cloud
         On-premise
         Virtualization
         Containers
         The Cloud
   Getting PostgreSQL on modern systems
      PostgreSQL on Docker
      PostgreSQL on Kubernetes
      PostgreSQL on The Cloud
   Conclusion
   Bibliography
4. Global Objects in PostgreSQL
   Introduction
   Structure
   Objectives
   Users/Groups/Roles
   Tablespaces
   Databases
      CREATE DATABASE command
      createdb program
      Using pgAdmin Wizard
   Conclusion
   Bibliography
5. Architecture of PostgreSQL
   Introduction
   Structure
   Objectives
   Memory architecture
      Shared memory
      Shared buffers
      WAL buffers
      CLOG buffers
      Process memory
         Temporary buffers
      Work memory
      Maintenance work memory
   Background processes
      Postmaster
      Checkpointer
      Writer or background writer
      Autovacuum
      Stats collector
      Logger
      Archiver
      WAL writer
      WAL sender
      WAL receiver
   Physical files
      Data files
      WAL files
      Temporary files
      CLOG files
      Stat files
      Log files
      WAL archive files
      Conclusion
   Bibliography
6. PostgreSQL Internals
   Introduction
   Structure
   Objectives
   ACID
      Atomicity
      Consistency
      Isolation
      Durability
   MVCC
   Vacuum
      Autovacuum
      VACUUM FULL
      Manual VACUUM
         pg_repack
      Preventing transaction ID wraparound failures
   Transaction isolation levels
      Phenomena
      Dirty read
      Non-repetable read
      Phantom read
      Serialization anomaly
      Isolation levels
      Read uncommitted/committed
         Repeatable read
         Serializable
   Query processing
      Parser
      Rewriter
      Planner
      Executor
   Conclusion
   Bibliography
7. Backup and Restore in PostgreSQL
   Introduction
   Structure
   Objectives
   Backup
      Physical backup
         pg_basebackup
      Point in time Recovery/Archival
      Pros and cons of physical backup
      Logical backup
         pg_dump
         pg_dumpall
      Pros and cons of logical backup
   Restore
      psql
      pg_restore
   Useful backup and restore tools
      pgBackRest
      Barman
      pg_probackup
   Conclusion
   Bibliography
8. Replicating Data
   Introduction
   Structure
   Objectives
   Physical replication
      Hot standby
      Archive recovery
      Streaming replication
      Cascading
      Delayed replica
      Configuration
   Logical replication
      Architecture
      Publication
      Subscription
      Publisher node as well as subscription node
   Conclusion
9. Security and Access Control
   Introduction
   Structure
   Objectives
   Authentication
      The pg_hba.conf
         local
         host
         hostssl
         hostnossl
         hostgssenc
         hostnogssenc
      Database
      User
      Address
      Method
      [Options]
   Authentication methods
      The pg_ident.conf
         Examples
   Authorization
      Role attributes
      Object ownership
      Objects privileges
   Conclusion
   Bibliography
10. Most used Extensions/Tools
   Introduction
   Structure
   Objectives
   Extensions
      pg_cron
      pg_stat_statements
      pg_repack
      Tools
         pgbadger
         pgbench
         pgbouncer
   Conclusion
   Bibliography
11. Basic Database Objects
   Introduction
   Structure
   Objectives
   Managing schemas
      DB cluster
      Users/roles
      Databases
      Tablespaces
      Schemas
      Default - Public Schema
         SEARCH_PATH in Schema
   Managing DB Objects using DDL commands
      Data types
      Table
         Create table
         Alter table
         Drop table
         Truncate
         View
         Sequences
   Enforcing data integrity using constraints
   Manipulating data using DML Queries
      Inserting data
      Updating data
      Deleting data
         Select (Retrieve) data
      Joins used in data retrieval
      Inner join
         Left outer join
         Right outer join
      Full outer join
         Aggregate functions
   Conclusion
   Bibliography
12. Advance Database Objects
   Introduction
   Structure
   Objectives
   Managing procedures/functions
      Function
         Function execution syntax
      Function execution example
      Procedure
         Procedure execution syntax
      Procedure execution
   Managing triggers
      Trigger function
      Event trigger
   Managing rules
      Trigger versus rules
   Custom data type
   Conclusion
   Bibliography
13. Performance Tuning
   Introduction
   Structure
   Objectives
   Indexes
      Reindex
      Index types
         Btree index
         Hash index
         GiST and SP-GiST index
         Gin index
         Brin index
      Indexes and expressions
   Statistics
      Statistics in pg_statistics
      Statistics in pg_statistics_ext_data
      Functional dependencies
      Number of distinct values counts
      Most common values list
   Explain plan
   Best practices for the postgresql.conf parameters
      shared_buffers
      work_mem
      autovacuum
      effective_cache_size
      maintenance_work_mem
      max_connections
   Summary
   Conclusion
   Bibliography
14. Troubleshooting
   Introduction
   Structure
   Objectives
   Debugging using log files
      Where to log
      logging_collector
      log_destination
      log_directory
      log_filename
      log_rotation_age
      log_rotation_size
      log_truncate_on_rotation
         What to log
      log_line_prefix
      log_connections/log_disconnections
      log_min_duration_statement
      log_lock_waits
      log_autovacuum_min_duration
      Parameters summary
   Debugging using PostgreSQL tools and commands
      Gather information
      Check the PostgreSQL version
      Check database and objects size
      Check database connections
      Check slow queries
      Instruct PostgreSQL
      Vacuuming and analyzing
         Terminate queries or user sessions
      Manage replication
   Debugging using Operating System tools and commands
      Service and system-wide tools
         systemctl
         free
         df
      Processes-oriented tools
      ps/pgrep
      Log and events
         journalctl
   Conclusion
15. Contributing to PostgreSQL Community
   Introduction
   Structure
   Objectives
   PostgreSQL community and its members
      Core members of the PostgreSQL community
      Working pattern of PostgreSQL community
      Earning of PostgreSQL community
   Different ways to contribute
      Code contributor
      Bug reporter
      Participate in the mailing lists
      Improving or creating new documentation
      Participating in events
      Supporting the community
   Conclusion
Index