Replication in databases is a technique used to create and maintain copies of data across multiple servers, known as replicas. These replicas are synchronized with the primary database to ensure data consistency and availability. In this technical article, we'll delve into the concept of database replication, explore its benefits, and provide code examples to illustrate its implementation.

Replication offers several advantages, including improved fault tolerance, scalability, and performance. By distributing data across multiple replicas, organizations can enhance system reliability and handle increased read traffic without overloading the primary database server.

There are various replication models, such as master-slave replication and multi-master replication, each with its own use cases and configurations. Let's focus on a simple example of master-slave replication using PostgreSQL, a popular open-source relational database.

• Configure the master PostgreSQL server:

CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'P@ssword321';

Then you need to edit your postgre's config file

sudo vim /etc/postgresql/14/main/postgresql.conf

# Modify PostgreSQL configuration file (e.g., postgresql.conf)
# Set listen_addresses to '*'
listen_addresses = '*'

With the file open, scroll down and locate the listen_addresses directive. The directive specifies the host under which the PostgreSQL database server listens for connections.

• Next, locate the wal_level directive. The setting specifies the amount of information to be written to the Write Ahead Log (WAL) file. Uncomment the line and set it to logical

• Next, locate the wal_log_hints directive. By default, it is set to off. When set to on the value allows the PostgreSQL server to write the entire content of each disk page to the WAL file during the first modification of the page. Uncomment it and set it to on.
• Modify pg_hba.conf to allow replication connections

host    replication     postgres        <slave_ip>/32     md5

• Restart the PostgreSQL server to apply the changes.

Configure the slave PostgreSQL server:

• Modify PostgreSQL configuration file

listen_addresses = '*'

• Modify recovery.conf to specify replication parameters

standby_mode = 'on'
primary_conninfo = 'host=<master_ip> port=5432 user=replication password=password'
trigger_file = '/tmp/postgresql.trigger'

Start the slave PostgreSQL server. It will connect to the master and replicate data.

With master-slave replication set up, changes made to the master database will be automatically replicated to the slave database, ensuring data consistency and fault tolerance.

import { Client } from 'pg';
// Connect to the master PostgreSQL server
const masterClient = new Client({
  user: 'postgres',
  host: '<master_ip>',
  database: 'db_name',
  password: 'password',
  port: 5432,
});

await masterClient.connect();

// Connect to the slave PostgreSQL server
const slaveClient = new Client({
  user: 'postgres',
  host: '<slave_ip>',
  database: 'db_name',
  password: 'password',
  port: 5432,
});

await slaveClient.connect();

// Perform database operations on the master
await masterClient.query('INSERT INTO table_name (column1, column2) VALUES ($1, $2)', ['value1', 'value2']);

// Query data from the slave (read-only operations)
const result = await slaveClient.query('SELECT * FROM table_name');
console.log(result.rows);

// Close database connections
await masterClient.end();
await slaveClient.end();

Can I or should I use slave to write data in?

Using a slave to write data in a database replication setup is generally not recommended.

It's recommended to use the slave(s) only for read operations to distribute the read load and improve performance. Reserve write operations for the master node to maintain data consistency and simplify your database architecture.

However, there are scenarios where writable slaves, also known as multi-master replication, are used in specific database setups. But implementing multi-master replication requires careful consideration of data conflict resolution, consistency models, and robust error handling mechanisms.

With database replication, organizations can achieve high availability, fault tolerance, and scalability in their applications, ensuring uninterrupted access to critical data and enhanced performance across distributed environments.