PostgreSQL 13 Cookbook E-Book

Vallarapu Naga Avinash Kumar

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 403

Veröffentlichungsjahr: 2021

Beschreibung

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.

Leseprobe

PostgreSQL 13 Cookbook

Over 120 recipes to build high-performance and fault-tolerant PostgreSQL database solutions

Vallarapu Naga Avinash Kumar

BIRMINGHAM - MUMBAI

PostgreSQL 13 Cookbook

Copyright © 2021 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kunal ParikhPublishing Product Manager: Mrinmayee KawalkarSenior Editor: Roshan KumarContent Development Editor:Joseph SunilTechnical Editor:Arjun VarmaCopy Editor:Safis EditingProject Coordinator: Aishwarya MohanProofreader: Safis EditingIndexer:Tejal SoniProduction Designer: Aparna Bhagat

First published: February 2021

Production reference: 1250221

Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.

ISBN 978-1-83864-813-8

www.packt.com

Contributors

About the author

Vallarapu Naga Avinash Kumar (Avi) is the CEO and co-founder of MigOps Inc, a company focused on migrations to open source databases such as PostgreSQL. Before co-founding MigOps, Avi worked as a PostgreSQL tech lead for global services at Percona, as a database architect at OpenSCG for 2 years, and as a DBA lead at Dell for 10 years in database technologies such as PostgreSQL, Oracle, MySQL, and MongoDB. He has given several talks and training on PostgreSQL. He has good experience in performing architectural health checks, performance tuning, and migrations to PostgreSQL environments. He has also co-authored a book – Beginning PostgreSQL on the Cloud – which gives an introduction to building PostgreSQL on the AWS, Microsoft Azure, Google Cloud, and Rackspace cloud platforms. He has been a speaker at several PostgreSQL conferences in North America, Europe, and Asia for many years and continues to blog about PostgreSQL.

About the reviewers

Marcelo Diaz is a software engineer with more than 15 years of experience, and with a special focus on PostgreSQL. He is passionate about open source software and has promoted its application in critical and high-demand environments where he has worked as a software developer and consultant for both private and public companies. He currently works very happily at Cybertec and as a technical reviewer for Packt Publishing. He enjoys spending his leisure time with his daughter, Malvina, and his wife, Romina. He also likes playing football.

Sandeep Purbiya has a bachelor’s degree in the field of computer science from Shivaji University, India. He has over two decades of experience in software product design, development, and delivery. He has worked with start-ups as well as large software product organizations. He is a full stack developer and has designed and developed applications using Java, JDBC, JavaScript, microservices, and databases such as Oracle, MySQL, MSSQL, and Postgres. In his free time, he likes to read about new tools and technologies, build prototypes to gain knowledge, and experience and enjoy life with family and friends.

Table of Contents

Title Page

Copyright and Credits

PostgreSQL 13 Cookbook

Contributors

About the author

About the reviewers

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

Getting ready

How to do it…

How it works…

There's more…

See also

Get in touch

Reviews

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

Getting ready 

How to do it...

How it works...

Starting a PostgreSQL cluster using pg_ctl 

Getting ready

How to do it...

How it works...

Clusters in PostgreSQL

Databases in PostgreSQL

There's more...

Shutting down a PostgreSQL cluster using different shutdown modes

Getting ready

How to do it...

How it works...

There's more...

Identifying a PostgreSQL data directory and its contents

Getting ready

How to do it...

How it works...

There's more...

Moving pg_wal to another location

Getting ready

How to do it...

How it works...

Running the psql client and some psql shortcuts

Getting ready

How to do it...

How it works...

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

There's more...

SQLs behind the shortcuts

Locating the Postgres configuration file

Getting ready

How to do it...

How it works...

There's more...

include_dir

include

Modifying the location of a postgresql.conf file in PostgreSQL

Getting ready

How to do it...

How it works...

Modifying the postgresql.auto.conf file in PostgreSQL

Getting ready

How to do it...

How it works...

There's more...

Enable archiving in PostgreSQL

Getting ready

How to do it...

How it works...

There's more...

Cluster Management Techniques

Technical requirements

Creating and dropping databases

Getting ready

How to do it...

How it works

There's more

