PostgreSQL 9 Administration Cookbook - Second Edition电子书

PostgreSQL 9 Administration Cookbook Second Edition

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