Book Overview & Buying
Table Of Contents

Developing Modern Database Applications with PostgreSQL

By : Le, Diaz

3.8 (8)

Buy this Book

Developing Modern Database Applications with PostgreSQL

3.8 (8)

By: Le, Diaz

Buy this Book

Overview of this book

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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

Section 1 - Introducing PostgreSQL Development and Administration

Free Chapter

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

Other Books You May Enjoy

Leave a review - let other readers know what you think

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

Loading spatial data using QGIS

QGIS is a very common open source tool for GIS analysts, geographers, civil engineers, and many other professionals for creating maps and editing, viewing, and analyzing geospatial data. PostGIS is open source for PostgreSQL databases only, whereas QGIS is a wider open source spatial tool for many other databases, such as MSSQL, Oracle, and DB2. Hence, many developers have chosen QGIS as their GIS tool. In the following steps, we will see how PostGIS can be integrated into QGIS by setting a connection between them:

Right-click on PostGIS in the list under the Browser panel, as shown in the following screenshot, and select New Connection:

Figure 5.18 – QGIS main window

Fill in the RDS information, as shown in the following screenshot, click on the Basic tab for authentication, and then enter the following PostgreSQL database credentials:

User name: dba
Password: bookdemo