Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

Scope -

·       Infrastructure planning and requirement for installation of the multi-node cluster database

·       Prerequisites Software, Network Port, the storage requirement for yugabyte DB 

Pre-requisites for yugabyte DB 2.11 Configurations

A) Node Lists:-

	Host		IP Address
Node 1	yblab101		192.168.0.101
Node 2	yblab102		192.168.0.102
Node 3	yblab103		192.168.0.103

B) The below-required Network ports and software are required for YugabyteDB:

S.No. Software / Port / Action Item Requirement Value

1             Yedis                                      Port         6379

2             Admin Tserver                          Port         9000

3             Node explorter                           Port         9300

4             Admin master                           Port         7000

5             YCQL Api                                   Port      12000

6             Ssh                                           Port         22

7             YSQL Api                                   Port        5433

8             YCQL                                           Port         9042

9         Yugabyte Software 2.11        Software

10 YB-Master RPC Communication RPC communication 7100

11 YB-TServer RPC communication RPC communication 9100

12 NTP package Ntp

13 psycopg2 PostgreSQL database adapter for the Python programming language

C) Yugabyte db software required values should be updated on the /etc/security/limits.conf (root user):- 

 

*                -       core            unlimited *                -       data            unlimited *                -       fsize           unlimited *                -       sigpending      119934 *                -       memlock         64 *                -       rss             unlimited *                -       nofile          1048576 *                -       msgqueue        819200 *                -       stack           8192 *                -       cpu             unlimited *                -       nproc           12000 *                -       locks           unlimited

 

D) Transparent Hugepages

The transparent huge pages should be always. Even if it was set to "madvise" or "never". Please ensure to update it by root user.

cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never

 

E) Ensure the below value should be set on the OS level. if it is Red Hat Enterprise Linux Server release 7.9:-

 

[root@hqliorpv108 ~]# cat /etc/default/grub | grep -i "GRUB_CMDLINE_LINUX"

GRUB_CMDLINE_LINUX="crashkernel=auto spectre_v2=retpoline rd.lvm.lv=VG00/root rd.lvm.lv=VG00/swap rd.lvm.lv=VG00/usr biosdevname=0 ipv6.disable=1 net.ifnames=0 rhgb quiet transparent_hugepage=never"

[root@hqliorpv108 ~]#

 

F) Installation Steps:-

1) enable the below ports on root user in each nodes:

sudo firewall-cmd --zone=public --add-port=9000/tcp --permanent
sudo firewall-cmd --zone=public --add-port=9300/tcp --permanent
sudo firewall-cmd --zone=public --add-port=7000/tcp --permanent
sudo firewall-cmd --zone=public --add-port=9100/tcp --permanent
sudo firewall-cmd --zone=public --add-port=12000/tcp --permanent
sudo firewall-cmd --zone=public --add-port=13000/tcp --permanent
sudo firewall-cmd --zone=public --add-port=7100/tcp --permanent
sudo firewall-cmd --zone=public --add-port=11000/tcp --permanent
sudo firewall-cmd --zone=public --add-port=9042/tcp --permanent

 

2) Create group , user and folder for both yugabyte software on all three nodes: (Node1/Node2/Node3)

=>

root>  groupadd  yugabytedb

root> useradd -d /home/yugabyte -g yugabytedb yugabytedb

root> useradd -g prometheus Prometheus (May required for monitoring purpose). 

 

=> cat the /etc/passwd file for after user yugabytedb creations :-

yugabytedb:x:3000:3000:00000 Yugabytedb software owner:/opt/yugabytedb:/bin/bash

 

[root@hqliorpv107 ~]# cat /etc/group | grep yugabytedb
yugabytedb:x:3000:
[root@hqliorpv107 ~]#

 

3) create two data directories

   data1 => postgresql data directory | data2 => yugabyte data directories :- 

cd /yugabyte01
mkdir YUGABYTE
chown yugabytedb:yugabtyedb YUGABYTE
sudo su - yugabytedb
mkdir -p /yugabyte01/YUGABYTE/data1 /yugabyte01/YUGABYTE/data2

 

4) Install Yugabyte software from the Nexus Repo and run post_install.sh as follows on all three nodes.

cd /opt/yugabyte
wget http://repository.emirates.com/repository/dbateam_repo/Yugabyte/yugabyte-2.11.0.1-b1-linux-x86_64.tar.gz
tar -zxvf yugabyte-2.11.0.1-b1-linux-x86_64.tar.gz
cd yugabyte-2.11.0.1/

run the post_install script after installations 

./bin/post_install.sh

 

5) Start YB master services as follows on each node:-

 

Node 1: yblab101 (192.168.0.101)

cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-master \
  --master_addresses 192.168.0.101:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.101:7100 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud hq \
  --placement_region hq \
  --placement_zone hq   --webserver_interface=0.0.0.0  \

 --master_enable_metrics_snapshotter=true  --webserver_port=6516
  >& /opt/yugabyte/yb-master.out &
cd /yugabyte01/YUGABYTE/data1/yb-data/master/logs
cat yb-master.INFO | grep "This master"

 

