Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library 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 11 Administration Cookbook
  • Table Of Contents Toc
PostgreSQL 11 Administration Cookbook

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Meesala
5 (1)
Buy this Book
close
close
PostgreSQL 11 Administration Cookbook

PostgreSQL 11 Administration Cookbook

5 (1)
By: Simon Riggs, Gianni Ciolli, Meesala
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 11 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. The book will introduce you to new features such as logical replication, native table partitioning, additional query parallelism, and much more to help you to understand and control, crash recovery and plan backups. You will learn how to tackle a variety of problems and pain points for any database administrator such as creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 11 database to help you understand roles and produce a summary of log files, ensuring high availability, concurrency, and replication. By the end of this book, you will have the necessary knowledge to manage your PostgreSQL 11 database efficiently.
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
Sections
Icon
Get in touch
Lock Free Chapter
1
First Steps
Icon
First Steps
Icon
Introducing PostgreSQL 11
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
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
Configuration
Icon
Configuration
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
Icon
Making bulk data changes using server-side procedures with transactions
6
Security
chevron up
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
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
Using pgAdmin for DBA tasks
Icon
Using OmniDB for DBA tasks
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
Icon
Monitoring and Diagnosis
Icon
Introduction
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 a 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
Finding slow SQL statements
Icon
Finding out what makes SQL slow
Icon
Collect regular statistics from pg_stat* views
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
Icon
Using optimistic locking
Icon
Reporting performance problems
11
Backup and Recovery
Icon
Backup and Recovery
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
Icon
Validating backups
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
Bidirectional replication
Icon
Archiving transaction log data
Icon
Upgrading minor releases
Icon
Major upgrades in-place
Icon
Major upgrades online
13
Other Books You May Enjoy
Icon
Other Books You May Enjoy
Icon
Leave a review - let other readers know what you think

Removing a user without dropping their data

When trying to drop a user who owns some tables or other database objects, you get the following error, and the user is not dropped:

testdb=# drop user bob;
ERROR:  role “bob” cannot be dropped because some objects depend on it
DETAIL:  owner of table bobstable
owner of sequence bobstable_id_seq

This recipe presents two solutions to this problem.

Getting ready

To modify users, you must either be a superuser or have the CREATEROLE privilege.

How to do it…

The easiest solution to this problem is to refrain from dropping the user and use the trick from a previous recipe to prevent the user from connecting:

pguser=# alter user bob nologin;
ALTER ROLE

This has the added benefit of the original owner of the table being available later, if needed, for auditing or debugging purposes (Why is this table here? Who created it?).

Then, you can assign the rights of the deleted user to a new user, using the following code:

pguser=# GRANT bob TO bobs_replacement;
GRANT

How...

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 11 Administration Cookbook
End of Section 6
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
Modal Close icon
Modal Close icon