Locating a database and a table on the file system

Getting ready

How to do it...

How it works...

Creating a schema in PostgreSQL

Getting ready

How to do it...

How it works...

There's more...

Checking table and index sizes in PostgreSQL

Getting ready

How to do it...

How it works...

There's more...

Creating tablespaces

Getting ready

How to do it...

How it works

There's more...

Moving tables to a different tablespace

Getting ready

How to do it...

How it works

Creating a user in PostgreSQL

Getting ready

How to do it...

How it works

There's more...

Dropping a user in PostgreSQL

Getting ready

How to do it ...

How it works ...

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

Getting ready

How to do it

How it works

Creating a group role for role-based segregation 

Getting ready

How to do it

How it works

MVCC implementation and VACUUM in PostgreSQL

Getting ready

How to do it...

How it works...

tableoid

xmin

xmax

ctid

pageinspect

There's more...

Backup and Recovery

Technical requirements

Backing up and restoring a database using pg_dump and pg_restore

Getting ready 

The RPM package for CentOS/Red Hat distributions

Debian and Ubuntu

How to do it

Section A

Section B

How it works 

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

Getting ready

How to do it

How it works

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

Getting ready

How to do it

How it works...

Parallel backup and restore using pg_dump and pg_restore

Getting ready

How to do it

How it works

Backing up a database cluster using pg_basebackup

Getting ready

How to do it

How it works...

Restoring a backup taken using pg basebackup

Getting ready

How to do it

How it works

Installing pgBackRest on CentOS/RedHat OS

Getting ready

How to do it

How it works

Installing pgBackRest on Ubuntu/Debian OS

Getting ready

How to do it

How it works

Backing up a database cluster using pgBackRest

Getting ready

How to do it

How it works

Restoring a backup taken using pgBackRest

Getting ready

How to do it

How it works

Advanced Replication Techniques

Setting up streaming replication in PostgreSQL 13

Getting ready...

How to do it ...

How it works ...

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

Getting ready...

How to do it...

How it works...

Promoting a standby to a master

Getting ready...

How to do it...

How it works...

Adding a cascaded streaming replica

Getting ready...

How to do it ...

How it works...

Promoting a standby in a replication cluster with multiple standby servers

Getting ready...

How to do it...

How it works...

Using pg_rewind to re-synchronize a demoted master

Getting ready...

How to do it...

How it works...

Enabling synchronous streaming replication

Getting ready...

How to do it...

How it works...

Setting up logical replication in PostgreSQL 13

Getting ready...

How to do it...

How it works...

High Availability and Automatic Failover

Technical requirements

Automatic failover using Patroni

Enabling distributed consensus using etcd

Getting ready

How to do it...

How it works...

Avoiding split-brain using Watchdog/softdog

Getting ready

How to do it...

How it works...

Installing Patroni along with its Python dependencies

Getting ready...

How to do it...

How it works...

Creating a Patroni configuration file

Getting ready...

How to do it...

How it works...

Starting Patroni as a service using systemd

Getting ready...

How to do it...

How it works...

Initializing a PostgreSQL primary database using Patroni

Getting ready...

How to do it...

How it works...

Adding a standby to a Patroni cluster

Getting ready...

How to do it...

How it works...

Performing a manual switchover using Patroni

Getting ready...

How to do it...

How it works...

Connection Pooling and Load Balancing

Technical requirements

Installing pgBouncer on a Linux server

Getting ready...

How to do it...

How it works...

Creating a pgBouncer configuration file

Getting ready...

How to do it...

How it works...

Configuring the pool settings on pgBouncer

Getting ready...

How to do it...

How it works...

Starting and stopping the pgBouncer service

Getting ready...

How to do it...

How it works...

Installing HAProxy on Linux servers

Getting ready...

How to do it...

How it works...

Using xinetd to detect a primary or a standby

Getting ready...

How to do it...

How it works...

Creating an HAProxy configuration file

Getting ready...

How to do it...

How it works...

Starting and stopping the HAProxy service

Getting ready...

How to do it...

How it works...

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

Getting ready...

How to do it...

How it works...

Securing through Authentication

Technical requirements

Securing client connections using the pg_hba.conf file

