Book Image

Developing Modern Database Applications with PostgreSQL

By : Dr. Quan Ha Le, Marcelo Diaz

Book Image

Developing Modern Database Applications with PostgreSQL

By: Dr. Quan Ha Le, Marcelo Diaz

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

Section 1 - Introducing PostgreSQL Development and Administration

Section 1 - Introducing PostgreSQL Development and Administration

Free Chapter

Introduction to PostgreSQL Development and Administration

Introduction to PostgreSQL Development and Administration

An overview of PostgreSQL development

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

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

Section 2 - Development in PostgreSQL

Section 2 - Development in PostgreSQL

Setting Up a PostgreSQL RDS for ATMs

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

Using PostgreSQL and Node.js for Banking Transactions

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

Managing Bank ATM Locations Using PostgreSQL and Django

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

Creating a Geospatial Database Using PostGIS and PostgreSQL

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

Managing Banking Transactions using PostgREST

Managing Banking Transactions using PostgREST

Technical requirements

Introduction to PostgREST

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

Section 3 - Administration in PostgreSQL

Section 3 - Administration in PostgreSQL

PostgreSQL with DevOps for Continuous Delivery

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

PostgreSQL High Availability Clusters

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

High-Performance Team Dashboards Using PostgreSQL and New Relic

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

Testing the Performance of Our Banking App with PGBench and JMeter

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 for AWS PostgreSQL RDS

Test Frameworks for PostgreSQL

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

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

APPENDIX - PostgreSQL among the Other Current Clouds

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

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Creating a PostgREST API schema on an RDS (AWS)

We will install PostgREST on a CentOS ec2 instance, and then we will set up PostgREST to create an API for our PostgreSQL version RDS on the AWS cloud:

Let's install PostgREST from the binary release by using the command shown in the following code block:

[root@ip-172-31-95-213 src]# yum install postgresql-libs
Please answer y (=Yes) when being asked,
[root@ip-172-31-95-213 src]# wget https://github.com/PostgREST/postgrest/releases/download/v6.0.1/postgrest-v6.0.1-centos7.tar.xz
[root@ip-172-31-95-213 src]# tar xfJ postgrest-v6.0.1-centos7.tar.xz
[root@ip-172-31-95-213 src]# mv postgrest /usr/local/bin/
[root@ip-172-31-95-213 src]# rm postgrest-v6.0.1-centos7.tar.xz

We will now define a user role that has permission to access the RDS ATM database and to read records from the ATM locations table, as shown:

create role web_anon nologin;
grant web_anon to dba;

grant usage on schema public to web_anon;
grant select on public."ATM locations...