Sign In Start Free Trial

PostgreSQL 14 Administration Cookbook

By : Simon Riggs, GIANNI CIOLLI

4.3 (15)

PostgreSQL 14 Administration Cookbook

4.3 (15)

By: Simon Riggs, GIANNI CIOLLI

Overview of this book

PostgreSQL is a powerful, open-source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 14 allows you to scale up your PostgreSQL infrastructure. With this book, you'll take a step-by-step, recipe-based approach to effective PostgreSQL administration. This book will get you up and running with all the latest features of PostgreSQL 14 while helping you explore the entire database ecosystem. You’ll learn how to tackle a variety of problems and pain points you may face as a database administrator such as creating tables, managing views, improving performance, and securing your database. As you make progress, the book will draw attention to important topics such as monitoring roles, validating backups, regular maintenance, and recovery of your PostgreSQL 14 database. This will help you understand roles, ensuring high availability, concurrency, and replication. Along with updated recipes, this book touches upon important areas like using generated columns, TOAST compression, PostgreSQL on the cloud, and much more. By the end of this PostgreSQL book, you’ll have gained the knowledge you need to manage your PostgreSQL 14 database efficiently, both in the cloud and on-premise.

Preface

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the color images

Conventions used

Sections

Get in touch

Reviews

Share Your Thoughts

Chapter 1: First Steps

Chapter 1: First Steps

Introducing PostgreSQL 14

How to get PostgreSQL

Connecting to the PostgreSQL server

Enabling access for network/remote users

Using the pgAdmin4 GUI tool

Using the OmniDB 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

Free Chapter

Chapter 2: Exploring the Database

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

Chapter 3: Server Configuration

Chapter 3: Server Configuration

RTFM

Planning a new database

Setting configuration parameters for the database server

Setting 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

Chapter 4: Server Control

Chapter 4: Server Control

Overview of controlling the database server

Starting the database server manually

Stopping the server safely and quickly

Stopping the server in an emergency

Reloading the 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

Chapter 5: Tables and Data

Chapter 5: Tables and Data

Choosing good names for database objects

Handling objects with quoted names

Enforcing the same name and definition for columns

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

Chapter 6: Security

Chapter 6: Security

Overview of PostgreSQL security

The PostgreSQL superuser

Revoking user access to a table

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

Chapter 7: Database Administration

Chapter 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

Chapter 8: Monitoring and Diagnosis

Chapter 8: Monitoring and Diagnosis

Overview of PostgreSQL monitoring

Cloud-native monitoring

Providing PostgreSQL information to monitoring tools

Real-time viewing using pgAdmin

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

Detecting an in-doubt prepared transaction

Knowing whether anybody is using a specific table

Knowing when a table was last used

Usage of disk space by temporary data

Understanding why queries slow down

Analyzing the real-time performance of your queries

Investigating and reporting a bug

Chapter 9: Regular Maintenance

Chapter 9: Regular Maintenance

Controlling automatic database maintenance

Avoiding auto-freezing and page corruptions

Removing issues that cause bloat

Removing old prepared transactions

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

Chapter 10: Performance and Concurrency

Chapter 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

Creating time-series tables using partitioning

Using optimistic locking to avoid long lock waits

Reporting performance problems

Chapter 11: Backup and Recovery

Chapter 11: Backup and Recovery

Understanding and controlling crash recovery

Planning your backups

Hot logical backups of one database

Hot logical backups of all databases

Backups 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 performance of logical backup/recovery

Improving performance of physical backup/recovery

Validating backups

Chapter 12: Replication and Upgrades

Chapter 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

Monitoring replication

Performance and sync rep

Delaying, pausing, and synchronizing replication

Logical replication

BDR

Archiving transaction log data

Upgrading minor releases

Major upgrades in-place

Major upgrades online

Why subscribe?

Other Books You May Enjoy

Other Books You May Enjoy

Packt is searching for authors like you

Share Your Thoughts

Customer Reviews

4.3 (15)

5 star

60%

4 star

26.7%

3 star

6.7%

2 star

0

1 star

6.7%

Which are my biggest tables?

We've looked at getting the size of a specific table, so now it's time to widen the problem to related areas. Rather than having an absolute value for a specific table, let's look at the relative sizes.

How to do it...

The following basic query will tell us the 10 biggest tables:

SELECT quote_ident(table_schema)||'.'||quote_ident(table_name) as name 
      ,pg_relation_size(quote_ident(table_schema)
              || '.' || quote_ident(table_name)) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

The tables are shown in descending order of size, with at the most 10 rows displayed. In this case, we look at all the tables in all the schemas, apart from the tables in information_schema or pg_catalog, as we did in the How many tables are in the database? recipe.

How it works…

PostgreSQL provides a dedicated function, pg_relation_size, to compute the actual disk space used by a specific table or index. We just need to provide the table name. In addition to the main data files, there are other files (called forks) that can be measured by specifying an optional second argument. These include the Visibility Map (VM), the Free Space Map (FSM), and the initialization fork for unlogged objects.

Search

Your notes and bookmarks