PostgreSQL 17 QuickStart Pro (Tessa Vorin) (Z-Library)

PostgreSQL 17 QuickStart Pro Add expertise around WAL processing, JSON table, IO performance, logical replication and index vacuuming Tessa Vorin

Preface PostgreSQL 17 QuickStart Pro is the definitive hands-on, practical book for professionals at every level, from entry-level administrators to seasoned experts. It provides rapid learning and practical implementation of PostgreSQL 17, focusing on the latest features and best practices to effectively manage, configure, and optimize PostgreSQL databases—and it does so effectively. The book begins by using the Titanic dataset to illustrate practical examples of upgrade strategies, post-upgrade validation, and database configuration. Next, it covers cluster administration, configuration settings, and performance tracking. You will master the management of permissions and roles through intricate role hierarchies, authentication methods, and security settings. Next, we'll optimize server performance, plan queries, and manage resources based on real performance data. The next section dives deep into complicated data types, bulk data operations, advanced indexing methods, and the creation of triggers and functions, all with an emphasis on effective data management. Next, you will learn about table partitioning strategies, performing physical and logical backups, database restoration, and process automation using BART. We then move on to streaming replication, where we will configure, administer, and monitor replication to ensure optimal uptime. Finally, we will explore point-in-time recovery, which allows us to restore databases to specific points in time by replaying WAL logs. In short, this book will equip database administrators with the knowledge and skills to confidently handle PostgreSQL 17 databases.

In this book you will learn how to: Upgrade and configure PostgreSQL 17, including post-upgrade validation and configuration. Learn PostgreSQL architecture, memory models, and cluster management. Use hierarchical permissions, authentication, and security for advanced role management. Tune server performance with query planning, resource management, and configuration tuning. Effectively use PostgreSQL extensions, JSONB, and arrays. Optimize queries with GIN, GiST, and BRIN indexing. Master table partitioning for large dataset performance and scalability. Automate physical and logical backups and confidently restore databases. Manage PostgreSQL streaming replication for high availability and automatic failover. Restore data using WAL logs and Point-in-Time Recovery.

Prologue Hi, I'm Tessa Vorin, and I'm thrilled to present my new book, "PostgreSQL 17 QuickStart Pro"! It's packed with all the tips and tricks I've picked up along the way to help you get the most out of PostgreSQL 17 since its beta version became available. This book offers a fantastic, hands-on approach to learning all about PostgreSQL's newest features, optimization techniques, and high-availability solutions. It's perfect for database administrators of all experience levels! I want you to feel like you're creating something truly special from the very beginning! I've used the incredible Titanic dataset as a consistent example throughout the chapters, and it's going to be a great way to illustrate the concepts we're covering! I'm so excited to show you how we'll use this real-world dataset to perform tasks you're likely to encounter in your daily work! We're going to dive right in and start by focusing on upgrading to PostgreSQL 17! I'll show you exactly how to upgrade your system in the best way possible, with absolutely no stone left unturned! If you're starting with version 15, you'll see exactly how to upgrade, validate the setup, and reconfigure everything so it's production-ready—and it's going to be amazing! And now for something really exciting! We're going to go deep into cluster administration. I guarantee that you will understand the inner workings of the process and memory models that power PostgreSQL 17— and it's going to be a wild ride! I've also included some really practical examples for managing clusters, setting up multiple databases, and fine- tuning your configurations for peak performance. Role management and

security have become absolutely vital components of database management, and so here, I demonstrate how to design permissions and manage users efficiently. We will also discuss row-level security, authentication mechanisms such as LDAP and SSL/TLS integration, and data compliance. Another topic that I believe you should master is performance tuning. I'll walk you through analyzing query execution plans with EXPLAIN and EXPLAIN ANALYZE, allowing you to optimize queries based on the performance insights they provide. We will look at memory management, parallel queries, and configuration parameters such as work_mem and shared_buffers. If you've been tasked with improving performance, this section will provide immediate benefits that you can apply to your existing setup. I've also included a chapter on managing complex data types and also discussing important extensions such as pg_trgm and hstore, to handle text searches and key-value stores. I also emphasize table partitioning, wherein I show you to create partitioned tables and manage them effectively using partition pruning and maintenance techniques. Finally, no PostgreSQL installation is complete without an effective backup and recovery strategy. I demonstrate how to perform physical and logical backups, automate them with BART, and, most importantly, use PITR to recover from accidental data loss or corruption. Throughout this book, you will learn how to manage streaming replication, ensure high availability, and implement failover and switchover procedures to keep your databases up and running. In this, book, you will discover workable answers to common problems,

