book

PostgreSQL 14 Administration Cookbook

by Simon Riggs, Gianni Ciolli

March 2022

Intermediate to advanced

608 pages

12h 36m

English

Packt Publishing

Read now

Unlock full access

ContributorsAbout the authorsAbout the reviewers
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesDownload the color imagesConventions usedSectionsGetting readyHow to do it…How it works…There's more…See alsoGet in touchReviewsShare Your Thoughts
Introducing PostgreSQL 14What makes PostgreSQL different?How to get PostgreSQLHow to do it...How it works...There's more…Connecting to the PostgreSQL serverGetting readyHow to do it…How it works…There's more…See alsoEnabling access for network/remote usersHow to do it…How it works…There's more…See alsoUsing the pgAdmin4 GUI toolHow to do it…How it works…Using the OmniDB GUI toolHow to do it…See alsoUsing the psql query and scripting toolGetting readyHow to do it…How it works…There's more…See alsoChanging your password securelyHow to do it…How it works…Avoiding hardcoding your passwordGetting readyHow to do it…How it works…There's more…Using a connection service fileHow to do it…How it works…Troubleshooting a failed connectionHow to do it…There's more…PostgreSQL in the cloudGetting readyHow to do it…How it works…There's more…
What type of server is this?How to do it…There's more...What version is the server?How to do it…How it works…There's more…What is the server uptime?How to do it…How it works…See alsoLocating the database server filesGetting readyHow to do it...How it works...There's more…Locating the database server's message logGetting readyHow to do it...How it works...There's more...See alsoLocating the database's system identifierGetting readyHow to do it…How it works…Listing databases on the database serverHow to do it…How it works...There's more...How many tables are there in a database?How to do it...How it works…There's more…How much disk space does a database use?How to do it...How it works...How much disk space does a table use?How to do it…How it works…There's more…Which are my biggest tables?How to do it...How it works…How many rows are there in a table?How to do it…How it works...Quickly estimating the number of rows in a tableHow to do it…How it works…There's more…Listing extensions in this databaseHow to do it…How it works…There's more…See alsoUnderstanding object dependenciesGetting readyHow to do it…How it works…There's more…
RTFMHow to do it…How it works…There's more…Planning a new databaseGetting readyHow to do it…How it works…There's more…Setting configuration parameters for the database serverGetting readyHow to do it…How it works…There's more…Setting configuration parameters in your programsHow to do it…How it works…There's more…Finding the configuration settings for your sessionHow to do it…How it works…Finding parameters with non-default settingsHow to do it…How it works...There's more...Setting parameters for particular groups of usersHow to do it…How it works…A basic server configuration checklistGetting readyHow to do it…There's more…Adding an external module to PostgreSQLGetting readyHow to do it…How it works...Using an installed module/extensionGetting readyHow to do it…How it works...Managing installed extensionsHow to do it…How it works…There's more…
Overview of controlling the database serverStarting the database server manuallyGetting readyHow to do it…How it works…Stopping the server safely and quicklyHow to do it…How it works…See alsoStopping the server in an emergencyHow to do it…How it works…Reloading the server configuration filesHow to do it…How it works…There's more…Restarting the server quicklyHow to do it…There's more…Preventing new connectionsHow to do it…How it works…Restricting users to only one session eachHow to do it…How it works…Pushing users off the systemHow to do it…How it works…Deciding on a design for multitenancyHow to do it…How it works…Using multiple schemasGetting readyHow to do it…How it works…Giving users their own private databasesGetting readyHow to do it…How it works…There's more…See alsoRunning multiple servers on one systemGetting readyHow to do it…How it works…Setting up a connection poolGetting readyHow to do it…How it works…There's more…Accessing multiple servers using the same host and portGetting readyHow to do it…There's more…
Choosing good names for database objectsGetting readyHow to do it…There's more…Handling objects with quoted namesGetting readyHow to do it...How it works…There's more…Enforcing the same name and definition for columnsGetting readyHow to do it...How it works…There's more…Identifying and removing duplicatesGetting readyHow to do it…How it works…There's more…Preventing duplicate rowsGetting readyHow to do it…How it works…There's more...Finding a unique key for a set of dataGetting readyHow to do it…How it works…Generating test dataHow to do it...How it works…There's more…See alsoRandomly sampling dataHow to do it…How it works...Loading data from a spreadsheetGetting readyHow to do it...How it works...There's more...Loading data from flat filesGetting readyHow to do it...How it works…There's more…Making bulk data changes using server-side procedures with transactionsGetting readyHow to do it…There's more…
Overview of PostgreSQL securityTypical user rolesThe PostgreSQL superuserHow to do it…How it works…There's more…See alsoRevoking user access to a tableGetting readyHow to do it…How it works…There's more…Granting user access to a tableGetting readyHow to do it…How it works...There's more…Granting user access to specific columnsGetting readyHow to do it…How it works…There's more…Granting user access to specific rowsGetting readyHow to do it…How it works…There's more...Creating a new userGetting readyHow to do it...How it works…There's more…Temporarily preventing a user from connectingGetting readyHow to do it…How it works...There's more…Removing a user without dropping their dataGetting readyHow to do it…How it works…Checking whether all users have a secure passwordHow to do it…How it works…Giving limited superuser powers to specific usersGetting readyHow to do it…How it works…There's more…Auditing database accessGetting readyAuditing accessAuditing SQLAuditing table accessManaging the audit logAuditing data changesAlways knowing which user is logged inGetting readyHow to do it…How it works…There's more…Integrating with LDAPGetting readyHow to do it…How it works…There's more…See alsoConnecting using encryption (SSL/GSSAPI)Getting readyHow to do it…How it works…There's more…Using SSL certificates to authenticateGetting readyHow to do it…How it works…There's more…See alsoMapping external usernames to database rolesGetting readyHow to do it…How it works…There's more…Using column-level encryptionGetting readyHow to do it…How it works…There's more…See alsoSetting up cloud security using predefined rolesGetting readyHow to do it…How it works…There's more…
Writing a script that either succeeds entirely or fails entirelyHow to do it…How it works…There's more…Writing a psql script that exits on the first errorGetting readyHow to do it…How it works…There's more…Using psql variablesGetting readyHow to do it…How it works…There's more…Placing query output into psql variablesGetting readyHow to do it…How it works…There's more…Writing a conditional psql scriptGetting readyHow to do it…How it works…There's more…Investigating a psql errorGetting readyHow to do it…There's more…Setting the psql prompt with useful informationGetting readyHow to do it…How it works…Using pgAdmin for DBA tasksGetting readyHow to do it…How it works...There's more…Scheduling jobs for regular background executionGetting readyHow to do it…How it works…There's more…Performing actions on many tablesGetting readyHow to do it…How it works…There's more…Adding/removing columns on a tableHow to do it…How it works…There's more…Changing the data type of a columnGetting readyHow to do it…How it works…There's more…Changing the definition of an enum data typeGetting readyHow to do it…How it works…There's more…Adding a constraint concurrentlyGetting readyHow to do it…How it works…There's more…Adding/removing schemasHow to do it…There's more…Moving objects between schemasHow to do it…How it works…There's more…Adding/removing tablespacesGetting readyHow to do it…How it works…There's more…Moving objects between tablespacesGetting readyHow to do it…How it works…There's more…Accessing objects in other PostgreSQL databasesGetting readyHow to do it…How it works…There's more…Accessing objects in other foreign databasesGetting readyHow to do it…How it works…There's more…Making views updatableGetting readyHow to do it…How it works…There's more…Using materialized viewsGetting readyHow to do it…How it works…There's more…Using GENERATED data columnsHow to do it…How it works…There's more…Using data compressionGetting readyHow to do it…How it works…There's more…
Overview of PostgreSQL monitoringCloud-native monitoringProviding PostgreSQL information to monitoring toolsFinding more information about generic monitoring toolsReal-time viewing using pgAdminGetting readyHow to do it…Checking whether a user is connectedGetting readyHow to do it…How it works…There's more…Checking whether a computer is connectedHow to do it…There's more…Repeatedly executing a query in psqlHow to do it…There's more…Checking which queries are runningGetting readyHow to do it…How it works…There's more…See alsoMonitoring the progress of commandsGetting readyHow to do it…How it works…There's more…Checking which queries are active or blockedGetting readyHow to do it…How it works…There's more…Knowing who is blocking a queryGetting readyHow to do it…How it works…Killing a specific sessionHow to do it…How it works…There's more…Detecting an in-doubt prepared transactionHow to do it…Knowing whether anybody is using a specific tableGetting readyHow to do it…How it works…There's more...Knowing when a table was last usedGetting readyHow to do it…How it works...There's more…Usage of disk space by temporary dataGetting readyHow to do it…How it works…There's more…Understanding why queries slow downGetting readyHow to do it…How it works…There's more…See alsoAnalyzing the real-time performance of your queriesGetting readyHow to do it…How it works…There's more…Investigating and reporting a bugGetting readyHow to do it…How it works…
Controlling automatic database maintenanceGetting readyHow to do it…How it works…There's more…See alsoAvoiding auto-freezing and page corruptionsHow to do it…Removing issues that cause bloatGetting readyHow to do it…How it works…There's more…Removing old prepared transactionsGetting readyHow to do it…How it works…There's more…Actions for heavy users of temporary tablesHow to do it…How it works…Identifying and fixing bloated tables and indexesGetting readyHow to do it…How it works…There's more…Monitoring and tuning a vacuumGetting readyHow to do it…How it works…There's more…Maintaining indexesGetting readyHow to do it…How it works…There's more…Finding unused indexesHow to do it…How it works…Carefully removing unwanted indexesGetting readyHow to do it…How it works…Planning maintenanceHow to do it…How it works…There's more…
Finding slow SQL statementsGetting readyHow to do it…How it works…There's more…Finding out what makes SQL slowGetting readyHow to do it…There's more…See alsoReducing the number of rows returnedHow to do it…There's more…Simplifying complex SQL queriesGetting readyHow to do it…There's more…Speeding up queries without rewriting themHow to do it…There's more…Discovering why a query is not using an indexGetting readyHow to do it…How it works…There's more…Forcing a query to use an indexGetting readyHow to do it…There's more…Using parallel queryHow to do it…How it works…Creating time-series tables using partitioningHow to do it…How it works…There's more…Using optimistic locking to avoid long lock waitsHow to do it…How it works…There's more…Reporting performance problemsHow to do it…There's more…
Understanding and controlling crash recoveryHow to do it…How it works…There's more…Planning your backupsHow to do it…There's more…Hot logical backups of one databaseHow to do it…How it works…There's more…See alsoHot logical backups of all databasesHow to do it…How it works…See alsoBackups of database object definitionsHow to do it…There's more…A standalone hot physical backupGetting readyHow to do it…How it works…There's more…Hot physical backups with BarmanGetting readyHow to do it…How it works…There's more…Recovery of all databasesGetting readyHow to do it…How it works…There's more…Recovery to a point in timeGetting readyHow to do it…How it works…There's more…See alsoRecovery of a dropped/damaged tableHow to do it…How it works…See alsoRecovery of a dropped/damaged databaseHow to do it…Extracting a logical backup from a physical oneGetting readyHow to do it…There's more…Improving performance of logical backup/recoveryGetting readyHow to do it…How it works…There's more…Improving performance of physical backup/recoveryGetting readyHow to do it...How it works…There's more…See alsoValidating backupsGetting readyHow to do it…How it works…There's more…
Replication conceptsTopicsBasic conceptsHistory and scopePractical aspectsData lossSingle-master replicationMultinode architecturesMulti-master replicationOther approaches to replicationReplication best practicesGetting readyHow to do it…There's more…Setting up streaming replicationGetting readyHow to do it…How it works…There's more…Setting up streaming replication securityGetting readyHow to do it…How it works…There's more…Hot Standby and read scalabilityGetting readyHow to do it…How it works…Managing streaming replicationGetting readyHow to do it…There's more…See alsoUsing repmgrGetting readyHow to do it…How it works…There's more…Using replication slotsGetting readyHow to do it…There's more…See alsoMonitoring replicationGetting readyHow to do it…There's more…Performance and sync repGetting readyHow to do it...How it works…There's more…Delaying, pausing, and synchronizing replicationGetting readyHow to do it…There's more…See alsoLogical replicationGetting readyHow to do it…How it works…There's more…BDRGetting readyHow to do it…How it works...There's more…Archiving transaction log dataGetting readyHow to do it…There's more...See alsoUpgrading minor releasesGetting readyHow to do it…How it works…There's more…Major upgrades in-placeGetting readyHow to do it…How it works…Major upgrades onlineHow to do it…How it works...
Other Books You May EnjoyPackt is searching for authors like youShare Your Thoughts

