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

Testing remote connectivity

After configuring the network environment in PostgreSQL, it is usually a good idea to test it out.

How to do it...

You can use the psql program to test connections to the PostgreSQL server from a remote client:

D:\Postgresql_Project\bin>psql -h 192.168.12.10 hrdb agovil
Password for user agovil:
psql (9.3.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

Hrdb=>

How it works...

After enabling client authentication between the PostgreSQL server and the client application, as well as after configuring access control rules in the host's pg_hba.conf configuration file, it is a good idea to test for remote connectivity.

This will help you to find out whether the access control rules are configured correctly in the pg_hba.conf file and whether the clients face any connection errors despite being allowed...

Previous Section
End of Section 6
Next Section