Developing Modern Database Applications with PostgreSQL

✍ Scribed by Dr. Quan Ha Le, Marcelo Diaz

• Publisher: Packt Publishing
• Year: 2021
• Tongue: English
• Leaves: 440
• Edition: 1
• Category: Library

✦ Synopsis

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

✦ Table of Contents

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

✦ Subjects

postgresql