GO TO TOP

You're reading from PostgreSQL 16 Administration Cookbook Solve real-world Database Administration challenges with 180+ practical recipes and best practices

Product type Paperback

Published in Dec 2023

Publisher Packt

ISBN-13 9781835460580

Length 636 pages

Edition 1st Edition

Languages

SQL

Tools

PostgreSQL

Concepts

Database Administration

Authors (5):

Gianni Ciolli

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 over 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

See other products by Gianni Ciolli

Boriss Mejías

Boriss Mejias 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 Engineering 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

See other products by Boriss Mejías

Jimmy Angelakos

Jimmy Angelakos is a Systems and Database Architect and recognized PostgreSQL expert. He studied Computer Science at the University of Aberdeen, has worked with Open-Source tools for 25+ years, is an active member of PostgreSQL Europe and occasional contributor to the PostgreSQL project. He also speaks frequently at database and Free & Open-Source Software conferences

See other products by Jimmy Angelakos

Vibhor Kumar

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, in 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, cloud, and database optimization through blogging and speaking at events.

See other products by Vibhor Kumar

Simon Riggs

Simon Riggs is a Major Developer of PostgreSQL since 2004. Formerly, Simon was the Founder and CEO of 2ndQuadrant, acquired by EDB in 2020. Simon has contributed widely to PostgreSQL, initiating new projects, contributing ideas, committing many important features as well and working directly with database architects and users on advanced solutions

See other products by Simon Riggs

+1 more Show less

View More author details

Table of Contents (15) Chapters

Preface

Who this book is for
What this book covers
To get the most out of this book
Get in touch

1. First Steps FREE CHAPTER

Introducing PostgreSQL 16
How to get PostgreSQL
Connecting to the PostgreSQL server
Enabling access for network/remote users
Using the pgAdmin 4 GUI tool
Using the psql query and scripting tool
Changing your password securely
Avoiding hardcoding your password
Using a connection service file
Troubleshooting a failed connection
PostgreSQL in the cloud
PostgreSQL with Kubernetes
PostgreSQL with TPA

2. Exploring the Database

What type of server is this?
What version is the server?
What is the server uptime?
Locating the database server files
Locating the database server’s message log
Locating the database’s system identifier
Listing databases on the database server
How many tables are there in a database?
How much disk space does a database use?
How much memory does a database currently use?
How much disk space does a table use?
Which are my biggest tables?
How many rows are there in a table?
Quickly estimating the number of rows in a table
Listing extensions in this database
Understanding object dependencies

3. Server Configuration

Read the fine manual (RTFM)
Planning a new database
Setting the configuration parameters for the database server
Setting the configuration parameters in your programs
Finding the configuration settings for your session
Finding parameters with non-default settings
Setting parameters for particular groups of users
A basic server configuration checklist
Adding an external module to PostgreSQL
Using an installed module/extension
Managing installed extensions

4. Server Control

An overview of controlling the database server
Starting the database server manually
Stopping the server safely and quickly
Stopping the server in an emergency
Reloading server configuration files
Restarting the server quickly
Preventing new connections
Restricting users to only one session each
Pushing users off the system
Deciding on a design for multitenancy
Using multiple schemas
Giving users their own private databases
Running multiple servers on one system
Setting up a connection pool
Accessing multiple servers using the same host and port
Running multiple PgBouncer on the same port to leverage multiple cores

5. Tables and Data

Choosing good names for database objects
Handling objects with quoted names
Identifying and removing duplicates
Preventing duplicate rows
Finding a unique key for a set of data
Generating test data
Randomly sampling data
Loading data from a spreadsheet
Loading data from flat files
Making bulk data changes using server-side procedures with transactions
Dealing with large tables with table partitioning
Finding good candidates for partition keys
Consolidating data with MERGE
Deciding when to use JSON data types

6. Security

