Statistics
14
Views
0
Downloads
0
Donations
Support
Share
Uploader

高宏飞

Shared on 2026-04-19

AuthorGianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs

Written by a team of well-known PostgreSQL experts, this new edition will cover all the latest updates of PostgreSQL 16 including 12+ new and improved recipes on logging, monitoring, security and high-performance Key Features • Skill-up as a database administrator by achieving improved query performance, backup, and recovery management, setting up replication and so on • Get to grips with the essentials of database management with a recipe-based approach using the latest features of PostgreSQL 16 • New and updated recipes on crucial PostgreSQL topics like Monitoring, Logging, Scalability and so on Book Description PostgreSQL has seen a huge increase in its customer base in the past few years and is becoming one of the go-to solutions for anyone who has a database-specific challenge. This PostgreSQL book touches on all the fundamentals of Database Administration in a problem-solution format. It is intended to be the perfect desk reference guide. This new edition focuses on recipes based on the new PostgreSQL 16 release. The additions include handling complex batch loading scenarios with the SQL MERGE statement, security improvements, running Postgres on Kubernetes or with TPA and Ansible, and more. This edition also focuses on certain performance gains, such as query optimization, and the acceleration of specific operations, such as sort. It will help you understand roles, ensuring high availability, concurrency, and replication. It also draws your attention to aspects like validating backups, recovery, monitoring, and scaling aspects. This book will act as a one-stop solution to all your real-world database administration challenges. By the end of this book, you will be able to manage, monitor, and replicate your PostgreSQL 16 database for efficient administration and maintenance with the best practices from experts.

Tags
No tags
Publisher: Packt Publishing
Publish Year: 2021
Language: 英文
Pages: 637
File Format: PDF
File Size: 10.5 MB
Support Statistics
¥.00 · 0times
Text Preview (First 20 pages)
Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

