Table of Contents
PostgreSQL 9 Administration Cookbook Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and more
Why Subscribe?
Free Access for Packt account holders
Preface
What this book covers
What you need for this book
Who this book is for
Sections
Getting ready
How to do it…
How it works…
There's more…
See also
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. First Steps
Introduction
Introducing PostgreSQL 9
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…
There's more…
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…
2. 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 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 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…
3. Configuration
Introduction
Reading The Fine Manual (RTFM)
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 nondefault 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…
Using the extension infrastructure
Without the extension infrastructure
How it works…
There's more…
Managing installed extensions
Getting ready
How to do it…
How it works…
There's more…
4. 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…
5. Tables and Data
Introduction
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…
6. 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
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 DDL changes
Getting ready
How to do it…
How it works…
There's more…
Was the change committed?
Who made the change?
Can I find this information from the database?
You may still miss some DDL…
Auditing data changes
Getting ready
How to do it…
Collecting data changes from the server log
Collecting changes using triggers
Using a single audit trigger to collect changes from multiple tables
Collecting changes using triggers and saving them in another database using dblink or plproxy
Always knowing which user is logged in
Getting ready
How to do it…
How it works…
There's more…
Not inheriting the 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 the client
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
7. 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…
Performing actions on many tables
Getting ready
How to do it…
How it works…
There's more…
Using pg_batch to run tasks in parallel
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_xlog 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…
8. Monitoring and Diagnosis
Introduction
Providing PostgreSQL information to monitoring tools
Finding more information about generic monitoring tools
Real-time viewing using pgAdmin
Checking whether a user is connected
Getting ready
How to do it…
How it works…
There's more…
What if I want to know whether that computer is connected?
What if I want to repeatedly execute a query in psql?
Checking which queries are running
Getting ready
How to do it…
How it works…
There's more…
Catching queries which 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
This catches only 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…
Trying 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…
See also
Analyzing the real-time performance of your queries
Getting ready
How to do it…
How it works…
There's more…
9. Regular Maintenance
Introduction
Controlling automatic database maintenance
Getting ready
How to do it…
How it works…
There's more…
See also
Avoiding auto-freezing and page corruptions
Getting ready
How to do it…
There's more…
Avoiding transaction wraparound
Getting ready
How to do it…
How it works…
There's more…
See also
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…
Maintaining indexes
Getting ready
How to do it…
How it works…
There's more…
See also
Adding a constraint without checking existing rows
Getting ready
How to do it…
There's more…
Finding unused indexes
How to do it…
How it works…
Carefully removing unwanted indexes
How to do it…
How it works…
Planning maintenance
How to do it…
How it works…
10. Performance and Concurrency
Introduction
Finding slow SQL statements
Getting ready
How to do it…
See also
Collecting 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
How to do it…
There's more…
The query returns too much data
Locking problems
Not enough CPU power or disk I/O capacity for the current load
EXPLAIN options
See also
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…
There's more…
In case of many updates, set fillfactor on the table
Rewriting the schema – a more radical approach
Why a query is not using an index
How to do it…
Forcing a query to use an index
Getting ready
How to do it…
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…
11. 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 backup of one database
How to do it…
How it works…
There's more…
See also
Hot logical backup of all databases
How to do it…
How it works…
See also
Hot logical backup of all tables in a tablespace
How to do it…
How it works…
Backup of database object definitions
How to do it…
There's more…
Standalone hot physical database backup
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 the 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 the custom dump taken with pg_dump -F c
Logical – from the script dump
Physical
How it works…
See also
Recovery of a dropped/damaged tablespace
How to do it…
Logical – from the custom dump taken with pg_dump -F c
Logical – from the script dump
Physical
There's more…
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…
12. Replication and Upgrades
Introduction
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
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…
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…
Index
