Multi-master replication solution for PostgreSQL

May 24, 2021

With the growing demand for databases for several applications in the modern world, scaling them is equally important to increase the database capability to store and process more data. Achieving the same can be critical. Database scaling can be done horizontally or vertically. While vertical scaling means adding resources such as a vCPU's, disk, or memory to the existing nodes, horizontal scaling deals with adding nodes with the existing nodes behind the load balancer. Horizontal scalability eliminates the need to add the new hardware in the existing node and cuts the related cost too.

Implementing horizontal scalability is not as easy as it appears and demands development effort. PostgreSQL is a stable database management system, known for its resilience and integrity. The qualities of this open-source relational database management system make it preferable for large-scale web, mobile, and analytical applications. Due to its added stability, PostgreSQL offers both vertical and horizontal scalability. This article explains one of the most important concepts of PostgreSQL, that is, multi-master replication, and how it is implemented practically for scaling resources.

What is Multi-master Replication?

Multi-Master Replication is a concept where data is replicated in a group of connected database servers and anyone can update the database which is replicated to other systems updated by those servers. The multi-master replication adds a bi-directionality element to the process.

In Multi-master replication, multiple nodes accept write queries, and all the nodes house the same data. The primary usage of multi-master replication is ensuring all-time availability, thus when the primary server experiences failure, standby is not required.

In the further section, we will explain the actual Multi-master Replication in PostgreSQL.

PostgreSQL Multi-master Replication

PostgreSQL is by default built to offer single-master replication and multi-master replication is not present in it. At present, some vendors offer Bidirectional replication products supporting multi-master replication in PostgreSQL. The latest PostgreSQL versions are not open-source.

Multi-master Replication is achievable using Bidirectional replication (BDR), which is explained further.

PostgreSQL Multi-master Replication Process

Multimaster replication in PostgreSQL is done in six parts and starts once the system configuration is done.

Part 1: System Configuration

This is the first part of replication where BDR installation is performed with the pglogical plugin (logical replication solution). Here, postgresql. conf and pg_hba.conf files are modified and the service is restarted explained in the following steps.

Step1: Install pglogical plugin and BDR.

Step 2: Configure postgresql.conf file using the commands:

wal_level = logical

shared_preload_libraries= ‘pg_logical, bdr’

track_commit_timestamp= ‘on’   (For resolving conflict).

Step 3: Create a user and grant it superuser privileges for managing BDR connectivity.

CREATE USER user1 WITH SUPERUSER REPLICATION PASSWORD ‘user_password’;

Step 4: Alter pg_hba.conf file using the commands

host   all  bdr   10.20.30.40/24   md5

host   replication bdr   10.20.30.40/24   md5

Step 5: Add .pgpass file user.

hostname:port:database:_user1: user_password

Step 6: Now, restart Postgresql

The next part will explain how a single BDR node is created.

Part 2: Single BDR Node Creation

For creating the BDR node, activate BDR on db1 and the host ‘host1’ as ‘user1’.

Step 1: Create an extension.

CREATE EXTENSION bdr CASCADE;  (CASCADE is used to create the extension.)

Step 2: Initialise the current node.

SELECT bdr.create_node( node_name:= ‘initail_node’, local_dsn:= ‘dbname=db1 host= host1 user=user1’);

Step 3: Create BDR cluster definition.

SELECT bdr.create_node_group(node_group_name:='the_node_group');

Step 4: Wait for the bdr.wait_for_join_completion function to complete.

SELECT bdr.wait_for_join_completion();

We have created the BDR cluster, now let’s create another BDR Node.

Part 3: Another BDR Node Creation

Step 1: Create a BDR extension using the command:

CREATE EXTENSION bdr CASCADE;

Step 2: Initialise the current node with the command on another host:

SELECT bdr.create_node( node_name:= ‘next_node’, local_dsn:= ‘dbname=db1 host= host2 user=user1’);

Step 3: Create the BDR cluster definition.

SELECT bdr.join_node_group(join_target_dsn:= ‘dbname=db1 host= host1 user=user1’, wait_for_completion:=True);

Part 4: HAProxy Configuration

For getting higher availability, install HAProxy software by following the steps below:

Step 1: Modify the haproxy.cfg file global section.

global

state socket /var/run/haproxy/sock level admin  (For sending HAProxy commands and retrieving information)

Step 2: Edit the haproxy.cfg file’s bk_db.

stick-table type ip size 1

stick on dst

server bdr_initial_node host1: 5432 check

server bdr_next_node my_host2: 5432 backup check

Step 3: Reload the configuration file.

sudo systemctl reload haproxy

Part 5: Merge PgBouncer to HAProxy

PgBouncer pools PostgreSQL connection with which PostgreSQL interacts with the clients. HAProxy redirects traffic and PgBouncer completes the transactions. PgBouncer is using the below steps.

Step 1: In ft_postgresql, modify the haproxy.cfg file.

frontend ft_postgresql

bind *: 5433

default_backend bk_db

Step 2: Change the databases section in the pgbouncer.ini file

* = host= proxy_server port= 5433

For modifying the pgbouncer section, use:

listen_port = 5432

Step 3: Restart HAProxy and PgBouncer.

Part 6: Switching over the Node

Lastly, we will be moving away from the connections from the initial node by making some changes and reinitialising the server.

Step 1: Disable HAProxy initial_node.

echo “disable server bk_db/bdr_initial_node” | socat /var/run/haproxy/sock –

Step 2: Reconnect PgBouncer should reconnect and wait for connections to be re-established.

psql –h proxy_server –U pgbouncer pgbouncer –c “For reconnection”

psql –h proxy_server –U pgbouncer pgbouncer –c “For waiting”

Step 3: Manage the initial node before re-enabling.

Step 4: Re-enable the initial node.

echo “enable server bk_db/bdr_initial_node” |  socat /var/run/haproxy/sock –

Conclusion:

Here, we conclude the multi-master replication process in PostgreSQL, which goes through several stages: installation, upgradation, and testing. We have tried to explain how the process is accomplished using simple steps. We hope it proves to be useful for you.

Latest Blogs
This is a decorative image for: A Complete Guide To Customer Acquisition For Startups
October 18, 2022

A Complete Guide To Customer Acquisition For Startups

Any business is enlivened by its customers. Therefore, a strategy to constantly bring in new clients is an ongoing requirement. In this regard, having a proper customer acquisition strategy can be of great importance.

So, if you are just starting your business, or planning to expand it, read on to learn more about this concept.

The problem with customer acquisition

As an organization, when working in a diverse and competitive market like India, you need to have a well-defined customer acquisition strategy to attain success. However, this is where most startups struggle. Now, you may have a great product or service, but if you are not in the right place targeting the right demographic, you are not likely to get the results you want.

To resolve this, typically, companies invest, but if that is not channelized properly, it will be futile.

So, the best way out of this dilemma is to have a clear customer acquisition strategy in place.

How can you create the ideal customer acquisition strategy for your business?

  • Define what your goals are

You need to define your goals so that you can meet the revenue expectations you have for the current fiscal year. You need to find a value for the metrics –

  • MRR – Monthly recurring revenue, which tells you all the income that can be generated from all your income channels.
  • CLV – Customer lifetime value tells you how much a customer is willing to spend on your business during your mutual relationship duration.  
  • CAC – Customer acquisition costs, which tells how much your organization needs to spend to acquire customers constantly.
  • Churn rate – It tells you the rate at which customers stop doing business.

All these metrics tell you how well you will be able to grow your business and revenue.

  • Identify your ideal customers

You need to understand who your current customers are and who your target customers are. Once you are aware of your customer base, you can focus your energies in that direction and get the maximum sale of your products or services. You can also understand what your customers require through various analytics and markers and address them to leverage your products/services towards them.

  • Choose your channels for customer acquisition

How will you acquire customers who will eventually tell at what scale and at what rate you need to expand your business? You could market and sell your products on social media channels like Instagram, Facebook and YouTube, or invest in paid marketing like Google Ads. You need to develop a unique strategy for each of these channels. 

  • Communicate with your customers

If you know exactly what your customers have in mind, then you will be able to develop your customer strategy with a clear perspective in mind. You can do it through surveys or customer opinion forms, email contact forms, blog posts and social media posts. After that, you just need to measure the analytics, clearly understand the insights, and improve your strategy accordingly.

Combining these strategies with your long-term business plan will bring results. However, there will be challenges on the way, where you need to adapt as per the requirements to make the most of it. At the same time, introducing new technologies like AI and ML can also solve such issues easily. To learn more about the use of AI and ML and how they are transforming businesses, keep referring to the blog section of E2E Networks.

Reference Links

https://www.helpscout.com/customer-acquisition/

https://www.cloudways.com/blog/customer-acquisition-strategy-for-startups/

https://blog.hubspot.com/service/customer-acquisition

This is a decorative image for: Constructing 3D objects through Deep Learning
October 18, 2022

Image-based 3D Object Reconstruction State-of-the-Art and trends in the Deep Learning Era

3D reconstruction is one of the most complex issues of deep learning systems. There have been multiple types of research in this field, and almost everything has been tried on it — computer vision, computer graphics and machine learning, but to no avail. However, that has resulted in CNN or convolutional neural networks foraying into this field, which has yielded some success.

The Main Objective of the 3D Object Reconstruction

Developing this deep learning technology aims to infer the shape of 3D objects from 2D images. So, to conduct the experiment, you need the following:

  • Highly calibrated cameras that take a photograph of the image from various angles.
  • Large training datasets can predict the geometry of the object whose 3D image reconstruction needs to be done. These datasets can be collected from a database of images, or they can be collected and sampled from a video.

By using the apparatus and datasets, you will be able to proceed with the 3D reconstruction from 2D datasets.

State-of-the-art Technology Used by the Datasets for the Reconstruction of 3D Objects

The technology used for this purpose needs to stick to the following parameters:

  • Input

Training with the help of one or multiple RGB images, where the segmentation of the 3D ground truth needs to be done. It could be one image, multiple images or even a video stream.

The testing will also be done on the same parameters, which will also help to create a uniform, cluttered background, or both.

  • Output

The volumetric output will be done in both high and low resolution, and the surface output will be generated through parameterisation, template deformation and point cloud. Moreover, the direct and intermediate outputs will be calculated this way.

  • Network architecture used

The architecture used in training is 3D-VAE-GAN, which has an encoder and a decoder, with TL-Net and conditional GAN. At the same time, the testing architecture is 3D-VAE, which has an encoder and a decoder.

  • Training used

The degree of supervision used in 2D vs 3D supervision, weak supervision along with loss functions have to be included in this system. The training procedure is adversarial training with joint 2D and 3D embeddings. Also, the network architecture is extremely important for the speed and processing quality of the output images.

  • Practical applications and use cases

Volumetric representations and surface representations can do the reconstruction. Powerful computer systems need to be used for reconstruction.

Given below are some of the places where 3D Object Reconstruction Deep Learning Systems are used:

  • 3D reconstruction technology can be used in the Police Department for drawing the faces of criminals whose images have been procured from a crime site where their faces are not completely revealed.
  • It can be used for re-modelling ruins at ancient architectural sites. The rubble or the debris stubs of structures can be used to recreate the entire building structure and get an idea of how it looked in the past.
  • They can be used in plastic surgery where the organs, face, limbs or any other portion of the body has been damaged and needs to be rebuilt.
  • It can be used in airport security, where concealed shapes can be used for guessing whether a person is armed or is carrying explosives or not.
  • It can also help in completing DNA sequences.

