Developing modern database applications with PostgreSQL : use the highly available object-relational database to build scalable and reliable PostgreSQL / Quan Ha Le, Marcelo Diaz.

Format:

Book

Author/Creator:

Le, Quan Ha, author.

Diaz, Marcelo, author.

Language:

English

Subjects (All):

Database design.

Physical Description:

1 online resource (428 pages)

Place of Publication:

Birmingham, England ; Mumbai : Packt, [2021]

Biography/History:

Le Quan Ha: Dr. Quan Ha Le graduated with a Ph. D. in computer science from the Queen's University of Belfast, United Kingdom, in 2005. Since his Ph. D. graduation, he has been working as a PostgreSQL database administrator all over Alberta and Ontario, Canada, until now. From 2008 to 2019, Dr. Le Quan Ha administered, designed, and developed 24 small, medium, large, and huge PostgreSQL databases in Canada. Since 2016, after writing a good publication on PostgreSQL database clusters on clouds, he has been a member of the United States PostgreSQL Association (PgUS) in New York City. Dr. Le Quan Ha has also been a board member of the PgUS Diversity committee since 2018. Diaz Marcelo: Marcelo Diaz is a software engineer with more than 15 years of experience, with a special focus on PostgreSQL. He is passionate about open source software and has promoted its application in critical and high-demand environments where he has worked as a software developer and consultant for both private and public companies. He currently works very happily at Cybertec and as a technical reviewer for Packt Publishing. He enjoys spending his leisure time with his daughter, Malvina, and his wife, Romina. He also likes playing football.

Summary:

Get up to speed with core PostgreSQL tasks such as database administration, application development, database performance monitoring, and database testingKey FeaturesBuild real-world enterprise database management systems using Postgres 12 featuresExplore the development, administrative and security aspects of PostgreSQL 12Implement best practices from industry experts to build powerful database applicationsBook DescriptionPostgreSQL 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.What you will learnSet up high availability PostgreSQL database clusters in the same containment, a cross-containment, and on the cloudMonitor the performance of a PostgreSQL databaseCreate automated unit tests and implement test-driven development for a PostgreSQL databaseDevelop PostgreSQL apps on cloud platforms using DevOps with Python and Node.jsWrite robust APIs for PostgreSQL databases using Python programming, Node.js, and PostgRESTCreate a geospatial database using PostGIS and PostgreSQLImplement automatic configuration by Ansible and Terraform for PostgresWho this book is forThis PostgreSQL book is for database developers, database administrators, data architects, or anyone who wants to build end-to-end database projects using Postgres. This book will also appeal to software engineers, IT technicians, computer science researchers, and university students who are interested in database development and administration. Some familiarity with PostgreSQL and Linux is required to grasp the concepts covered in the book effectively.

Contents:

Cover

Title Page

Copyright

Contributors

About Packt

Table of Contents

Preface

Section 1 - Introducing PostgreSQL Development and Administration

Chapter 1: 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

Chapter 2: 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

Chapter 3: Using PostgreSQL and Node.js for Banking Transactions

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

Chapter 4: Managing Bank ATM Locations Using PostgreSQL and Django

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

Chapter 5: Creating a Geospatial Database Using PostGIS and PostgreSQL

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

Chapter 6: Managing Banking Transactions using PostgREST

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

Section 3 - Administration in PostgreSQL

Chapter 7: PostgreSQL with DevOps for Continuous Delivery

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.

Chapter 8: PostgreSQL High Availability Clusters

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

Chapter 9: High-Performance Team Dashboards Using PostgreSQL and New Relic

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

Chapter 10: Testing the Performance of Our Banking App with PGBench and JMeter

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

Chapter 11: Test Frameworks for PostgreSQL

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

Chapter 12: APPENDIX - PostgreSQL among the Other Current Clouds

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

Other Books You May Enjoy

Index.

Notes:

Description based on print version record.

ISBN:

9781838641061

1838641068

OCLC:

1262317017