Vallarapu Naga Avinash Kumar - PostgreSQL 13 Cookbook: Over 120 recipes to build high-performance and fault-tolerant PostgreSQL database solutions

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 Parikh
Publishing Product Manager: Mrinmayee Kawalkar
Senior Editor: Roshan Kumar
Content Development Editor: Joseph Sunil
Technical Editor: Arjun Varma
Copy Editor: Safis Editing
Project Coordinator: Aishwarya Mohan
Proofreader: Safis Editing
Indexer: Tejal Soni
Production 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

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.

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 bachelors 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.

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.

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.

, 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.

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

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

, 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.

, 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.

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

, 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

, 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.