PostgreSQL 10 Administration Cookbook
Simon Riggs Gianni Ciolli更新时间：2021-06-25 22:06:02
最新章节：Leave a review - let other readers know what you think封面
版权信息
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the authors
About the reviewer
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 example code files
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
Introduction
Introducing PostgreSQL 10
What makes PostgreSQL different?
Robustness
Security
Ease of use
Extensibility
Performance and concurrency
Scalability
SQL and NoSQL
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
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
Introduction
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...
Locating the database's system identifier
Getting ready
How to do it…
How it works…
Listing databases on this 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…
Function 1 – Estimating the number of rows
Function 2 – Computing the size of a table without locks
Listing extensions in this database
Getting ready
How to do it…
How it works…
There's more…
Understanding object dependencies
Getting ready
How to do it…
How it works…
There's more…
Configuration
Introduction
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…
There's more…
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 a manually downloaded package
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…
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…
Access to the schema
Granting access to a table through a group role
Granting access to all objects in a schema
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
Introduction
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…
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…
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
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… (with pgAdmin)
How to do it… (with 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…
No need for the = true part
Do we catch all queries waiting on locks?
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 any more
Logging temporary file usage
Understanding why queries slow down
Getting ready
How to do it…
How it works…
There's more…
Do the queries return significantly more data than they did earlier?
Do the queries also run slowly when they are 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
How to do it…
How it works…
There's more…
Monitoring and tuning 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…
Performance and Concurrency
Introduction
Finding slow SQL statements
Getting ready
How to do it…
How it works…
There's more…
Collect regular statistics from pg_stat* views
Getting ready
How to do it…
How it works…
There's more…
Another statistics collection package
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
Reducing the number of rows returned
How to do it…
There's more…
See also
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 even more
Using parallel query
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
Introduction
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…
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
Bi-directional 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…
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-25 22:06:02