Node 2: yblab102 (192.168.0.102)

cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-master \
  --master_addresses 192.168.0.101:7100:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.102:7100 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud hq \
  --placement_region hq \
  --placement_zone hq \
  >& /opt/yugabyte/yb-master.out &

cat yb-master.INFO | grep "This master"

 

Node 3: yblab103 (192.168.0.103)

cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-master \
  --master_addresses 192.168.0.101:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.103:7100 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud hq \
  --placement_region hq \
  --placement_zone hq \
  >& /opt/yugabyte/yb-master.out &

 

 Find the Leader node using below log output running in each node.

cd /yugabyte01/YUGABYTE/data1/yb-data/master/logs
cat yb-master.INFO | grep "This master"

 

 :/yugabyte01/YUGABYTE/data1/yb-data/master/logs>cat yb-master.yblab101.yugabytedb.log.INFO.20220207-063044.57683 | grep "This master"
I0207 06:30:45.855286 57707 sys_catalog.cc:384] T 00000000000000000000000000000000 P b36db44b23a4490b9514bccc1fab2e8e [sys.catalog]: This master's current role is: FOLLOWER
I0207 06:30:45.855345 57707 sys_catalog.cc:384] T 00000000000000000000000000000000 P b36db44b23a4490b9514bccc1fab2e8e [sys.catalog]: This master's current role is: FOLLOWER
I0207 06:52:20.829769 63103 sys_catalog.cc:384] T 00000000000000000000000000000000 P b36db44b23a4490b9514bccc1fab2e8e [sys.catalog]: This master's current role is: LEADER
 :/yugabyte01/YUGABYTE/data1/yb-data/master/logs>

 

6. Start the YB tablet servers on each node as follows:-

Node 1: yblab101 (192.168.0.101)

 cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-tserver \
  --tserver_master_addrs 192.168.0.101:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.101:9100 \
  --start_pgsql_proxy \
  --pgsql_proxy_bind_address 192.168.0.101:6518 \
  --cql_proxy_bind_address 192.168.0.101:9042 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud doz \
  --placement_region doz \
  --placement_zone doz \
  >& /opt/yugabyte/yb-tserver.out &

 

Node 2: yblab102 (192.168.0.102)

cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-tserver \
  --tserver_master_addrs 192.168.0.101:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.102:9100 \
  --start_pgsql_proxy \
  --pgsql_proxy_bind_address 192.168.0.102::6518 \
  --cql_proxy_bind_address 192.168.0.102::9042 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud hq \
  --placement_region hq \
  --placement_zone hq \
  >& /opt/yugabyte/yb-tserver.out &

 

Node 3: yblab103 (192.168.0.103)

cd /opt/yugabyte/yugabyte-2.11.0.1
./bin/yb-tserver \
  --tserver_master_addrs 192.168.0.101:7100,192.168.0.102:7100,192.168.0.103:7100 \
  --rpc_bind_addresses 192.168.0.103:9100 \
  --start_pgsql_proxy \
  --pgsql_proxy_bind_address 192.168.0.103:6518 \
  --cql_proxy_bind_address 192.168.0.103:9042 \
  --fs_data_dirs "/yugabyte01/YUGABYTE/data1,/yugabyte01/YUGABYTE/data2" \
  --placement_cloud doz \
  --placement_region doz \
  --placement_zone doz \
  >& /opt/yugabyte/yb-tserver.out &

 

Verify the cluster on all nodes using below log output running in each node:-

cd /yugabyte01/YUGABYTE/data1/yb-data/tserver/logs

 :/yugabyte01/YUGABYTE/data1/yb-data/tserver/logs>cat yb-tserver.yblab102.yugabytedb.log.INFO.20220208-121828.112811 | grep -i "Connected to a leader master server"
I0208 12:18:28.482936 112848 heartbeater.cc:305] P 8e87587653304075a23a19c7e0b43f98: Connected to a leader master server at 192.168.0.101:7100
 :/yugabyte01/YUGABYTE/data1/yb-data/tserver/logs>

 

#Docker Administration

#Verify dockers log 

If you want to verify the docker containers logs then follow the syntax:

• docker logs <containerid>

Ex: The below commands will display the log information of container "efca1e07d460" and 
details history. Earlier we installed PostgreSQL 13.2 on this container.

root@worldpgdb:~# docker logs efca1e07d460
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-05-11 17:35:32.503 UTC [45] LOG:  starting PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-05-11 17:35:32.505 UTC [45] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-05-11 17:35:32.519 UTC [46] LOG:  database system was shut down at 2021-05-11 17:35:29 UTC
2021-05-11 17:35:32.531 UTC [45] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

