Database Design & DevelopmentTrending Courses

PostgreSQL Replication, High Availability HA and Scalability


PostgreSQL is likely one of the strongest and easy-to-use database administration techniques. It has sturdy help from the neighborhood and is being actively developed with a brand new launch yearly.

PostgreSQL helps essentially the most superior options included in SQL requirements. It additionally supplies NoSQL capabilities and very wealthy knowledge sorts and extensions. All of this makes PostgreSQL a really engaging answer in software program techniques.

On this course, we mentioned the issue of constructing scalable options primarily based on PostgreSQL using the assets of a number of servers. There’s a pure limitation for such techniques—mainly, there’s at all times a compromise between efficiency, reliability, and consistency. It is attainable to enhance one side, however others will endure. On this course, we’ll see find out how to discover the very best match for our use-cases in order that we all know eactly which features want scaling, and keep away from the widespread trade-offs of distributed techniques.

Scaling PostgreSQL is a journey. You must come out of this course extra ready to evaluate your scaling wants and perceive find out how to scale reads and find out how to scale writes.

Every of this answer introduced on this course will enhance some side of the scalability matter, however every of them will add some complexity, and perhaps some limitation or constraint.

We now have to ask the fitting inquiries to get the system necessities, and this why we devoted a complete lecture, in order that we study what questions we now have to place ourself, earlier than beginning the Scaling Journey.

After this course, we should always come out extra ready and perceive find out how to scale reads.

We now have a number of choices for replication, relying on wether we favor efficiency or flexibility.

Replication can be utilized as a backup or a standby answer that might take over in case the primary server crashes.

Replication may also be used to enhance the efficiency of a software program system by making it attainable to distribute the load on a number of database servers.

Then, if we now have one kind of replication in place, we might ask ourself if we wish to enable a number of computer systems to serve the identical knowledge.

To realize this, we should always have a mechanism to distribute the requests. We’ll see right here two of the most well-liked choices obtainable.

Subsequent, if the variety of database connections is nice, then we’ll in all probability wish to use a connection pooler. Once more, we’ll cowl two choices right here.

We’ll additionally see, find out how to scale writes, and find out how to make your visitors progress extra predictable by including queuing to your structure.

Then, we’ll verify partitioning for these circumstances when we now have to cope with large tables.

Additionally, we’ll verify sharding to scale writes, and all of the complicated choices that include it.

Lastly, we’ll see shortly the multi-master answer, which is a comparatively new idea that appears to be promising.

If our purpose is to realize solely High availability, or the flexibility to proceed working even within the scenario the place one a part of the cluster fails, we are able to try solely these options.

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

Then, we are able to use instruments to permit a second server to take over shortly, if the first server fails.

Introduction to Scaling PostgreSQL

  • Why scale PostgreSQL?

  • What’s Vertical Scaling?

  • What’s Horizontal Scaling?

  • Learn Versus Write Sure Workloads

  • Why Statistics are important?

  • Easy methods to allow and make us of Statistics? (Palms-on)

  • Easy methods to scale Postgres for Reads?

  • How replication helps to scale out?

  • What are the Load-Balancers?

  • Easy methods to scale Postgres for Writes?

  • Easy methods to make use of Queues?

  • How might Partitioning and Sharding assist in scaling out?

  • What’s the Multi-Grasp answer about?

Understanding the Limitations of Scaling out PostgreSQL

  • CAP Theorem Defined

  • PostgreSQL vs. Cassandra

  • Use case: CA Techniques

  • Use case: AP Techniques

Easy methods to use Streaming Replication?

  • What’s Streaming Replication?

  • Asynchronous vs. Synchronous Replication

  • Easy methods to Initialise Major Database? (Palms-on)

  • Easy methods to Configuring the Major for Replication?  (Palms-on)

  • Easy methods to Configuring the Reproduction Occasion?  (Palms-on)

  • Testing Replication Setup  (Palms-on)

Easy methods to use Logical Replication?

  • What’s Logical Replication in Postgres?

  • Step-by-step Logical Replication setup

  • Easy methods to setup the servers for Logical Replication? (Palms-on)

  • Easy methods to make a selective Copy of the Knowledge? (Palms-on)

  • Easy methods to Create the Publication? (Palms-on)

  • Easy methods to Create the Subscription? (Palms-on)

  • Postgres Limitations of Logical Replication

  • Easy methods to Monitoring Logical Replication? (Palms-on)

  • Finest use-cases for utilizing Logical Replication

Easy methods to make use of PgBouncer?

  • What’s PgBouncer?

  • Elementary ideas of connection pooling

  • Easy methods to construct a PgBouncer Setup? (Palms-on)

  • Easy methods to set up and configure PgBouncer? (Palms-on)

  • Easy methods to create a primary configuration file for PgBouncer? (Palms-on)

  • How to hook up with PgBouncer? (Palms-on)

  • Explaining Superior Settings for Efficiency

  • That are the obtainable Pool Modes?

  • Executing a benchmark with PgBouncer (Palms-on)

Easy methods to scale PostgreSQL in Google Cloud?

  • Introduction

  • Key Elements on Google Cloud

  • Key Traits of the Structure

  • Easy methods to create PostgreSQL Cases on Google Cloud?  (Palms-on)

  • Easy methods to create a Google Cloud Engine (GCE) for HAProxy? (Palms-on)

  • Easy methods to configure HAProxy for Load-Balancing? (Palms-on)

  • Testing Load-Balancing

Easy methods to make use of PostgreSQL Partitioning?

  • What’s Partitioning?

  • Which Tables Want Partitioning?

  • How ought to the Tables be Partitioned? 

  • Declarative vs. Inheritance Partitioning

  • Easy methods to create a Partitioned Desk? (Palms-on)

  • Partitioning Strategies

Easy methods to Shard PostgreSQL?

  • What’s Sharding?

  • Ache-Factors of Sharding?

  • What’s Second Degree Sharding?

  • What is nice Sharding?

  • Easy methods to question throughout a number of Shards?

Easy methods to setup High Availability (HA) on PostgreSQL?

  • Why High Availability?

  • Steps to realize High Availability

  • Important Inquiries to ask earlier than setting-up High Availability

  • Log-Transport Replication

  • Streaming Replication and Logical Replication

  • Cascading Replication

  • Synchronous vs. Asynchronous Replication

  • Automated Failover and All the time-on Technique

  • Easy HA Resolution Instance

  • Higher HA Resolution Instance

Easy methods to make use of PgPool II?

  • What’s PgPool II?

  • Pgpool-II Options

  • Easy methods to Configure Pgpool-II with Streaming Replication? (Palms-on)

  • Easy methods to setup Streaming Replication? (Palms-on)

  • Easy methods to Configuring Pgpool-II for Load Balancing ? (Palms-on)

  • Testing load-balancing & learn/write separation (Palms-on)

  • Easy methods to Configure Pgpool for PostgreSQL High-Availability? (Palms-on)

  • Easy methods to Configure PostgreSQL Major Server? (Palms-on)

  • Easy methods to Configure Pgpool-II Server? (Palms-on)

  • Easy methods to Configure PostgreSQL Reproduction Server? (Palms-on)

  • Testing The Failover (Palms-on)

  • Easy methods to restore failed nodes? (Palms-on)



Get Coupon

Join us on telegram for Course Updates

Join Whatsapp Group for Daily Free Courses

Leave a Reply

Your email address will not be published. Required fields are marked *