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 firewalld1.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. |
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 firewalld2.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 PGDATASource to make it effective:
$ source ~/.bash_profile3. 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)