Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL 16 Administration Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
PostgreSQL 16 Administration Cookbook

PostgreSQL 16 Administration Cookbook

By : GIANNI CIOLLI, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs
4.8 (27)
Buy this Book
close
close
PostgreSQL 16 Administration Cookbook

PostgreSQL 16 Administration Cookbook

4.8 (27)
By: GIANNI CIOLLI, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs
Buy this Book

Overview of this book

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.
Table of Contents (15 chapters)
close
close
close
Preface
Icon
Preface
Icon
Who this book is for
Icon
What this book covers
Icon
To get the most out of this book
Icon
Get in touch
Free Chapter
1
First Steps
Icon
First Steps
Icon
Introducing PostgreSQL 16
Icon
How to get PostgreSQL
Icon
Connecting to the PostgreSQL server
Icon
Enabling access for network/remote users
Icon
Using the pgAdmin 4 GUI tool
Icon
Using the psql query and scripting tool
Icon
Changing your password securely
Icon
Avoiding hardcoding your password
Icon
Using a connection service file
Icon
Troubleshooting a failed connection
Icon
PostgreSQL in the cloud
Icon
PostgreSQL with Kubernetes
Icon
PostgreSQL with TPA
2
Exploring the Database
Icon
Exploring the Database
Icon
What type of server is this?
Icon
What version is the server?
Icon
What is the server uptime?
Icon
Locating the database server files
Icon
Locating the database server’s message log
Icon
Locating the database’s system identifier
Icon
Listing databases on the database server
Icon
How many tables are there in a database?
Icon
How much disk space does a database use?
Icon
How much memory does a database currently use?
Icon
How much disk space does a table use?
Icon
Which are my biggest tables?
Icon
How many rows are there in a table?
Icon
Quickly estimating the number of rows in a table
Icon
Listing extensions in this database
Icon
Understanding object dependencies
3
Server Configuration
Icon
Server Configuration
Icon
Read the fine manual (RTFM)
Icon
Planning a new database
Icon
Setting the configuration parameters for the database server
Icon
Setting the configuration parameters in your programs
Icon
Finding the configuration settings for your session
Icon
Finding parameters with non-default settings
Icon
Setting parameters for particular groups of users
Icon
A basic server configuration checklist
Icon
Adding an external module to PostgreSQL
Icon
Using an installed module/extension
Icon
Managing installed extensions
4
Server Control
Icon
Server Control
Icon
An overview of controlling the database server
Icon
Starting the database server manually
Icon
Stopping the server safely and quickly
Icon
Stopping the server in an emergency
Icon
Reloading server configuration files
Icon
Restarting the server quickly
Icon
Preventing new connections
Icon
Restricting users to only one session each
Icon
Pushing users off the system
Icon
Deciding on a design for multitenancy
Icon
Using multiple schemas
Icon
Giving users their own private databases
Icon
Running multiple servers on one system
Icon
Setting up a connection pool
Icon
Accessing multiple servers using the same host and port
Icon
Running multiple PgBouncer on the same port to leverage multiple cores
5
Tables and Data
Icon
Tables and Data
Icon
Choosing good names for database objects
Icon
Handling objects with quoted names
Icon
Identifying and removing duplicates
Icon
Preventing duplicate rows
Icon
Finding a unique key for a set of data
Icon
Generating test data
Icon
Randomly sampling data
Icon
Loading data from a spreadsheet
Icon
Loading data from flat files
Icon
Making bulk data changes using server-side procedures with transactions
Icon
Dealing with large tables with table partitioning
Icon
Finding good candidates for partition keys
Icon
Consolidating data with MERGE
Icon
Deciding when to use JSON data types
6
Security
Icon
Security
Icon
An overview of PostgreSQL security
Icon
The PostgreSQL superuser
Icon
Revoking user access to tables
Icon
Granting user access to a table
Icon
Granting user access to specific columns
Icon
Granting user access to specific rows
Icon
Creating a new user
Icon
Temporarily preventing a user from connecting
Icon
Removing a user without dropping their data
Icon
Checking whether all users have a secure password
Icon
Giving limited superuser powers to specific users
Icon
Auditing database access
Icon
Always knowing which user is logged in
Icon
Integrating with LDAP
Icon
Connecting using encryption (SSL / GSSAPI)
Icon
Using SSL certificates to authenticate
Icon
Mapping external usernames to database roles
Icon
Using column-level encryption
Icon
Setting up cloud security using predefined roles
7
Database Administration
Icon
Database Administration
Icon
Writing a script that either succeeds entirely or fails entirely
Icon
Writing a psql script that exits on the first error
Icon
Using psql variables
Icon
Placing query output into psql variables
Icon
Writing a conditional psql script
Icon
Investigating a psql error
Icon
Setting the psql prompt with useful information
Icon
Using pgAdmin for DBA tasks
Icon
Scheduling jobs for regular background execution
Icon
Performing actions on many tables
Icon
Adding/removing columns on a table
Icon
Changing the data type of a column
Icon
Changing the definition of an enum data type
Icon
Adding a constraint concurrently
Icon
Adding/removing schemas
Icon
Moving objects between schemas
Icon
Adding/removing tablespaces
Icon
Moving objects between tablespaces
Icon
Accessing objects in other PostgreSQL databases
Icon
Accessing objects in other foreign databases
Icon
Making views updatable
Icon
Using materialized views
Icon
Using GENERATED data columns
Icon
Using data compression
8
Monitoring and Diagnosis
Icon
Monitoring and Diagnosis
Icon
Cloud-native monitoring
Icon
Providing PostgreSQL information to monitoring tools
Icon
Real-time viewing using pgAdmin
Icon
Monitoring the PostgreSQL message log
Icon
Checking whether a user is connected
Icon
Checking whether a computer is connected
Icon
Repeatedly executing a query in psql
Icon
Checking which queries are running
Icon
Monitoring the progress of commands
Icon
Checking which queries are active or blocked
Icon
Knowing who is blocking a query
Icon
Killing a specific session
Icon
Knowing whether anybody is using a specific table
Icon
Knowing when a table was last used
Icon
Monitoring I/O statistics
Icon
Usage of disk space by temporary data
Icon
Understanding why queries slow down
Icon
Analyzing the real-time performance of your queries
Icon
Tracking important metrics over time
9
Regular Maintenance
chevron up
Icon
Regular Maintenance
Icon
Controlling automatic database maintenance
Icon
Avoiding auto-freezing
Icon
Removing issues that cause bloat
Icon
Actions for heavy users of temporary tables
Icon
Identifying and fixing bloated tables and indexes
Icon
Monitoring and tuning a vacuum
Icon
Maintaining indexes
Icon
Finding unused indexes
Icon
Carefully removing unwanted indexes
Icon
Planning maintenance
10
Performance and Concurrency
Icon
Performance and Concurrency
Icon
Finding slow SQL statements
Icon
Finding out what makes SQL slow
Icon
Reducing the number of rows returned
Icon
Simplifying complex SQL queries
Icon
Speeding up queries without rewriting them
Icon
Discovering why a query is not using an index
Icon
Forcing a query to use an index
Icon
Using parallel query
Icon
Using Just-In-Time (JIT) compilation
Icon
Creating time-series tables using partitioning
Icon
Using optimistic locking to avoid long lock waits
Icon
Reporting performance problems
11
Backup and Recovery
Icon
Backup and Recovery
Icon
Understanding and controlling crash recovery
Icon
Planning your backups
Icon
Hot logical backup of one database
Icon
Hot logical backup of all databases
Icon
Backup of database object definitions
Icon
A standalone hot physical backup
Icon
Hot physical backups with Barman
Icon
Recovery of all databases
Icon
Recovery to a point in time
Icon
Recovery of a dropped/damaged table
Icon
Recovery of a dropped/damaged database
Icon
Extracting a logical backup from a physical one
Icon
Improving the performance of logical backup/recovery
Icon
Improving the performance of physical backup/recovery
Icon
Validating backups
12
Replication and Upgrades
Icon
Replication and Upgrades
Icon
Replication concepts
Icon
Replication best practices
Icon
Setting up streaming replication
Icon
Setting up streaming replication security
Icon
Hot Standby and read scalability
Icon
Managing streaming replication
Icon
Using repmgr
Icon
Using replication slots
Icon
Setting up replication with TPA
Icon
Setting up replication with CloudNativePG
Icon
Monitoring replication
Icon
Performance and synchronous replication (sync rep)
Icon
Delaying, pausing, and synchronizing replication
Icon
Logical replication
Icon
EDB Postgres Distributed
Icon
Archiving transaction log data
Icon
Upgrading minor releases
Icon
Major upgrades in-place
Icon
Major upgrades online
13
Other Books You May Enjoy
Icon
Other Books You May Enjoy
14
Index
Icon
Index

Removing issues that cause bloat

Bloat is disk space previously used by tables that is available for reuse by the database but no longer by the filesystem. It can be caused by long-running queries or long-running write transactions that execute alongside write-heavy workloads. Resolving that is mostly down to understanding the workloads that are running on the server.

Getting ready

Look at the age of the oldest snapshots that are running, like this:

postgres=# SELECT now() -
  CASE
  WHEN backend_xid IS NOT NULL
  THEN xact_start
  ELSE query_start END
  AS age
, pid
, backend_xid AS xid
, backend_xmin AS xmin
, state
FROM  pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY 1 DESC;
age             |  pid  |   xid    |   xmin   |        state       
----------------+-------+----------+----------+------------------
00:00:25.791098 | 27624 |          | 10671262 | active
00:00:08.018103 | 27591 |          |          | idle in transaction
00:00:00.002444...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 4
Next Section

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY