Connect to Postgres Patroni to minimize risks

Bizfly Cloud Database is a service that provides database solutions on a cloud computing platform, helping users and businesses implement an effective data management solution, ensuring data integrity recovery in disaster scenarios. It also monitors information related to users’ database management systems, automatically adjusting data storage capacity according to the resource usage needs of the database management system.

Bizfly Cloud Database is a free service provided by Bizfly Cloud during the Beta and trial period. The information related to usage costs is for reference only.

PostgreSQL Patroni is an open-source tool used to manage and deploy a highly available distributed PostgreSQL cluster. It is a component of the Patroni project, developed by Zalando SE. Patroni uses the concepts and mechanisms of PostgreSQL Streaming Replication and PostgreSQL High Availability (HA) to create a self-healing and highly available PostgreSQL cluster. Between PostgreSQL servers in the cluster, Patroni uses etcd or ZooKeeper to store configuration and state information of the cluster. Patroni simplifies the deployment and management of highly available PostgreSQL clusters. It provides automatic mechanisms to ensure that PostgreSQL data and services remain continuously operational and can recover from failures. Currently, Bizfly Cloud provides a PostgreSQL database engine with the Patroni component pre-installed to facilitate the deployment of database models suitable for user needs.

Connection Guide

First, access the dashboard to create a database.

Select the PostgreSQL database engine with the Patroni component available.

After the database instance is built, you can access the database instance management page. Go to the nodes section and create an additional secondary node.

Enter the name of the second node and select Confirm to create.

Testing Guide

You can further test the Patroni component through the failover feature with the following code:

import psycopg2

hosts = [
    'your_database_host'
]

hosts = ",".join(hosts)
database_name = "postgres"
username = "root"
password = "password_database"
port = 5432
target_session_attrs = "any"

conn = psycopg2.connect(
    database=database_name,
    host=hosts,
    user=username,
    password=password,
    port=port,
    target_session_attrs=target_session_attrs
)

cur = conn.cursor()
cur.execute("select pg_is_in_recovery(), pg_postmaster_start_time()")
row = cur.fetchone()
print("recovery = ", row[0])
print("time = ", row[1])

The returned result will be:

recovery =  False
time =  2023-12-27 07:09:41.656979+00:00

After shutting down the primary node, the secondary node is promoted to become the primary node, and the connection remains uninterrupted.

The returned result will be:

recovery =  False
time =  2023-12-27 08:18:02.939977+00:00

Example code

• When using the provided account to connect to the Patroni PostgreSQL system, SSL must be used to connect to this default account to ensure data security.

• Using NodeJS to connect to Patroni:

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
  ssl: {
    rejectUnauthorized: false,
  },
});

pool.connect()
  .then(client => {
    console.log('Connected to the database');
    client.release();
  })
  .catch(err => console.error('Connection error', err.stack));