PostgreSQL Administration Essentials -- Table of Contents -- PostgreSQL Administration Essentials -- Credits -- About the Author -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers, and more -- Why subscribe? -- Free access for Packt account holders -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Errata -- Piracy -- Questions -- 1. Installing PostgreSQL -- Preparing your setup -- Understanding the PostgreSQL version numbers -- Choosing the right version -- Installing binary packages -- Installing PostgreSQL on Debian or Ubuntu -- Installing PostgreSQL on Red-Hat-based systems -- Compiling PostgreSQL from source -- How it works -- Installing the contrib packages -- Finalizing your installation -- Creating a database instance -- Firing up PostgreSQL -- Understanding the existing databases -- Creating databases -- Summary -- 2. Indexing and Performance Tuning -- Using simple binary trees -- Preparing the data -- Understanding the concept of execution plans -- Calculating costs -- Drawing important conclusions -- Creating indexes -- Analyzing the performance of a query -- The internal structure of a B-tree index -- Understanding the B-tree internals -- Providing a sorted order -- Combined indexes -- Partial indexes -- Dealing with different types of indexes -- Detecting missing indexes -- Detecting slow queries -- How to reset statistics -- Adjusting memory parameters -- Optimizing shared buffers -- Considering huge pages -- Tweaking work_mem -- Improving maintenance_work_mem -- Adjusting effective_cache_size -- Summary -- 3. Users and Permissions -- Understanding PostgreSQL security -- Configuring the TCP -- Managing network authentication -- Managing contradictions -- Authentication methods available.

Some more examples -- Handling SSL -- Changing pg_hba.conf -- Handling instance-level permissions -- Creating roles -- Modifying and dropping roles -- Controlling database-level permissions -- Understanding schema-level permissions -- Handling table-level permissions -- Managing column rights -- Improving security with SELinux -- Summary -- 4. Managing Logfiles -- Understanding the PostgreSQL log architecture -- Configuring log destinations -- Creating local logfiles -- Using syslog -- Configuring logs on Windows -- Performance considerations -- Configuring the amount of log output -- Making logs more readable -- Additional settings -- Making log creation more fine grained -- Logging selectively -- Focusing on slow queries -- Silencing notices -- Summary -- 5. Backup and Recovery -- Importing and exporting data -- Using the COPY command -- Basic operations of the COPY command -- Making use of pipes -- Performing backups -- Handling pg_dump -- More sophisticated dumping -- Performing partial replays -- Passing users and passwords -- Dumping an entire instance -- Understanding backups and user creation -- Summary -- 6. Handling Replication and Improving Performance -- Understanding the PostgreSQL transaction log -- The purpose of the transaction log -- Inspecting the size of the transaction log -- Configuring the checkpoints -- Optimizing the checkpoints -- Configuring the distance between checkpoints -- Controlling writes -- Setting up an asynchronous replication -- Obtaining a high-level overview -- Setting up replication step by step -- Preparing the slave -- Configuring the master -- Fetching an initial backup -- Creating and modifying the recovery.conf file -- Firing up the slave -- Turning slaves into masters -- Upgrading to synchronous replication -- Improving and monitoring the replication -- Keeping an eye on streaming.

Making things more robust -- Managing conflicts -- Handling point-in-time recovery -- Setting up PITR -- Replaying transaction logs -- Understanding timelines -- The importance of timelines -- Summary -- 7. Monitoring PostgreSQL -- Understanding the system statistics of PostgreSQL -- Checking out the pg_stat_activity file -- Monitoring databases -- Monitoring tables -- Monitoring indexes -- Checking out the information in the background writer -- Resetting statistics -- Integrating Nagios -- Handling Linux cgroups -- Setting up cgroups -- Summary -- Index.