PostgreSQL 16 Cookbook, Second Edition (Peter G) (Z-Library)

  PostgreSQL 16 Cookbook   Second Edition   Solve challenges across scalability, performance optimization, essential commands, cloud provisioning, backup, and recovery   Peter G  

Preface   Offering a detailed practical look at PostgreSQL 16's new features, "PostgreSQL 16 Cookbook, Second Edition" equips database administrators and developers to take advantage of the most recent developments. Building on the foundation of version 15, this second edition with version 16 walks you through the enhancements that make PostgreSQL a game changer in the database world.   This edition provides in-depth coverage of enhanced logical replication, which now includes the ability to replicate from standby servers. We provide detailed instructions for setting up these advanced replication configurations, allowing you to better distribute workloads and improve data availability. The optimization of concurrent bulk loading capabilities for faster data ingestion is another noteworthy addition. Another standout feature of PostgreSQL 16 is the expanded SQL/JSON syntax, which gives developers more control over JSON data management. Our book contains practical solutions and examples for using the new JSON functions and operators, which make it easier to store, query, and manipulate JSON data in your applications. We walk you through the process of

configuring refined user roles and permissions, ensuring effective access control in complex environments. Additionally, the book teaches new monitoring capabilities introduced with the pg_stat_io view, which provide insights into I/O operations to help optimize performance.   The book goes on to implement performance enhancements such as SIMD acceleration for processing ASCII and JSON strings, as well as the new load balancing feature, load_balance_hosts, which distributes traffic efficiently among multiple servers. The goal of this book is to provide you with the knowledge you need to successfully manage, optimize, and troubleshoot database environments by providing a deep-dive understanding of how to implement and benefit from PostgreSQL 16's latest features.   In this book you will learn how to:   Boost data availability and workload distribution using advanced logical replication techniques. Apply the SIMD acceleration to expedite the processing of ASCII and JSON strings. Make use of improved SQL/JSON syntax to manage complicated JSON data operations.

Enhance efficiency and decrease query times by optimizing query performance with parallel execution. Utilize pg_stat_io for troubleshooting and monitoring I/O operations. Utilize Rust libraries like pgx and rust-postgres for easy integration with PostgreSQL. Distribute workload among numerous PostgreSQL instances by configuring load_balance_hosts. Simplify user role configurations and security with refined privilege management. Utilize pgBackRest and Barman to implement strong backup strategies. Optimize database performance using concurrent bulk loading.  

Prologue   It is with great pleasure that I, as the author of "PostgreSQL 16 Cookbook, Second Edition," join you on such a journey of addressing PostgreSQL flaws. PostgreSQL has long been regarded as a pillar of the open-source database community, known for its robustness, scalability, and extensibility. My goals in revising this book for a second time are to make it more up-to-date with the features and improvements in PostgreSQL 16 and to fix any mistakes or missing information from the original so that it is useful for database administration novices and experts alike.   With great effort, I have integrated the new features and enhancements brought in by PostgreSQL 16, such as improved logical replication, which permits replication from standby servers. I explore these new possibilities for data availability and workload distribution in detail, giving you examples and solutions to help you implement them in your own environments. One of the most notable features of PostgreSQL 16 is the addition of SIMD acceleration, which significantly improves the performance of string processing tasks. I've dedicated an entire chapter to SIMD acceleration, demonstrating how to use this feature to optimize database operations,

especially when dealing with large amounts of JSON and ASCII data. Through practical examples, I demonstrate the performance gains possible with SIMD, allowing you to maximize the potential of your hardware. I've expanded the security and access control sections to include enhancements, as well as tips for configuring user roles and privileges to effectively protect your data. This edition also covers advanced authentication methods like LDAP and SSL, giving you the knowledge you need to protect your databases from modern threats.   Along with these updates, I've fixed the problems with the previous edition by breaking down tricky concepts and giving more thorough explanations where they were lacking. For example, the backup and recovery sections have been thoroughly revised to include new tools such as pgBackRest and Barman, which provide powerful solutions for point-in-time recovery and continuous archiving. I've included practical examples to help you set up reliable backup strategies, ensuring that you're prepared for any situation. I've also worked on integrating PostgreSQL with modern programming languages, particularly Rust. Rust's growing popularity in the systems programming community makes it an excellent choice for developing high-performance database applications. I've included examples from popular Rust libraries such as pgx and rust-postgres that

show how you can use Rust's safety and concurrency features to create efficient and dependable database applications.   My goal throughout this book is to give you a clear and practical understanding of PostgreSQL 16, allowing you to face the challenges of database management with confidence. Whether you're upgrading from PostgreSQL 15 or starting from scratch with version 16, this cookbook provides a wealth of information and insights to help you succeed. I've worked hard to make this second edition an indispensable resource for anyone working with PostgreSQL by filling in the gaps and inaccuracies of the previous edition and incorporating the most recent developments. Thank you for joining me on this journey, and I hope you find this edition useful and inspiring.   Copyright © 2024 by GitforGits  

All rights reserved. This book is protected under copyright laws and no part of it may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system, without the prior written permission of the publisher. Any unauthorized reproduction, distribution, or transmission of this work may result in civil and criminal penalties and will be dealt with in the respective jurisdiction at anywhere in India, in accordance with the applicable copyright laws.   Published by: GitforGits   Publisher: Sonal Dhandre   www.gitforgits.com   support@gitforgits.com   Printed in India   First Printing: August 2024   Cover Design by: Kitten Publishing  

For permission to use material from this book, please contact GitforGits at support@gitforgits.com.  

Content   Preface   GitforGits   Acknowledgement   Chapter 1: Preparing PostgreSQL 16   Introduction   Recipe #1: Simplify PostgreSQL Architecture Core Components Auxiliary Processes Data Storage Objects Query Execution Process Practical Tips   Recipe #2: Installing PostgreSQL 16.1 from Binaries Step-by-step Installation Post-Installation Configuration Verification and Testing  

