Books: 20

PostgreSQL

CoverTitleYear
KEY FEATURES * Create a PostgreSQL cluster that stays online even when disaster strikes * Avoid costly downtime and data loss that can ruin your business * Updated to include the newest features introduced in PostgreSQL 9.6 with hands-on industry-driven recipes BOOK DESCRIPTION Databases are nothing more » without the data they store. In the event of a failure - catastrophic or otherwise - immediate recovery is essential. By carefully combining multiple servers, it's even possible to hide the fact a failure occurred at all. From hardware selection to software stacks and horizontal scalability, this book will help you build a versatile PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand. It all begins with hardware selection for the skeleton of an efficient PostgreSQL database cluster. Then it's on to preventing downtime as well as troubleshooting some real life problems that administrators commonly face. Next, we add database monitoring to the stack, using collectd, Nagios, and Graphite. And no stack is complete without replication using multiple internal and external tools, including the newly released pglogical extension. Pacemaker or Raft consensus tools are the final piece to grant the cluster the ability to heal itself. We even round off by tackling the complex problem of data scalability. This book exploits many new features introduced in PostgreSQL 9.6 to make the database more efficient and adaptive, and most importantly, keep it running. WHAT YOU WILL LEARN * Protect your data with PostgreSQL replication and management tools such as Slony, Bucardo, pglogical, and WAL-E * Hardware planning to help your database run efficiently * Prepare for catastrophes and prevent them before they happen * Reduce database resource contention with connection pooling using pgpool and PgBouncer * Automate monitoring and alerts to visualize cluster activity using Nagios and collected * Construct a robust software stack that can detect and fix outages * Learn simple PostgreSQL High Availability with Patroni, or dive into the full power of Pacemaker. ABOUT THE AUTHOR Shaun M. Thomas has been working with PostgreSQL since late 2000. He is a frequent contributor to the PostgreSQL Performance and General mailing lists, assisting other DBAs with the knowledge he's gained over the years. In 2011 and 2012, he gave presentations at the Postgres Open conference on topics such as handling extreme throughput, high availability, server redundancy, and failover techniques. Most recently, he has contributed the Shard Manager extension and the walctl WAL management suite. Currently, he serves as the database architect at PEAK6 Investments, where he develops standard operating procedure (SOP) guidelines to facilitate reliable server architecture among many other tasks. Many of the techniques used in this book were developed specifically for this extreme environment. He believes that PostgreSQL has a stupendous future ahead, and he can't wait to see the advancements subsequent versions will bring. https://www.linkedin.com/in/bonesmoses TABLE OF CONTENTS 1. Hardware Planning 2. Handling and Avoiding Downtime 3. Pooling Resources 4. Troubleshooting 5. Monitoring 6. Replication 7. Replication Management Tools 8. Simple Stack 9. Advanced Stack 10. Cluster Control 11. Data Distribution « less
2017
Create, develop and manage relational databases in real world applications using PostgreSQL
***** About This Book ***** * Learn about the PostgreSQL development life cycle including its testing and refactoring * Build productive database solutions and use them in Java applications * A comprehensive guide to learn about SQL, PostgreSQL procedural language and PL/pgSQL ***** Who This more » Book Is For ***** If you are a student, database developer or an administrator, interested in developing and maintaining a PostgreSQL database, then this book is for you. No knowledge of database programming or administration is necessary. ***** What You Will Learn ***** * Learn concepts of data modelling and relation algebra * Install and set up PostgreSQL database server and client software * Implement data structures in PostgreSQL * Manipulate data in the database using SQL * Implement data processing logic in the database with stored functions, triggers and views * Test database solutions and assess the performance * Integrate database with Java applications * Detailed knowledge of the main PostgreSQL building objects, most used extensions * Practice database development life cycle including analysis, modelling, (documentation), testing, bug fixes and refactoring ***** In Detail ***** PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. Also it provides NoSQL capabilities, and very rich data types and extensions. All that makes PostgreSQL a very attractive solution in various kinds of software systems. The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework. ***** Style and approach ***** An easy-to-follow guide to learn programming build applications with PostgreSQL, and manage a PostgreSQL database instance. « less
2015
Over 90 hands-on recipes to effectively manage, administer, and design solutions using PostgreSQL
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). With the goal of teaching you the skills to master PostgreSQL, the book begins by giving you a glimpse more » 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. « less
2015
Design, develop and implement streamlined database with PostgreSQL
PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead. This book is a comprehensive and pragmatic guide to developing databases in PostgreSQL. Beginning with a refresher more » of basic SQL skills, you will gradually be exposed to advanced concepts, such as learning how to program in native PostgreSQL procedural languages, implementing triggers, custom functions, and views. You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability. « less
2015
Discover how to design, develop, and maintain your database application effectively with PostgreSQL
***** About This Book ***** * Understand how to utilize the most frequently used PostgreSQL ecosystem-related tools and technologies * A hands-on guide focused primarily on providing a practical approach to learning about architecture and design considerations for database architects * The book more » covers PostgreSQL from a data architect's perspective – covering topics from installation from source to designing tables using SQL Power Architect, as well as deciding replication and failover strategies. ***** Who This Book Is For ***** This book is for developers and data architects who have some exposure to databases. It is assumed that you understand the basic concepts of tables and common database objects, including privileges and security. ***** What You Will Learn ***** * Compile PostgreSQL from source and understand the PostgreSQL architecture * Configure parameters and change default settings for a PostgreSQL server * Leverage the logging mechanism to identify errors and suboptimal queries * Use replication to scale horizontally * Set up backup and recovery processes * Tweak parameters to optimize queries and processes * Troubleshoot connection errors * Use indexes and rewrite queries to improve performance * Install and use PostgreSQL extensions * Learn how to leverage the command-line client (psql) ***** In Detail ***** PostgreSQL is an incredibly flexible and dependable open source relational database. Harnessing its power will make your applications more reliable and extensible without increasing costs. Using PostgreSQL's advanced features will save you work and increase performance, once you've discovered how to set it up. PostgreSQL for Data Architects will teach you everything you need to learn in order to get a scalable and optimized PostgreSQL server up and running. The book starts with basic concepts like installing PostgreSQL from source and covers theoretical aspects such as concurrency and transaction management. After this, you'll learn how to set up replication, use load balancing to scale horizontally, and troubleshoot errors. Finally, you will get acquainted with useful tools available in the PostgreSQL ecosystem used for analyzing PostgreSQL logs, setting up load balancing, and recovery. « less
2015
Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language
This book will show you that PostgreSQL is so much more than a database server. In fact, it could even be seen as an application development framework, with the added bonuses of transaction support, massive data storage, journaling, recovery, and a host of other features that the PostgreSQL engine provides. You more » will get to grips with creating libraries of useful code, grouping them into even more useful components, and distributing them to the community. Then, you will take a look at user-defined functions, and how to define and utilize them efficiently. You will also learn how to extract data from a multitude of foreign data sources and extend PostgreSQL to do it natively. What's more, you can do all of this in a nifty debugging interface that will allow you to do it efficiently and reliably. This book explores all possible ways to extend PostgreSQL and write server-side code using various programming languages with concrete and easy-to-understand examples. « less
2015
Over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL
ABOUT THIS BOOK * Create a PostgreSQL cluster that stays online even when disaster strikes * Avoid costly downtime and data loss that can ruin your business * Perform data replication and monitor your data with hands-on industry-driven recipes and detailed step-by-step explanations WHO THIS BOOK more » IS FOR If you are a PostgreSQL DBA working on Linux systems who want a database that never gives up, this book is for you. If you've ever experienced a database outage, restored from a backup, spent hours trying to repair a malfunctioning cluster, or simply want to guarantee system stability, this book is definitely for you. WHAT YOU WILL LEARN * Protect your data with PostgreSQL replication and management tools such as Slony, Bucardo, and Londiste * Choose the correct hardware for redundancy and scale * Prepare for catastrophes and prevent them before they happen * Reduce database resource contention with connection pooling * Automate monitoring and alerts to visualize cluster activity using Nagios and collectd * Construct a robust software stack that can detect and fix outages * Design a scalable schema architecture to handle billions of queries IN DETAIL PostgreSQL, often known as simply "Postgres", is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. From hardware selection to software stacks and horizontal scalability, this book will help you build a versatile PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand. We start with selecting the necessary hardware to handle multiple failure scenarios with redundancy. Then, we discuss how to automate and visualize these checks with Nagios, check_mk, and Graphite. We'll finally round off by tackling the complex problem of data scalability. « less
2014
A Practical Guide to the Advanced Open Source Database
Thinking of migrating to PostgreSQL? This clear, fast-paced introduction helps you understand and use this open source database system. Not only will you learn about the enterprise class features in versions 9.2, 9.3, and 9.4, you'll also discover that PostgeSQL is more than a database system - it's more » also an impressive application platform. With examples throughout, this book shows you how to achieve tasks that are difficult or impossible in other databases. This second edition covers LATERAL queries, augmented JSON support, materialized views, and other key topics. If you're a current PostgreSQL user, you'll pick up gems you may have missed before. « less
2014
Replication is an advanced subject but this tutorial makes it so simple to assimilate thanks to a carefully structured approach based on examples and screenshots. As a PostrgreSQL administrator it will expand your horizons. Overview * Explains the new replication features introduced in PostgreSQL more » 9 * Contains easy to understand explanations and lots of screenshots that simplify an advanced topic like replication * Teaches PostgreSQL administrators how to maintain consistency between redundant resources and to improve reliability, fault-tolerance, and accessibility In Detail PostgreSQL offers a comprehensive set of replication related features, which can be used to make your database servers more robust and way more scalable. Unleashing the power of PostgreSQL provides the user with countless opportunities and a competitive advantage over other database systems. To make things more powerful, PostgreSQL can be used in conjunction with a handful of sophisticated tools serving various different needs such as queuing, logical replication, or simplified transaction log handling. "PostgreSQL Replication" is a practical, hands-on guide to PostgreSQL replication. It will provide you with the theoretical background as well as simple examples showing you how to make replication work on your system. A broad toolchain will be presented along with mature PostgreSQL-core technology. "PostgreSQL Replication" starts with an introduction to replication concepts as well as the physical limitations of different replication solutions. You will be guided through various techniques such as Point-In-Time-Recovery, transaction-log-based replication and you will be introduced to a set of replication-related tools. In the final chapter you will learn to scale PostgreSQL to many different servers using PL/Proxy. You will learn how to reset PostgreSQL to a certain point in time and figure out how to replicate data in many ways. You will deal with both synchronous as well as asynchronous replication. In addition to that, the book covers important topics, such as Slony, and upgrades with virtually no downtime. We will also cover important performance-related topics to make sure your database setups will provide you with high speed AND high availability. "PostgreSQL Replication" contains all the information you need to design and operate replicated setups. You will learn everything you need to know for your daily work and a lot more. What you will learn from this book * Recover a PostgreSQL database to a certain point in time * Set up asynchronous and synchronous streaming replication * Handle transaction log files efficiently * Learn about Linux High Availability * Get to grips with Slony replication * Get PL/Proxy to scale infinitely * Understand pgpool and pgbouncer to improve speed and reliability * Perform fast on-the-fly backups * Increase reliability and throughput * Improve data security and geographically distribute data Approach This book has a chapter dedicated to each aspect of replication. The new features of PostgreSQL 9 are introduced and there are lots of practical examples and screenshots. « less
2013
Extend PostgreSQL and integrate the database layer into your development framework
Learn how to work with PostgreSQL as if you spent the last decade working on it. PostgreSQL is capable of providing you with all of the options that you have in your favourite development language and then extending that right on to the database server. With this knowledge in hand, you will be able to more » respond to the current demand for advanced PostgreSQL skills in a lucrative and booming market. PostgreSQL Server Programming will show you that PostgreSQL is so much more than a database server. In fact, it could even be seen as an application development framework, with the added bonuses of transaction support, massive data storage, journaling, recovery and a host of other features that the PostgreSQL engine provides. « less
2013