PostgreSQL is an advanced SQL database server available on a wide range of platforms, and is fast becoming one of the world's most popular server databases, with an enviable reputation for performance, stability, and an enormous range of advanced features. PostgreSQL is one of the oldest open source projects, completely free to use, and developed by a very diverse worldwide community. Most of all, it just works!
One of the clearest benefits of PostgreSQL is that it is open source, meaning that you have a very permissive license to install, use, and distribute PostgreSQL without paying anyone any fees or royalties. On top of that, PostgreSQL is well-known as a database that stays up for long periods, and requires little or no maintenance in many cases. Overall, PostgreSQL provides a very low total cost of ownership.
PostgreSQL 9 Administration Cookbook Second Edition offers the information you need to manage your live production databases on PostgreSQL. The book contains insights straight from the main author of the PostgreSQL replication and recovery features, and the database architect of the most successful start-up that uses PostgreSQL: Skype. This hands-on guide will assist developers working on live databases, supporting web or enterprise software applications using Java, Python, Ruby, and .NET from any development framework. It's easy to manage your database when you've got PostgreSQL 9 Administration Cookbook Second Edition at hand.
This practical guide gives you quick answers to common questions and problems, building on the authors' experience as trainers, users, and core developers of the PostgreSQL database server.
Each technical aspect is broken down into short recipes that demonstrate solutions with working code, and then explain why and how they work. This book is intended to be a desk reference for both new users and technical experts.
The book covers all the latest features available in PostgreSQL 9. Soon, you will be running a smooth database with ease.
Chapter 1, First Steps, covers topics such as introduction to PostgreSQL 9, downloading and installing PostgreSQL 9, connecting to a PostgreSQL server, enabling server access to network/remote users, using graphical administration tools, using the psql query and scripting tools, changing your password securely, avoiding hardcoding your password, using a connection service file, and troubleshooting a failed connection.
Chapter 2, Exploring the Database, helps you identify the version of the database server you are using and also the server uptime. This chapter helps you locate the database server files, database server message log, and database's system identifier. It shows you how to list a database on the database server and contains recipes that let you know the number of tables in your database, how much disk space is used by the database and tables, which are the biggest tables, how many rows a table has, how to estimate rows in a table, and how to understand object dependencies.
Chapter 3, Configuration, covers topics such as reading the fine manual (RTFM), planning a new database, changing parameters in your programs, the current configuration settings, parameters that are at non-default settings, updating the parameter file, setting parameters for particular groups of users, the basic server configuration checklist, and adding an external module to the PostgreSQL server.
Chapter 4, Server Control, provides information about starting the database server manually, stopping the server quickly and safely, stopping the server in an emergency, reloading the server configuration files, restarting the server quickly, preventing new connections, restricting users to one session each, and pushing users off the system. It contains recipes that help you decide on a design for multitenancy. You can learn how to use multiple schemas, give users their own private database, run multiple database servers on one system, and set up a connection pool.
Chapter 5, Tables and Data, guides you through the process of choosing good names for database objects, handling objects with quoted names, enforcing the same name and the same definition for columns, identifying and removing duplicate rows, preventing duplicate rows, finding a unique key for a set of data, generating test data, randomly sampling data, loading data from a spreadsheet, and loading data from flat files.
Chapter 6, Security, provides recipes on revoking user access to a table, granting user access to a table, creating a new user, temporarily preventing a user from connecting, removing a user without dropping their data, checking whether all users have a secure password, giving limited superuser powers to specific users, auditing DDL changes, auditing data changes, integrating with LDAP, connecting using SSL, and encrypting sensitive data.
Chapter 7, Database Administration, covers useful topics such as writing a script wherein either all succeed or all fail, writing a psql script that exits immediately after the first error, performing actions on many tables, adding or removing columns from tables, changing the data type of a column, adding or removing schemas, moving objects between schemas, adding or removing tablespaces, moving objects between tablespaces, accessing objects in other PostgreSQL databases, and making views updatable.
Chapter 8, Monitoring and Diagnosis, provides recipes that answer questions such as, "Is the user connected? What are they running? Are they active or blocked? Who is blocking them? Is anybody using a specific table? When did anybody last use it? How much disk space is used by temporary data? And why are my queries slowing down?" It also helps you with investigating and reporting a bug, producing a daily summary report of log file errors, killing a specific session, and resolving an in-doubt prepared transaction.
Chapter 9, Regular Maintenance, includes useful recipes on controlling automatic database maintenance, avoiding auto-freezing and page corruptions, avoiding transaction wraparound, removing old prepared transactions, actions for heavy users of temporary tables, identifying and fixing bloated tables and indexes, maintaining indexes, finding unused indexes, carefully removing unwanted indexes, and planning maintenance.
Chapter 10, Performance and Concurrency, covers topics such as finding slow SQL statements, collecting regular statistics from pg_stat* views, finding out what makes SQL slow, reducing the number of rows returned, simplifying complex SQL code, speeding up queries without rewriting them, finding out why a query is not using an index, forcing a query to use an index, using optimistic locking, and reporting performance problems.
Chapter 11, Backup and Recovery, provides useful information about backup and recovery of your PostgreSQL database through recipes on understanding and controlling crash recovery, planning backups, hot logical backup of one database, hot logical backup of all databases, hot logical backup of all tables in a tablespace, backup of database object definitions, standalone hot physical database backup, and hot physical backup and continuous archiving. It also includes topics such as recovery of all databases; recovery to a point in time; recovery of a dropped or damaged table, database, or tablespace; improving performance of backup and recovery; and incremental/differential backup and restore.
Chapter 12, Replication and Upgrades, covers replication best practices; setting up file-based or streaming replication; setting up streaming replication security; Hot Standby and read scalability; managing Streaming Replication; using repmgr; using replication slots; monitoring replication; performance and synchronous replication; delaying, pausing, and synchronizing replication; Logical Replication; Bi-Directional Replication; archiving transaction log data, upgrading minor release upgrades, and major release upgrades, both in-place and online.
You'll need the following pieces of software for this book:
PostgreSQL 9.4 server software
psql client utility (a part of 9.4)
pgAdmin3 1.20
This book is for system administrators, database administrators, architects, developers, and anyone with an interest in planning for, or running, live production databases. This book is most suited to those who have some technical experience.
In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).
To give clear instructions on how to complete a recipe, we use the following sections.
This section tells you what to expect in the recipe, and describes how to set up any software or any preliminary settings required for the recipe.
This section usually consists of a detailed explanation of what happened in the previous section.
This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The service can also be set using an environment variable named PGSERVICE."
A block of code is set as follows:
[dbservice1] host=postgres1 port=5432 dbname=postgres
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
Database system identifier: 5805760367713220187
Database cluster state: in production
Any command-line input or output is written as follows:
$ psql -c "SELECT current_time"
New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Keep the Guru Hints option on."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at <[email protected]>, and we will do our best to address the problem.