Read replicas and Spring Data Part 1: Configuring the Databases

Emmanouil GkatziourasOctober 13th, 2019Last Updated: October 10th, 2019

0 225 3 minutes read

This is a series of blog posts on our quest to increase our application’s performance by utilizing read replicas.

For this project our goal is to set up our spring data application and use read repositories for writes and repositories based on read replicas for reads.

In order to simulate this environment we shall use PostgreSQL instances through Docker.

The motives are simple. Your Spring application has become increasingly popular and you want it to handle more requests. Most of the applications out there have a higher demand for read operations rather than write operations. Thus I assume that your application falls into the same category.
Although SQL databases are not horizontally scalable on their own, you can work you way with them by using read replicas.

Our goal is not to make an actual Read replication in PostgreSQL

thereforeinstead of configuring any replication

we will just copy some data from both databases

This is the script we shall use to populate the databases.

#!/bin/bash
set -e
 
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" &lt;&lt;-EOSQL
    create schema spring_data_jpa_example;
 
    create table spring_data_jpa_example.employee(
        id  SERIAL PRIMARY KEY,
        firstname   TEXT    NOT NULL,
        lastname    TEXT    NOT NULL,
        email       TEXT    not null,
        age         INT     NOT NULL,
        salary         real,
        unique(email)
    );
 
    insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary)
    values ('John','Doe 1','john1@doe.com',18,1234.23);
    insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary)
    values ('John','Doe 2','john2@doe.com',19,2234.23);
    insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary)
    values ('John','Doe 3','john3@doe.com',20,3234.23);
    insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary)
    values ('John','Doe 4','john4@doe.com',21,4234.23);
    insert into spring_data_jpa_example.employee (firstname,lastname,email,age,salary)
    values ('John','Doe 5','john5@doe.com',22,5234.23);
EOSQL

Since we shall use and Docker and Docker Compose the script above shall be used in order to initialize the database. Now on to create our Docker Compose stack.

version: '3.5'
 
services:
  write-db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: db-user
      POSTGRES_PASSWORD: your-password
      POSTGRES_DB: postgres
    networks:
      - postgresql-network
    ports:
      - "127.0.0.2:5432:5432"
    volumes:
      - $PWD/init-db-script.sh:/docker-entrypoint-initdb.d/init-db-script.sh
  read-db-1:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: db-user
      POSTGRES_PASSWORD: your-password
      POSTGRES_DB: postgres
    networks:
      - postgresql-network
    ports:
      - "127.0.0.3:5432:5432"
    volumes:
      - $PWD/init-db-script.sh:/docker-entrypoint-initdb.d/init-db-script.sh
networks:
  postgresql-network:
    name: postgresql-network

As you see our configuration is pretty simple. If you are careful enough you would see that I gave the number one to the read-db. This is because in the future we will add more replicas to it.

What I also did is bounding the machines to different local ips.

If you have problem binding addresses like 127.0.0.*:5432
You should try

sudo ifconfig lo0 alias 127.0.0.2 up
sudo ifconfig lo0 alias 127.0.0.3 up

If you are unsuccessful then just change the ports and it will work. It might not be as convenient but it’s still ok.

So let’s get up and running our Docker Compose stack.

1	`docker-compose -f ./postgresql-stack.yaml up`

We must be able to query data in both postgresql instances.

docker exec -it deploy_read-db-1_1 /bin/bash
root@07c502968cb3:/# psql -v --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"
db-user=# select*from spring_data_jpa_example.employee;
 id | firstname | lastname |     email     | age | salary
----+-----------+----------+---------------+-----+---------
  1 | John      | Doe 1    | john1@doe.com |  18 | 1234.23
  2 | John      | Doe 2    | john2@doe.com |  19 | 2234.23
  3 | John      | Doe 3    | john3@doe.com |  20 | 3234.23
  4 | John      | Doe 4    | john4@doe.com |  21 | 4234.23
  5 | John      | Doe 5    | john5@doe.com |  22 | 5234.23
(5 rows)

We pretty much set up for our next step. We have some databases up and running and we are going to spin up a spring application running upon them. The next blog focuses on implementing an application running upon our primary database.

Published on Java Code Geeks with permission by Emmanouil Gkatziouras, partner at our JCG program. See the original article here: Read replicas and Spring Data Part 1: Configuring the Databases

Opinions expressed by Java Code Geeks contributors are their own.