Developing Modern Database Applications with PostgreSQL

Description

Get up to speed with core PostgreSQL tasks such as database administration, application development, database performance monitoring, and database testing Key Features Build real-world enterprise database management systems using Postgres 12 features Explore the development, administrative and security aspects of PostgreSQL 12 Implement best practices from industry experts to build powerful database applications Book Description PostgreSQL is an open-source object-relational database management system (DBMS) that provides enterprise-level services, including high performance and scalability. This book is a collection of unique projects providing you with a wealth of information relating to administering, monitoring, and testing PostgreSQL. The focus of each project is on both the development and the administrative aspects of PostgreSQL. Starting by exploring development aspects such as database design and its implementation, you'll then cover PostgreSQL administration by understanding PostgreSQL architecture, PostgreSQL performance, and high-availability clusters. Various PostgreSQL projects are explained through current technologies such as DevOps and cloud platforms using programming languages like Python and Node.js. Later, you'll get to grips with the well-known database API tool, PostgREST, before learning how to use popular PostgreSQL database testing frameworks. The book is also packed with essential tips and tricks and common patterns for working seamlessly in a production environment. All the chapters will be explained with the help of a real-world case study on a small banking application for managing ATM locations in a city. By the end of this DBMS book, you'll be proficient in building reliable database solutions as per your organization's needs. What you will learn Set up high availability PostgreSQL database clusters in the same containment, a cross-containment, and on the cloud Monitor the performance of a PostgreSQL database Create automated unit tests and implement test-driven development for a PostgreSQL database Develop PostgreSQL apps on cloud platforms using DevOps with Python and Node.js Write robust APIs for PostgreSQL databases using Python programming, Node.js, and PostgREST Create a geospatial database using PostGIS and PostgreSQL Implement automatic configuration by Ansible and Terraform for Postgres Who this book is for This PostgreSQL book is for database developers, database administrators, data architects, or anyone who wants to build end-to-end database projects using Postgres. This book will also appeal to software engineers, IT technicians, computer science researchers, and university students who are interested in database development and administration. Some familiarity with PostgreSQL and Linux is required to grasp the concepts covered in the book effectively. Table of Contents Introduction to PostgreSQL Development and Administration Setting up a PostgreSQL RDS for ATM Machines Using PostgreSQL and Node.js for Banking Transactions Managing Bank ATM locations using PostgreSQL and Django Creating a Geospatial Database using PostGIS and PostgreSQL Managing Banking Transactions using PostgREST PostgreSQL with DevOps for Continuous Delivery PostgreSQL High Availability Clusters High-Performance Team Dashboards Using PostgreSQL and New Relic Testing the Performance of Our Banking App with PGBench and JMeter Test Frameworks for PostgreSQL Appendix - PostgreSQL among the Other Current Clouds Title Page Copyright Contributors About Packt Table of Contents Preface Section 1 - Introducing PostgreSQL Development and Administration Introduction to PostgreSQL Development and Administration An overview of PostgreSQL development What is DBaaS? The development of PostgreSQL by various environments Features of PostgreSQL with NodeJS Features of PostgreSQL with Python PostGIS spatial extension The PostgREST RESTful API for PostgreSQL databases An overview of PostgreSQL administration The PostgreSQL architecture Shared memory PostgreSQL autovacuum The PostgreSQL writer and checkpointer PostgreSQL process types Managing HA in PostgreSQL Benchmarking PostgreSQL performance Key performance factors in PostgreSQL Using pgbench for PostgreSQL benchmarking Monitoring PostgreSQL databases The DevOps environment for PostgreSQL PostgreSQL testing frameworks Summary Section 2 - Development in PostgreSQL Setting Up a PostgreSQL RDS for ATMs Technical requirements An overview of the project Creating a PostgreSQL RDS with AWS Creating a PostgreSQL database Editing the RDS security group Connecting to a PostgreSQL database instance The pgAdmin server The ATM locations table Creating a PostgreSQL database snapshot Deleting a PostgreSQL database instance Restoring data from a PostgreSQL database snapshot Point-in-time recovery for PostgreSQL Summary Using PostgreSQL and Node.js for Banking Transactions Technical requirements Setting up a Node.js project Installing Node.js RESTful web services Setting up PostgreSQL for Node.js Working with the server side: routes Working with the client side: Angular Creating an Angular view Angular app refactoring Automated testing with Mocha and Chai Summary Managing Bank ATM Locations Using PostgreSQL and Django Technical requirements Setting up a Django project Installing Python 3 Creating a virtual environment Installing Django Creating a Django project Creating the ATM app Django database settings with PostgreSQL Database models in Django Migrating the database Understanding the Django user interface – admin, views, templates, and URLs Making the atmapp modifiable inside the admin Exploring the free admin functionality Summary Creating a Geospatial Database Using PostGIS and PostgreSQL Technical requirements Installing PostGIS for RDS on AWS Importing spatial data files into PostgreSQL Setting up QGIS Loading spatial data using QGIS Executing PostGIS queries Ordering ATM locations by distance from the Brooklyn Bridge Finding ATM locations within 1 kilometer of Times Square Summary Managing Banking Transactions using PostgREST Technical requirements Introduction to PostgREST Using Docker Installing standalone PostgREST Creating a PostgREST API schema on an RDS (AWS) Executing PostgREST Adding a trusted user Creating a PostgREST token PostgREST administration PostgREST on TimescaleDB Summary Section 3 - Administration in PostgreSQL PostgreSQL with DevOps for Continuous Delivery Technical requirements Setting up PostgreSQL using Vagrant and VirtualBox Installing VirtualBox Installing Vagrant Selecting a Vagrant box Spinning up Ubuntu server 18.04 with Vagrant Creating the Puppet module for PostgreSQL Working with Jenkins and PostgreSQL Creating an Ansible playbook for PostgreSQL Managing PostgreSQL by Terraform Summary PostgreSQL High Availability Clusters Technical requirements Setting up streaming replication on PostgreSQL Setting up a PostgreSQL HA cluster through the Heimdall data proxy Heimdall installation by Docker Heimdall Webapp and Wizard configuration Testing load balancing and high availability Summary High-Performance Team Dashboards Using PostgreSQL and New Relic Technical requirements Signing up for and installing New Relic Defining PostgreSQL role permissions Configuring New Relic for PostgreSQL Adding new metric data for PostgreSQL Infrastructure inventory data collection Summary Testing the Performance of Our Banking App with PGBench and JMeter Technical requirements How to benchmark PostgreSQL performance pgbench 1 – Creating and initializing a benchmark database pgbench 2 – Running a baseline pgbench test pgbench 3 – Creating and testing a connection pool JMeter setup JMeter for AWS PostgreSQL RDS Summary Test Frameworks for PostgreSQL Technical requirements Making unit tests with pgTAP Setting up pgTAP for PostgreSQL RDS pgTAP test examples Uninstalling pgTAP for PostgreSQL RDS Making unit tests in a simple way with PG_Unit Setting up PGUnit for PostgreSQL RDS PGUnit test examples Uninstalling PGUnit for PostgreSQL RDS PGUnit – same name but a different approach Setting up simple pgunit for PostgreSQL RDS Simple pgunit test examples Uninstalling simple pgunit for PostgreSQL RDS Testing with Python – Testgres Setting up Testgres for PostgreSQL Testgres test examples Uninstalling Testgres for PostgreSQL RDS Summary APPENDIX - PostgreSQL among the Other Current Clouds Technical requirements Google Cloud SQL Creating an instance pgAdmin connection for Google Cloud Microsoft Azure for PostgreSQL Creating an Azure database for the PostgreSQL server Getting an Azure resource connection for pgAdmin Heroku Postgres Creating a Heroku app Creating a Heroku PostgreSQL database Connecting Heroku PostgreSQL using pgAdmin EnterpriseDB cloud database Creating a PostgreSQL cluster Using pgAdmin to connect to EDB PostgreSQL Summary Other Books You May Enjoy Index