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

Description

Get to grips with building reliable, scalable, and maintainable database solutions for enterprises and production databases Key FeaturesImplement PostgreSQL 13 features to perform end-to-end modern database managementDesign, manage, and build enterprise database solutions using a unique recipe-based approachSolve common and not-so-common challenges faced while working to achieve optimal database performanceBook Description 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. What you will learnUnderstand logical and physical backups in PostgresDemonstrate the different types of replication methods possible with PostgreSQL todaySet up a high availability cluster that provides seamless automatic failover for applicationsSecure a PostgreSQL encryption through authentication, authorization, and auditingAnalyze the live and historic activity of a PostgreSQL serverUnderstand how to monitor critical services in Postgres 13Manage maintenance activities and performance tuning of a PostgreSQL clusterWho this book is for This PostgreSQL book is for database architects, database developers and administrators, or anyone who wants to become well-versed with PostgreSQL 13 features to plan, manage, and design efficient database solutions. Prior experience with the PostgreSQL database and SQL language is expected. Table of ContentsCluster Management FundamentalsCluster Management TechniquesBackup and RecoveryAdvanced Replication TechniquesHigh Availability and Automatic FailoverConnection Pooling and Load BalancingSecuring through Authentication Logging and Analyzing PostgreSQL ServersCritical services MonitoringExtensions and Performance TuningUpgrades and Patches 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 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... Chapter 2: 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... Chapter 3: 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 Chapter 4: 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... Chapter 5: 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... Chapter 6: 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... Chapter 7: 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... Chapter 8: 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... Chapter 9: 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... Chapter 10: 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... Chapter 11: 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 Other Books You May Enjoy Index