An overview of PostgreSQL security
The PostgreSQL superuser
Revoking user access to tables
Granting user access to a table
Granting user access to specific columns
Granting user access to specific rows
Creating a new user
Temporarily preventing a user from connecting
Removing a user without dropping their data
Checking whether all users have a secure password
Giving limited superuser powers to specific users
Auditing database access
Always knowing which user is logged in
Integrating with LDAP
Connecting using encryption (SSL / GSSAPI)
Using SSL certificates to authenticate
Mapping external usernames to database roles
Using column-level encryption
Setting up cloud security using predefined roles

7. Database Administration

Writing a script that either succeeds entirely or fails entirely
Writing a psql script that exits on the first error
Using psql variables
Placing query output into psql variables
Writing a conditional psql script
Investigating a psql error
Setting the psql prompt with useful information
Using pgAdmin for DBA tasks
Scheduling jobs for regular background execution
Performing actions on many tables
Adding/removing columns on a table
Changing the data type of a column
Changing the definition of an enum data type
Adding a constraint concurrently
Adding/removing schemas
Moving objects between schemas
Adding/removing tablespaces
Moving objects between tablespaces
Accessing objects in other PostgreSQL databases
Accessing objects in other foreign databases
Making views updatable
Using materialized views
Using GENERATED data columns
Using data compression

8. Monitoring and Diagnosis

Cloud-native monitoring
Providing PostgreSQL information to monitoring tools
Real-time viewing using pgAdmin
Monitoring the PostgreSQL message log
Checking whether a user is connected
Checking whether a computer is connected
Repeatedly executing a query in psql
Checking which queries are running
Monitoring the progress of commands
Checking which queries are active or blocked
Knowing who is blocking a query
Killing a specific session
Knowing whether anybody is using a specific table
Knowing when a table was last used
Monitoring I/O statistics
Usage of disk space by temporary data
Understanding why queries slow down
Analyzing the real-time performance of your queries
Tracking important metrics over time

9. Regular Maintenance

Controlling automatic database maintenance
Avoiding auto-freezing
Removing issues that cause bloat
Actions for heavy users of temporary tables
Identifying and fixing bloated tables and indexes
Monitoring and tuning a vacuum
Maintaining indexes
Finding unused indexes
Carefully removing unwanted indexes
Planning maintenance

10. Performance and Concurrency

Finding slow SQL statements
Finding out what makes SQL slow
Reducing the number of rows returned
Simplifying complex SQL queries
Speeding up queries without rewriting them
Discovering why a query is not using an index
Forcing a query to use an index
Using parallel query
Using Just-In-Time (JIT) compilation
Creating time-series tables using partitioning
Using optimistic locking to avoid long lock waits
Reporting performance problems

11. Backup and Recovery

Understanding and controlling crash recovery
Planning your backups
Hot logical backup of one database
Hot logical backup of all databases
Backup of database object definitions
A standalone hot physical backup
Hot physical backups with Barman
Recovery of all databases
Recovery to a point in time
Recovery of a dropped/damaged table
Recovery of a dropped/damaged database
Extracting a logical backup from a physical one
Improving the performance of logical backup/recovery
Improving the performance of physical backup/recovery
Validating backups

12. Replication and Upgrades

Replication concepts
Replication best practices
Setting up streaming replication
Setting up streaming replication security
Hot Standby and read scalability
Managing streaming replication
Using repmgr
Using replication slots
Setting up replication with TPA
Setting up replication with CloudNativePG
Monitoring replication
Performance and synchronous replication (sync rep)
Delaying, pausing, and synchronizing replication
Logical replication
EDB Postgres Distributed
Archiving transaction log data
Upgrading minor releases
Major upgrades in-place
Major upgrades online

13. Other Books You May Enjoy

14. Index

Real-time viewing using pgAdmin

You can also use a GUI tool such as pgAdmin, which we discussed for the first time in Chapter 1, First Steps, to get a quick view of what is going on in the database.

Getting ready

pgAdmin 4 no longer requires an extension to access PostgreSQL fully, so there is no need to install adminpack, as was required in earlier editions.

How to do it…

This section illustrates the pgAdmin tool.

Once you have connected to the database server, a window similar to the one shown in the following screenshot will be displayed, where you can see a general view, plus information about connections, overall activity, and running transactions: