Book Image

PostgreSQL 13 Cookbook

By : Vallarapu Naga Avinash Kumar

Book Image

PostgreSQL 13 Cookbook

By: Vallarapu Naga Avinash Kumar

Overview of this book

PostgreSQL has become the most advanced open source database on the market. This book follows a step-by-step approach, guiding you effectively in deploying PostgreSQL in production environments. The book starts with an introduction to PostgreSQL and its architecture. You’ll cover common and not-so-common challenges faced while designing and managing the database. Next, the book focuses on backup and recovery strategies to ensure your database is steady and achieves optimal performance. Throughout the book, you’ll address key challenges such as maintaining reliability, data integrity, a fault-tolerant environment, a robust feature set, extensibility, consistency, and authentication. Moving ahead, you’ll learn how to manage a PostgreSQL cluster and explore replication features for high availability. Later chapters will assist you in building a secure PostgreSQL server, along with covering recipes for encrypting data in motion and data at rest. Finally, you’ll not only discover how to tune your database for optimal performance but also understand ways to monitor and manage maintenance activities, before learning how to perform PostgreSQL upgrades during downtime. By the end of this book, you’ll be well-versed with the essential PostgreSQL 13 features to build enterprise relational databases.

Preface

Who this book is for

What this book covers

To get the most out of this book

Cluster Management Fundamentals

Cluster Management Fundamentals

Technical requirements

Installing PostgreSQL 13 using RPMs on CentOS

Initializing a PostgreSQL cluster using initdb

Starting a PostgreSQL cluster using pg_ctl

Shutting down a PostgreSQL cluster using different shutdown modes

Identifying a PostgreSQL data directory and its contents

Moving pg_wal to another location

Running the psql client and some psql shortcuts

Locating the Postgres configuration file

Modifying the location of a postgresql.conf file in PostgreSQL

Modifying the postgresql.auto.conf file in PostgreSQL

Enable archiving in PostgreSQL

Free Chapter

Cluster Management Techniques

Cluster Management Techniques

Technical requirements

Creating and dropping databases

Locating a database and a table on the file system

Creating a schema in PostgreSQL

Checking table and index sizes in PostgreSQL

Creating tablespaces

Moving tables to a different tablespace

Creating a user in PostgreSQL

Dropping a user in PostgreSQL

Assigning and revoking a privilege to/from a user or a role

Creating a group role for role-based segregation

MVCC implementation and VACUUM in PostgreSQL

Backup and Recovery

Backup and Recovery

Technical requirements

Backing up and restoring a database using pg_dump and pg_restore

Backing up and restoring one or more tables using pg_dump and pg_restore

Backing up and restoring globals or an entire cluster using pg_dumpall and psql

Parallel backup and restore using pg_dump and pg_restore

Backing up a database cluster using pg_basebackup

Restoring a backup taken using pg basebackup

Installing pgBackRest on CentOS/RedHat OS

Installing pgBackRest on Ubuntu/Debian OS

Backing up a database cluster using pgBackRest

Restoring a backup taken using pgBackRest

Advanced Replication Techniques

Advanced Replication Techniques

Setting up streaming replication in PostgreSQL 13

Adding a delayed standby for faster point-in-time recovery

Promoting a standby to a master

Adding a cascaded streaming replica

Promoting a standby in a replication cluster with multiple standby servers

Using pg_rewind to re-synchronize a demoted master

Enabling synchronous streaming replication

Setting up logical replication in PostgreSQL 13

High Availability and Automatic Failover

High Availability and Automatic Failover

Technical requirements

Automatic failover using Patroni

Enabling distributed consensus using etcd

Avoiding split-brain using Watchdog/softdog

Installing Patroni along with its Python dependencies

Creating a Patroni configuration file

Starting Patroni as a service using systemd

Initializing a PostgreSQL primary database using Patroni

Adding a standby to a Patroni cluster

Performing a manual switchover using Patroni

Connection Pooling and Load Balancing

