How to configure a High Availability System in PostgreSQL

Install, configure and monitor a replica system for 24×7 availability, step by step with out of the box scripts

A database system must be available when it is needed, and even more so in specific business cases such as large companies, emergency services, banks, etc.

What you’ll learn

  • High availability.
  • Replication System.
  • Streaming Replication Asynchronous.
  • Hot standby to allow read-only connections.
  • Failover so that when the primary database goes down, a replica is activated immediately and the service is not interrupted.
  • Switchover to reverse the roles between the Primary and Standby.
  • Promote a standby, that is, make it a master.
  • Clone a standby to connect it to the master.
  • Virtual IP to connect to the main database.
  • Virtual IP to connect to the standby database.

Course Content

  • Introduction –> 1 lecture • 6min.
  • Replica System for High Availability in PostgreSQL 12+ –> 12 lectures • 1hr 36min.

How to configure a High Availability System in PostgreSQL

Requirements

  • Basic Linux knowledge.
  • Basic knowledge of PostgreSQL.

A database system must be available when it is needed, and even more so in specific business cases such as large companies, emergency services, banks, etc.

To guarantee an availability close to 100% it is necessary to implement a replica system that allows us to ensure that clients can work with another server when the main database server has to be disconnected to carry out maintenance tasks or expansion tasks. Anothre feature this replica system enables is that in the event of an uncontrolled drop in service, clients have another server to work with while the problem is resolved.

The availability of 99.99% is possible and in this course I install, configure and monitor a replica system to teach you how to do it, step by step, so that you do not have any difficulties when backing up your PostgreSQL databases with this system. .

Not only will you have a secondary server (also called standby or slave) with which to work, with all the data, in the event of a main server crash, but also you will be able to use that secondary server for reading operations. In so doing, customers who access the system to perform reports, statistics, pattern analysis, etc., will be able to do it through the standby server, reducing the workload on the main server, for the benefit of the entire system.

The replication system that we will configure in this course will have a failover system implemented. This means that, in the event of an unexpected crash of the database service, the standby server will become the new master without intervention from the DBA,  automatically, whatever time it is, whatever day it is, and the service can continue without problems.

Finally, and to further assure the clients of this availability, we will use two virtual IPs, one to connect to the main database, and another to connect to the replica, so that clients do not have the need to know how many servers form part of the replica system, if they are active or down, nor the role that each one of them has.

 

Get Tutorial