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 10 Administration Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
PostgreSQL 10 Administration Cookbook

PostgreSQL 10 Administration Cookbook

Buy this Book
close
close
PostgreSQL 10 Administration Cookbook

PostgreSQL 10 Administration Cookbook

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 10 allows users to scale up their PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. Throughout this book, you will be introduced to these new features such as logical replication, native table partitioning, additional query parallelism, and much more. You will learn how to tackle a variety of problems that are basically the pain points for any database administrator - from creating tables to managing views, from improving performance to securing your database. More importantly, the book pays special attention to topics such as monitoring roles, backup, and recovery of your PostgreSQL 10 database, ensuring high availability, concurrency, and replication. By the end of this book, you will know everything you need to know to be the go-to PostgreSQL expert in your organization.
Table of Contents (19 chapters)
close
close
close
Title Page
Icon
Title Page
Copyright and Credits
Icon
Copyright and Credits
Packt Upsell
Icon
Packt Upsell
Contributors
Icon
Contributors
Preface
Icon
Preface
Free Chapter
1
First Steps
Icon
First Steps
Icon
Introduction
Icon
Getting PostgreSQL
Icon
Connecting to the PostgreSQL server
Icon
Enabling access for network/remote users
Icon
Using graphical administration tools
Icon
OmniDB
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
2
Exploring the Database
Icon
Exploring the Database
Icon
Introduction
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 this 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
Configuration
Icon
Configuration
Icon
Introduction
Icon
Reading the fine manual
Icon
Planning a new database
Icon
Changing parameters in your programs
Icon
Finding the current configuration settings
Icon
Which parameters are at non-default settings?
Icon
Updating the parameter file
Icon
Setting parameters for particular groups of users
Icon
The basic server configuration checklist
Icon
Adding an external module to PostgreSQL
Icon
Using an installed module
Icon
Managing installed extensions
4
Server Control
Icon
Server Control
Icon
Introduction
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 database
Icon
Running multiple servers on one system
Icon
Setting up a connection pool
Icon
Accessing multiple servers using the same host and port
5
Tables and Data
Icon
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
6
Security
Icon
Security
Icon
Introduction
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 SSL
Icon
Using SSL certificates to authenticate
Icon
Mapping external usernames to database roles
Icon
Encrypting sensitive data
7
Database Administration
Icon
Database Administration
Icon
Introduction
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
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 a data type
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
Updatable views
Icon
Using materialized views
8
Monitoring and Diagnosis
chevron up
Icon
Monitoring and Diagnosis
Icon
Providing PostgreSQL information to monitoring tools
Icon
Real-time viewing using pgAdmin or OmniDB
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
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
Investigating and reporting a bug
Icon
Producing a daily summary of log file errors
Icon
Analyzing the real-time performance of your queries
9
Regular Maintenance
Icon
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 vacuum
Icon
Maintaining indexes
Icon
Adding a constraint without checking existing rows
Icon
Finding unused indexes
Icon
Carefully removing unwanted indexes
Icon
Planning maintenance
10
Performance and Concurrency
Icon
Performance and Concurrency
Icon
Introduction
Icon
Finding slow SQL statements
Icon
Collect regular statistics from pg_stat* views
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
Using optimistic locking
Icon
Reporting performance problems
11
Backup and Recovery
Icon
Backup and Recovery
Icon
Introduction
Icon
Understanding and controlling crash recovery
Icon
Planning backups
Icon
Hot logical backups of one database
Icon
Hot logical backups of all databases
Icon
Backups of database object definitions
Icon
Standalone hot physical database backup
Icon
Hot physical backup and continuous archiving
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
Improving performance of backup/recovery
Icon
Incremental/differential backup and restore
Icon
Hot physical backups with Barman
Icon
Recovery with Barman
12
Replication and Upgrades
Icon
Replication and Upgrades
Icon
Replication concepts
Icon
Replication best practices
Icon
Setting up file-based replication – deprecated
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 synchronous replication
Icon
Delaying, pausing, and synchronizing replication
Icon
Logical replication
Icon
Bi-directional replication
Icon
Archiving transaction log data
Icon
Upgrading minor releases
Icon
Major upgrades in-place
Icon
Major upgrades online
Other Books You May Enjoy
Icon
Other Books You May Enjoy
Icon
Leave a review - let other readers know what you think
Index
Icon
Index

Detecting an in-doubt prepared transaction

While using two-phase commit (2PC), you may end up in a situation where you have something locked but cannot find a backend that holds the locks. This recipe describes how to detect such a case.

How to do it…

  1. You need to look up the pg_locks table for those entries with an empty pid value. Run this query:
SELECT t.schemaname || '.' || t.relname AS tablename,
       l.pid, l.granted
       FROM pg_locks l JOIN pg_stat_user_tables t
       ON l.relation = t.relid;
  1. The output will be something similar to the following:
tablename |  pid  | granted
-----------+-------+---------
    db.x   |       | t
    db.x   | 27289 | f
(2 rows)

The preceding example shows a lock on the db.x table, which has no process associated with it.

If you need to remove a particular prepared transaction, you can refer to the Removing old prepared transactions recipe in Chapter 9, Regular Maintenance.

Limited Time Offer

$10p/m for 3 months

Get online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech and supported with AI assistants
Claim Now Or Sign In
End of Section 11
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
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

Create a Note

Modal Close icon
You need to login to use this feature.

Delete Bookmark

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

Delete Note

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

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note