PostgreSQL Replication, High Availability HA and Scalability

Solutions for Scaling Postgres with Master-Slave Replication, PgBouncer, PgPool II, HAProxy, Partitioning, Sharding

4.33 (259 reviews)
Udemy
platform
English
language
Databases
category
instructor
1,956
students
3 hours
content
May 2022
last update
$64.99
regular price

What you will learn

Assess your scaling needs

How to scale reads using Replication and Load-Balancing

Which is the best Replication solution for a certain use case

How to manage database connections with PgBouncer connection pooler

How to make use of multiple PostgreSQL instances in the cloud (Google Cloud)

How to achieve High-Availability

How to perform Automatic Failover using PgPool II

How to scale writes using Partitioning and Sharding

Description

PostgreSQL is one of the most powerful and easy-to-use database management systems. It has strong support from the community and is being actively developed with a new release every year.

PostgreSQL supports the most advanced features included in SQL standards. It also provides NoSQL capabilities and very rich data types and extensions. All of this makes PostgreSQL a very attractive solution in software systems.


In this course, we discussed the problem of building scalable solutions based on PostgreSQL utilizing the resources of several servers. There is a natural limitation for such systems—basically, there is always a compromise between performance, reliability, and consistency. It's possible to improve one aspect, but others will suffer. In this course, we'll see how to find the best match for our use-cases so that we know eactly which aspects need scaling, and avoid the common trade-offs of distributed systems.


Scaling PostgreSQL is a journey. You should come out of this course more prepared to assess your scaling needs and understand how to scale reads and how to scale writes.


Each of this solution presented in this course will improve some aspect of the scalability topic, but each of them will add some complexity, and maybe some limitation or constraint.

We have to ask the right questions to get the system requirements, and this why we dedicated an entire lecture, so that we examine what questions we have to put ourself, before starting the Scaling Journey.

After this course, we should come out more prepared and understand how to scale reads.

We have several options for replication, depending on wether we favor performance or flexibility.

Replication can be used as a backup or a standby solution that would take over in case the main server crashes.

Replication can also be used to improve the performance of a software system by making it possible to distribute the load on several database servers.

Then, if we have one sort of replication in place, we could ask ourself if we want to allow several computers to serve the same data.

To achieve this, we should have a mechanism to distribute the requests. We’ll see here two of the most popular options available.

Next, if the number of database connections is great, then we’ll probably want to use a connection pooler. Again, we’ll cover two options here.

We’ll also see, how to scale writes, and how to make your traffic growth more predictable by adding queuing to your architecture.

Then, we’ll check partitioning for those cases when we have to deal with big tables.

Also, we’ll check sharding to scale writes, and all the complex decisions that come with it.

Finally, we’ll see shortly the multi-master solution, which is a relatively new concept that seems to be promising.

If our goal is to achieve only High availability, or the ability to continue working even in the situation where one part of the cluster fails, we can check out only those solutions.

The pre-requirements for HA is to put in place a replication strategy.

Then, we can use tools to allow a second server to take over quickly, if the primary server fails.


Introduction to Scaling PostgreSQL

  • Why scale PostgreSQL?

  • What is Vertical Scaling?

  • What is Horizontal Scaling?

  • Read Versus Write Bound Workloads

  • Why Statistics are essential?

  • How to enable and make us of Statistics? (Hands-on)

  • How to scale Postgres for Reads?

  • How replication helps to scale out?

  • What are the Load-Balancers?

  • How to scale Postgres for Writes?

  • How to make use of Queues?

  • How could Partitioning and Sharding help in scaling out?

  • What is the Multi-Master solution about?


Understanding the Limitations of Scaling out PostgreSQL

  • CAP Theorem Explained

  • PostgreSQL vs. Cassandra

  • Use case: CA Systems

  • Use case: AP Systems


How to use Streaming Replication?

  • What is Streaming Replication?

  • Asynchronous vs. Synchronous Replication

  • How to Initialise Primary Database? (Hands-on)

  • How to Configuring the Primary for Replication?  (Hands-on)

  • How to Configuring the Replica Instance?  (Hands-on)

  • Testing Replication Setup  (Hands-on)


How to use Logical Replication?

  • What is Logical Replication in Postgres?

  • Step by step Logical Replication setup

  • How to setup the servers for Logical Replication? (Hands-on)

  • How to make a selective Copy of the Data? (Hands-on)

  • How to Create the Publication? (Hands-on)

  • How to Create the Subscription? (Hands-on)

  • Postgres Limitations of Logical Replication

  • How to Monitoring Logical Replication? (Hands-on)

  • Best use-cases for using Logical Replication


How to make use of PgBouncer?

  • What is PgBouncer?

  • Fundamental concepts of connection pooling

  • How to build a PgBouncer Setup? (Hands-on)

  • How to install and configure PgBouncer? (Hands-on)

  • How to create a basic configuration file for PgBouncer? (Hands-on)

  • How to connect to PgBouncer? (Hands-on)

  • Explaining Advanced Settings for Performance

  • Which are the available Pool Modes?

  • Executing a benchmark with PgBouncer (Hands-on)


