book

PostgreSQL 13 Cookbook

Name: PostgreSQL 13 Cookbook
Author: Vallarapu Naga Avinash Kumar
ISBN: 9781838648138

by Vallarapu Naga Avinash Kumar

February 2021

Intermediate to advanced

344 pages

6h 35m

English

Packt Publishing

Read now

Unlock full access

Title Page
Copyright and Credits
PostgreSQL 13 Cookbook
Contributors
About the authorAbout the reviewers
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the color imagesConventions usedSectionsGetting readyHow to do it…How it works…There's more…See alsoGet in touchReviews
Cluster Management Fundamentals
Technical requirementsInstalling PostgreSQL 13 using RPMs on CentOSGetting readyHow to do it...How it works...Initializing a PostgreSQL cluster using initdbGetting ready How to do it...How it works...Starting a PostgreSQL cluster using pg_ctl Getting readyHow to do it...How it works...Clusters in PostgreSQLDatabases in PostgreSQLThere's more...Shutting down a PostgreSQL cluster using different shutdown modesGetting readyHow to do it...How it works...There's more...Identifying a PostgreSQL data directory and its contentsGetting readyHow to do it...How it works...There's more...Moving pg_wal to another locationGetting readyHow to do it...How it works...Running the psql client and some psql shortcutsGetting readyHow to do it...How it works...Running a SQL server using psqlGetting a list of databasesFinding the database's sizeConnecting to a databaseGetting the list of schemas in a databaseGetting the list of tablesDescribing a tableThere's more...SQLs behind the shortcutsLocating the Postgres configuration fileGetting readyHow to do it...How it works...There's more...include_dirincludeModifying the location of a postgresql.conf file in PostgreSQLGetting readyHow to do it...How it works...Modifying the postgresql.auto.conf file in PostgreSQLGetting readyHow to do it...How it works...There's more...Enable archiving in PostgreSQLGetting readyHow to do it...How it works...There's more...
Cluster Management Techniques
Technical requirementsCreating and dropping databasesGetting readyHow to do it...How it worksThere's moreLocating a database and a table on the file systemGetting readyHow to do it...How it works...Creating a schema in PostgreSQLGetting readyHow to do it...How it works...There's more...Checking table and index sizes in PostgreSQLGetting readyHow to do it...How it works...There's more...Creating tablespacesGetting readyHow to do it...How it worksThere's more...Moving tables to a different tablespaceGetting readyHow to do it...How it worksCreating a user in PostgreSQLGetting readyHow to do it...How it worksThere's more...Dropping a user in PostgreSQLGetting readyHow to do it ...How it works ...Assigning and revoking a privilege to/from a user or a role Getting readyHow to do itHow it worksCreating a group role for role-based segregation Getting readyHow to do itHow it worksMVCC implementation and VACUUM in PostgreSQLGetting readyHow to do it...How it works...tableoidxminxmaxctidpageinspectThere's more...
Backup and Recovery
Technical requirementsBacking up and restoring a database using pg_dump and pg_restoreGetting ready The RPM package for CentOS/Red Hat distributionsDebian and UbuntuHow to do itSection ASection BHow it works Backing up and restoring one or more tables using pg_dump and pg_restoreGetting readyHow to do itHow it worksBacking up and restoring globals or an entire cluster using pg_dumpall and psqlGetting readyHow to do itHow it works...Parallel backup and restore using pg_dump and pg_restoreGetting readyHow to do itHow it worksBacking up a database cluster using pg_basebackupGetting readyHow to do itHow it works...Restoring a backup taken using pg basebackupGetting readyHow to do itHow it worksInstalling pgBackRest on CentOS/RedHat OSGetting readyHow to do itHow it worksInstalling pgBackRest on Ubuntu/Debian OSGetting readyHow to do itHow it worksBacking up a database cluster using pgBackRestGetting readyHow to do itHow it worksRestoring a backup taken using pgBackRestGetting readyHow to do itHow it works
Advanced Replication Techniques
Setting up streaming replication in PostgreSQL 13Getting ready...How to do it ...How it works ...Adding a delayed standby for faster point-in-time recoveryGetting ready...How to do it...How it works...Promoting a standby to a masterGetting ready...How to do it...How it works...Adding a cascaded streaming replicaGetting ready...How to do it ...How it works...Promoting a standby in a replication cluster with multiple standby serversGetting ready...How to do it...How it works...Using pg_rewind to re-synchronize a demoted masterGetting ready...How to do it...How it works...Enabling synchronous streaming replicationGetting ready...How to do it...How it works...Setting up logical replication in PostgreSQL 13Getting ready...How to do it...How it works...
High Availability and Automatic Failover
Technical requirementsAutomatic failover using PatroniEnabling distributed consensus using etcdGetting readyHow to do it...How it works...Avoiding split-brain using Watchdog/softdogGetting readyHow to do it...How it works...Installing Patroni along with its Python dependenciesGetting ready...How to do it...How it works...Creating a Patroni configuration fileGetting ready...How to do it...How it works...Starting Patroni as a service using systemdGetting ready...How to do it...How it works...Initializing a PostgreSQL primary database using PatroniGetting ready...How to do it...How it works...Adding a standby to a Patroni clusterGetting ready...How to do it...How it works...Performing a manual switchover using PatroniGetting ready...How to do it...How it works...
Connection Pooling and Load Balancing
Technical requirementsInstalling pgBouncer on a Linux serverGetting ready...How to do it...How it works...Creating a pgBouncer configuration fileGetting ready...How to do it...How it works...Configuring the pool settings on pgBouncerGetting ready...How to do it...How it works...Starting and stopping the pgBouncer serviceGetting ready...How to do it...How it works...Installing HAProxy on Linux serversGetting ready...How to do it...How it works...Using xinetd to detect a primary or a standbyGetting ready...How to do it...How it works...Creating an HAProxy configuration fileGetting ready...How to do it...How it works...Starting and stopping the HAProxy serviceGetting ready...How to do it...How it works...Building a robust HA cluster using Patroni, pgBouncer, and HAProxyGetting ready...How to do it...How it works...

