Preface

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 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 such as 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 to meet your organization's needs.

Who this book is for

This 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.

What this book covers

Chapter 1, Introduction to PostgreSQL Development and Administration, introduces the development of PostgreSQL and how PostgreSQL has become a popular Database as a Service (DBaaS) among the current clouds. We will present an overview of the numerous features of PostgreSQL in various development environments, such as Node.js and Django. Moving forward, we will introduce you to the PostGIS extension, which is a PostgreSQL facility for a geospatial PostgreSQL database. We will also present the PostgREST standalone web server, which aims to do one thing well: add an HTTP interface to any PostgreSQL databases or RESTful APIs. Then, in the second part of the chapter, we will learn about the administration of PostgreSQL.

Chapter 2, Setting Up a PostgreSQL RDS for ATM Machines, describes the DBaaS options for PostgreSQL through Amazon RDS for PostgreSQL.

Chapter 3, Using PostgreSQL and Node.js for Banking Transactions, describes the steps to create a RESTful web service that will be an Application Programming Interface (API) based on Node.js, Express, and PostgreSQL and will implement HTTP requests such as the GET, PUT, POST, and DELETE methods on data.

Chapter 4, Managing Bank ATM Locations Using PostgreSQL and Django, shows how to install and configure PostgreSQL so that you can use it with a Django application.

Chapter 5, Creating a Geospatial Database Using PostGIS and PostgreSQL, introduces you to PostGIS, a spatial extension for PostgreSQL. Through the project in the chapter, we will learn to implement geographic mapping for our Postgres Relational Database Service (RDS).

Chapter 6, Managing Banking Transactions Using PostgREST, teaches how to develop with PostgREST. The project in this chapter will help us use PostgREST to create an API for sending requests to the RDS.

Chapter 7, PostgreSQL with DevOps for Continuous Delivery, teaches how to set up DevOps tasks for PostgreSQL databases.

Chapter 8, PostgreSQL High Availability Clusters, focuses on High Availability (HA) clusters and how to achieve them with PostgreSQL.

Chapter 9, High-Performance Team Dashboard Using PostgreSQL and New Relic, demonstrates how to install and activate PostgreSQL integration and will help you gain an understanding of the data collected by the New Relic infrastructure.

Chapter 10, Testing the Performance of Our Banking App with PGBench and JMeter, shows us how to create a load test for a PostgreSQL database to benchmark PostgreSQL performance with PGBench and JMeter.

Chapter 11, Test Frameworks for PostgreSQL, showcases how to write automated tests for existing stored procedures and develop procedures using the concepts of unit tests and Test-Driven Development (TDD).

Chapter 12,  Appendix - PostgreSQL among the Other Current Clouds, talks about the various DBaaS options for PostgreSQL with other cloud platforms.

To get the most out of this book

Software/hardware covered in the book	OS requirements
PostgreSQL 11 and above	Windows/macOS/Linux

 

If you are using the digital version of this book, we advise you to type the code yourself or access the code via the GitHub repository (link available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Download the example code files

You can download the example code files for this book from GitHub at https://github.com/PacktPublishing/Developing-Modern-Database-Applications-with-PostgreSQL. In case there's an update to the code, it will be updated on the existing GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://static.packt-cdn.com/downloads/9781838648145_ColorImages.pdf

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "This getAllATMLocations module will first send a SELECT statement to PostgreSQL to retrieve all of the ATM locations."

A block of code is set as follows:

// add query functions
module.exports = {
    getAllATMLocations: getAllATMLocations,
    getSingleATMLocation: getSingleATMLocation,
    createATMLocation: createATMLocation,
    updateATMLocation: updateATMLocation,
    removeATMLocation: removeATMLocation
};

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

[default]
exten => s,1,Dial(Zap/1|30)
exten => s,2,Voicemail(u100)
exten => s,102,Voicemail(b100)
exten => i,1,Voicemail(s0)

Any command-line input or output is written as follows:

[centos@ip-172-31-95-213 ~]$ sudo su
[root@ip-172-31-95-213 centos]# cd /usr/local/src/

Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Right-click on PostGIS in the list under the Browser panel as shown in the following screenshot and select New Connection:"

Note that a base box is a template of a virtual machine, defined on the Vagrant site as follows: "Boxes are the package format for Vagrant environments. A box can be used by anyone on any platform that Vagrant supports to bring up an identical working environment."

Tips and tricks appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [e-mail].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [e-mail] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Reviews

Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit packt.com.