PostgreSQL Replication, High Availability HA and Scalability

PostgreSQL Replication, High Availability HA and Scalability, available at $64.99, has an average rating of 4.39, with 80 lectures, 47 quizzes, based on 321 reviews, and has 2327 subscribers.

You will learn about 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 This course is ideal for individuals who are Software Engineers interested in designing Scalable and HA solutions on top of PostgreSQL or Database Administrators or Everyone interested in building better PostgreSQL applications It is particularly useful for Software Engineers interested in designing Scalable and HA solutions on top of PostgreSQL or Database Administrators or Everyone interested in building better PostgreSQL applications.

Enroll now: PostgreSQL Replication, High Availability HA and Scalability

Summary

Title: PostgreSQL Replication, High Availability HA and Scalability

Price: $64.99

Average Rating: 4.39

Number of Lectures: 80

Number of Quizzes: 47

Number of Published Lectures: 80

Number of Published Quizzes: 47

Number of Curriculum Items: 127

Number of Published Curriculum Objects: 127

Original Price: $49.99

Quality Status: approved

Status: Live

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

Who Should Attend

Software Engineers interested in designing Scalable and HA solutions on top of PostgreSQL

Database Administrators

Everyone interested in building better PostgreSQL applications

Target Audiences

Software Engineers interested in designing Scalable and HA solutions on top of PostgreSQL

Database Administrators

Everyone interested in building better PostgreSQL applications

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)

Course Curriculum

Chapter 1: Scaling PostgreSQL

Lecture 1: Why Scale PostgreSQL?

Lecture 2: Vertical Scaling

Lecture 3: Horizontal Scaling

Lecture 4: CAP Theorem Explained

Lecture 5: PostgreSQL vs. NoSQL

Lecture 6: Use case: Consistent and Available System

Lecture 7: Use case: Available and Partition-tolerant System

Lecture 8: Read Versus Write Bound Workload

Lecture 9: How statistics will answer to all questions?

Lecture 10: Enable Statistics

Lecture 11: Replication

Lecture 12: Load Balancing

Lecture 13: Connection Pooling

Lecture 14: Queuing

Lecture 15: Partitioning

Lecture 16: Sharding

Lecture 17: Multi-master

Chapter 2: Streaming Replication

Lecture 1: What is Streaming Replication?

Lecture 2: Asynchronous vs. Synchronous Replication

Lecture 3: Hands-on – Initialise Primary Database

Lecture 4: Configuring the Primary for Replication

Lecture 5: Configuring the Replica Instance

Lecture 6: Testing Replication Setup

Chapter 3: Logical Replication

Lecture 1: What is Logical Replication in Postgres?

Lecture 2: Setting-up Postgres Servers for Logical Replication

Lecture 3: Selective Copy of the Data

Lecture 4: Create the Publication

Lecture 5: Create the Subscription

Lecture 6: Limitations of Logical Replication

Lecture 7: Monitoring Logical Replication

Lecture 8: Best use-cases for Logical Replication

Chapter 4: PgBouncer

Lecture 1: Introduction

Lecture 2: Fundamental concepts of connection pooling

Lecture 3: Building a PgBouncer Setup

Lecture 4: Installing and Configure PgBouncer

Lecture 5: Creating a basic configuration file for PgBouncer

Lecture 6: Connecting to PgBouncer

Lecture 7: Advanced Settings for Performance

Lecture 8: Pool Modes

Lecture 9: A simple benchmark

Chapter 5: Scaling PostgreSQL with Google Cloud and HAProxy

Lecture 1: Introduction

Lecture 2: Key Components

Lecture 3: Key Characteristics of the Architecture

Lecture 4: Creating PostgreSQL Instances on Google Cloud

Lecture 5: Creating a GCE for HAProxy

Lecture 6: Configure HAProxy for Load-Balancing

Lecture 7: Testing Load-Balancing

Chapter 6: Partitioning

Lecture 1: Introduction

Lecture 2: Which Tables Need Partitioning?

Lecture 3: How should the Tables be Partitioned?

Lecture 4: Declarative vs. Inheritance Partitioning

Lecture 5: Creating a Partitioned Table

Lecture 6: Partitioning Methods

Chapter 7: Sharding

Lecture 1: Introduction

Lecture 2: Pain Points of Sharding

Lecture 3: How to Partition Data in PostgreSQL

Lecture 4: Second Level Sharding

Instructors

Lucian Oprea
Software Engineer

Rating Distribution

1 stars: 3 votes

2 stars: 12 votes

3 stars: 44 votes

4 stars: 115 votes

5 stars: 147 votes

Frequently Asked Questions

How long do I have access to the course materials?

You can view and review the lecture materials indefinitely, like an on-demand channel.

Can I take my courses with me wherever I go?

Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don’t have an internet connection, some instructors also let their students download course lectures. That’s up to the instructor though, so make sure you get on their good side!