How to scale PostgreSQL in Google Cloud?

  • Introduction

  • Key Components on Google Cloud

  • Key Characteristics of the Architecture

  • How to create PostgreSQL Instances on Google Cloud?  (Hands-on)

  • How to create a Google Cloud Engine (GCE) for HAProxy? (Hands-on)

  • How to configure HAProxy for Load-Balancing? (Hands-on)

  • Testing Load-Balancing


How to make use of PostgreSQL Partitioning?

  • What is Partitioning?

  • Which Tables Need Partitioning?

  • How should the Tables be Partitioned? 

  • Declarative vs. Inheritance Partitioning

  • How to create a Partitioned Table? (Hands-on)

  • Partitioning Methods


How to Shard PostgreSQL?

  • What is Sharding?

  • Pain-Points of Sharding?

  • What is Second Level Sharding?

  • What is good Sharding?

  • How to query across multiple Shards?


How to setup High Availability (HA) on PostgreSQL?

  • Why High Availability?

  • Steps to achieve High Availability

  • Essential Questions to ask before setting-up High Availability

  • Log-Shipping Replication

  • Streaming Replication and Logical Replication

  • Cascading Replication

  • Synchronous vs. Asynchronous Replication

  • Automatic Failover and Always-on Strategy

  • Simple HA Solution Example

  • Better HA Solution Example


How to make use of PgPool II?

  • What is PgPool II?

  • Pgpool-II Features

  • How to Configure Pgpool-II with Streaming Replication? (Hands-on)

  • How to setup Streaming Replication? (Hands-on)

  • How to Configuring Pgpool-II for Load Balancing ? (Hands-on)

  • Testing load-balancing & read/write separation (Hands-on)

  • How to Configure Pgpool for PostgreSQL High-Availability? (Hands-on)

  • How to Configure PostgreSQL Primary Server? (Hands-on)

  • How to Configure Pgpool-II Server? (Hands-on)

  • How to Configure PostgreSQL Replica Server? (Hands-on)

  • Testing The Failover (Hands-on)

  • How to restore failed nodes? (Hands-on)



Content

Scaling PostgreSQL

Why Scale PostgreSQL?
Quiz Why Scale PostgreSQL?
Vertical Scaling
Quiz Vertical Scaling
Horizontal Scaling
Horizontal Scaling
CAP Theorem Explained
PostgreSQL vs. NoSQL
Quiz PostgreSQL vs. NoSQL
Use case: Consistent and Available System
Quiz Use case: Consistent and Available System
Use case: Available and Partition-tolerant System
Quiz Use case: Available and Partition-tolerant System
Read Versus Write Bound Workload
Quiz Read Versus Write Bound Workload
How statistics will answer to all questions?
Quiz How statistics will answer to all questions?
Enable Statistics
Quiz Enable Statistics
Replication
Quiz Replication
Load Balancing
Connection Pooling
Quiz Connection Pooling
Queuing
Partitioning
Sharding
Quiz Sharding
Multi-master

Streaming Replication

What is Streaming Replication?
Asynchronous vs. Synchronous Replication
Quiz Asynchronous vs. Synchronous Replication
Hands-on - Initialise Primary Database
Hands-on - Initialise Primary Database
Configuring the Primary for Replication
Quiz Configuring the Primary for Replication
Configuring the Replica Instance
Quiz Configuring the Replica Instance
Testing Replication Setup

Logical Replication

What is Logical Replication in Postgres?
Quiz What is Logical Replication in Postgres?
Setting-up Postgres Servers for Logical Replication
Quiz Setting-up Postgres Servers for Logical Replication
Selective Copy of the Data
Quiz Selective Copy of the Data
Create the Publication
Create the Subscription
Quiz Create the Subscription
Limitations of Logical Replication
Quiz Limitations of Logical Replication
Monitoring Logical Replication
Best use-cases for Logical Replication

PgBouncer

Introduction
Quiz Introduction
Fundamental concepts of connection pooling
Quiz Fundamental concepts of connection pooling
Building a PgBouncer Setup
Quiz Building a PgBouncer Setup
Installing and Configure PgBouncer
Creating a basic configuration file for PgBouncer
Quiz Installing and Configure PgBouncer
Connecting to PgBouncer
Advanced Settings for Performance
Quiz Advanced Settings for Performance
Pool Modes
A simple benchmark

Scaling PostgreSQL with Google Cloud and HAProxy

Introduction
Quiz Introduction
Key Components
Key Characteristics of the Architecture
Creating PostgreSQL Instances on Google Cloud
Quiz Creating PostgreSQL Instances on Google Cloud
Creating a GCE for HAProxy
Configure HAProxy for Load-Balancing
Quiz Configure HAProxy for Load-Balancing
Testing Load-Balancing

Partitioning

Introduction
Quiz Introduction
Which Tables Need Partitioning?
Quiz Which Tables Need Partitioning?
How should the Tables be Partitioned?
Quiz How should the Tables be Partitioned?
Declarative vs. Inheritance Partitioning
Creating a Partitioned Table
Quiz Creating a Partitioned Table
Partitioning Methods

