Core Logic implements projects using advanced technologies such as Patroni, PG Bouncer, ETCD and PG Backrest, ensuring high availability, reliability and data security in PostgreSQL-based database environments. The solution provides high availability, data security and application performance reliability, minimizing the risk of downtime in case of server failures. The article will outline the main technical principles, challenges and benefits of using this architecture in a wide range of ongoing projects.

In the projects implemented by Core Logic, we use the PostgreSQL cluster architecture with several important elements, such as:

Patrons – a PostgreSQL cluster management tool that is responsible for ensuring high database availability and automatic failover between servers.

ETCD – a distributed configuration management and coordination tool that monitors the status of servers in a cluster and decides which server acts as the leader (primary).

PG Bouncer – a lightweight proxy for managing connections to the PostgreSQL database, which distributes traffic among the database servers, directing SQL queries to the current leader.

PG Backrest – a backup tool that further protects data by storing it on an external server.

PostgreSQL cluster architecture can be implemented in an environment where there are a minimum of two (or more) database servers, one of which acts as the leader (primary) and the others are standby. Servers occurring in the cluster are continuously replicated servers, ensuring real-time data synchronization. In the event of a failure of the leader server, an automatic switchover (failover) takes place in a short time, and the role of the leader is transferred to another standby server.

The central element that coordinates the operation of the entire system is ETCD. This is a service that monitors the status of each server in the cluster, sending information regularly about their current state. In the event of a failure of the main server, ETCD automatically selects a backup server from among those available, so that there is no interruption in the operation of the application.

PG Bouncer, in turn, acts as a “letter carrier” for SQL queries – directing them to the appropriate database server. When there is a failure and a change of server, PG Bouncer receives information about the change and automatically starts redirecting queries to the new server.

The biggest advantage of this solution is security and reliability. Thanks to the Patroni-based architecture, the risk of data loss is almost completely eliminated. Even in the event of a server failure, the system automatically switches to the backup server, which ensures uninterrupted operation of applications without the need for administrator intervention. Manual switching of servers, as was the case with traditional solutions, is no longer necessary.

The second key benefit is the automated failover process. In a situation where one of the servers fails, there is no downtime – the system switches to the backup server by itself, and in the meantime administrators can work on restoring the failed server to normal operation.

Although the documentation of tools such as Patroni, ETCD and PG Bouncer is very well done, the implementation process can encounter problems, such as developing a suitable tool to migrate existing databases to a new Patroni-based environment. In this case, you can create an Ansible playbook that automatically migrates databases to the cluster with Patroni.

Currently, the infrastructure used in Core Logic’s projects meets all performance and reliability assumptions, eliminating the need for immediate resource expansion. However, as the database continues to grow, it is necessary to regularly monitor its condition and consider possible future expansion of hardware resources to maintain optimal system performance and stability.

The implementation of a PostgreSQL cluster with Patroni, PG Bouncer and ETCD has brought numerous benefits, including automation of the failover process, increased data security and reliability of application performance. As a result, such projects can operate without interruption, and data is always available, regardless of possible server failures. This solution is an excellent example of how modern technologies can secure data and improve application performance.