PostgreSQL 11 Administration Cookbook
更新时间：2021-06-24 14:37:10
最新章节：Leave a review - let other readers know what you thinkcoverpage
Title Page
Copyright and Credits
PostgreSQL 11 Administration Cookbook
About Packt
Why subscribe?
Packt.com
Contributors
About the authors
About the reviewers
Packt is searching for authors like you
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
Getting ready
How to do it…
How it works…
There's more…
See also
Get in touch
Reviews
First Steps
Introducing PostgreSQL 11
What makes PostgreSQL different?
Robustness
Security
Ease of use
Extensibility
Performance and concurrency
Scalability
SQL and NoSQL data models
Popularity
Commercial support
Research and development funding
Getting PostgreSQL
How to do it...
How it works...
There's more…
Connecting to the PostgreSQL server
Getting ready
How to do it…
How it works…
There's more…
See also
Enabling access for network/remote users
How to do it…
How it works…
There's more…
See also
Using graphical administration tools
How to do it…
How it works…
OmniDB
How to do it…
See also
Using the psql query and scripting tool
Getting ready
How to do it…
How it works…
There's more…
See also
Changing your password securely
How to do it…
How it works…
Avoiding hardcoding your password
Getting ready
How to do it…
How it works…
There's more…
Using a connection service file
How to do it…
How it works…
Troubleshooting a failed connection
How to do it…
There's more…
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 also
Locating the database server files
Getting ready
How to do it...
How it works...
There's more…
Locating the database server's message log
Getting ready
How to do it...
How it works...
There's more...
See also
Locating the database's system identifier
Getting ready
How to do it…
How it works…
Listing databases on the database server
How 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 table
How to do it…
How it works…
There's more…
Listing extensions in this database
How to do it…
How it works…
There's more…
See also
Understanding object dependencies
Getting ready
How to do it…
How it works…
There's more…
Configuration
Reading the fine manual
How to do it…
How it works…
There's more…
Planning a new database
Getting ready
How to do it…
How it works…
There's more…
Changing parameters in your programs
How to do it…
How it works…
There's more…
Finding the current configuration settings
How to do it…
How it works…
Which parameters are at non-default settings?
How to do it…
How it works...
There's more...
Updating the parameter file
Getting ready
How to do it…
How it works…
There's more…
Setting parameters for particular groups of users
How to do it…
How it works…
The basic server configuration checklist
Getting ready
How to do it…
There's more…
Adding an external module to PostgreSQL
Getting ready
How to do it…
Installing modules using a software installer
Installing modules from PGXN
Installing modules from source code
How it works...
Using an installed module
Getting ready
How to do it…
How it works...
Managing installed extensions
How to do it…
How it works…
There's more…
Server Control
Introduction
Starting the database server manually
Getting ready
How to do it…
How it works…
Stopping the server safely and quickly
How to do it…
How it works…
See also
Stopping the server in an emergency
How to do it…
How it works…
Reloading the server configuration files
How to do it…
How it works…
There's more…
Restarting the server quickly
How to do it…
There's more…
Preventing new connections
How to do it…
How it works…
Restricting users to only one session each
How to do it…
How it works…
Pushing users off the system
How to do it…
How it works…
Deciding on a design for multitenancy
How to do it…
How it works…
Using multiple schemas
Getting ready
How to do it…
How it works…
Giving users their own private database
Getting ready
How to do it…
How it works…
There's more…
See also
Running multiple servers on one system
Getting ready
How to do it…
How it works…
Setting up a connection pool
Getting ready
How to do it…
How it works…
There's more…
Accessing multiple servers using the same host and port
Getting ready
How to do it…
There's more…
Tables and Data
Choosing good names for database objects
Getting ready
How to do it…
There's more…
Handling objects with quoted names
Getting ready
How to do it...
How it works…
There's more…
Enforcing the same name and definition for columns
Getting ready
How to do it...
How it works…
There's more…
Identifying and removing duplicates
Getting ready
How to do it…
How it works…
There's more…
Preventing duplicate rows
Getting ready
How to do it…
How it works…
There's more...
Duplicate indexes
Uniqueness without indexes
Real-world example – IP address range allocation
Real-world example – range of time
Real-world example – prefix ranges
Finding a unique key for a set of data
Getting ready
How to do it…
How it works…
Generating test data
How to do it...
How it works…
There's more…
See also
Randomly sampling data
How to do it…
How it works...
Loading data from a spreadsheet
Getting ready
How to do it...
How it works...
There's more...
Loading data from flat files
Getting ready
How to do it...
How it works…
There's more…
Making bulk data changes using server-side procedures with transactions
How to do it…
There's more…
Security
Introduction
Typical user role
The PostgreSQL superuser
How to do it…
How it works…
There's more…
Other superuser-like attributes
Attributes are never inherited
See also
Revoking user access to a table
Getting ready
How to do it…
How it works…
There's more…
Database creation scripts
Default search path
Securing views
Granting user access to a table
Getting ready
How to do it…
How it works...
There's more…
Granting user access to specific columns
Getting ready
How to do it…
How it works…
There's more…
Granting user access to specific rows
Getting ready
How to do it…
How it works…
There's more...
Creating a new user
Getting ready
How to do it...
How it works…
There's more…
Temporarily preventing a user from connecting
Getting ready
How to do it…
How it works...
There's more…
Limiting the number of concurrent connections by a user
Forcing NOLOGIN users to disconnect
Removing a user without dropping their data
Getting ready
How to do it…
How it works…
Checking whether all users have a secure password
How to do it…
How it works…
Giving limited superuser powers to specific users
Getting ready
How to do it…
How it works…
There's more…
Writing a debugging_info function for developers
Auditing database access
Getting ready
Auditing SQL
Auditing table access
Managing the audit log
Auditing data changes
Always knowing which user is logged in
Getting ready
How to do it…
How it works…
There's more…
Not inheriting user attributes
Integrating with LDAP
Getting ready
How to do it…
How it works…
There's more…
Setting up the client to use LDAP
Replacement for the User Name Map feature
See also
Connecting using SSL
Getting ready
How to do it…
How it works…
There's more…
Getting the SSL key and certificate
Setting up a client to use SSL
Checking server authenticity
Using SSL certificates to authenticate
Getting ready
How to do it…
How it works…
There's more…
Avoiding duplicate SSL connection attempts
Using multiple client certificates
Using the client certificate to select the database user
See also
Mapping external usernames to database roles
Getting ready
How to do it…
How it works…
There's more…
Encrypting sensitive data
Getting ready
How to do it…
How it works…
There's more…
For really sensitive data
For really really really sensitive data
See also
Database Administration
Writing a script that either succeeds entirely or fails entirely
How to do it…
How it works…
There's more…
Writing a psql script that exits on the first error
Getting ready
How to do it…
How it works…
There's more…
Using psql variables
Getting ready
How to do it…
How it works…
There's more…
Placing query output into psql variables
Getting ready
How to do it…
How it works…
There's more…
Writing a conditional psql script
Getting ready
How to do it…
How it works…
There's more…
Investigating a psql error
Getting ready
How to do it…
There's more…
Using pgAdmin for DBA tasks
Getting ready
How to do it…
How it works...
There's more
Using OmniDB for DBA tasks
Getting ready
How to do it...
How it works
There's more...
Performing actions on many tables
Getting ready
How to do it…
How it works…
There's more…
Adding/removing columns on a table
How to do it…
How it works…
There's more…
Changing the data type of a column
Getting ready
How to do it…
How it works…
There's more…
Changing the definition of a data type
Getting ready
How to do it…
How it works…
There's more…
Adding/removing schemas
How to do it…
There's more…
Using schema-level privileges
Moving objects between schemas
How to do it…
How it works…
There's more…
Adding/removing tablespaces
Getting ready
How to do it…
How it works…
There's more…
Putting pg_wal on a separate device
Tablespace-level tuning
Moving objects between tablespaces
Getting ready
How to do it…
How it works…
There's more…
Accessing objects in other PostgreSQL databases
Getting ready
How to do it…
How it works…
There's more…
Accessing objects in other foreign databases
Getting ready
How to do it…
How it works…
There's more…
Updatable views
Getting ready
How to do it…
How it works…
There's more…
Using materialized views
Getting ready
How to do it…
How it works…
There's more…
Monitoring and Diagnosis
Introduction
Providing PostgreSQL information to monitoring tools
Finding more information about generic monitoring tools
Real-time viewing using pgAdmin or OmniDB
Getting ready
How to do it…
Using pgAdmin
Using OmniDB
Checking whether a user is connected
Getting ready
How to do it…
How it works…
There's more…
Checking whether a computer is connected
How to do it…
There's more…
Repeatedly executing a query in psql
How to do it…
There's more…
Checking which queries are running
Getting ready
How to do it…
How it works…
There's more…
Catching queries that only run for a few milliseconds
Watching the longest queries
Watching queries from ps
See also
Checking which queries are active or blocked
Getting ready
How to do it…
How it works…
There's more…
Knowing who is blocking a query
Getting ready
How to do it…
How it works…
Killing a specific session
How to do it…
How it works…
There's more…
Try to cancel the query first
What if the backend won't terminate?
Using statement_timeout to clean up queries that take too long to run
Killing idle in-transaction queries
Killing the backend from the command line
Detecting an in-doubt prepared transaction
How to do it…
Knowing whether anybody is using a specific table
Getting ready
How to do it…
How it works…
There's more...
The quick-and-dirty way
Collecting daily usage statistics
Knowing when a table was last used
Getting ready
How to do it…
How it works...
There's more…
Usage of disk space by temporary data
Getting ready
How to do it…
How it works…
There's more…
Finding out whether a temporary file is in use anymore
Logging temporary file usage
Understanding why queries slow down
Getting ready
How to do it…
How it works…
There's more…
Do queries return significantly more data than they did earlier?
Do queries also run slowly when they run alone?
Is the second run of the same query also slow?
Table and index bloat
See also
Investigating and reporting a bug
Getting ready
How to do it…
How it works…
Producing a daily summary of log file errors
Getting ready
How to do it…
How it works…
There's more…
Analyzing the real-time performance of your queries
Getting ready
How to do it…
How it works…
There's more…
Regular Maintenance
Controlling automatic database maintenance
Getting ready
How to do it…
How it works…
There's more…
See also
Avoiding auto-freezing and page corruptions
How to do it…
Removing issues that cause bloat
Getting ready
How to do it…
How it works…
There's more…
Removing old prepared transactions
Getting ready
How to do it…
How it works…
There's more…
Actions for heavy users of temporary tables
How to do it…
How it works…
Identifying and fixing bloated tables and indexes
Getting ready
How to do it…
How it works…
There's more…
Monitoring and tuning a vacuum
Getting ready
How to do it…
How it works…
There's more…
Maintaining indexes
Getting ready
How to do it…
How it works…
There's more…
Adding a constraint without checking existing rows
Getting ready
How to do it…
How it works…
Finding unused indexes
How to do it…
How it works…
Carefully removing unwanted indexes
Getting ready
How to do it…
How it works…
Planning maintenance
How to do it…
How it works…
There's more…
Performance and Concurrency
Finding slow SQL statements
Getting ready
How to do it…
How it works…
There's more…
Finding out what makes SQL slow
Getting ready
How to do it…
There's more…
Not enough CPU power or disk I/O capacity for the current load
Locking problems
EXPLAIN options
See also
Collect regular statistics from pg_stat* views
Getting ready
How to do it…
How it works…
There's more…
Another statistics collection package
Reducing the number of rows returned
How to do it…
There's more…
Simplifying complex SQL queries
Getting ready
How to do it…
There's more…
Using materialized views (long-living temporary tables)
Using set-returning functions for some parts of queries
Speeding up queries without rewriting them
How to do it…
Increasing work_mem
More ideas with indexes
There's more…
Time series partitioning
Using a TABLESAMPLE view
In case of many updates set fillfactor on the table
Rewriting the schema – a more radical approach
Discovering why a query is not using an index
Getting ready
How to do it…
How it works…
There's more…
Forcing a query to use an index
Getting ready
How to do it…
There's more…
There's more
Using parallel query
How to do it…
How it works…
There's more…
Creating time series tables
How to do it…
How it works…
There's more…
Using optimistic locking
How to do it…
How it works…
There's more…
Reporting performance problems
How to do it…
There's more…
Backup and Recovery
Understanding and controlling crash recovery
How to do it…
How it works…
There's more…
Planning backups
How to do it…
Hot logical backups of one database
How to do it…
How it works…
There's more…
See also
Hot logical backups of all databases
How to do it…
How it works…
See also
Backups of database object definitions
How to do it…
There's more…
Standalone hot physical database backup
Getting ready
How to do it…
How it works…
There's more…
See also
Hot physical backup and continuous archiving
Getting ready
How to do it…
How it works…
Recovery of all databases
Getting ready
How to do it…
Logical – from custom dump taken with pg_dump -F c
Logical – from the script dump created by pg_dump -F p
Logical – from the script dump created by pg_dumpall
Physical
How it works…
There's more…
See also
Recovery to a point in time
Getting ready
How to do it…
How it works…
There's more…
See also
Recovery of a dropped/damaged table
How to do it…
Logical – from custom dump taken with pg_dump -F c
Logical – from the script dump
Physical
How it works…
See also
Recovery of a dropped/damaged database
How to do it…
Logical – from the custom dump -F c
Logical – from the script dump created by pg_dump
Logical – from the script dump created by pg_dumpall
Physical
Improving performance of backup/recovery
Getting ready
How to do it…
How it works…
There's more…
See also
Incremental/differential backup and restore
How to do it…
How it works…
There's more…
Hot physical backups with Barman
Getting ready
How to do it…
How it works…
There's more…
Recovery with Barman
Getting ready
How to do it…
How it works…
There's more…
Validating backups
Getting ready
How to do it…
How it works…
There's more…
Replication and Upgrades
Replication concepts
Topics
Basic concepts
History and scope
Practical aspects
Data loss
Single-master replication
Multinode architectures
Clustered or massively parallel databases
Multimaster replication
Scalability tools
Other approaches to replication
Replication best practices
Getting ready
How to do it…
There's more…
Setting up file-based replication – deprecated
Getting ready
How to do it…
How it works…
There's more…
See also
Setting up streaming replication
Getting ready
How to do it…
How it works…
There's more…
Setting up streaming replication security
Getting ready
How to do it…
How it works…
There's more…
Hot standby and read scalability
Getting ready
How to do it…
How it works…
Managing streaming replication
Getting ready
How to do it…
There's more…
See also
Using repmgr
Getting ready
How to do it…
How it works…
There's more…
Using replication slots
Getting ready
How to do it…
There's more…
See also
Monitoring replication
Getting ready
How to do it…
There's more…
Performance and synchronous replication
Getting ready
How to do it...
How it works…
There's more…
Delaying pausing and synchronizing replication
Getting ready
How to do it…
There's more…
See also
Logical replication
Getting ready
How to do it…
How it works…
There's more…
See also
Bidirectional replication
Getting ready
How to do it…
How it works...
There's more…
Archiving transaction log data
Getting ready
How to do it…
There's more…
See also
Upgrading minor releases
Getting ready
How to do it…
How it works…
There's more…
Major upgrades in-place
Getting ready
How to do it…
How it works…
Major upgrades online
How to do it...
How it works...
Other Books You May Enjoy
Leave a review - let other readers know what you think
更新时间：2021-06-24 14:37:10