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

Key performance factors in PostgreSQL

A production environment is consolidated with different components, hardware such as CPU/memory, and operating systems. PostgreSQL is installed to communicate with other components of the production environment. Please bear in mind that if the production environment is not properly configured, the overall performance will be degraded.

Some PostgreSQL queries can run faster or slower depending on the configuration that has been set. The goal of database performance benchmarking is to achieve the largest possible throughput. The key performance factors that affect a database are the workload, resources, optimization, and contention. The workload includes batch jobs, dynamic queries for online transactions, and data analytics queries that are used to generate reports. The workload will be different according to the period of each day, week, or month, and it also depends on the applications. The optimization of every database is unique: it can be a database-level configuration or query-level optimization. Contention refers to the conflict condition because two or more components of the workload could attempt to use a single resource at the same time. If contention increases, the throughput will decrease.