Securing through Authentication
Technical requirementsSecuring client connections using the pg_hba.conf fileCategories in the pg_hba.conf fileGetting readyHow to do it...How it works...Performing authorization using roles and privilegesGetting readyHow to do it...How it works...Setting up row-level securityGetting readyHow to do it...How it works...Configuring encryption of data over the wire using SSLGetting readyHow to do it...How it works...Enabling certificate authentication using SSLGetting readyHow to do it...How it works...Auditing PostgreSQL through loggingGetting readyHow to do it...How it works...Auditing PostgreSQL using pgauditGetting readyHow to do it...How it works...Setting up object-level auditing using pgauditGetting readyHow to do it...How it works...
Logging and Analyzing PostgreSQL Servers
Technical requirementsSetting up slow query logging in PostgreSQLGetting readyHow to do it...How it works...There's more...Logging runtime execution plans in PostgreSQL using auto_explainGetting readyHow to do it...Global levelSession levelHow it works...Logging locks, waits, and temp in PostgreSQLGetting readyHow to do it...How it works...Logging autovacuum and analyzing activity in PostgreSQLGetting readyHow to do it...How it works...Generating a pgBadger reportGetting readyHow to do it...How it works...Configuring pg_stat_statements as an extensionGetting readyHow to do it...How it works...Query analysis using pg_stat_statementsGetting readyHow to do it...How it works...Getting the kernel-level statistics of a query using pg_stat_kcacheGetting readyHow to do it...How it works...
Critical Services Monitoring
Technical requirementsInstallation of Grafana and its dependenciesGetting readyHow to do it...How it works... Prometheus as a data source on the monitoring serverGetting readyHow to do it...How it works...Configuring Node Exporter on Postgres servers to monitor operating system metricsGetting readyHow to do it...How it works...Adding metrics being collected using node_exporter to PrometheusGetting readyHow to do it...How it works...Collecting PostgreSQL metrics using postgres_exporterGetting readyHow to do it...How it works...Adding metrics exposed by postgres_exporter to PrometheusGetting readyHow to do it...How it works...Importing a dashboard for monitoring Linux metricsGetting readyHow to do it...How it works...How to import a dashboard for monitoring Postgres metricsGetting readyHow to do it...How it works...Adding custom queries to postgres_exporterGetting readyHow to do it...How it works...
Extensions and Performance Tuning
Technical requirementsInstalling and creating pg_repack to rebuild objects onlineGetting readyHow to do it...Installing pg_repack on CentOSInstalling pg_repack on UbuntuHow it works...How to rebuild a table online using pg_repackGetting readyHow to do it...How it works...How to rebuild indexes of a table online using pg_repackGetting readyHow to do it...Rebuilding all the indexes of a tableRebuilding a specific indexHow it works...Moving a table or an index to another tablespace onlineGetting readyHow to do it...How it works...Warming up the cache using pg_prewarmGetting readyHow to do it...How it works...How to tune a function or a stored procedure using plprofilerGetting readyHow to do it...How it works...Capturing statements that require tuning using pg_stat_statementsGetting readyHow to do it...How it works...Viewing the execution plans using EXPLAIN in PostgreSQLGetting readyHow to do it...How it works...
Upgrades and Patches
Technical requirementsFinding the difference between a major and minor release in PostgreSQLWhat is an obsolete version?Technical requirementsMajor version upgrade to PostgreSQL 13 using pg_dumpallGetting readyHow to do it...How it works...Major version upgrade to PostgreSQL 13 using pg_dump and pg_restoreGetting readyHow to do it...How it works...Major version upgrade to PostgreSQL 13 using pg_upgrade with downtimeGetting readyHow to do it...How it works...Major version upgrade to PostgreSQL 13 using pg_upgrade with hard links for seamless downtimeGetting readyHow to do it...How it works...Installing the pglogical extension to upgrade older versions to PostgreSQL 13Getting readyHow to do it...How it works...Upgrading to PostgreSQL 13 using the pglogical extensionGetting readyHow to do it...How it works...Upgrading to PostgreSQL 13 using logical replication and logical decodingGetting readyHow to do it...How it works...Updating the minor version of PostgreSQL 13Getting readyHow to do it...How it works...
About Packt
Why subscribe?
Other Books You May Enjoy
Packt is searching for authors like youLeave a review - let other readers know what you think

Content preview from PostgreSQL 13 Cookbook

High Availability and Automatic Failover

The first step toward making a database highly available is setting up replication to another server, which could either be within the same or a different data center. While it is fairly difficult to see such replication methods included for free with some of the commercial databases that are available, you do not have to pay any license fees to set up replication with PostgreSQL. The community PostgreSQL software includes it by default. You may refer to Chapter 4, Advanced Replication Techniques to learn how to set up replication in PostgreSQL.

Now that we have replication set up, how do we ensure that we can avoid downtime for an application by letting a failover happen automatically? Are there any ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

PostgreSQL 12 High Availability Cookbook - Third Edition

Publisher Resources

ISBN: 9781838648138

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

PostgreSQL 13 Cookbook

by Vallarapu Naga Avinash Kumar

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.