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.
  • Book Overview & Buying PostgreSQL 14 Administration Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
PostgreSQL 14 Administration Cookbook

PostgreSQL 14 Administration Cookbook

By : Simon Riggs, Gianni Ciolli
4.3 (14)
Buy this Book
close
close
PostgreSQL 14 Administration Cookbook

PostgreSQL 14 Administration Cookbook

4.3 (14)
By: Simon Riggs, Gianni Ciolli
Buy this Book

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

Which are my biggest tables?

We've looked at getting the size of a specific table, so now it's time to widen the problem to related areas. Rather than having an absolute value for a specific table, let's look at the relative sizes.

How to do it...

The following basic query will tell us the 10 biggest tables:

SELECT quote_ident(table_schema)||'.'||quote_ident(table_name) as name 
      ,pg_relation_size(quote_ident(table_schema)
              || '.' || quote_ident(table_name)) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

The tables are shown in descending order of size, with at the most 10 rows displayed. In this case, we look at all the tables in all the schemas, apart from the tables in information_schema or pg_catalog, as we did in the How many tables are in the database? recipe.

How it works…

PostgreSQL provides a dedicated function, pg_relation_size, to compute the actual disk space used by a specific table or index. We just need to provide the table name. In addition to the main data files, there are other files (called forks) that can be measured by specifying an optional second argument. These include the Visibility Map (VM), the Free Space Map (FSM), and the initialization fork for unlogged objects.

Visually different images
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
PostgreSQL 14 Administration Cookbook
End of Section 2
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon