PostgreSQL Administration Essentials电子书

PostgreSQL Administration Essentials

Table of Contents

PostgreSQL Administration Essentials

Credits

About the Author

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

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Installing PostgreSQL

Preparing your setup

Understanding the PostgreSQL version numbers

Choosing the right version

Installing binary packages

Installing PostgreSQL on Debian or Ubuntu

Installing PostgreSQL on Red-Hat-based systems

Compiling PostgreSQL from source

How it works

Installing the contrib packages

Finalizing your installation

Creating a database instance

Firing up PostgreSQL

Understanding the existing databases

Creating databases

Summary

2. Indexing and Performance Tuning

Using simple binary trees

Preparing the data

Understanding the concept of execution plans

Calculating costs

Drawing important conclusions

Creating indexes

Analyzing the performance of a query

The internal structure of a B-tree index

Understanding the B-tree internals

Providing a sorted order

Combined indexes

Partial indexes

Dealing with different types of indexes

Detecting missing indexes

Detecting slow queries

How to reset statistics

Adjusting memory parameters

Optimizing shared buffers

Considering huge pages

Tweaking work_mem

Improving maintenance_work_mem

Adjusting effective_cache_size

Summary

3. Users and Permissions

Understanding PostgreSQL security

Configuring the TCP

Managing network authentication

Managing contradictions

Authentication methods available

Some more examples

Handling SSL

Changing pg_hba.conf

Handling instance-level permissions

Creating roles

Modifying and dropping roles

Controlling database-level permissions

Understanding schema-level permissions

Handling table-level permissions

Managing column rights

Improving security with SELinux

Summary

4. Managing Logfiles

Understanding the PostgreSQL log architecture

Configuring log destinations

Creating local logfiles

Using syslog

Configuring logs on Windows

Performance considerations

Configuring the amount of log output

Making logs more readable

Additional settings

Making log creation more fine grained

Logging selectively

Focusing on slow queries

Silencing notices

Summary

5. Backup and Recovery

Importing and exporting data

Using the COPY command

Basic operations of the COPY command

Making use of pipes

Performing backups

Handling pg_dump

More sophisticated dumping

Performing partial replays

Passing users and passwords

Dumping an entire instance

Understanding backups and user creation

Summary

6. Handling Replication and Improving Performance

Understanding the PostgreSQL transaction log

The purpose of the transaction log

Inspecting the size of the transaction log

Configuring the checkpoints

Optimizing the checkpoints

Configuring the distance between checkpoints

Controlling writes

Setting up an asynchronous replication

Obtaining a high-level overview

Setting up replication step by step

Preparing the slave

Configuring the master

Fetching an initial backup

Creating and modifying the recovery.conf file

Firing up the slave

Turning slaves into masters

Upgrading to synchronous replication

Improving and monitoring the replication

Keeping an eye on streaming

Making things more robust

Managing conflicts

Handling point-in-time recovery

Setting up PITR

Replaying transaction logs

Understanding timelines

The importance of timelines

Summary

7. Monitoring PostgreSQL

Understanding the system statistics of PostgreSQL

Checking out the pg_stat_activity file

Monitoring databases

Monitoring tables

Monitoring indexes

Checking out the information in the background writer

Resetting statistics

Integrating Nagios

Handling Linux cgroups

Setting up cgroups

Summary

Index