regardless of whether you are managing databases in simple, localized settings or massive, enterprise-level empires. 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: October 2024 For permission to use material from this book, please contact GitforGits at support@gitforgits.com.

Content Preface GitforGits Acknowledgement Chapter 1: Upgrading and Setting up PostgreSQL 17 Brief Overview Overview of PostgreSQL 17 Key Enhancements Incremental Backup Support Performance Optimizations for Queries Enhanced Logical Replication JSON Enhancements and SQL/JSON Support Partitioning and Indexing Improvements Impact of PostgreSQL 17 Upgrade on Existing Systems Upgradation Strategies for PostgreSQL 17 Pre-Upgrade Assessments Evaluate Current Database Version and Features Assess Hardware and System Resources Review Database Workloads Backup the Database PostgreSQL 17 Compatibility Check

Review Deprecations Check Compatibility of Extensions Database Drivers and Client Libraries Deprecation Considerations Configuration Deprecations SQL Feature Deprecations Pre-Upgrade Assessment Tools Performing Upgrade Prepare the System Check Current PostgreSQL Version Install PostgreSQL 17 Packages Stop PostgreSQL Services Prepare Data Directory Backup Configuration files Run pg_upgrade Command Update Extensions List Installed Extensions Update Extensions Handling Custom Extensions Review Custom Configurations Restore Configuration files Security Configurations (pg_hba.conf) Adjust New Parameters Start PostgreSQL 17 Clean up Old PostgreSQL Version Post-Upgrade Validation Verifying Database Integrity Run pg_upgrade Validation

Run Consistency Checks with CHECKSUMS Verify Application-Specific Data Integrity Analyze Database Performance Check Logs for Errors or Warnings Testing Application Compatibility Test Application Connection Test Application Features and Queries Test Extensions and Plugins Test Transaction Behavior Test Backup and Restore Procedures Conduct Load Testing Setting up Titanic Database Download Titanic Dataset Importing Dataset into PostgreSQL Create New Database Import Titanic SQL file Exploring Titanic Schema Inspect Table Schema Check Number of Records View Sample Data Understand Data Types and Constraints Analyze Data Distribution Summary Chapter 2: Expert Database Cluster Administration Brief Overview

Deep Dive into PostgreSQL Architecture Background Workers Extensions Managing Database Clusters Starting, Stopping, and Restarting Clusters Starting a PostgreSQL Cluster Stopping a PostgreSQL Cluster Restarting a PostgreSQL Cluster Managing Multiple PostgreSQL Clusters Creating New Cluster Specifying Different Port for each Cluster Managing Multiple Clusters Simultaneously Viewing and Listing Active Clusters Removing a Cluster Advanced Configuration Parameters Fine-Tuning postgresql.conf Memory Allocation Settings Checkpoint Configuration WAL Configuration Parallelism Settings Autovacuum Configuration Configuring pg_hba.conf Structure of pg_hba.conf Configuring IP-Based Access Control Implementing Secure Authentication Methods Restricting Superuser Access Setting up SSL Connections

Summary Chapter 3: Advanced Database and Role Management Brief Overview Sophisticated Database Management Recreating Titanic Database with a Template Create a Custom Template Database Recreate Titanic Database using a Template Managing Schemas and Namespaces Understanding Default Schema Behavior Create and use Custom Schemas Switching between Schemas Managing Object Access between Schemas Moving Objects between Schemas Dropping Schemas Role Hierarchies and Permissions Sample Program: Role Hierarchies and Permissions Defining Basic Roles Creating Admin Role Granting Permissions to Data Engineering Role Granting Read-Only Permissions to Data Analyst Role Granting Elevated Permissions to Admin Role Role Inheritance and Group Roles Creating Group Roles for each Team Assigning Group Roles to Team Members

Assigning Role Inheritance to Group Roles Testing Role Inheritance Managing Role Privileges and Revoking Permissions Authentication Mechanisms Restrict Remote Access by IP Range Restricting Superuser Access Integrating LDAP Integrating Kerberos Authentication Configuring Kerberos in ‘pg_hba.conf’ Setting up PostgreSQL Server Verifying Kerberos Authentication Enabling SSL/TLS Configuring SSL Configuring ‘pg_hba.conf’ for SSL Enforcing Client Certificates Testing SSL Connections Fine-Grained Access Control Enabling Row-Level Security (RLS) Implementing Row-Level Security Policies Define a Policy for Data Engineers Define a Policy for Data Analysts Testing Row-Level Security Policies Testing for Data Engineers Testing for Data Analysts Restricting Access Based on User Identity Combining Multiple Policies Managing Row-Level Security and Policies