2021-05-11 17:35:32.639 UTC [45] LOG:  received fast shutdown request
waiting for server to shut down....2021-05-11 17:35:32.641 UTC [45] LOG:  aborting any active transactions
2021-05-11 17:35:32.645 UTC [45] LOG:  background worker "logical replication launcher" (PID 52) exited with exit code 1
2021-05-11 17:35:32.646 UTC [47] LOG:  shutting down
2021-05-11 17:35:32.705 UTC [45] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2021-05-11 17:35:32.850 UTC [1] LOG:  starting PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-05-11 17:35:32.852 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-05-11 17:35:32.854 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-05-11 17:35:32.871 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-05-11 17:35:32.881 UTC [64] LOG:  database system was shut down at 2021-05-11 17:35:32 UTC
2021-05-11 17:35:32.884 UTC [1] LOG:  database system is ready to accept connections
2021-05-12 07:45:32.835 UTC [254] FATAL:  role "root" does not exist
root@worldpgdb:~# 

• If you want to do continue stream then use the below command:-"docker logs --follow <containerid>"

• to shows the logs with timestamp ( nano milliseconds) the syntax as follow:-  "docker logs --timestamp <containerid>"

• To start the inactive containers, just try "docker start <containerid>"   
  
  
  

Python Scripting for Databases

$PostgreSQL$ 

 

 #Prerequisites

We need to verify the installed packages . I have installed "psycopg2 2.8.6" using pip. 

The below "pip list" command will list down the details:

C:\Users\welcome>pip list
Package    Version
---------- -------
cx-Oracle  8.0.1
pip        20.2.4
psycopg2   2.8.6
setuptools 28.8.0

We have another way to check all the installed modules using ' help ("module") ' command after connecting to python console.

C:\Users\welcome>python
Python 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules');

Please wait a moment while I gather a list of all available modules...

Fetchdata           _weakrefset         gzip                runpy
SELECT ALL          _winapi             hashlib             sched
SELECTMANY          abc                 heapq               secrets
_string             errno               pstats              weakref
_strptime           faulthandler        psycopg2            webbrowser

Enter any module name to get more help.  Or, type "modules spam" to search
for modules whose name or summary contain the string "spam".

>>>

 >>> help('modules psy');

Here is a list of modules whose name or summary contains 'psy'.
If there are any, enter a module name to get more help.

psycopg2 - A Python driver for PostgreSQL
psycopg2._ipaddress - Implementation of the ipaddres-based network types adaptation
psycopg2._json - Implementation of the JSON adaptation objects
psycopg2._lru_cache - LRU cache implementation for Python 2.7
psycopg2._psycopg
psycopg2._range - Implementation of the Range type and adaptation
psycopg2.compat
psycopg2.errorcodes - Error codes for PostgresSQL
psycopg2.errors - Error classes for PostgreSQL error codes
psycopg2.extensions - psycopg extensions to the DBAPI-2.0
psycopg2.extras - Miscellaneous goodies for psycopg2
psycopg2.pool - Connection pooling for psycopg2
psycopg2.sql - SQL composition utility module
psycopg2.tz - tzinfo implementations for psycopg2

>>>
C:\Users\welcome> 

------------------------------------------------------------------------------------------------------------------------------

# creating table

Creating a table  by using connect() method that belongs to connection objects.  Then we call execute method of the cursor object to help us in creating table then we will commit and close the connection.

 

# Script

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute('''CREATE TABLE SCHOLAR
      (ADMISSION INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      COURSE        CHAR(50),
      DEPARTMENT    CHAR(50));''')
print("Table created successfully")

con.commit()
con.close()

 

 

# Inserting data 

We will insert data after connecting to the database by using connection object. Then, we use execute() method to insert single record.

#Script

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, 'John', 18, 'Computer Science', 'ICT')");

con.commit()
print("Record inserted successfully")
con.close()

 

 

 

# Insert multiple rows 

We use execute() method to insert multiple records then commit and close method() to finish the tasks.

 import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3419, 'Abel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3421, 'Joel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3422, 'Antony', 19, 'Electrical Engineering', 'Engineering')");
cur.execute("INSERT INTO SCHOLAR (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3423, 'Alice', 18, 'Information Technology', 'ICT')");

con.commit()
print("Records inserted successfully")
con.close()

 

 

# Retrieving Data (  Query the record from table  )

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute("SELECT admission, name, age, course, department from SCHOLAR")
rows = cur.fetchall()

for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Operation done successfully")
con.close()

 

 

# Updating Data

After connecting to the database using connect() method then by using cursor() method we will execute update statement with input.

 

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("UPDATE SCHOLAR set AGE = 20 where ADMISSION = 3420")
con.commit()
print("Total updated rows:", cur.rowcount)

cur.execute("SELECT admission, age, name, course, department from SCHOLAR")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[2])
    print("DEPARTMENT =", row[3], "\n")

print("Operation done successfully")
con.close()

 

We have updated the John Age is 20 for 3420 admission.

##  Deleting Rows
To delete a record from a Postgres database table, we should first establish a connection to the database server. Secondly, a cursor object should be created by calling the cursor() function then, run the DELETE statement to perform the deletion.

import psycopg2

con = psycopg2.connect(database="largedata", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("DELETE from SCHOLAR where ADMISSION=3420;")
con.commit()
print("Total deleted rows:", cur.rowcount)

cur.execute("SELECT admission, name, age, course, department from SCHOLAR")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Deletion successful")
con.close()

SCRIPT OUTPUT