PostgreSQL Cookbook

Over 90 hands-on recipes to effectively manage, administer, and design solutions using PostgreSQL

PostgreSQL Cookbook

Cookbook
Chitij Chauhan

Over 90 hands-on recipes to effectively manage, administer, and design solutions using PostgreSQL
$26.99
$44.99
RRP $26.99
RRP $44.99
eBook
Print + eBook
$29.99 p/month

Join FREE for a month

Instantly access this course right now and get the skills you need in 2017

With unlimited access to a constantly growing library of over 4,000 eBooks and Videos, a subscription to Mapt gives you everything you need to get that next promotion or to land that dream job. Cancel anytime.

Code Files
Free Sample

Book Details

ISBN 139781783555338
Paperback286 pages

Book Description

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).

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.

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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
Chapter 8: Connection Pooling
Introduction
Installing pgpool
Configuring pgpool and testing the setup
Starting and stopping pgpool
Setting up pgbouncer
Connection pooling using pgbouncer
Managing pgbouncer
Chapter 9: Table Partitioning
Introduction
Implementing partitioning
Managing partitions
Partitioning and constraint exclusion
Alternate partitioning methods
Installing PL/Proxy
Partitioning with PL/Proxy
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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

What You Will Learn

  • Perform regular maintenance tasks to keep your database steady and achieve optimal performance
  • Design and implement various high availability and replication features to provide redundancy, fault tolerance, and failover
  • Diagnose and troubleshoot CPU, memory, and I/O related database performance issues
  • Perform database operations using languages such as Perl and Python
  • Discover the different backup and recovery strategies that can be implemented in PostgreSQL
  • Implement connection pooling methods to achieve load balancing

Authors

Table of Contents

Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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
Chapter 8: Connection Pooling
Introduction
Installing pgpool
Configuring pgpool and testing the setup
Starting and stopping pgpool
Setting up pgbouncer
Connection pooling using pgbouncer
Managing pgbouncer
Chapter 9: Table Partitioning
Introduction
Implementing partitioning
Managing partitions
Partitioning and constraint exclusion
Alternate partitioning methods
Installing PL/Proxy
Partitioning with PL/Proxy
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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

Book Details

ISBN 139781783555338
Paperback286 pages
Read More

Read More Reviews

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 Server Programming
$ 29.99
Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.
PostgreSQL 9 Admin Cookbook
$ 29.99
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.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 Server Programming - Second Edition
$ 29.99
Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language
PostgreSQL Developer's Guide
$ 26.99
Design, develop, and implement streamlined databases with PostgreSQL
Nginx Module Extension
$ 20.99
Customize and regulate the robust Nginx web server, and write your own Nginx modules efficiently