دانلود کتاب 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