PostgreSQL 16 Administration Cookbook

By : Gianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs

5 (1)

Buy this Book

PostgreSQL 16 Administration Cookbook

5 (1)

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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Free Chapter

First Steps

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

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

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

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

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

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

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

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

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

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

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

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

Other Books You May Enjoy

Index

Customer Reviews

5 (1)

5 star

100%

4 star

3 star

2 star

1 star

Knowing whether anybody is using a specific table

This recipe will help you when you are in doubt about whether an obscure table is being used anymore, or if it has been left over from past use and is just taking up space.

Getting ready

Make sure that you are a superuser, or at least have full rights to the table in question.

How to do it…

Perform the following steps:

To see whether a table is currently in active use (that is, whether anyone is using it while you are watching it), run the following query on the database you plan to inspect:
```
CREATE TEMPORARY TABLE tmp_stat_user_tables AS
       SELECT * FROM pg_stat_user_tables;
```

Then, wait for a while and see what has changed:

SELECT * FROM pg_stat_user_tables n
  JOIN tmp_stat_user_tables t
    ON n.relid=t.relid
   AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
    <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

...

PostgreSQL 16 Administration Cookbook

By : Gianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs

PostgreSQL 16 Administration Cookbook

By: Gianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs

Overview of this book

Related Content you might be interested in

Current Title:

PostgreSQL 16 Administration Cookbook

Learn PostgreSQL

PostgreSQL 10 High Performance

Learning PostgreSQL 11

Knowing whether anybody is using a specific table

Getting ready

How to do it…