Recipe #3: Installing PostgreSQL 16.1 from Source Code Step-by-step Installation Compile PostgreSQL Post-Installation Configuration Verification and Testing   Recipe #4: Parsing Database Startup Logs Parsing PostgreSQL Logs Sample Program: Troubleshooting a Startup Error   Recipe #5: Discovering PostgreSQL Database Structural Objects Key Structural Objects Tables Indexes Sequences Views Stored Procedures Discovering Structural Objects Sample Program: Analyzing Database Structure List All Tables Describe a Specific Table Identify Indexes Analyze Views Explore Stored Procedures  

Recipe #6: Understanding Use of Key Configuration Parameters Key Configuration Parameters shared_buffers work_mem maintenance_work_mem effective_cache_size max_connections checkpoint_timeout autovacuum wal_buffers   Summary   Chapter 2: Performing Basic PostgreSQL Operations   Introduction   Recipe #1: Exploring AdventureWorks Database Production Schema Sales Schema Purchasing Schema HumanResources Schema Person Schema dbo Schema

  Recipe #2: Selecting Right Database Schema Understanding PostgreSQL Schema Types Public Schema Private Schema Creating Schemas Moving Objects Between Schemas Benefits of using Schemas   Recipe #3: Selecting Indexing Techniques Indexing Techniques in PostgreSQL B-tree Indexing Hash Indexing GiST Indexing (Generalized Search Tree) SP-GiST Indexing (Space-Partitioned Generalized Search Tree) GIN Indexing (Generalized Inverted Index) BRIN Indexing (Block Range INdex) Optimizing Database Optimize Sales Orders by Date Improve Full-Text Search on Product Descriptions Enhance Query Performance for Customer Lookups   Recipe #4: Preparing Database Log Directory Setting up the Log Directory Using ‘logging_collector’  

Recipe #5: Using PostgreSQL TOAST Using TOAST Viewing TOASTed Data   Recipe #6: Creating and Administering PostgreSQL Temporary Tables Creating Temporary Tables Sample Program: Performing Data Analysis   Recipe #7: Using SELECT in WITH Queries Understanding CTEs Using Multiple CTEs Sample Program: Analyzing Sales Performance   Recipe #8: Running Recursive Queries Understanding Recursive Queries Recursive Queries for Organizational Charts Sample Program: Exploring Product Categories   Summary   Chapter 3: PostgreSQL Cloud Provisioning   Introduction  

Recipe #1: Create PostgreSQL AWS Instance and Manage Database Connection Configure RDS Instance Connect to RDS Instance using pgAdmin Launch and Connect EC2 Instance   Recipe #2: Native Backup/Restore with AWS EC2 Instance Backup the Database Restore the Database from Backup   Recipe #3: Natively Backup/Restore with AWS RDS Instance Create Manual Snapshot Restore from Snapshot and Verify Resoration   Recipe #4: Manage Connection to Database on AWS Modify Security Group Rules Use PgBouncer   Recipe #5: Perform Replication of Database on AWS Create a Read Replica Monitor Replication Performance  

Recipe #6: Run PostgreSQL Bi-directional Replication using pglogical Install pglogical Extension Configure Replication Nodes Create Replication Subscriptions   Summary   Chapter 4: Database Migration to Cloud and PostgreSQL   Introduction   Recipe #1: Migrating from On-Premise to AWS EC2/RDS Instance Create Database Backup Transfer Backup to AWS Environment Verify Migration   Recipe #2: Utilizing AWS Data Migration Service (DMS) Setup AWS Environment Create Endpoints in AWS DMS Replication Instance Create Database Migration Task   Recipe #3: Migrating Database from EC2 to RDS Instance Create Backup of Database

Restore Database Monitor and Optimize   Recipe #4: Preparing Pgloader to Use with Database Install Pgloader Create Pgloader Command File   Recipe #5: Migrating from MySQL to PostgreSQL Create Pgloader Command File Run the Migration   Recipe #6: Setting up Foreign Data Wrapper (FDW) Install MySQL FDW Create FDW Table   Summary   Chapter 5: WAL, AutoVacuum & ArchiveLog   Introduction   Recipe #1: WAL Compression Option for Space Management Enable WALCompression Tune WAL Compression

  Recipe #2: Configure WAL Performance Parameters Adjust WAL Performance Parameters Monitor WAL Performance Evaluate Impact and Fine-Tune Settings   Recipe #3: Administer Continuous Archiving Test Archiving Setup Manage Archive Retention and Cleanup Prepare Recovery Environment   Recipe #4: Using Remote WAL Archive Options Setup SSH Keys for Secure Transfer: Enable Archive Mode: Archived WAL Files on Remote Server   Recipe #5: Exploring Vacuum Process Perform a Basic VACUUM Execute VACUUM FULL Automate VACUUM Handle Large Tables Optimize and Run VACUUM in Parallel   Recipe #6: Debug PostgreSQL Autovacuum Check Autovacuum Settings

Review Autovacuum Logs and Manually Trigger Adjust Autovacuum Settings Adjust Cost-Based Vacuum Parameters   Summary   Chapter 6: Partitioning and Sharding Strategies   Introduction   Recipe #1: Setup Partitioning Define Partitions Monitor Partition Usage and Performance   Recipe #2: Vertical & Horizontal Partitioning Implement Vertical Partitioning Implement Horizontal Partitioning   Recipe #3: Perform Attaching, Detaching, and Dropping Partitions Attach a New Partition Detach an Existing Partition Drop a Detached Partition   Recipe #4: Tables Partitioning using Table Inheritance