PacktLib: PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Administration Cookbook

Credits

About the Authors

About the Reviewers

Preface

First Steps

Introduction

Introducing PostgreSQL 9

Getting PostgreSQL

Connecting to PostgreSQL server

Enabling access for network/remote users

Using graphical administration tools

Using psql query and scripting tool

Changing your password securely

Avoiding hardcoding your password

Using a connection service file

Troubleshooting a failed connection

Exploring the Database

Introduction

What version is the server?

What is the server uptime?

Locate the database server files

Locate the database server message log

List databases on this database server?

How many tables in a database?

How much disk space does a database use?

How much disk space does a table use?

Which are my biggest tables?

How many rows in a table?

Quick estimate of the number of rows in a table

Understanding object dependencies

Configuration

Introduction

Reading the Fine Manual (RTFM)

Planning a new database

Changing parameters in your programs

What are the current configuration settings?

Which parameters are at non-default settings?

Updating the parameter file

Setting parameters for particular groups of users

Basic server configuration checklist

Adding an external module to PostgreSQL

Running server in power saving mode

Server Control

Introduction

Starting the database server manually

Stopping the server safely and quickly

Stopping the server in an emergency

Reloading the server configuration files

Restarting the server quickly

Preventing new connections

Restricting users to just one session each

Pushing users off the system

Deciding on a design for multi-tenancy

Using multiple schemas

Giving users their own private database

Running multiple servers on one system

Set up a Connection Pool

Tables & Data

Introduction

Choosing good names for database objects

Handling objects with quoted names

Enforcing same name, same column definition

Identifying and removing duplicates

Preventing duplicate rows

Finding a unique key for a set of data

Generating test data

Randomly sampling data

Loading data from a spreadsheet

Loading data from flat files

Security

Introduction

Revoking user access to a table

Granting user access to a table

Creating a new user

Temporarily preventing a user from connecting

Removing a user without dropping their data

Checking all users have a secure password

Giving limited superuser powers to specific users

Auditing DDL changes

Auditing data changes

Integrating with LDAP

Connecting using SSL

Encrypting sensitive data

Database Administration

Introduction

Writing a script that either all succeeds or all fails

Writing a psql script that exits on first error

Performing actions on many tables

Adding/Removing the columns of a table

Changing datatype of a column

Adding/Removing schemas

Moving objects between schemas

Adding/Removing tablespaces

Moving objects between tablespaces

Accessing objects in other PostgreSQL databases

Making views updateable

Monitoring and Diagnosis

Introduction

Is the user connected?

What are they running?

Are they active or blocked?

Who is blocking them?

Killing a specific session

Resolving an in-doubt prepared transaction

Is anybody using a specific table?

When did anybody last use it?

How much disk space is used by temporary data?

Why are my queries slowing down?

Investigating and reporting a bug

Producing a daily summary of logfile errors

Regular Maintenance

Introduction

Controlling automatic database maintenance

Avoiding auto freezing and page corruptions

Avoiding transaction wraparound

Removing old prepared transactions

Actions for heavy users of temporary tables

Identifying and fixing bloated tables and indexes

Maintaining indexes

Finding the unused indexes

Carefully removing unwanted indexes

Planning maintenance

Performance & Concurrency

Introduction

Finding slow SQL statements

Collecting regular statistics from pg_stat* views

Finding what makes SQL slow

Reducing the number of rows returned

Simplifying complex SQL

Speeding up queries without rewriting them

Why is my query not using an index?

How do I force a query to use an index

Using optimistic locking

Reporting performance problems

Backup & Recovery

Introduction

Understanding and controlling crash recovery

Planning backups

Hot logical backup of one database

Hot logical backup of all databases

Hot logical backup of all tables in a tablespace

Backup of database object definitions

Standalone hot physical database backup

Hot physical backup & Continuous Archiving

Recovery of all databases

Recovery to a point in time

Recovery of a dropped/damaged table

Recovery of a dropped/damaged tablespace

Recovery of a dropped/damaged database

Improving performance of backup/restore

Incremental/Differential backup and restore

Replication & Upgrades

Introduction

Understanding replication concepts

Replication best practices

File-based log-shipping replication

Setting up streaming log replication

Managing log shipping replication

Managing Hot Standby

Selective replication using Londiste

Selective replication using Slony 2.0

Load balancing with pgpool-II 3.0

Upgrading (minor)

Major upgrades in-place

Major upgrades online using replication tools

Index