PostgreSQLl 13 cookbook : over 120 recipes to build high-performance and fault-tolerant postgresql database solutions / Vallarapu Naga Avinash Kumar.

Format:

Book

Author/Creator:

Kumar, Vallarapu, author.

Language:

English

Subjects (All):

PostgreSQL.

SQL (Computer program language).

Database management.

Databases--Design.

Databases.

Physical Description:

1 online resource (344 pages) : illustrations

Place of Publication:

Birmingham ; Mumbai : Packt Publishing, 2021.

Summary:

PostgreSQL has become the most advanced open source database on the market. This book adopts a step-by-step approach to meet almost every requirement you can think of while deploying PostgreSQL in production environments. You will not only learn how to design and manage your database but also discover how to administer and secure the database.

Contents:

Intro

Title Page

Cover

Copyright and Credits

Contributors

Table of Contents

Preface

Chapter 1: Cluster Management Fundamentals

Technical requirements

Installing PostgreSQL 13 using RPMs on CentOS

Getting ready

How to do it...

How it works...

Initializing a PostgreSQL cluster using initdb

Starting a PostgreSQL cluster using pg_ctl

Clusters in PostgreSQL

Databases in PostgreSQL

There's more...

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

Running a SQL server using psql

Getting a list of databases

Finding the database's size

Connecting to a database

Getting the list of schemas in a database

Getting the list of tables

Describing a table

SQLs behind the shortcuts

Locating the Postgres configuration file

include_dir

include

Modifying the location of a postgresql.conf file in PostgreSQL

Modifying the postgresql.auto.conf file in PostgreSQL

Enable archiving in PostgreSQL

Chapter 2: Cluster Management Techniques

Technical requirements.

Creating and dropping databases

How it works

There's more

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

How to do it ...

How it works ...

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

How to do it

Creating a group role for role-based segregation

MVCC implementation and VACUUM in PostgreSQL

tableoid

xmin

xmax

ctid

pageinspect

Chapter 3: Backup and Recovery

Backing up and restoring a database using pg_dump and pg_restore

The RPM package for CentOS/Red Hat distributions

Debian and Ubuntu

Section A

Section B

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

How it works.

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

Chapter 4: Advanced Replication Techniques

Setting up streaming replication in PostgreSQL 13

Getting ready...

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

Chapter 5: High Availability and Automatic Failover

Automatic failover using Patroni

Enabling distributed consensus using etcd

Avoiding split-brain using Watchdog/softdog

How it works...

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

Chapter 6: Connection Pooling and Load Balancing

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

Chapter 7: Securing through Authentication

Securing client connections using the pg_hba.conf file

Categories in the pg_hba.conf file

How to do it...

How it works...

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

Chapter 8: Logging and Analyzing PostgreSQL Servers

Setting up slow query logging in PostgreSQL

Logging runtime execution plans in PostgreSQL using auto_explain

Global level

Session level

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

Chapter 9: Critical Services Monitoring

Installation of Grafana and its dependencies

How it works...

Notes:

Includes index.

Description based on print version record.

ISBN:

9781838641054

183864105X

OCLC:

1241448083