Book Image

PostgreSQL Cookbook

By : Chitij Chauhan
Book Image

PostgreSQL Cookbook

By: Chitij Chauhan

Overview of this book

<p>PostgreSQL is an open source database management system. It is used for a wide variety of development practices such as software and web design, as well as for handling large datasets (big data).</p> <p>With the goal of teaching you the skills to master PostgreSQL, the book begins by giving you a glimpse of the unique features of PostgreSQL and how to utilize them to solve real-world problems. With the aid of practical examples, the book will then show you how to create and manage databases. You will learn how to secure PostgreSQL, perform administration and maintenance tasks, implement high availability features, and provide replication. The book will conclude by teaching you how to migrate information from other databases to PostgreSQL.</p>

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL Cookbook
Chitij Chauhan
Jan, 2015 | 286 pages
No titles found
Table of Contents (19 chapters)
PostgreSQL Cookbook
PostgreSQL Cookbook
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Managing Databases and the PostgreSQL Server
Managing Databases and the PostgreSQL Server
Introduction
Creating databases
Creating schemas
Creating users
Creating groups
Destroying databases
Creating and dropping tablespaces
Moving objects between tablespaces
Initializing a database cluster
Starting the server
Stopping the server
Displaying the server status
Reloading the server configuration files
Terminating connections
2
Controlling Security
Controlling Security
Introduction
Securing database objects
Controlling access via firewalls
Controlling access via configuration files
Testing remote connectivity
Auditing database changes
Enabling SSL in PostgreSQL
Testing SSL encryption
Encrypting confidential data
Cracking PostgreSQL passwords
3
Backup and Recovery
Backup and Recovery
Introduction
A logical backup of a single PostgreSQL database
A logical backup of all PostgreSQL databases
A logical backup of specific objects
File system level backup
Taking a base backup
Hot physical backup and continuous archiving
Point-in-time recovery
Restoring databases and specific database objects
4
Routine Maintenance Tasks
Routine Maintenance Tasks
Introduction
Controlling automatic database maintenance
Preventing auto freeze and page corruption
Preventing transaction ID wraparound failures
Updating planner statistics
Dealing with bloating tables and indexes
Monitoring data and index pages
Routine reindexing
Maintaining log files
5
Monitoring the System Using Unix Utilities
Monitoring the System Using Unix Utilities
Introduction
Monitoring CPU usage
Monitoring paging and swapping
Finding the worst user on the system
Monitoring system load
Identifying CPU bottlenecks
Identifying disk I/O bottlenecks
Monitoring system performance
Examining historical CPU load
Examining historical memory load
Monitoring disk space usage
Monitoring network status
6
Monitoring Database Activity and Investigating Performance Issues
Monitoring Database Activity and Investigating Performance Issues
Introduction
Checking active sessions
Finding out what queries users are currently running
Getting the execution plan for a statement
Logging slow statements
Collecting statistics
Monitoring database load
Finding blocking sessions
Table access statistics
Finding unused indexes
Forcing a query to use an index
Determining disk usage
7
High Availability and Replication
High Availability and Replication
Introduction
Setting up hot streaming replication
Replication using Slony-I
Replication using Londiste
Replication using Bucardo
Replication using DRBD
Setting up the Postgres-XC cluster
8
Connection Pooling
Connection Pooling
Introduction
Installing pgpool
Configuring pgpool and testing the setup
Starting and stopping pgpool
Setting up pgbouncer
Connection pooling using pgbouncer
Managing pgbouncer
9
Table Partitioning
Table Partitioning
Introduction
Implementing partitioning
Managing partitions
Partitioning and constraint exclusion
Alternate partitioning methods
Installing PL/Proxy
Partitioning with PL/Proxy
10
Accessing PostgreSQL from Perl
Accessing PostgreSQL from Perl
Introduction
Making a connection to a PostgreSQL database using Perl
Creating tables using Perl
Inserting records using Perl
Accessing table data using Perl
Updating records using Perl
Deleting records using Perl
11
Accessing PostgreSQL from Python
Accessing PostgreSQL from Python
Introduction
Making connections to a PostgreSQL database using Python
Creating tables using Python
Inserting records using Python
Accessing table data using Python
Updating records using Python
Deleting records using Python
12
Data Migration from Other Databases and Upgrading the PostgreSQL Cluster
Data Migration from Other Databases and Upgrading the PostgreSQL Cluster
Introduction
Using pg_dump to upgrade data
Using the pg_upgrade utility for a version upgrade
Replicating data from other databases to PostgreSQL using GoldenGate
Index
Index

PostgreSQL Cookbook

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: January 2015

Production reference: 1240115

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78355-533-8

www.packtpub.com

Previous Section
Next Chapter