Categories in the pg_hba.conf file

Getting ready

How to do it...

How it works...

Performing authorization using roles and privileges

Getting ready

How to do it...

How it works...

Setting up row-level security

Getting ready

How to do it...

How it works...

Configuring encryption of data over the wire using SSL

Getting ready

How to do it...

How it works...

Enabling certificate authentication using SSL

Getting ready

How to do it...

How it works...

Auditing PostgreSQL through logging

Getting ready

How to do it...

How it works...

Auditing PostgreSQL using pgaudit

Getting ready

How to do it...

How it works...

Setting up object-level auditing using pgaudit

Getting ready

How to do it...

How it works...

Logging and Analyzing PostgreSQL Servers

Technical requirements

Setting up slow query logging in PostgreSQL

Getting ready

How to do it...

How it works...

There's more...

Logging runtime execution plans in PostgreSQL using auto_explain

Getting ready

How to do it...

Global level

Session level

How it works...

Logging locks, waits, and temp in PostgreSQL

Getting ready

How to do it...

How it works...

Logging autovacuum and analyzing activity in PostgreSQL

Getting ready

How to do it...

How it works...

Generating a pgBadger report

Getting ready

How to do it...

How it works...

Configuring pg_stat_statements as an extension

Getting ready

How to do it...

How it works...

Query analysis using pg_stat_statements

Getting ready

How to do it...

How it works...

Getting the kernel-level statistics of a query using pg_stat_kcache

Getting ready

How to do it...

How it works...

Critical Services Monitoring

Technical requirements

Installation of Grafana and its dependencies

Getting ready

How to do it...

How it works... 

Prometheus as a data source on the monitoring server

Getting ready

How to do it...

How it works...

Configuring Node Exporter on Postgres servers to monitor operating system metrics

Getting ready

How to do it...

How it works...

Adding metrics being collected using node_exporter to Prometheus

Getting ready

How to do it...

How it works...

Collecting PostgreSQL metrics using postgres_exporter

Getting ready

How to do it...

How it works...

Adding metrics exposed by postgres_exporter to Prometheus

Getting ready

How to do it...

How it works...

Importing a dashboard for monitoring Linux metrics

Getting ready

How to do it...

How it works...

How to import a dashboard for monitoring Postgres metrics

Getting ready

How to do it...

How it works...

Adding custom queries to postgres_exporter

Getting ready

How to do it...

How it works...

Extensions and Performance Tuning

Technical requirements

Installing and creating pg_repack to rebuild objects online

Getting ready

How to do it...

Installing pg_repack on CentOS

Installing pg_repack on Ubuntu

How it works...

How to rebuild a table online using pg_repack

Getting ready

How to do it...

How it works...

How to rebuild indexes of a table online using pg_repack

Getting ready

How to do it...

Rebuilding all the indexes of a table

Rebuilding a specific index

How it works...

Moving a table or an index to another tablespace online

Getting ready

How to do it...

How it works...

Warming up the cache using pg_prewarm

Getting ready

How to do it...

How it works...

How to tune a function or a stored procedure using plprofiler

Getting ready

How to do it...

How it works...

Capturing statements that require tuning using pg_stat_statements

Getting ready

How to do it...

How it works...

Viewing the execution plans using EXPLAIN in PostgreSQL

Getting ready

How to do it...

How it works...

Upgrades and Patches

Technical requirements

Finding the difference between a major and minor release in PostgreSQL

What is an obsolete version?

Technical requirements

Major version upgrade to PostgreSQL 13 using pg_dumpall

Getting ready

How to do it...

How it works...

Major version upgrade to PostgreSQL 13 using pg_dump and pg_restore

Getting ready

How to do it...

How it works...

Major version upgrade to PostgreSQL 13 using pg_upgrade with downtime

Getting ready

How to do it...

How it works...

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

Getting ready

How to do it...

How it works...

Installing the pglogical extension to upgrade older versions to PostgreSQL 13

Getting ready

How to do it...

How it works...

Upgrading to PostgreSQL 13 using the pglogical extension

Getting ready

How to do it...

How it works...

Upgrading to PostgreSQL 13 using logical replication and logical decoding

Getting ready

How to do it...

