PostgreSQL Administration Cookbook

547 Pages·2017·4.3 MB·English

by  Simon Riggs

PostgreSQL Administration Cookbook
9.5/9.6 Edition

Effective database management using PostgreSQL 9.5/9.6

Simon Riggs
Gianni Ciolli
Gabriele Bartolini
BIRMINGHAM - MUMBAI
PostgreSQL Administration Cookbook
9.5/9.6 Edition
Copyright © 2017 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 authors, 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: April 2017
Production reference: 1250417
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78588-318-7
www.packtpub.com
Credits
Authors Copy Editor
Simon Riggs Safis Editing
Gianni Ciolli
Gabriele Bartolini
Reviewer Project Coordinator
Sheldon Strauch Shweta H Birwatkar
Commissioning Editor Proofreader
Amey Varangaonkar Safis Editor
Acquisition Editor Indexer

Ajith Menon Aishwarya Gangawane
Content Development Editor Graphics

Amrita Noronha Tania Dutta
Technical Editor Production Coordinator

Sneha Hanchate Melwyn Dsa
About the Authors
Simon Riggs is the CTO of 2ndQuadrant and an active PostgreSQL committer. He has
contributed to PostgreSQL as a major developer for more than 12 years, having written and
designed many new features in every release over that period. His feature credits include
replication, performance, business intelligence, management, and security. Under his
guidance, 2ndQuadrant is now a leading developer of open source PostgreSQL and a
platinum sponsor of the PostgreSQL Project, serving hundreds of clients in USA, Europe,
Asia-Pacific, the Middle East, and Africa. Simon is a frequent speaker at many conferences
and is well known for his speeches on PostgreSQL Futures and different aspects of
replication. He has worked with many different databases as a developer, architect, data
analyst, and designer with companies across USA and Europe for nearly 30 years.
Gianni Ciolli is the head of professional services at 2ndQuadrant. He has spoken at
PostgreSQL conferences in Europe and abroad, and his other IT skills include functional
languages and symbolic computing. Gianni has a PhD in mathematics, and is the author of
published research on Algebraic Geometry, Theoretical Physics, and Formal Proof Theory.
He previously worked at the University of Florence as a researcher and teacher. Gianni has
been working on free and open source software for almost 20 years. From 2001 to 2004, he
was a cofounder and the president of PLUG, short for Prato Linux User Group. He
organized many sessions of the Italian PostgreSQL conference, and in 2013, he was elected
to the board of ITPUG, the Italian PostgreSQL Users Group. He currently lives in London
with his son. His other interests include music, drama, poetry, and sport-athletics in
particular, where he competes in combined events.
Gabriele Bartolini is a long-time open source programmer, now head of support at
2ndQuadrant, and an active member of the international PostgreSQL community. Gabriele
has a degree in statistics from the University of Florence. His areas of expertise are data
mining and data warehousing, and he has worked on web traffic analysis in Australia and
Italy. He currently lives in Prato, a small but vibrant city located in the northern part of
Tuscany, Italy. His second home is Melbourne, Australia, where he studied at Monash
University and worked in the ICT sector. Gabriele's hobbies include playing his Fender
Stratocaster electric guitar and "calcio" (football or soccer, depending on which part of the
world you come from).
About the Reviewer
Sheldon Strauch is a 22-year veteran of software consulting at companies such as IBM,
Sears, Ernst & Young, and Kraft Foods. He has a Bachelor's degree in business
administration and leverages his technical skills to improve the business' self-awareness.
His interests include data gathering, management, and mining; maps and mapping;
business intelligence; and application of data analysis for continuous improvement. He is
currently focused on development of end-to-end data management and mining at Enova
International, a financial services company located in Chicago. In his spare time, he enjoys
the performing arts, particularly music, and traveling with his wife, Marilyn.
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a
print book customer, you are entitled to a discount on the eBook copy. Get in touch with us
at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a
range of free newsletters and receive exclusive discounts and offers on Packt books and
eBooks.
https://www.packtpub.com/mapt

Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt
books and video courses, as well as industry-leading tools to help you plan your personal
development and advance your career.
Why subscribe?
Fully searchable across every book published by Packt
Copy and paste, print, and bookmark content
On demand and accessible via a web browser
Customer Feedback
Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial
process. To help us improve, please leave us an honest review on this book's Amazon page
at https://www.amazon.com/dp/1785883186.
If you'd like to join our team of regular reviewers, you can e-mail us at
[email protected]. We award our regular reviewers with free eBooks and
videos in exchange for their valuable feedback. Help us be relentless in improving our
products
Table of Contents
Preface
1
Chapter 1: First Steps
9
Introduction 9
Introducing PostgreSQL 9.6 9
What makes PostgreSQL different? 10
Robustness 12
Security 12
Ease of use 13
Extensibility 13
Performance and concurrency 14
Scalability 14
SQL and NoSQL 14
Popularity 15
Commercial support 15
Research and development funding 16
Getting PostgreSQL 16
How to do it... 16
How it works... 17
There's more... 17
Connecting to the PostgreSQL server 18
Getting ready 18
How to do it... 18
How it works... 19
There's more... 20
See also 21
Enabling access for network/remote users 21
How to do it... 21
How it works... 22
There's more... 24
See also 24
Using graphical administration tools 24
How to do it... 24
How it works... 27
See also 28
Using the psql query and scripting tool 28
Getting ready 29
How to do it... 29
How it works... 31
There's more... 32
See also 32
Changing your password securely 33
How to do it... 33
How it works... 33
Avoiding hardcoding your password 34
Getting ready 34
How to do it... 34
How it works... 35
There's more... 35
Using a connection service file 36
How to do it... 36
How it works... 37
Troubleshooting a failed connection 37
How to do it... 37
There's more... 39
Chapter 2: Exploring the Database
40
Introduction 40
What version is the server? 41
How to do it... 41
How it works... 41
There's more... 42
What is the server uptime? 43
How to do it... 43
How it works... 44
See also 44
Locating the database server files 44
Getting ready 44
How to do it... 45
How it works... 46
There's more... 47
Locating the database server's message log 48
Getting ready 48
How to do it... 48
How it works... 49
There's more... 50
Locating the database's system identifier 51
[ ii ]