PostgreSQLl 13 cookbook over 120 recipes to build high-performance and fault-tolerant postgresql database solutions
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 discov...
| Other Authors: | |
|---|---|
| Format: | eBook |
| Language: | Inglés |
| Published: |
Birmingham ; Mumbai :
Packt Publishing
2021.
|
| Subjects: | |
| See on Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009631709406719 |
Table of 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
- 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...