Summary Chapter 4: Configuration and Performance Tuning Brief Overview Optimizing Server Performance Memory and Resource Allocation Strategies Configuring Database Caching Memory Allocation for Operations Memory for Maintenance Tasks Estimating Available Memory Other Key Parameters for Optimizing Performance Limiting Concurrent Connections Adjusting Checkpoint Frequency Tuning Write-Ahead Logging Query Planning and Execution Using EXPLAIN to understand Query Plans Using EXPLAIN ANALYZE to Measure Query Performance Using EXPLAIN to Optimize Complex Queries Resource Management Techniques Connection Pooling with pgBouncer Installing pgBouncer Configuring pgBouncer Starting and Monitoring pgBouncer Managing Workloads with pg_stat_statements Enabling ‘pg_stat_statements’

Using ‘pg_stat_statements’ Resetting Statistics Analyzing Slow Queries Optimizing Queries based on Statistics Logging and Auditing Sample Program: Monitoring Passenger Data Queries Enable Logging Log All Queries Log Long-Running Queries Schema Changes Logging Log Connection and Disconnections Log Client Information Reviewing the Logs Sample Program: Auditing Access to Passenger Data Installing pgAudit Configuring pgAudit Reviewing Audit Logs Summary Chapter 5: Effective Data Management Brief Overview Advanced Data Types and Extensions Working with JSONB Alter Table to Add JSONB Column Inserting JSONB Data Querying JSONB Data Indexing JSONB Data

Working with Arrays Alter Table to Add Array Column Inserting Array Data Querying Array Data Manipulating Arrays Working with Composite Types Create Composite Type Add Composite Type as a Column Inserting Data into Composite Type Querying Composite Types Using ‘hstore’ and ‘pg_trgm’ Install and Enable ‘hstore’ Adding ‘hstore’ Data Inserting Data Querying ‘hstore’ Data Install and Enable ‘pg_trgm’ Using ‘pg_trgm’ for Fuzzy Search Creating Index for Fuzzy Search Sophisticated Indexing Techniques GIN (Generalized Inverted Index) Creating GIN Index Querying with GIN Index Maintaining GIN Indexes GiST (Generalized Search Tree) Adding Geometric Data Column Inserting Geometric Data Creating GiST Index Querying with GiST Index BRIN (Block Range INdex)

Creating BRIN Index Querying with BRIN Index Maintaining BRIN Indexes Index Maintenance and Monitoring Monitoring Index Usage Rebuilding and Removing Indexes Constraints, Triggers, and Functions Sample Program: Creating Complex Constraints Adding a CHECK Constraint on Age Adding a CHECK Constraint for Age and Ticket Type Testing Constraints Sample Program: Writing Advanced Triggers Create the Log Table Create a Trigger Function Create the Trigger Testing the Trigger Sample Program: Designing Stored Functions Create the Function Using the Function Creating a Function with Parameters Combining Constraints, Triggers, and Functions Add a Constraint for Ticket Price Create a Trigger to Recalculate Revenue on Insert/Update Summary Chapter 6: Table Partitioning Strategies Brief Overview

Partitioning Concepts Implementing Partitioned Tables Sample Program: Partitioning by Range Creating a Partitioned Table Creating Range Partitions Inserting Data into the Partitioned Table Querying the Partitioned Table Sample Program: Partitioning by List Creating a Partitioned Table Creating List Partitions Inserting Data into the Partitioned Table Querying the Partitioned Table Sample Program: Partitioning by Hash Creating a Partitioned Table Creating Hash Partitions Inserting Data into the Partitioned Table Querying the Partitioned Table Managing Partitions Effectively Sample Program: Adding Partitions Add a New Range Partition for Infants Verify the New Partition Sample Program: Merging Partitions Merge Partitions Validate the Data Movement Sample Program: Splitting Partitions Create New Partitions for Age Groups Move Data to the New Partitions

Drop the Old Partition Verify the New Partitions Optimizing Queries on Partitioned Tables Sample Program: Partition Pruning in Range Partitioning Query Without Partition Pruning Partition Pruning in Action Automatic Partition Pruning Sample Program: Indexing Range-Partitioned Tables Creating Indexes on Partitions Querying with Indexed Partitions Sample Program: Indexing List-Partitioned Tables Creating Indexes on Partitions Querying with Indexed Partitions Sample Program: Indexing Hash-Partitioned Tables Creating Indexes on Hash Partitions Querying with Indexed Hash Partitions Monitoring Query Performance Using EXPLAIN ANALYZE Monitoring Index Usage Summary Chapter 7: Backup and Recovery Best Practices Brief Overview Designing a Backup Strategy RPO