别人升 PostgreSQL 16 后稳定高效，而你还在摸索命令？

##

别人升 PostgreSQL 16 后稳定高效，而你还在摸索命令？

最近看了一本特别实用的 PostgreSQL 书籍，叫 《PostgreSQL 16 Cookbook, Second Edition》，强烈推荐你看看，尤其是如果你已经在用 PostgreSQL，或者正在往 DBA、高级开发的方向发展。

这本书的特点是完全围绕 PostgreSQL 16 新功能展开，而且是“实战型”的结构，每一章都是一个实际问题加上具体解决方案。比如：

• 如何配置从 standby 上进行逻辑复制？
• 如何用 SIMD 加速处理 JSON 和 ASCII？
• 如何用 pg_stat_io 分析 I/O 性能瓶颈？
• 如何优化批量数据导入和并行执行？
• 怎么配置 load_balance_hosts 实现负载均衡？

它还介绍了 JSON 语法的增强、新的权限管理方式，甚至包括 Rust 语言如何和 PostgreSQL 集成（pgx、rust-postgres）。

不管你是 DBA、后端开发，还是对 PostgreSQL 高级特性感兴趣，这本书都非常值得一读。不像教科书那样枯燥，而是一本“随时可查、拿来就用”的技术菜谱。

目录如下：

1、Chapter 1: Preparing PostgreSQL 16

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

2、Chapter 2: Performing Basic PostgreSQL Operations

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

3、Chapter 3: PostgreSQL Cloud Provisioning

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

4、Chapter 4: Database Migration to Cloud and PostgreSQL

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

5、Chapter 5: WAL, AutoVacuum & ArchiveLog

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

6、Chapter 6: Partitioning and Sharding Strategies

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
Create Child Tables using Inheritance
Create Indexes on Child Tables
Monitor and Adjust Partition Strategy
Recipe #5: Implement Automatic Partitioning
Create Initial Partitions
Implement Automatic Partition Creation
Test Automatic Partitioning
Recipe #6: Run Declarative Partitioning
Understand Declarative Partitioning:
Automate Partition Creation
Query Partitioned Data
Recipe #7: Configure Sharding with FWD and CitusData
Setup FWD
Configure Sharding with CitusData
Summary

7、Chapter 7: Troubleshooting Replication, Scalability & High Availability

Chapter 7: Troubleshooting Replication, Scalability & High Availability
Introduction
Recipe #1: Using Master-Slave Replication
Create Replication
Setup Slave Database and Monitor Replication
Recipe #2: Install and Configure ‘repmgr’
Getting Started with ‘repmgr’
Managing Nodes
Monitor and Manage Replication
Recipe #3: Cloning Database with ‘repmgr’
Configure ‘repmgr.conf’
Clone the Source Node
Streaming Replication
Recipe #4: Deploy High Availability Cluster with Patroni
Up and Running with ‘etcd’
Configuring Patroni
Verify Replication
Test Failover
Recipe #5: Using HAProxy and PgBouncer for High
Availability
Start HAProxy
Configuring PgBouncer
Monitor HAProxy and PgBouncer
Recipe #6: Perform Database Upgrade on Replication
Cluster
Run the Upgrade Script
Test the Upgrade
Recipe #7: Optimizing JSON Queries with SIMD
Acceleration
Analyze Performance Bottleneck
Optimize the Query for SIMD
Summary

8、Chapter 8: Blob, JSON Query, CAST Operator & Connections

Chapter 8: Blob, JSON Query, CAST Operator & Connections
Introduction
Recipe #1: Import BLOB Data Types
Convert Binary File to Hex-Encoded String
Convert BLOB Data Back to Binary
Recipe #2: Running Queries using Shell Script
Create Executable Shell Script
Integrate Script into Automation Workflow
Recipe #3: Working with PostgreSQL JSON Data
Create a Table with JSONB Data
Query JSON Data:
Recipe #4: Working with PostgreSQL CAST Operator
Convert Data Types
Filter Data
Summary

9、Chapter 9: Authentication, Audit & Encryption

Chapter 9: Authentication, Audit & Encryption
Introduction
Recipe #1: Manage Roles, Membership Attributes,
Authentication, and Authorizations
Manage Roles and Attributes
User Authentication
User Authorizations
Recipe #2: Setting up SSL Authentication
Configure PostgreSQL to Use SSL
SSL Authentication
Recipe #3: Configure Encryption with OpenSSL
Encrypt Database Files
Decrypt and Access Encrypted Files
Recipe #4: Implement Audit Logging with pgAudit and
Triggers
Up and Running with pgAudit
Verify pgAudit Logging
Create Trigger Function
Recipe #5: Install and Configure LDAP Authentication
Up and Running with LDAP
Test LDAP Authentication
Summary

10、Chapter 10: Implementing Database Backup Strategies

Chapter 10: Implementing Database Backup Strategies
Introduction
Recipe #1: Automate Database Backup
Schedule the Backup with Cron
Test Backup and Restore
Recipe #2: Execute Continuous Archiving PostgreSQL
Backup
Monitor WAL Archiving
Test Point-in-Time Recovery
Recipe #3: Working with pg_probackup and pgBackRest
Using pg_probackup
Using pgBackRest
Recipe #4: Perform Incremental and Differential
Backups
Incremental Backups
Differential Backups
Recipe #5: Execute Schema-Level Backup
Summary

11、Chapter 11: Perform Database Recovery & Restoration

Chapter 11: Perform Database Recovery & Restoration
Introduction
Recipe #1: Perform Full and Point-in-Time Recovery
(PITR)
Full Recovery
Point-in-Time Recovery (PITR)
Recipe #2: Restore Database using Barman with
Incremental/Differential Restore
Perform Backup
Start PostgreSQL in Recovery Mode
Restart PostgreSQL
Recipe #3: Perform Tablespace and Table Recovery
Tablespace Recovery
Table Recovery
Recipe #4: Perform Schema-Level Restore
Select and Mark Backup for Restoration
Schema Restoration
Recipe #5: Monitor Restore Operations
View Restore Logs
Verify Successful Restoration
Summary
Index
Epilogue