(This page has no text content)
PostgreSQL 16 Administration Cookbook Solve real-world Database Administration challenges with 180+ practical recipes and best practices Gianni Ciolli Boriss Mejías Jimmy Angelakos Vibhor Kumar Simon Riggs BIRMINGHAM—MUMBAI
PostgreSQL 16 Administration Cookbook Copyright © 2023 Packt Publishing All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information. Senior Publishing Product Manager: Gebin George Acquisition Editor – Peer Reviews: Swaroop Singh Project Editor: Parvathy Nair Content Development Editors: Davide Oliveri, Elliot Dallow, Soham Amburle Copy Editor: Safis Editing Technical Editor: Aniket Shetty Proofreader: Safis Editing Indexer: Manju Arasan Presentation Designer: Ganesh Bhadwalkar Developer Relations Marketing Executive: Vignesh Raju First published: December 2023 Production reference: 1301123 Published by Packt Publishing Ltd. Grosvenor House 11 St Paul’s Square Birmingham B3 1RB, UK. ISBN 978-1-83546-058-0 www.packt.com
Boriss, Gianni, Jimmy, and Vibhor are grateful to Simon Riggs, for having been the main author of all the past editions of this book. They hope that, by joining forces, they were able to continue that high standard.
Contributors About the authors Gianni Ciolli is Vice President and Field CTO at EDB; he was Global Head of Professional Services at 2ndQuadrant until it was acquired by EDB. Gianni has been a PostgreSQL consultant, trainer, and speaker at many PostgreSQL conferences in Europe and abroad for more than 10 years. He has a PhD in Mathematics from the University of Florence. He has worked with free and Open- Source software since the 1990s and is active in the community. He lives between Frankfurt and London and plays the piano in his spare time. Gianni has learned a lot from his colleagues and customers over the years and would like to thank them. Boriss Mejías is a Senior Solutions Architect at EDB, building on his experience as PostgreSQL consultant and trainer at 2ndQuadrant. He has been working with Open-Source software since the beginning of the century, contributing to several projects both with code and community work. He has a PhD in Computer Science from the Université catholique de Louvain, and an En- gineering degree from Universidad de Chile. Complementary to his role as Solutions Architect, he gives PostgreSQL training and is a regular speaker at PostgreSQL conferences. He loves spending time with his family and playing air guitar. I would like to thank my co-authors for the great collaboration in writing this book. It has been a great experience. I would also like to thank the PostgreSQL community for everything I have learned from them during all these years. Special thanks to my family for all their support, laughs, and daily fun.
Jimmy Angelakos is a Systems and Database Architect and recognized PostgreSQL expert, with a wealth of experience gained from his career in Software Architecture and his key roles at 2nd- Quadrant and EDB. He studied Computer Science at the University of Aberdeen and has worked with, and contributed to, Open Source tools for 25+ years. He is passionate about participating in the community, and is an active member of PostgreSQL Europe and an occasional contributor to the PostgreSQL project. He is a regular speaker at conferences and events focused on databases and Open Source software, sharing his insights with the community. No one is an island, and none of this would have been possible without the mentoring, knowledge sharing, and guidance that the PostgreSQL community has so generously provided to me over the years. Vibhor Kumar, Global VP at EDB, is a pioneering data tech leader. He manages a global team of engineers, optimizing clients’ Postgres databases for peak performance and scalability. He advises Fortune 500 clients, including many financial institutes, on innovating and transforming their data platforms. His past experience spans IBM, BMC Software, and CMC Ltd. He holds a BSc in Computer Science from the University of Lucknow and a Master’s from the Army Institute of Management. As a certified expert in numerous technologies, he often shares his insights on DevOps the cloud, and database optimization through blogging and speaking at events. I’m thankful to everyone who supported this project. Special thanks to my wife, Nandini Karkare, for her constant support and love. I’m also grateful to my colleagues and co-authors for their insights and contributions and to Marc Linster for his mentorship. This book is a result of our collective efforts. Thank you all for being part of this journey. Simon Riggs is a Major Developer of PostgreSQL since 2004. Formerly, Simon was Founder and CEO of 2ndQuadrant, acquired by EDB in 2020. Simon has contributed widely to PostgreSQL, initiating new projects, contributing ideas, and committing many important features, as well as working directly with database architects and users on advanced solutions.
About the reviewers Marcelo Diaz is a Software Engineer with more than 15 years of experience, with a special focus on PostgreSQL. He is passionate about Open-Source software and has promoted its application in critical and high-demand environments, working as a software developer and consultant for both private and public companies. He currently works very happily at Cybertec and as a technical reviewer for Packt Publishing. He enjoys spending his leisure time with his daughter, Malvina, his wife, Romina, and their pets. He also likes to play “fulbo”, but currently he enjoys it more watching Messi on TV. Martín Marqués began his career as a DBA and Software Developer at a local university in Argentina over 20 years ago. He dedicated 13 years to these roles, during which he provided train- ing using custom materials to various agencies. Later, he transitioned to a technical support role, specializing in remote DBA services and consulting for clients at 2ndQuadrant. In recent years, Martín shifted into a management role within technical support at EnterpriseDB. In the past year, he has taken on the position of Engineering Manager for five EnterpriseDB products. Afroditi Loukidou is a PostgreSQL and Open-Source enthusiast, currently working as a Tech- nical Lead at EnterpriseDB. She has studied Industrial Informatics and holds an MSc in Computer Networks. Her journey with PostgreSQL started at 2ndQuadrant and went on with EDB, where she has gained a wealth of experience working as a PostgreSQL engineer assisting smaller and bigger customers with PostgreSQL operational aspects, maintenance, tuning, upgrades and more. In her role as a Technical Lead, she also gets exposure to more architectural aspects and larger- scale projects of varied complexity and has always found this book to be a great resource to turn to. She lives in London and loves music, mountaineering, and generally spending time in nature.
Learn more on Discord To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below: https://discord.gg/pQkghgmgdG
(This page has no text content)
Table of Contents Preface xxxix Chapter 1: First Steps 1 Introducing PostgreSQL 16 �������������������������������������������������������������������������������������������������� 2 What makes PostgreSQL different? • 2 Robustness • 4 Security • 4 Ease of use • 5 Extensibility • 5 Performance and concurrency • 6 Scalability • 6 SQL and NoSQL data models • 6 Popularity • 7 Commercial support • 7 Research and development funding • 8 How to get PostgreSQL �������������������������������������������������������������������������������������������������������� 8 How to do it... • 8 How it works... • 10 There’s more… • 11 Connecting to the PostgreSQL server ��������������������������������������������������������������������������������� 11 Getting ready • 11 How to do it... • 13
Table of Contentsx How it works… • 13 There’s more… • 14 See also • 15 Enabling access for network/remote users ������������������������������������������������������������������������� 15 How to do it… • 15 How it works… • 16 There’s more… • 17 See also • 18 Using the pgAdmin 4 GUI tool ������������������������������������������������������������������������������������������� 18 How to do it… • 19 How it works… • 22 See also • 23 Using the psql query and scripting tool ����������������������������������������������������������������������������� 24 Getting ready • 24 How to do it… • 24 How it works… • 27 There’s more… • 28 See also • 29 Changing your password securely ������������������������������������������������������������������������������������� 29 How to do it… • 29 How it works… • 30 Avoiding hardcoding your password ��������������������������������������������������������������������������������� 30 Getting ready • 30 How to do it… • 30 How it works… • 31 There’s more… • 32 Using a connection service file ������������������������������������������������������������������������������������������ 32 How to do it… • 32 How it works… • 33 There’s more... • 33
Table of Contents xi Troubleshooting a failed connection �������������������������������������������������������������������������������� 33 How to do it… • 33 There’s more… • 35 PostgreSQL in the cloud ���������������������������������������������������������������������������������������������������� 35 Getting ready • 35 How to do it… • 35 How it works… • 40 There’s more… • 40 PostgreSQL with Kubernetes �������������������������������������������������������������������������������������������� 41 Getting ready • 41 How to do it... • 42 How it works… • 43 There’s more... • 44 PostgreSQL with TPA �������������������������������������������������������������������������������������������������������� 45 Getting ready • 45 How to do it... • 46 There’s more • 49 Chapter 2: Exploring the Database 51 What type of server is this? ����������������������������������������������������������������������������������������������� 52 How to do it… • 52 There’s more... • 53 What version is the server? ����������������������������������������������������������������������������������������������� 53 How to do it… • 53 How it works… • 54 There’s more… • 55 What is the server uptime? ������������������������������������������������������������������������������������������������ 56 How to do it… • 56 How it works… • 56 See also • 57
Table of Contentsxii Locating the database server files ������������������������������������������������������������������������������������� 57 Getting ready • 57 How to do it... • 57 How it works... • 58 There’s more… • 59 Locating the database server’s message log ���������������������������������������������������������������������� 60 Getting ready • 61 How to do it... • 61 How it works... • 62 There’s more... • 62 Locating the database’s system identifier ������������������������������������������������������������������������� 63 Getting ready • 63 How to do it… • 63 How it works… • 64 Listing databases on the database server �������������������������������������������������������������������������� 64 How to do it… • 65 How it works... • 66 There’s more... • 67 How many tables are there in a database? ������������������������������������������������������������������������� 68 How to do it... • 68 How it works… • 69 There’s more… • 71 How much disk space does a database use? ������������������������������������������������������������������������ 71 How to do it... • 72 How it works... • 72 How much memory does a database currently use? ���������������������������������������������������������� 72 How to do it… • 73 How it works… • 74 How much disk space does a table use? ����������������������������������������������������������������������������� 74 How to do it… • 74 How it works… • 75 There’s more… • 76
Table of Contents xiii Which are my biggest tables? �������������������������������������������������������������������������������������������� 76 How to do it... • 76 How it works… • 77 How many rows are there in a table? ��������������������������������������������������������������������������������� 77 How to do it… • 77 How it works... • 78 Quickly estimating the number of rows in a table ������������������������������������������������������������� 79 How to do it… • 79 How it works… • 80 There’s more… • 81 Listing extensions in this database ����������������������������������������������������������������������������������� 82 How to do it… • 82 How it works… • 83 There’s more… • 83 See also • 83 Understanding object dependencies ��������������������������������������������������������������������������������� 84 Getting ready • 84 How to do it… • 85 How it works… • 86 There’s more… • 86 Chapter 3: Server Configuration 89 Read the fine manual (RTFM) ������������������������������������������������������������������������������������������� 90 How to do it… • 90 How it works… • 90 There’s more… • 91 Planning a new database �������������������������������������������������������������������������������������������������� 91 Getting ready • 91 How to do it… • 91 How it works… • 92 There’s more… • 93
Table of Contentsxiv Setting the configuration parameters for the database server ������������������������������������������� 93 Getting ready • 93 How to do it… • 94 How it works… • 97 There’s more… • 97 Setting the configuration parameters in your programs ��������������������������������������������������� 99 How to do it… • 100 How it works… • 101 There’s more… • 101 Finding the configuration settings for your session �������������������������������������������������������� 102 How to do it… • 102 How it works… • 104 Finding parameters with non-default settings ��������������������������������������������������������������� 104 How to do it… • 105 How it works... • 105 There’s more... • 105 Setting parameters for particular groups of users ����������������������������������������������������������� 106 How to do it… • 106 How it works… • 106 A basic server configuration checklist ������������������������������������������������������������������������������ 107 Getting ready • 107 How to do it… • 107 There’s more… • 108 Adding an external module to PostgreSQL ���������������������������������������������������������������������� 109 Getting ready • 110 How to do it… • 110 Installing modules using a software installer • 110 Installing modules from PGXN • 111 Installing modules from source code • 112 How it works... • 112
Table of Contents xv Using an installed module/extension ������������������������������������������������������������������������������� 113 Getting ready • 113 How to do it… • 113 How it works... • 114 Managing installed extensions ���������������������������������������������������������������������������������������� 114 How to do it… • 114 How it works… • 116 There’s more… • 117 Chapter 4: Server Control 119 An overview of controlling the database server ��������������������������������������������������������������� 120 Starting the database server manually ����������������������������������������������������������������������������� 121 Getting ready • 121 How to do it… • 121 How it works… • 123 Stopping the server safely and quickly ����������������������������������������������������������������������������� 124 How to do it… • 124 How it works… • 125 See also • 125 Stopping the server in an emergency ������������������������������������������������������������������������������� 125 How to do it… • 126 How it works… • 126 Reloading server configuration files ��������������������������������������������������������������������������������� 126 How to do it… • 126 How it works… • 128 There’s more… • 129 Restarting the server quickly ������������������������������������������������������������������������������������������� 129 How to do it… • 129 There’s more… • 130 Preventing new connections �������������������������������������������������������������������������������������������� 131 How to do it… • 131 How it works… • 132
Table of Contentsxvi Restricting users to only one session each ������������������������������������������������������������������������ 132 How to do it… • 132 How it works… • 133 Pushing users off the system �������������������������������������������������������������������������������������������� 134 How to do it… • 134 How it works… • 135 Deciding on a design for multitenancy ���������������������������������������������������������������������������� 136 How to do it… • 136 How it works… • 137 Using multiple schemas ��������������������������������������������������������������������������������������������������� 137 Getting ready • 137 How to do it… • 138 How it works… • 139 Giving users their own private databases ������������������������������������������������������������������������ 140 Getting ready • 140 How to do it… • 140 How it works… • 141 There’s more… • 141 See also • 142 Running multiple servers on one system �������������������������������������������������������������������������� 142 Getting ready • 142 How to do it… • 142 How it works… • 143 Setting up a connection pool ������������������������������������������������������������������������������������������� 144 Getting ready • 144 How to do it… • 144 How it works… • 146 There’s more… • 146 Accessing multiple servers using the same host and port ������������������������������������������������ 148 Getting ready • 148 How to do it… • 148
Table of Contents xvii There’s more… • 150 Running multiple PgBouncer on the same port to leverage multiple cores ��������������������� 150 Getting ready • 150 How to do it… • 151 How it works… • 152 Chapter 5: Tables and Data 153 Choosing good names for database objects ���������������������������������������������������������������������� 154 Getting ready • 154 How to do it… • 154 There’s more… • 155 Handling objects with quoted names ������������������������������������������������������������������������������� 156 Getting ready • 157 How to do it... • 157 How it works… • 158 There’s more… • 158 Identifying and removing duplicates ������������������������������������������������������������������������������� 159 Getting ready • 159 How to do it… • 160 How it works… • 162 There’s more… • 163 Preventing duplicate rows ���������������������������������������������������������������������������������������������� 164 Getting ready • 164 How to do it… • 164 How it works… • 167 There’s more... • 167 Duplicate indexes • 167 Uniqueness without indexes • 167 A real-world example – IP address range allocation • 168 A real-world example – a range of time • 169
Table of Contentsxviii Finding a unique key for a set of data ������������������������������������������������������������������������������� 169 Getting ready • 170 How to do it… • 170 How it works… • 172 Generating test data ��������������������������������������������������������������������������������������������������������� 172 How to do it... • 172 How it works… • 175 There’s more… • 175 See also • 176 Randomly sampling data ������������������������������������������������������������������������������������������������� 176 How to do it… • 177 How it works... • 178 Loading data from a spreadsheet ������������������������������������������������������������������������������������ 180 Getting ready • 180 How to do it... • 181 How it works... • 182 There’s more... • 183 Loading data from flat files ��������������������������������������������������������������������������������������������� 183 Getting ready • 183 How to do it... • 183 How it works… • 185 There’s more… • 186 Making bulk data changes using server-side procedures with transactions �������������������� 187 Getting ready • 187 How to do it… • 188 There’s more… • 188 Dealing with large tables with table partitioning ������������������������������������������������������������ 191 How to do it… • 192 How it works… • 193 There’s more… • 193
Table of Contents xix Finding good candidates for partition keys ��������������������������������������������������������������������� 194 Getting ready • 194 How to do it… • 194 There’s more… • 195 Consolidating data with MERGE �������������������������������������������������������������������������������������� 195 Getting ready • 195 How to do it… • 196 There’s more... • 197 Deciding when to use JSON data types ����������������������������������������������������������������������������� 197 Getting ready • 197 How to do it… • 198 Example: moving sparse columns to JSON • 199 Example: expose JSON data using a view • 201 There’s more… • 202 Chapter 6: Security 205 An overview of PostgreSQL security �������������������������������������������������������������������������������� 206 Typical user roles • 207 The PostgreSQL superuser ���������������������������������������������������������������������������������������������� 207 How to do it… • 207 How it works… • 208 There’s more… • 208 Other superuser-like attributes • 208 See also • 208 Revoking user access to tables ��������������������������������������������������������������������������������������� 208 Getting ready • 208 How to do it… • 208 How it works… • 210 There’s more… • 210 Database creation scripts • 210 Default search path • 211 Securing views • 211