book

PostgreSQL 14 Administration Cookbook

Name: PostgreSQL 14 Administration Cookbook
ISBN: 9781803248974

by Simon Riggs, Gianni Ciolli

March 2022

Intermediate to advanced

608 pages

12h 36m

English

Packt Publishing

Read now

Unlock full access

PostgreSQL 14 Administration Cookbook
ContributorsAbout the authorsAbout the reviewers
Preface
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
Chapter 1: First Steps
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…
Chapter 2: Exploring the Database
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…
Chapter 3: Server Configuration
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…
Chapter 4: Server Control
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…
Chapter 5: Tables and Data
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…
Chapter 6: Security
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…
Chapter 7: Database Administration
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…
Chapter 8: Monitoring and Diagnosis
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…

Chapter 9: Regular Maintenance
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…
Chapter 10: Performance and Concurrency
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…
Chapter 11: Backup and Recovery
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…
Chapter 12: Replication and Upgrades
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...
Why subscribe?
Other Books You May EnjoyPackt is searching for authors like youShare Your Thoughts

Content preview from PostgreSQL 14 Administration Cookbook

Chapter 12: Replication and Upgrades

Replication isn't magic, though it can be pretty cool! It's even cooler when it works, and that's what this chapter is all about.

Replication requires understanding, effort, and patience. There are a significant number of points to get right. Our emphasis here is on providing simple approaches to get you started, as well as some clear best practices on operational robustness.

PostgreSQL has included some form of native or in-core replication since version 8.2, though that support has steadily improved over time. External projects and tools have always been a significant part of the PostgreSQL landscape, with most of them being written and supported by very skilled PostgreSQL technical developers. Some people ...

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

Publisher Resources

ISBN: 9781803248974

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

PostgreSQL 14 Administration Cookbook

by Simon Riggs, Gianni Ciolli

Chapter 12: Replication and Upgrades

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.