Log In
Or create an account -> 
Imperial Library
  • Home
  • About
  • News
  • Upload
  • Forum
  • Help
  • Login/SignUp

Index
Title Page Copyright and Credits
PostgreSQL 10 Administration Cookbook
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
  • ← Prev
  • Back
  • Next →
  • ← Prev
  • Back
  • Next →

Chief Librarian: Las Zenow <zenow@riseup.net>
Fork the source code from gitlab.

This is a mirror of the Tor onion service:
http://kx5thpx2olielkihfyo4jgjqfb7zx7wxr3sd4xzt26ochei4m6f7tayd.onion