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

Getting the execution plan for a statement

In this recipe, we are going to see how to get the execution plan for a SQL statement.

Getting ready

The EXPLAIN command is used to get the execution plan for a SQL statement.

How to do it...

Every query that is triggered in PostgreSQL has an execution plan. The EXPLAIN command can be run in any of the three given modes:

  • Generic Mode: In this mode, we just need to specify the EXPLAIN command followed by the SQL statement. The PostgreSQL planner will display the execution plan that it generated for the specified SQL statement. The execution plan will show the scan method used to access the table referenced in the query. Other details included could be the estimated execution cost of the SQL statement, which is the planner's estimation of how long it will take to execute the SQL statement. The EXPLAIN command can be invoked as follows:

    dvdrental=# EXPLAIN  select * from payment where amount > 4.99;
                         QUERY PLAN
--------------...
Previous Section
End of Section 5
Next Section