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

About the Reviewers

Naoya Hashimoto has been working on system design and integration with open source software for years. Recently, his career and interest have shifted toward cloud engineering on both public and hybrid clouds, such as AWS, as well as toward orchestration tools, such as Chef or CloudFormation. He has reviewed the books Icinga Network Monitoring and Building a Home Security System with BeagleBone, both by Packt Publishing. Moreover, currently he is a technical reviewer of the book Building Networks and Servers Using Beaglebone, which is also by Packt Publishing.

Thanks to the author and the project coordinator, Purav, who gave me this opportunity to review the book. I am very impressed with their work and this project because it gives us a chance to learn about the latest technology of PostgreSQL 9.x.

Sergio Martínez-Losa Del Rincón is a computer engineer who loves programming languages since the time he was in high school, where he learned about programming and computer interactions. He is always learning and discovers something new to learn everyday.

He likes all kind of programming languages, but he focuses his efforts on mobile development with native languages, such as Objective-C (iPhone), Java (Android), and Xamarin (C#). He builds Google Glass applications as well as mobile applications for iPhone and Android devices at work. He also develops games for mobile devices with cocos2d-x and cocos2d. He likes cross-platform applications as well. He has reviewed Learning Xamarin Studio, Packt Publishing.

He loves challenging problems, and he is always keen to work with new technologies. More information about his experience and details can be found at www.linkedin.com/in/sergiomtzlosa.

Danny Sauer has been a professional Unix geek of various stripes for roughly 20 years, most recently in the flavor of security engineer. His experience with open source databases extends through most of that time period, both as DBA and as a user. He currently lives with his wife in an old house in a small town outside of a small city, which provides plenty of opportunity to restore antique houses, cars, and computers.

Previous Section
Next Chapter