How it works...

Updating the minor version of PostgreSQL 13

Getting ready

How to do it...

How it works...

About Packt

Why subscribe?

Other Books You May Enjoy

Packt is searching for authors like you

Leave a review - let other readers know what you think

Preface

PostgreSQL has become the most advanced open source database on the market. This book follows a step-by-step approach to guide 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 a database. Next, the book focuses on backup and recovery strategies to ensure your database is steady and achieves optimal performance. Throughout the book, we'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 in the essential PostgreSQL 13 features that are needed to build enterprise relational databases.

Who this book is for

This PostgreSQL book is for database architects, database developers, and administrators, or anyone who wants to become well-versed in PostgreSQL 13 features in order to plan, manage, and design efficient database solutions. Prior experience with the PostgreSQL database and SQL language is expected.

What this book covers

Chapter 1, Cluster Management Fundamentals, starts by showing how to install PostgreSQL 13 and initializing a data directory. We will then discuss pg_ctl and how it can be used to start and stop a PostgreSQL cluster.

Chapter 2, Cluster Management Techniques, helps you get familiar with some of the important routine tasks in PostgreSQL.

Chapter 3, Backup and Recovery, shows you several methods available to back up and restore a PostgreSQL database.

Chapter 4, Advanced Replication Techniques, teaches you how to configure both streaming and logical replication, and you'll learn about some more crucial tasks in a production database environment.

Chapter 5, High Availability and Automatic Failover, discusses one of the most heavily adopted open source high-availability tools available, called Patroni. It can be used to set up automatic failover. We shall learn how it can be configured and will also discuss the advantages and disadvantages of using it for high availability.

Chapter 6, Connection Pooling and Load Balancing,discusses pgBouncer, which is a widely implemented, lightweight external pooler.

Chapter 7, Securing through Authentication, teaches you how to secure PostgreSQL encryption through authentication, authorization, and auditing. You will also see how to encrypt connections over the wire and also at rest using SSL. We will also look at some extensions for performing auditing in PostgreSQL

Chapter 8, Logging and Analyzing PostgreSQL Servers, has been designed to let admins know of the available options in PostgreSQL to enable the logging of certain important activities and to help admins/developers get familiar with some views, extensions, and tools to analyze live and historic activity in Postgres catalogs and log files.

Chapter 9, Critical Services Monitoring, shows how we can set up the monitoring of servers using Grafana and Prometheus.

Chapter 10, Extensions and Performance Tuning, shows us what extensions are and examines some of the most commonly used extensions for routine maintenance activities. We will also discuss some of the extensions that are helpful in many of the more important activities performed by an administrator or developer.

Chapter 11, Upgrades and Patches, shows us the various upgrade paths for PostgreSQL and how to implement them.

To get the most out of this book

You will need a virtual machine with either the CentOS/Red Hat or Debian/Ubuntu families ofoperating systems installed on it. This server must be able to connect to the internet to download the packages required to install PostgreSQL 13.

Software/hardware covered in the book

OS requirements

PostgreSQL 13

Linux

If you are using the digital version of this book, we advise you to type the code yourself or access the code via the GitHub repository (link available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://static.packt-cdn.com/downloads/9781838648138_ColorImages.pdf

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "So, it is always recommended to add the directory to PATH for ease."

A block of code is set as follows:

# yum install -y postgresql13-server-- To need contrib for the extensions, install postgresql13-contrib as well# yum install -y postgresql13-server postgresql13-contrib

Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "PGDG stands for the PostgreSQL Global DevelopmentGroup."

Warnings or important notes appear like this.

Tips and tricks appear like this.

Sections

In this book, you will find several headings that appear frequently (Getting ready, How to do it..., How it works..., There's more..., and See also).

To give clear instructions on how to complete a recipe, use these sections as follows:

Getting ready

This section tells you what to expect in the recipe and describes how to set up any software or any preliminary settings required for the recipe.

How to do it…

This section contains the steps required to follow the recipe.

How it works…

This section usually consists of a detailed explanation of what happened in the previous section.

There's more…

This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.

See also

This section provides helpful links to other useful information for the recipe.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [e-mail].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [e-mail] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Reviews

Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit packt.com.