Get up to speed with core PostgreSQL tasks such as database administration, application development, database performance monitoring, and database testing

Key Features

  • Build real-world enterprise database management systems using Postgres 12 features
  • Explore the development, administrative and security aspects of PostgreSQL 12
  • Implement best practices from industry experts to build powerful database applications

Book Description

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.

What you will learn

  • Set up high availability PostgreSQL database clusters in the same containment, a cross-containment, and on the cloud
  • Monitor the performance of a PostgreSQL database
  • Create automated unit tests and implement test-driven development for a PostgreSQL database
  • Develop PostgreSQL apps on cloud platforms using DevOps with Python and Node.js
  • Write robust APIs for PostgreSQL databases using Python programming, Node.js, and PostgREST
  • Create a geospatial database using PostGIS and PostgreSQL
  • Implement automatic configuration by Ansible and Terraform for Postgres

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.

Table of Contents

  1. Title Page
  2. Copyright
    1. Developing Modern Database Applications with PostgreSQL
  3. Contributors
    1. About the authors
    2. About the reviewer
    3. Packt is searching for authors like you
  4. About Packt
    1. Why subscribe?
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Download the example code files
    5. Download the color images
    6. Conventions used
    7. Get in touch
    8. Reviews
  6. Section 1 - Introducing PostgreSQL Development and Administration
  7. Introduction to PostgreSQL Development and Administration
    1. An overview of PostgreSQL development
    2. What is DBaaS?
    3. The development of PostgreSQL by various environments
    4. Features of PostgreSQL with NodeJS
    5. Features of PostgreSQL with Python
    6. PostGIS spatial extension
    7. The PostgREST RESTful API for PostgreSQL databases
    8. An overview of PostgreSQL administration
    9. The PostgreSQL architecture
    10. Shared memory
    11. PostgreSQL autovacuum
    12. The PostgreSQL writer and checkpointer
    13. PostgreSQL process types
    14. Managing HA in PostgreSQL
    15. Benchmarking PostgreSQL performance
    16. Key performance factors in PostgreSQL
    17. Using pgbench for PostgreSQL benchmarking
    18. Monitoring PostgreSQL databases
    19. The DevOps environment for PostgreSQL
    20. PostgreSQL testing frameworks
    21. Summary
  8. Section 2 - Development in PostgreSQL
  9. Setting Up a PostgreSQL RDS for ATMs
    1. Technical requirements
    2. An overview of the project
    3. Creating a PostgreSQL RDS with AWS
    4. Creating a PostgreSQL database
    5. Editing the RDS security group
    6. Connecting to a PostgreSQL database instance
    7. The pgAdmin server
    8. The ATM locations table
    9. Creating a PostgreSQL database snapshot
    10. Deleting a PostgreSQL database instance
    11. Restoring data from a PostgreSQL database snapshot
    12. Point-in-time recovery for PostgreSQL
    13. Summary
  10. Using PostgreSQL and Node.js for Banking Transactions
    1. Technical requirements
    2. Setting up a Node.js project
    3. Installing Node.js
    4. RESTful web services
    5. Setting up PostgreSQL for Node.js
    6. Working with the server side: routes
    7. Working with the client side: Angular
    8. Creating an Angular view
    9. Angular app refactoring
    10. Automated testing with Mocha and Chai
    11. Summary
  11. Managing Bank ATM Locations Using PostgreSQL and Django
    1. Technical requirements
    2. Setting up a Django project
    3. Installing Python 3
    4. Creating a virtual environment
    5. Installing Django
    6. Creating a Django project
    7. Creating the ATM app
    8. Django database settings with PostgreSQL
    9. Database models in Django
    10. Migrating the database
    11. Understanding the Django user interface – admin, views, templates, and URLs
    12. Making the atmapp modifiable inside the admin
    13. Exploring the free admin functionality
    14. Summary
  12. Creating a Geospatial Database Using PostGIS and PostgreSQL
    1. Technical requirements
    2. Installing PostGIS for RDS on AWS
    3. Importing spatial data files into PostgreSQL
    4. Setting up QGIS
    5. Loading spatial data using QGIS
    6. Executing PostGIS queries
    7. Ordering ATM locations by distance from the Brooklyn Bridge
    8. Finding ATM locations within 1 kilometer of Times Square
    9. Summary
  13. Managing Banking Transactions using PostgREST
    1. Technical requirements
    2. Introduction to PostgREST
    3. Using Docker
    4. Installing standalone PostgREST
    5. Creating a PostgREST API schema on an RDS (AWS)
    6. Executing PostgREST
    7. Adding a trusted user
    8. Creating a PostgREST token
    9. PostgREST administration
    10. PostgREST on TimescaleDB
    11. Summary
  14. Section 3 - Administration in PostgreSQL
  15. PostgreSQL with DevOps for Continuous Delivery
    1. Technical requirements
    2. Setting up PostgreSQL using Vagrant and VirtualBox
    3. Installing VirtualBox
    4. Installing Vagrant
    5. Selecting a Vagrant box
    6.  Spinning up Ubuntu server 18.04 with Vagrant 
    7. Creating the Puppet module for PostgreSQL
    8. Working with Jenkins and PostgreSQL
    9. Creating an Ansible playbook for PostgreSQL
    10. Managing PostgreSQL by Terraform
    11. Summary
  16. PostgreSQL High Availability Clusters
    1. Technical requirements
    2. Setting up streaming replication on PostgreSQL
    3. Setting up a PostgreSQL HA cluster through the Heimdall data proxy
    4.  Heimdall installation by Docker
    5. Heimdall Webapp and Wizard configuration
    6. Testing load balancing and high availability
    7. Summary
  17. High-Performance Team Dashboards Using PostgreSQL and New Relic
    1. Technical requirements
    2. Signing up for and installing New Relic
    3. Defining PostgreSQL role permissions
    4. Configuring New Relic for PostgreSQL
    5. Adding new metric data for PostgreSQL
    6. Infrastructure inventory data collection
    7. Summary
  18. Testing the Performance of Our Banking App with PGBench and JMeter
    1. Technical requirements
    2. How to benchmark PostgreSQL performance
    3. pgbench 1 – Creating and initializing a benchmark database
    4. pgbench 2 – Running a baseline pgbench test
    5. pgbench 3 – Creating and testing a connection pool
    6. JMeter setup
    7. JMeter for AWS PostgreSQL RDS
    8. Summary
  19. Test Frameworks for PostgreSQL
    1. Technical requirements
    2. Making unit tests with pgTAP
    3. Setting up pgTAP for PostgreSQL RDS
    4. pgTAP test examples
    5. Uninstalling pgTAP for PostgreSQL RDS
    6. Making unit tests in a simple way with PG_Unit
    7. Setting up PGUnit for PostgreSQL RDS
    8. PGUnit test examples
    9. Uninstalling PGUnit for PostgreSQL RDS
    10. PGUnit – same name but a different approach
    11. Setting up simple pgunit for PostgreSQL RDS
    12. Simple pgunit test examples
    13. Uninstalling simple pgunit for PostgreSQL RDS
    14. Testing with Python – Testgres
    15. Setting up Testgres for PostgreSQL
    16. Testgres test examples
    17. Uninstalling Testgres for PostgreSQL RDS
    18. Summary
  20. APPENDIX - PostgreSQL among the Other Current Clouds
    1. Technical requirements
    2. Google Cloud SQL
    3. Creating an instance
    4. pgAdmin connection for Google Cloud
    5. Microsoft Azure for PostgreSQL
    6. Creating an Azure database for the PostgreSQL server
    7. Getting an Azure resource connection for pgAdmin
    8. Heroku Postgres
    9. Creating a Heroku app
    10. Creating a Heroku PostgreSQL database
    11. Connecting Heroku PostgreSQL using pgAdmin
    12. EnterpriseDB cloud database
    13. Creating a PostgreSQL cluster 
    14. Using pgAdmin to connect to EDB PostgreSQL
    15. Summary
  21. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think