PostgreSQL for Jobseekers: Introduction to PostgreSQL Administration for Modern DBAs

Book title
Inner title
Copyright
Dedicated
About the Author
About the Reviewer
Acknowledgements
Preface
Coloured Images
Piracy
Table of Contents
Chapter 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 release cycle
		The current impact of PostgreSQL on the market
	Companies that use PostgreSQL
		Companies that help in enhancing PostgreSQL
		Distributed architecture with PostgreSQL
		StatefulSet with PostgreSQL
	Conclusion
	Bibliography
Chapter 2: Getting PostgreSQL to work
	Introduction
	Structure
	Objectives
	Source code installation
		Short version of source code installation
		Pre-requisites
	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
Chapter 3: Modern Options to get PostgreSQL
	Introduction
	Structure
	Objectives
	Other ways to get PostgreSQL
		On-premise, virtualization, containers, and cloud
	Getting PostgreSQL on modern systems
		PostgreSQL on Docker
		PostgreSQL on Kubernetes
		PostgreSQL on The Cloud
	Conclusion
	Bibliography
Chapter 4: Global Objects in PostgreSQL
	Introduction
	Structure
	Objectives
	Users/Groups/Roles
	Tablespaces
	Databases
		CREATE DATABASE command
		createdb program
		Using pgAdmin Wizard
	Conclusion
	Bibliography
Chapter 5: Architecture of PostgreSQL
	Introduction
	Structure
	Objectives
	Memory architecture
		Shared memory
		Shared buffers
		WAL buffers
		CLOG buffers
		Process memory
		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
Chapter 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
	Query processing
		Parser
		Rewriter
		Planner
		Executor
	Conclusion
	Bibliography
Chapter 7: Backup and Restore in PostgreSQL
	Introduction
	Structure
	Objectives
	Backup
		Physical backup
		Point in time Recovery/Archival
		Pros and cons of physical backup
		Logical backup
		Pros and cons of logical backup
	Restore
	Useful backup and restore tools
		pgBackRest
		Barman
		pg_probackup
	Conclusion
	Bibliography
Chapter 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
Chapter 9: Security and Access Control
	Introduction
	Structure
	Objectives
	Authentication
		The pg_hba.conf
		Database
		User
		Address
		Method
		[Options]
	Authentication methods
		The pg_ident.conf
	Authorization
		Role attributes
		Object ownership
		Objects privileges
	Conclusion
	Bibliography
Chapter 10: Most used Extensions/Tools
	Introduction
	Structure
	Objectives
	Extensions
		pg_cron
		pg_stat_statements
		pg_repack
		Tools
	Conclusion
	Bibliography
Chapter 11: Basic Database Objects
	Introduction
	Structure
	Objectives
	Managing schemas
		DB cluster
		Users/roles
		Databases
		Tablespaces
		Schemas
		Default - Public Schema
	Managing DB Objects using DDL commands
		Data types
		Table
	Enforcing data integrity using constraints
	Manipulating data using DML Queries
		Inserting data
		Updating data
		Deleting data
		Joins used in data retrieval
		Inner join
		Full outer join
	Conclusion
	Bibliography
Chapter 12: Advance Database Objects
	Introduction
	Structure
	Objectives
	Managing procedures/functions
		Function
		Function execution example
		Procedure
		Procedure execution
	Managing triggers
		Trigger function
		Event trigger
	Managing rules
		Trigger versus rules
	Custom data type
	Conclusion
	Bibliography
Chapter 13: Performance Tuning
	Introduction
	Structure
	Objectives
		Reindex
		Index types
		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
Chapter 14: Troubleshooting
	Introduction
	Structure
	Objectives
	Debugging using log files
		Where to log
		log_destination
		log_directory
		log_filename
		log_rotation_age
		log_rotation_size
		log_truncate_on_rotation
		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
		Check statistics
		Instruct PostgreSQL
		Vacuuming and analyzing
		Manage replication
	Debugging using Operating System tools and commands
		Service and system-wide tools
		Processes-oriented tools
		top/htop
		ps/pgrep
		Log and events
	Conclusion
	Bibliography
Chapter 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
	Bibliography
Index
back title