PostgreSQL 9 Admin Cookbook

By working through the recipes in this book, you can soon be running a more efficient database. Bring your PostreSQL know-how up to date in record time with a hands-on guide that covers all the vital techniques.

PostgreSQL 9 Admin Cookbook

Cookbook
Simon Riggs, Hannu Krosing

By working through the recipes in this book, you can soon be running a more efficient database. Bring your PostreSQL know-how up to date in record time with a hands-on guide that covers all the vital techniques.
$29.99
$49.99
RRP $29.99
RRP $49.99
eBook
Print + eBook
$12.99 p/month

Get Access

Get Unlimited Access to every Packt eBook and Video course

Enjoy full and instant access to over 3000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Code Files
+ Collection
Free Sample

Book Details

ISBN 139781849510288
Paperback360 pages

Book Description

<p>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.</p> <p>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.</p> <p>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!</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>The book covers all the latest features in PostgreSQL 9. Soon you will be running a smooth database with ease!</p>

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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
Chapter 8: 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
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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

What You Will Learn

<ul> <li>How to implement PostgreSQL features for reliability and performance</li> <li>How to use the latest PostgreSQL 9.0 features</li> <li>Summaries to help DBAs with experience of other database systems</li> <li>Advanced technical tips and scripts for experienced users</li> <li>Best practice for planning and designing live databases</li> <li>How and when to VACUUM your databases</li> <li>Selecting and implementing robust backup techniques</li> <li>Concise and clear guidance on replication and high availability</li> </ul>

Authors

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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
Chapter 8: 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
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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

Book Details

ISBN 139781849510288
Paperback360 pages
Read More

DEAL OF THE DAY

Sign up here to get exclusive deep discounts on our latest and bestselling eBooks, delivered straight to your inbox every day.

FREE LEARNING FOREVER

Master new IT skills and unlock valuable software & web development knowledge with a FREE eBook every day.

Recommended for You

PostgreSQL 9.0 High Performance
$ 29.99
If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.
PostgreSQL Replication
$ 29.99
Replication is an advanced subject but this tutorial makes it so simple to assimilate thanks to a carefully structured approach based on examples and screenshots. As a PostrgreSQL administrator it will expand your horizons.
CentOS 6 Linux Server Cookbook
$ 29.99
An all-in-one guide to installing, configuring, and running a Centos 6 server. Ideal for newbies and old-hands alike, this practical tutorial ensures you get the best from this popular, enterprise-class free server solution.
PostGIS Cookbook
$ 29.99
For web developers and software architects this book will provide a vital guide to the tools and capabilities available to PostGIS spatial databases. Packed with hands-on recipes and powerful concepts
High Availability MySQL Cookbook
$ 26.99
There’s more than one way to achieve high availability for MySQL and this Cookbook covers a range of techniques and tools in over 60 practical recipes. The only book of its kind, you’ll be learning the natural, engaging way.
PostgreSQL for Data Architects
$ 39.99
Discover how to design, develop, and maintain your database application effectively with PostgreSQL