So, if you are planning to implement this technology, then you can rent the required infrastructure from E2E Networks and avoid investing in it. And if you plan to learn more about such topics, then keep a tab on the blog section of the website

Reference Links

https://tongtianta.site/paper/68922

https://github.com/natowi/3D-Reconstruction-with-Deep-Learning-Methods

This is a decorative image for: Comprehensive Guide to Deep Q-Learning for Data Science Enthusiasts
October 18, 2022

A Comprehensive Guide To Deep Q-Learning For Data Science Enthusiasts

For all data science enthusiasts who would love to dig deep, we have composed a write-up about Q-Learning specifically for you all. Deep Q-Learning and Reinforcement learning (RL) are extremely popular these days. These two data science methodologies use Python libraries like TensorFlow 2 and openAI’s Gym environment.

So, read on to know more.

What is Deep Q-Learning?

Deep Q-Learning utilizes the principles of Q-learning, but instead of using the Q-table, it uses the neural network. The algorithm of deep Q-Learning uses the states as input and the optimal Q-value of every action possible as the output. The agent gathers and stores all the previous experiences in the memory of the trained tuple in the following order:

State> Next state> Action> Reward

The neural network training stability increases using a random batch of previous data by using the experience replay. Experience replay also means the previous experiences stocking, and the target network uses it for training and calculation of the Q-network and the predicted Q-Value. This neural network uses openAI Gym, which is provided by taxi-v3 environments.

Now, any understanding of Deep Q-Learning   is incomplete without talking about Reinforcement Learning.

What is Reinforcement Learning?

Reinforcement is a subsection of ML. This part of ML is related to the action in which an environmental agent participates in a reward-based system and uses Reinforcement Learning to maximize the rewards. Reinforcement Learning is a different technique from unsupervised learning or supervised learning because it does not require a supervised input/output pair. The number of corrections is also less, so it is a highly efficient technique.

Now, the understanding of reinforcement learning is incomplete without knowing about Markov Decision Process (MDP). MDP is involved with each state that has been presented in the results of the environment, derived from the state previously there. The information which composes both states is gathered and transferred to the decision process. The task of the chosen agent is to maximize the awards. The MDP optimizes the actions and helps construct the optimal policy.

For developing the MDP, you need to follow the Q-Learning Algorithm, which is an extremely important part of data science and machine learning.

What is Q-Learning Algorithm?

The process of Q-Learning is important for understanding the data from scratch. It involves defining the parameters, choosing the actions from the current state and also choosing the actions from the previous state and then developing a Q-table for maximizing the results or output rewards.

The 4 steps that are involved in Q-Learning:

  1. Initializing parameters – The RL (reinforcement learning) model learns the set of actions that the agent requires in the state, environment and time.
  2. Identifying current state – The model stores the prior records for optimal action definition for maximizing the results. For acting in the present state, the state needs to be identified and perform an action combination for it.
  3. Choosing the optimal action set and gaining the relevant experience – A Q-table is generated from the data with a set of specific states and actions, and the weight of this data is calculated for updating the Q-Table to the following step.
  4. Updating Q-table rewards and next state determination – After the relevant experience is gained and agents start getting environmental records. The reward amplitude helps to present the subsequent step.  

In case the Q-table size is huge, then the generation of the model is a time-consuming process. This situation requires Deep Q-learning.

Hopefully, this write-up has provided an outline of Deep Q-Learning and its related concepts. If you wish to learn more about such topics, then keep a tab on the blog section of the E2E Networks website.

Reference Links

https://analyticsindiamag.com/comprehensive-guide-to-deep-q-learning-for-data-science-enthusiasts/

https://medium.com/@jereminuerofficial/a-comprehensive-guide-to-deep-q-learning-8aeed632f52f

Build on the most powerful infrastructure cloud

A vector illustration of a tech city using latest cloud technologies & infrastructure