Overview

PostgreSQL 14 Administration Cookbook provides a hands-on guide to mastering the administration of PostgreSQL 14. With over 175 recipes, this book equips you with practical techniques to manage, secure, and optimize your PostgreSQL databases, ensuring they are robust and high-performing.

What this Book will help me do

Master managing PostgreSQL databases both on-premises and in the cloud efficiently.
Implement effective backup and recovery strategies to secure your data.
Leverage the latest features of PostgreSQL 14 to enhance your database workflows.
Understand and apply best practices for maintaining high availability and performance.
Troubleshoot real-world challenges with guided solutions and expert insights.

Author(s)

Simon Riggs and Gianni Ciolli are seasoned database experts with years of experience working with PostgreSQL. Simon is a PostgreSQL core team member, contributing his technical knowledge towards building robust database solutions, while Gianni brings a wealth of expertise in database administration and support. Together, they share a passion for making complex database concepts accessible and actionable.

Who is it for?

This book is for database administrators, data architects, and developers who manage PostgreSQL databases and are looking to deepen their knowledge. It is suitable for professionals with some experience in PostgreSQL who aim to maximize their database's performance and security, as well as for those new to the system seeking a comprehensive start. Readers with an interest in practical, problem-solving approaches to database management will greatly benefit from this cookbook.