Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • PostgreSQL 14 Administration Cookbook
  • Toc
  • feedback
PostgreSQL 14 Administration Cookbook

PostgreSQL 14 Administration Cookbook

By : Simon Riggs, GIANNI CIOLLI
4.3 (15)
close
PostgreSQL 14 Administration Cookbook

PostgreSQL 14 Administration Cookbook

4.3 (15)
By: Simon Riggs, GIANNI CIOLLI

Overview of this book

PostgreSQL is a powerful, open-source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 14 allows you to scale up your PostgreSQL infrastructure. With this book, you'll take a step-by-step, recipe-based approach to effective PostgreSQL administration. This book will get you up and running with all the latest features of PostgreSQL 14 while helping you explore the entire database ecosystem. You’ll learn how to tackle a variety of problems and pain points you may face as a database administrator such as creating tables, managing views, improving performance, and securing your database. As you make progress, the book will draw attention to important topics such as monitoring roles, validating backups, regular maintenance, and recovery of your PostgreSQL 14 database. This will help you understand roles, ensuring high availability, concurrency, and replication. Along with updated recipes, this book touches upon important areas like using generated columns, TOAST compression, PostgreSQL on the cloud, and much more. By the end of this PostgreSQL book, you’ll have gained the knowledge you need to manage your PostgreSQL 14 database efficiently, both in the cloud and on-premise.
Table of Contents (14 chapters)
close
close
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the color images
Conventions used
Sections
Get in touch
Reviews
Share Your Thoughts
1
Chapter 1: First Steps
Chapter 1: First Steps
Introducing PostgreSQL 14
How to get PostgreSQL
Connecting to the PostgreSQL server
Enabling access for network/remote users
Using the pgAdmin4 GUI tool
Using the OmniDB GUI tool
Using the psql query and scripting tool
Changing your password securely
Avoiding hardcoding your password
Using a connection service file
Troubleshooting a failed connection
PostgreSQL in the cloud
Free Chapter
2
Chapter 2: Exploring the Database
Chapter 2: Exploring the Database
What type of server is this?
What version is the server?
What is the server uptime?
Locating the database server files
Locating the database server's message log
Locating the database's system identifier
Listing databases on the database server
How many tables are there in a database?
How much disk space does a database use?
How much disk space does a table use?
Which are my biggest tables?
How many rows are there in a table?
Quickly estimating the number of rows in a table
Listing extensions in this database
Understanding object dependencies
3
Chapter 3: Server Configuration
Chapter 3: Server Configuration
RTFM
Planning a new database
Setting configuration parameters for the database server
Setting configuration parameters in your programs
Finding the configuration settings for your session
Finding parameters with non-default settings
Setting parameters for particular groups of users
A basic server configuration checklist
Adding an external module to PostgreSQL
Using an installed module/extension
Managing installed extensions
4
Chapter 4: Server Control
Chapter 4: Server Control
Overview of controlling the database server
Starting the database server manually
Stopping the server safely and quickly
Stopping the server in an emergency
Reloading the server configuration files
Restarting the server quickly
Preventing new connections
Restricting users to only one session each
Pushing users off the system
Deciding on a design for multitenancy
Using multiple schemas
Giving users their own private databases
Running multiple servers on one system
Setting up a connection pool
Accessing multiple servers using the same host and port
5
Chapter 5: Tables and Data
Chapter 5: Tables and Data
Choosing good names for database objects
Handling objects with quoted names
Enforcing the same name and definition for columns
Identifying and removing duplicates
Preventing duplicate rows
Finding a unique key for a set of data
Generating test data
Randomly sampling data
Loading data from a spreadsheet
Loading data from flat files
Making bulk data changes using server-side procedures with transactions
6
Chapter 6: Security
Chapter 6: Security
Overview of PostgreSQL security
The PostgreSQL superuser
Revoking user access to a table
Granting user access to a table
Granting user access to specific columns
Granting user access to specific rows
Creating a new user
Temporarily preventing a user from connecting
Removing a user without dropping their data
Checking whether all users have a secure password
Giving limited superuser powers to specific users
Auditing database access
Always knowing which user is logged in
Integrating with LDAP
Connecting using encryption (SSL/GSSAPI)
Using SSL certificates to authenticate
Mapping external usernames to database roles
Using column-level encryption
Setting up cloud security using predefined roles
7
Chapter 7: Database Administration
Chapter 7: Database Administration
Writing a script that either succeeds entirely or fails entirely
Writing a psql script that exits on the first error
Using psql variables
Placing query output into psql variables
Writing a conditional psql script
Investigating a psql error
Setting the psql prompt with useful information
Using pgAdmin for DBA tasks
Scheduling jobs for regular background execution
Performing actions on many tables
Adding/removing columns on a table
Changing the data type of a column
Changing the definition of an enum data type
Adding a constraint concurrently
Adding/removing schemas
Moving objects between schemas
Adding/removing tablespaces
Moving objects between tablespaces
Accessing objects in other PostgreSQL databases
Accessing objects in other foreign databases
Making views updatable
Using materialized views
Using GENERATED data columns
Using data compression
8
Chapter 8: Monitoring and Diagnosis
Chapter 8: Monitoring and Diagnosis
Overview of PostgreSQL monitoring
Cloud-native monitoring
Providing PostgreSQL information to monitoring tools
Real-time viewing using pgAdmin
Checking whether a user is connected
Checking whether a computer is connected
Repeatedly executing a query in psql
Checking which queries are running
Monitoring the progress of commands
Checking which queries are active or blocked
Knowing who is blocking a query
Killing a specific session
Detecting an in-doubt prepared transaction
Knowing whether anybody is using a specific table
Knowing when a table was last used
Usage of disk space by temporary data
Understanding why queries slow down
Analyzing the real-time performance of your queries
Investigating and reporting a bug
9
Chapter 9: Regular Maintenance
Chapter 9: Regular Maintenance
Controlling automatic database maintenance
Avoiding auto-freezing and page corruptions
Removing issues that cause bloat
Removing old prepared transactions
Actions for heavy users of temporary tables
Identifying and fixing bloated tables and indexes
Monitoring and tuning a vacuum
Maintaining indexes
Finding unused indexes
Carefully removing unwanted indexes
Planning maintenance
10
Chapter 10: Performance and Concurrency
chevron up
Chapter 10: Performance and Concurrency
Finding slow SQL statements
Finding out what makes SQL slow
Reducing the number of rows returned
Simplifying complex SQL queries
Speeding up queries without rewriting them
Discovering why a query is not using an index
Forcing a query to use an index
Using parallel query
Creating time-series tables using partitioning
Using optimistic locking to avoid long lock waits
Reporting performance problems
11
Chapter 11: Backup and Recovery
Chapter 11: Backup and Recovery
Understanding and controlling crash recovery
Planning your backups
Hot logical backups of one database
Hot logical backups of all databases
Backups of database object definitions
A standalone hot physical backup
Hot physical backups with Barman
Recovery of all databases
Recovery to a point in time
Recovery of a dropped/damaged table
Recovery of a dropped/damaged database
Extracting a logical backup from a physical one
Improving performance of logical backup/recovery
Improving performance of physical backup/recovery
Validating backups
12
Chapter 12: Replication and Upgrades
Chapter 12: Replication and Upgrades
Replication concepts
Replication best practices
Setting up streaming replication
Setting up streaming replication security
Hot Standby and read scalability
Managing streaming replication
Using repmgr
Using replication slots
Monitoring replication
Performance and sync rep
Delaying, pausing, and synchronizing replication
Logical replication
BDR
Archiving transaction log data
Upgrading minor releases
Major upgrades in-place
Major upgrades online
Why subscribe?
13
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Using optimistic locking to avoid long lock waits

If you perform work in one long transaction, the database will lock rows for long periods of time. Long lock times often result in application performance issues because of long lock waits: 

BEGIN;
SELECT * FROM accounts WHERE holder_name ='BOB' FOR UPDATE;
<do some calculations here>
UPDATE accounts SET balance = 42.00 WHERE holder_name ='BOB';
COMMIT;

If that is happening, then you may gain some performance benefits by moving from explicit locking (SELECT ... FOR UPDATE) to optimistic locking.

Optimistic locking assumes that others don't update the same record, and checks this at update time, instead of locking the record for the time it takes to process the information on the client side.

How to do it…

Rewrite your application so that the SQL is transformed into two separate transactions, with a double-check to ensure that the rows haven't changed (pay attention...

End of Section 11
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete