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.

Related Content you might be interested in

Current Title:

Small book image
Developing Modern Database Applications with PostgreSQL
Dr. Quan Ha Le | Marcelo Diaz
Aug, 2021 | 440 pages
Small book image
PostgreSQL 10 High Performance
Enrico Pirozzi
Apr, 2018 | 508 pages
Small book image
Performance Testing with JMeter 3
Bayo Erinle
Jul, 2017 | 166 pages
Table of Contents (17 chapters)
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
1
Section 1 - Introducing PostgreSQL Development and Administration
Section 1 - Introducing PostgreSQL Development and Administration
Free Chapter
2
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
3
Section 2 - Development in PostgreSQL
Section 2 - Development in PostgreSQL
4
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
5
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
6
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
7
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
8
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
9
Section 3 - Administration in PostgreSQL
Section 3 - Administration in PostgreSQL
10
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
11
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
12
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
13
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
14
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
15
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

Signing up for and installing New Relic

New Relic supplies a high-performance team dashboard for various systems, such as the following:

  • Backend, frontend, and mobile applications
  • Cloud and platform technologies
  • Host operating systems
  • Log ingestion
  • Infrastructure
  • Open source monitoring systems

We can plug New Relic into different cloud platforms such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform. Hence, obviously, New Relic installation for a PostgreSQL RDS from AWS is a typical application of New Relic.

You can access New Relic through your browser, and we will learn how to make a New Relic dashboard by taking the following steps:

  1. In a browser, open the New Relic website: https://infrastructure.newrelic.com/.
  1. Next, you need to sign in. If you do not have an account, you can register with New Relic by clicking on Create a free account:

Figure 9-1. New Relic login
  1. Enter the required information into the New Relic registration form as shown in Figure...
Previous Section
End of Section 3
Next Section