If you're a PostgreSQL database administrator looking for a comprehensive guide to managing your databases, look no further than the PostgreSQL 15 Cookbook. With 100 ready solutions to common database management challenges, this book provides a complete guide to administering and troubleshooting your databases using latest PostgreSQL 15.Starting with cloud provisioning and migration, the book covers all aspects of database administration, including replication, transaction logs, partitioning, sharding, auditing, realtime monitoring, backup, recovery, and error debugging. Each solution is presented in a clear, easy-to-follow format, using a real database called 'adventureworks' to provide an on-job practicing experience.Throughout the book, you'll learn how to use tools like pglogical, pgloader, WAL, repmgr, Patroni, HAProxy, PgBouncer, pgBackRest, pgAudit and Prometheus, gaining valuable experience and expertise in managing your databases. With its focus on practical solutions and real-world scenarios, the PostgreSQL 15 Cookbook is an essential resource for any PostgreSQL database administrator. Whether you're just starting out or you're a seasoned pro, this book has everything you need to keep your databases running smoothly and efficiently.
Key Learnings:
Streamline your PostgreSQL databases with cloud provisioning and migration techniques
Optimize performance and scalability through effective replication, partitioning, and sharding
Safeguard your databases with robust auditing, backup, and recovery strategies
Monitor your databases in real-time with powerful tools like pgAudit, Prometheus, and Patroni
Troubleshoot errors and debug your databases with expert techniques and best practices
Boost your productivity and efficiency with advanced tools like pglogical, pgloader, and HAProxy.
With the PostgreSQL 15 Cookbook as your guide, you'll gain valuable insights and expertise in managing your PostgreSQL databases, while learning practical solutions to common database management challenges. Whether you're a novice or an experienced PostgreSQL administrator, this book has everything you need to take your database management skills to the next level and become a PostgreSQL expert.
Author(s): Peter G.
Publisher: GitforGits
Year: 2023
Language: English
Pages: 364
Preface
Chapter 1: GETTING POSTGRESQL 15 READY
Recipe#1: Simplifying Understanding of PostgreSQL Architecture
Recipe#2: Installing PostgreSQL 15 from Binaries
Recipe#3: Installing PostgreSQL 15 from Source Code
Recipe#4: Upgrade to Minor & Major Releases
Recipe#5: Parsing Database Start-up Logs
Recipe#6: Using PostgreSQL Server Access Solutions
Recipe#7: Discovering PostgreSQL Database Structural Objects
Recipe#8: Understanding PostgreSQL Memory Configuration
Recipe#9: Understanding Use of Key Configuration Parameters
Chapter 2: Performing Basic PostgreSQL Operations
Recipe#1: Understanding AdventureWorks Database in this Book
Recipe#2: Selecting Right Database Schema
Recipe#3: Selecting Indexing Techniques
Recipe#4: Building Concurrent Index
Recipe#5: Prepare Database Log Directory
Recipe#6: Using PostgreSQL TOAST
Recipe#7: Create and Administer PostgreSQL Temporary Table
Recipe#8: Using SELECT in WITH Queries
Recipe#9: Running Recursive Query
Recipe#10: Writing Common Table Expression (CTE)
Chapter 3: PostgreSQL Cloud Provisioning
Recipe#1: Create PostgreSQL Cloud Instance and Manage Database Connection with AWS EC2 and RDS
Recipe#2: Native Backup/Restore with AWS EC2 Instance
Recipe#3: Natively Backup/Restore with AWS RDS Instance
Recipe#4: Manage Connection to Database on AWS
Recipe#5: Perform Replication of Database on AWS
Recipe#6: Run PostgreSQL Bi-directional Replication using pglogical
Chapter 4: Database Migration to Cloud and PostgreSQL
Recipe#1: Migrating from On-premise to AWS EC2/RDS Instance
Recipe#2: Utilizing AWS Data Migration Service (DMS)
Recipe#3: Migrating Database from EC2 to RDS Instance
Recipe#4: Preparing Pgloader to Use with Database
Recipe#5: Migrating from MySQL to PostgreSQL
Recipe#6: Setting Up Foreign Data Wrapper (FDW)
Chapter 5: WAL, AutoVacuum & ArchiveLog
Recipe#1: Enable and Disable Archive Mode
Recipe#2: WAL Compression Option for Space Management
Recipe#3: Configure WAL Performance Parameter
Recipe#4: Administer Continuous Archiving
Recipe#5: Using Remote WAL Archive Options
Recipe#6: Exploring Vacuum Process
Recipe#7: Estimate Transaction Log Size
Recipe#8: Debug PostgreSQL Autovaccum
Recipe#9: Delete PostgreSQL Archive Logs
Chapter 6: Partitioning and Sharding Strategies
Recipe#1: Setup Partitioning in PostgreSQL 15
Recipe#2: Vertical & Horizontal Partitioning
Recipe#3: Perform Attaching, Detaching and Drop Partition
Recipe#4: Tables Partitioning using Table Inheritance
Recipe#5: Implement Automatic Partition
Recipe#6: Partition Pruning
Recipe#7: Run Declarative Partition
Recipe#8: Administer Performance with Table Partitioning
Recipe#9: Setting up Shard with FWD
Recipe#10: Configure Sharding with Citusdata
Recipe#11: Repair Shards
Chapter 7: Solving Replication, Scalability & High Availability
Recipe#1: Using Master-Slave Replication
Recipe#2: Setup Delay Standby
Recipe#3: Install and Configure ‘repmgr’
Recipe#4: Cloning Database with ‘repmgr’
Recipe#5: Perform PITR Recovery using Delay Standby
Recipe#6: Deploy High Availability Cluster with Patroni
Recipe#7: Using HAProxy and PgBouncer for High Availability
Recipe#8: Perform Database Upgrade on Replication Cluster
Chapter 8: Blob, JSON Query, CAST Operator & Connections
Recipe#1: Perform Querying
Recipe#2: Import BLOB Data Types
Recipe#3: Running Queries using Shell Script
Recipe#4: Working with Postgres JSON Query
Recipe#5: Working with Postgres CAST Operator
Recipe#6: Assuring Database Consistency and Integrity
Chapter 9: Authentication, Audit & Encryption
Recipe#1: Manage Roles, Membership, Attributes, Authentication and Authorizations
Recipe#2: Setting Up SSL Authentication
Recipe#3: Configure Encryption
Recipe#4: Install and Configure pgAudit
Recipe#5: Using Audit Log with PostgreSQL Trigger
Recipe#6: Using log_statement/Audit Trail
Recipe#7: Install and Configure LDAP Authentication
Chapter 10: Implementing Database Backup Strategies
Recipe#1: Automate Database Backup
Recipe#2: Execute Continuous Archiving PostgreSQL Backup
Recipe#3: Working with ‘pg_probackup’ and ‘pgBackRest’
Recipe#4: Install and Configure Barman
Recipe#5: Perform Incremental/Differential Backup
Recipe#6: Execute Schema Level Backup
Recipe#7: Perform Backup Monitoring using pg_stat_activity
Chapter 11: Exploring database Recovery & Restoration Methods
Recipe#1: Perform Full and PITR Recovery
Recipe#2: Restore Database using Barman
Recipe#3: Perform Incremental/Differential Restore
Recipe#4: Working with Tablespace Recovery
Recipe#5: Working with Tables Recovery
Recipe#6: Working with Schema Level Restore
Recipe#7: Monitor Restore Operations
Recipe#8: Working with Recovery Mode
Recipe#9: Working with Recovery Target Options
Chapter 12: Prometheus & Continuous Monitoring
Recipe#1: Installing and Configuring Prometheus
Recipe#2: Real-time Monitoring using Prometheus
Recipe#3: Using Statistic Collector and Analyzer
Recipe#4: Using Prometheus to Monitor Active Session
Recipe#5: Parse Query String
Recipe#6: Use Database Connection Pooling
Recipe#7: Vacuum amd Bloat
Recipe#8: Using ‘fsync’
Chapter 13: Debugging PostgreSQL
Recipe#1: Benchmarking and its Importance
Recipe#2: Benchmark Performance using PGBench
Recipe#3: Responding Error Messages
