Building an IvorySQL cluster

1. Primary node

1.1. Installing and start database

For quick database installation by yum, please refer to Quick installation

For more installation options, please refer to Installation

The master node database needs to be installed and started.

1.2. Stopping firewall

Stop firewall for all the nodes in the cluster to ensure the communication:

$ sudo systemctl stop firewalld

1.3. Setting environment variables

To create the streaming replication, we need configure the postgresql.conf and pg_hba.conf files on the primary node.

  • postgresql.conf

    Append the following contents to the end of postgresql.conf:

listen_addresses = '*'
max_connections = 100
wal_level = replica
max_wal_senders = 5
hot_standby = on
  • pg_hba.conf

    Append the following contents to the end of pg_hba.conf:

host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust
The configuration of pg_hba in the example is only for demo purposes and testing. This configuration will result in invalidation of the database password. Please configure according to the actual environment.

1.4. Restarting IvorySQL sevice

$ pg_ctl restart

2. Standby node

2.1. Installing database

For quick database installation by yum, please refer to Quick installation

For more installation options, please refer to Installation

The standby node database only needs to be installed and not started.

2.2. Stopping firewall

Stop firewall for all the nodes in the cluster to ensure the communication:

$ sudo systemctl stop firewalld

2.3. Building streaming replication

Run below command on the standby node to take base backups of the primary, that is, to build a streaming replication:

$ sudo pg_basebackup -F p -P -X fetch -R -h <primary_ip> -p <primary_port> -U ivorysql -D /usr/local/ivorysql/ivorysql-3/data
  • Specifies the host name of the machine on which the server is running;

  • Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. Defaults is 5432;

  • User name to connect as;

  • Directory to write the output to. pg_basebackup will create the directory and any parent directories if necessary. The directory may already exist, but it is an error if the directory already exists and is not empty.

For more options, refer to pg_basebackup --help.

2.4. Setting environment variables

Add below contents in ~/.bash_profile file:

PATH=/usr/local/ivorysql/ivorysql-3/bin:$PATH
export PATH
LD_LIBRARY_PATH=/usr/local/ivorysql/ivorysql-3/lib
export LD_LIBRARY_PATH
PGDATA=/usr/local/ivorysql/ivorysql-3/data
export PGDATA

Source to make it effective:

$ source ~/.bash_profile

2.5. Starting IvorySQL sevice

$ sudo pg_ctl -D /usr/local/ivorysql/ivorysql-3/data start

3. Experience the IvorySQL cluster

3.1. Checking cluster status

Run below command on the primary node, you will see walsender:

$ ps -ef |grep postgres
...
ivorysql 11176  8067  0 21:54 ?        00:00:00 postgres: walsender ivorysql 192.168.31.102(53416) streaming 0/7000060...

while it is walreceiver on standby:

$ ps -ef | grep postgres
...
ivorysql  6567  6139  0 21:54 ?        00:00:00 postgres: walreceiver streaming 0/7000060
...

On the primary node, connect to IvorySQL and show the status:

$ psql -d postgres
psql (16.1)
Type "help" for help.

postgres=# select * from pg_stat_replication;
  pid  | usesysid | usename  | application_name |  client_addr   | client_hostname | client_port |         backend_start         | backend_
xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |
    reply_time
-------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+---------
-----+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------
-------------------------
 11176 |       10 | ivorysql | walreceiver      | 192.168.31.102 |                 |       53416 | 2024-02-25 21:54:52.041847-05 |
     | streaming | 0/7000148 | 0/7000148 | 0/7000148 | 0/7000148  |           |           |            |             0 | async      | 2024-
02-25 22:52:07.325111-05
(1 row)

Here 192.168.31.102 is the ip address of the standby node, and async means the data synchronization method is asynchronous.

3.2. Using the cluster

All writing operations are performed on the primary node, while reading can be on both primary and standby. The data on primary is synchronized to standby through streaming replication. The writing result can be queried on all the nodes in the cluster.

Below is an example. Create a new database test on primary and query:

$ psql -d postgres
psql (16.1)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/ivorysql          +
           |          |          |                 |             |             |            |           | ivorysql=CTc/ivorysql
 template1 | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/ivorysql          +
           |          |          |                 |             |             |            |           | ivorysql=CTc/ivorysql
 test      | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
(4 rows)

Query on the standby node:

$ psql -d postgres
psql (16.1)
Type "help" for help.

postgres=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/ivorysql          +
           |          |          |                 |             |             |            |           | ivorysql=CTc/ivorysql
 template1 | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/ivorysql          +
           |          |          |                 |             |             |            |           | ivorysql=CTc/ivorysql
 test      | ivorysql | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
(4 rows)