Sharding

Introduction
Quiz Introduction
Pain Points of Sharding
Quiz Pain Points of Sharding
How to Partition Data in PostgreSQL
Quiz How to Partition Data in PostgreSQL
Second Level Sharding
Quiz Second Level Sharding
Querying Across Shards

PostgreSQL High Availability

Why High Availability?
Steps to achieve High Availability
Quiz Steps to achieve High Availability
Essential Questions to set-up High Availability
Log-Shipping Replication
Quiz Log-Shipping Replication
Streaming Replication and Logical Replication
Quiz Streaming Replication and Logical Replication
Cascading Replication
Synchronous vs. Asynchronous Replication
Quiz Synchronous vs. Asynchronous Replication
Automatic Failover and Always-on Strategy
Simple HA Solution Example
Better HA Solution Example

PgPool II

Introduction
Pgpool-II Features
Quiz Pgpool-II Features
Configure Pgpool-II with Streaming Replication
Quiz Configure Pgpool-II with Streaming Replication
Setting up Streaming Replication
Configuring Pgpool-II for Load Balancing
Quiz Configuring Pgpool-II for Load Balancing
Testing load-balancing & read/write separation
Configure Pgpool for PostgreSQL High-Availability
Configuring PostgreSQL Primary Server
Quiz Configuring PostgreSQL Primary Server
Configuring Pgpool-II Server
Configuring PostgreSQL Replica Server
Quiz Configuring PostgreSQL Replica Server
Testing The Failover
Restoring failed nodes
Quiz Restoring failed nodes

Screenshots

PostgreSQL Replication, High Availability HA and Scalability - Screenshot_01PostgreSQL Replication, High Availability HA and Scalability - Screenshot_02PostgreSQL Replication, High Availability HA and Scalability - Screenshot_03PostgreSQL Replication, High Availability HA and Scalability - Screenshot_04

Reviews

Guilherme
September 4, 2023
The course content is very good, it managed to introduce me to several new things and held my attention throughout the course. I just wish there were more examples on primary server failback.
Gianluca
August 3, 2023
A good hands-on course about HA for PostgreSQL, but it doesn't shows how to configure HA on the pgpool side.
Motsoaledi
March 22, 2023
A lot of concepts are being explained. You'll only get one example of each so if you really want to understand, you're going to have to have your own practice examples multiple times. It would be nice if sample code was also made available to download.
Gianpio
February 26, 2023
Corso molto chiaro ed esaustivo, ho molto apprezzato le slide animate che a colpo d'occhio chiariscono il concetto trattato. Eccezionale la parte pratica con tutti i comandi da eseguire per mettere in atto quanto illustrato sull'argomento.
Rishabh
February 16, 2023
I recently completed a course on PostgreSQL Replication, High Availability HA and Scalability, and while I appreciate the effort you put into creating the content, I have to say that I was disappointed with the level of detail provided. The video was more of an overview than an in-depth exploration of the topic. As a viewer, I was hoping to gain a deeper understanding of the subject matter, but I found that the video only scratched the surface. It would have been helpful if you had gone into more detail, provided examples, or shared more insights to help me fully understand the topic. Going forward, I would suggest that you consider delving deeper into the topics you cover in your videos. Your audience will appreciate a more comprehensive explanation, and it will also help to differentiate your content from others in your field. Thank you for your hard work and dedication to creating informative videos.
Safi'ee
December 7, 2022
Very detail explanation and demonstration on implementing High Availability of PostgreSQL database. All demonstration is work on our own machine whether is single machine or multiple server. But it also good if it demonstrate the HA Proxy implementation without using Google Cloud because we want to see how its implement on Premise environment.
Christian
December 4, 2022
There are a lot of empty space. For example, configure the user and password in pgpool with SCRAM-SHA-256
Clément
December 2, 2022
Very good course, animated slides and terminal demo to really understand the concepts as if the student was typing herself. Small typos or misprononciations of english, but I understood it all nonetheless.
Fernando
October 11, 2022
It is great as a practical course because you can go along and doing the practices with the instructor or just receiving the information fast. Also the way the units are split favors advancing quickly. I am using it to prepare for a work interview.
Pruthvi
August 3, 2022
Good Course. It covers things at high level, which is very good when you are trying to find what what is the best solution for a certain usecase.
Rashad
July 17, 2022
Does not explain details of installation. Also the commands are no longer relative to the latest version
Juan
July 8, 2022
The instructor explains it very well, goes straight to the point and he manages to make a well balanced mix of theory with practice.

Coupons

DateDiscountStatus
5/30/2022100% OFF
expired

Charts

Price

PostgreSQL Replication, High Availability HA and Scalability - Price chart

Rating

PostgreSQL Replication, High Availability HA and Scalability - Ratings chart

Enrollment distribution

PostgreSQL Replication, High Availability HA and Scalability - Distribution chart
4685084
udemy ID
5/13/2022
course created date
5/30/2022
course indexed date
Angelcrc Seven
course submited by