PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Admin Cookbook

By : Simon Riggs, Hannu Krosing

Buy this Book

PostgreSQL 9 Admin Cookbook

By: Simon Riggs, Hannu Krosing

Buy this Book

Overview of this book

PostgreSQL is a powerful, open source object-relational database system. An enterprise database, PostgreSQL includes features such as Multi-Version Concurrency Control (MVCC), point-in-time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance. PostgreSQL 9 Admin cookbook covers everything a database administrator needs to protect, manage and run a healthy and efficient PostgreSQL 9.0 database. PostgreSQL 9 Admin Cookbook describes key aspects of the PostgreSQL open source database system. The book covers everything a sysadmin or DBA needs to protect, manage, and run a healthy and efficient PostgreSQL 9 database. This hands-on guide will assist developers working on live databases, supporting web or enterprise software applications using Java, Python, Ruby, or .Net from any development framework. It's easy to manage your database when you've got PostgreSQL 9 Admin Cookbook to hand. PostgreSQL is fast becoming one of the world's most popular server databases with an enviable reputation for performance, stability, and an enormous range of advanced features. PostgreSQL is one of the oldest open source projects, completely free to use and developed by a very diverse worldwide community. Most of all, It Just Works! PostgreSQL 9 Admin Cookbook offers the information you need to manage your live production databases on PostgreSQL. The book contains insights direct from the main author of the PostgreSQL replication and recovery features, and the database architect of the most successful startup using PostgreSQL, Skype. This practical guide gives quick answers to common questions and problems, building on the authors' experience as trainers, users, and core developers of the PostgreSQL database server. Each technical aspect is broken down into short recipes that demonstrate solutions with working code then explain why and how that works. The book is intended to be a desk reference for both new users and technical experts. The book covers all the latest features in PostgreSQL 9. Soon you will be running a smooth database with ease!

PostgreSQL 9 Administration Cookbook

Credits

About the Authors

About the Reviewers

Preface

Free Chapter

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

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Changing datatype of a column

Changing column datatypes is not an everyday task, thankfully. But when we do have to do it, we need to know all the details so that we can perform the conversion on a production system without error.

Getting ready

Let's start with a simple example table as follows:

postgres=# select * from birthday;
 name  |  dob   
-------+--------
 simon | 690926

(1 row)

It is created using the following:

CREATE TABLE birthday( name		TEXT
, dob		INTEGER);

How to do it...

Let's say we want to change the dob column to another data type. Let's try with a simple example first, which is as follows:

postgres=# ALTER TABLE birthday
postgres-# ALTER COLUMN dob SET DATA TYPE text;
ALTER TABLE

This works fine. Let's just put that back to integer, so that we can try moving to something more complex, such as a date datatype, like the following:

postgres=# ALTER TABLE birthday
postgres-# ALTER COLUMN dob SET DATA TYPE integer;
ERROR:  column "dob" cannot be cast to type integer

Oh! What went wrong...

PostgreSQL 9 Admin Cookbook

By : Simon Riggs, Hannu Krosing

PostgreSQL 9 Admin Cookbook

By: Simon Riggs, Hannu Krosing

Overview of this book

Related Content you might be interested in

Current Title:

PostgreSQL 9 Admin Cookbook

Changing datatype of a column

Getting ready

How to do it...