Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Developing Modern Database Applications with PostgreSQL

By : Quan Ha Le, Marcelo Diaz

3.8 (8)

Developing Modern Database Applications with PostgreSQL

3.8 (8)

By: 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

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

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

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

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

Summary

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

Summary

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

Summary

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

Summary

Managing Banking Transactions using PostgREST

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

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

Summary

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

Summary

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

Summary

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 setup

JMeter for AWS PostgreSQL RDS

Summary

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

Summary

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

Summary

pgbench 1 – Creating and initializing a benchmark database

We are going to use pgbench for the RDS on Amazon Web Services. We will set up pgbench from the Jenkins server at 192.168.0.200 that we set up with Vagrant in Chapter 7, PostgreSQL with DevOps for Continuous Delivery. We PuTTY into the Jenkins server:

First, open PowerShell as administrator:

PS C:\Windows\system32>
PS C:\Windows\system32> cd C:\Projects\Vagrant\Jenkins
PS C:\Projects\Vagrant\Jenkins> vagrant up --provider virtualbox
PS C:\Projects\Vagrant\Jenkins> vagrant ssh

vagrant@devopsubuntu1804:~$

Execute the following script to install pgbench:

vagrant@devopsubuntu1804:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 
vagrant@devopsubuntu1804:~$ echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list

vagrant@devopsubuntu1804:~$ sudo apt update
vagrant...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Developing Modern Database Applications with PostgreSQL

Search

Your notes and bookmarks