PostgreSQL 14 Administration Cookbook: Over 175 proven recipes for database administrators to manage enterprise databases effectively

608 Pages·2022·49.562 MB·English

by  Simon Riggs, Gianni Ciolli

PostgreSQL 14
Administration
Cookbook
Over 175 proven recipes for database administrators
to manage enterprise databases effectively
Simon Riggs
Gianni Ciolli
BIRMINGHAM—MUMBAI
PostgreSQL 14 Administration Cookbook
Copyright © 2022 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.
Publishing Product Manager: Devika Battike
Senior Editor: David Sugarman
Content Development Editor: Joseph Sunil
Technical Editor: Rahul Limbachiya
Copy Editor: Safis Editing
Project Coordinator: Aparna Nair
Proofreader: Safis Editing
Indexer: Tejal Daruwale Soni
Production Designer: Alishon Mendonca
Marketing Coordinator: Priyanka Mhatre
First published: October 2010
Second Edition: April 2015
Third Edition: April 2017
Fourth Edition: May 2018
Fifth Edition: May 2019
Sixth Edition: April 2022
Production reference: 1240222
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80324-897-4
www.packt.com
Contributors
About the authors
Simon Riggs is a Postgres Fellow at EnterpriseDB (EDB), a member of the company's
executive management team, and a major developer of PostgreSQL for over 15 years. At
EDB, he contributes to technical strategy and works with key customers directly. Before
this, Simon was the founder and CEO of 2ndQuadrant, acquired by EDB in 2020. Simon
has contributed widely to PostgreSQL over the course of 17 years, initiating new projects,
contributing ideas, and committing many important changes. He continues his work with
the PostgreSQL community as a developer and on patch review. Simon is also a regular
speaker at PostgreSQL conferences. Simon lives happily with his wife Karen in England.
Gianni Ciolli is the Vice President for Solutions Architecture at EnterpriseDB (EDB).
As a PostgreSQL consultant, he has driven many successful enterprise deployments for
customers in every part of the globe. Gianni is respected worldwide as a popular speaker
and trainer at many PostgreSQL conferences in Europe and abroad over the last 14 years.
He has worked with free and open source software since the 1990s as an active member
of the community (the Prato Linux User Group and the Italian PostgreSQL Users Group).
Gianni has a PhD in mathematics from the University of Florence. He lives in London
with his son. His other interests include music, drama, poetry, and athletics.
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 where he has worked 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, and his wife, Romina. He also likes playing
football.
Tomas Vondra is a PostgreSQL committer and works as a principal database engineer
at EnterpriseDB. He started working with PostgreSQL in 2003, initially as a user and
developer of systems on top of PostgreSQL, learning various aspects of operating and
tuning the database. Eventually, he started to contribute patches of increasing complexity.
After some time, he was recognized as a major contributor and became a committer in
2018. At EnterpriseDB, he helps customers and users with all sorts of issues and focuses
on features related to performance, query optimizer capabilities, extended statistics, and
reliability. He's also the author of various plugins and extensions. He lives in Prague,
Czech Republic.
Table of Contents
Preface
1
First Steps
Introducing PostgreSQL 14 2 Using the OmniDB GUI tool 22
What makes PostgreSQL different? 3 How to do it… 23
See also 27
How to get PostgreSQL 8
How to do it... 8 Using the psql query and
scripting tool 27
How it works... 10
There's more… 11 Getting ready 28
How to do it… 28
Connecting to the PostgreSQL
How it works… 30
server 11
There's more… 31
Getting ready 11
See also 31
How to do it… 11
How it works… 13 Changing your password
securely 32
There's more… 14
See also 14 How to do it… 32
How it works… 33
Enabling access for network/
remote users 14 Avoiding hardcoding your
password 33
How to do it… 15
How it works… 15 Getting ready 33
There's more… 17 How to do it… 34
See also 17 How it works… 34
There's more… 35
Using the pgAdmin4 GUI tool 17
Using a connection service file 35
How to do it… 17
How it works… 21 How to do it… 35
How it works… 36
vi Table of Contents
Troubleshooting a failed PostgreSQL in the cloud 38
connection 36
Getting ready 38
How to do it… 36 How to do it… 39
There's more… 38 How it works… 43
There's more… 44
2
Exploring the Database
What type of server is this? 46 Locating the database's system
identifier 57
How to do it… 47
There's more... 47 Getting ready 58
How to do it… 58
What version is the server? 47
How it works… 58
How to do it… 48
Listing databases on the
How it works… 48
database server 59
There's more… 49
How to do it… 59
What is the server uptime? 49
How it works... 60
How to do it… 50
There's more... 61
How it works… 50
How many tables are there in a
See also 51
database? 63
Locating the database server
How to do it... 63
files 51
How it works… 65
Getting ready 51
There's more… 66
How to do it... 51
How much disk space does a
How it works... 52
database use? 66
There's more… 54
How to do it... 66
Locating the database server's
How it works... 67
message log 55
How much disk space does a
Getting ready 55
table use? 67
How to do it... 56
How it works... 56 How to do it… 67
There's more... 57 How it works… 68
See also 57 There's more… 69
Table of Contents vii
Which are my biggest tables? 69 Listing extensions in this
database 74
How to do it... 69
How it works… 70 How to do it… 75
How it works… 75
How many rows are there in a
There's more… 76
table? 70
See also 76
How to do it… 70
How it works... 71 Understanding object
dependencies 76
Quickly estimating the number
Getting ready 76
of rows in a table 72
How to do it… 77
How to do it… 72
How it works… 77
How it works… 73
There's more… 78
There's more… 74
3
Server Configuration
RTFM 80 Setting configuration
parameters in your programs 89
How to do it… 80
How it works… 81 How to do it… 90
There's more… 81 How it works… 90
There's more… 91
Planning a new database 81
Finding the configuration
Getting ready 81
settings for your session 92
How to do it… 82
How it works… 83 How to do it… 92
There's more… 83 How it works… 94
Setting configuration Finding parameters with
parameters for the database non-default settings 94
server 83 How to do it… 95
Getting ready 84 How it works... 95
How to do it… 85 There's more... 96
How it works… 86
There's more… 87
viii Table of Contents
Setting parameters for Getting ready 100
particular groups of users 96 How to do it… 101
How to do it… 97 How it works... 103
How it works… 97
Using an installed module/
A basic server configuration extension 104
checklist 98 Getting ready 104
Getting ready 98 How to do it… 104
How to do it… 98 How it works... 104
There's more… 98
Managing installed extensions 105
Adding an external module to How to do it… 105
PostgreSQL 99 How it works… 107
There's more… 108
4
Server Control
Overview of controlling the Reloading the server
database server 110 configuration files 117
Starting the database server How to do it… 117
manually 111 How it works… 118
Getting ready 111 There's more… 119
How to do it… 111
Restarting the server quickly 119
How it works… 113
How to do it… 119
Stopping the server safely and There's more… 121
quickly 114
Preventing new connections 121
How to do it… 114
How to do it… 121
How it works… 115
How it works… 122
See also 115
Restricting users to only one
Stopping the server in an
session each 123
emergency 116
How to do it… 123
How to do it… 116
How it works… 123
How it works… 116
Table of Contents ix
Pushing users off the system 124 There's more… 132
How to do it… 124 See also 132
How it works… 125
Running multiple servers on
Deciding on a design for one system 132
multitenancy 126 Getting ready 132
How to do it… 127 How to do it… 133
How it works… 128 How it works… 134
Using multiple schemas 128 Setting up a connection pool 134
Getting ready 128 Getting ready 135
How to do it… 128 How to do it… 135
How it works… 130 How it works… 136
There's more… 137
Giving users their own private
databases 131 Accessing multiple servers
using the same host and port 139
Getting ready 131
How to do it… 131 Getting ready 139
How it works… 132 How to do it… 139
There's more… 141
5
Tables and Data
Choosing good names for How to do it... 149
database objects 144 How it works… 152
Getting ready 144 There's more… 152
How to do it… 144
Identifying and removing
There's more… 145
duplicates 154
Handling objects with quoted Getting ready 154
names 146 How to do it… 155
Getting ready 147 How it works… 157
How to do it... 147 There's more… 159
How it works… 148
Preventing duplicate rows 159
There's more… 148
Getting ready 159
Enforcing the same name and How to do it… 160
definition for columns 149 How it works… 162
Getting ready 149 There's more... 163