Connection Pooling and Load Balancing

Technical requirements

Installing pgBouncer on a Linux server

Creating a pgBouncer configuration file

Configuring the pool settings on pgBouncer

Starting and stopping the pgBouncer service

Installing HAProxy on Linux servers

Using xinetd to detect a primary or a standby

Creating an HAProxy configuration file

Starting and stopping the HAProxy service

Building a robust HA cluster using Patroni, pgBouncer, and HAProxy

Securing through Authentication

Securing through Authentication

Technical requirements

Securing client connections using the pg_hba.conf file

Performing authorization using roles and privileges

Setting up row-level security

Configuring encryption of data over the wire using SSL

Enabling certificate authentication using SSL

Auditing PostgreSQL through logging

Auditing PostgreSQL using pgaudit

Setting up object-level auditing using pgaudit

Logging and Analyzing PostgreSQL Servers

Logging and Analyzing PostgreSQL Servers

Technical requirements

Setting up slow query logging in PostgreSQL

Logging runtime execution plans in PostgreSQL using auto_explain

Logging locks, waits, and temp in PostgreSQL

Logging autovacuum and analyzing activity in PostgreSQL

Generating a pgBadger report

Configuring pg_stat_statements as an extension

Query analysis using pg_stat_statements

Getting the kernel-level statistics of a query using pg_stat_kcache

Critical Services Monitoring

Critical Services Monitoring

Technical requirements

Installation of Grafana and its dependencies

Prometheus as a data source on the monitoring server

Configuring Node Exporter on Postgres servers to monitor operating system metrics

Adding metrics being collected using node_exporter to Prometheus

Collecting PostgreSQL metrics using postgres_exporter

Adding metrics exposed by postgres_exporter to Prometheus

Importing a dashboard for monitoring Linux metrics

How to import a dashboard for monitoring Postgres metrics

Adding custom queries to postgres_exporter

Extensions and Performance Tuning

Extensions and Performance Tuning

Technical requirements

Installing and creating pg_repack to rebuild objects online

How to rebuild a table online using pg_repack

How to rebuild indexes of a table online using pg_repack

Moving a table or an index to another tablespace online

Warming up the cache using pg_prewarm

How to tune a function or a stored procedure using plprofiler

Capturing statements that require tuning using pg_stat_statements

Viewing the execution plans using EXPLAIN in PostgreSQL

Upgrades and Patches

Upgrades and Patches

Technical requirements

Finding the difference between a major and minor release in PostgreSQL

Technical requirements

Major version upgrade to PostgreSQL 13 using pg_dumpall

Major version upgrade to PostgreSQL 13 using pg_dump and pg_restore

Major version upgrade to PostgreSQL 13 using pg_upgrade with downtime

Major version upgrade to PostgreSQL 13 using pg_upgrade with hard links for seamless downtime

Installing the pglogical extension to upgrade older versions to PostgreSQL 13

Upgrading to PostgreSQL 13 using the pglogical extension

Upgrading to PostgreSQL 13 using logical replication and logical decoding

Updating the minor version of PostgreSQL 13

About Packt

Other Books You May Enjoy

Other Books You May Enjoy

Packt is searching for authors like you

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Query analysis using pg_stat_statements

The pg_stat_statements extension exposes a lot of query statistics that will be helpful in finding slow queries that need to be optimized. In this recipe, we shall see some example queries to view some information from pg_stat_statements.

Getting ready

In order to run the views or queries discussed in this recipe, we should have pg_stat_statements successfully configured and created as an extension. Please note that every query for which the statistics are collected by pg_stat_statements can be uniquely identified through queryid. A hash is generated based on the query text and is visible as queryid when we query the pg_stat_statements view.

How to do it...

In the following commands, we have used a function to trim the query up to just 40 characters for better visibility. We may use an expanded display using \x and avoid truncating the query when viewing the statistics:

Find the queries that are writing to temp the most:

select queryid, left(query...