# 1. 检查主备节点状态
学习交流加群风哥微信: itpux-com
[fgedu@fgedu.net.cn ~]$ gs_ctl status -D /gauss/fgdata
gs_ctl: server is running (PID: 12345)
/gauss/app/bin/gaussdb “-D” “/gauss/fgdata”

# 2. 在应用程序中实现读写分离逻辑（示例代码）
# Python示例代码
import psycopg2
from psycopg2 import pool

# 主节点连接池
master_pool = psycopg2.pool.SimpleConnectionPool(
1, 10,
host=”192.168.1.101″,
port=”5432″,
database=”fgedudb”,
user=”fgedu”,
password=”Fgedu@123″
)

# 读节点连接池（轮询方式）
read_nodes = [“192.168.1.102”, “192.168.1.103”]
read_pools = []
for node in read_nodes:
read_pools.append(psycopg2.pool.SimpleConnectionPool(
1, 10,
host=node,
port=”5432″,
database=”fgedudb”,
user=”fgedu”,
password=”Fgedu@123″
))

# 写操作函数
def execute_write(sql, params=None):
conn = master_pool.getconn()
try:
with conn.cursor() as cur:
cur.execute(sql, params)
conn.commit()
return cur.rowcount
finally:
master_pool.putconn(conn)

# 读操作函数（轮询选择读节点）
read_index = 0
def execute_read(sql, params=None):
global read_index
conn = read_pools[read_index].getconn()
read_index = (read_index + 1) % len(read_pools)
try:
with conn.cursor() as cur:
cur.execute(sql, params)
return cur.fetchall()
finally:
read_pools[read_index – 1].putconn(conn)

# 测试写操作
write_result = execute_write(“INSERT INTO fgedu_test (name, value) VALUES (%s, %s)”, (“test”, 123))
print(f”Write result: {write_result}”)

# 测试读操作
read_result = execute_read(“SELECT * FROM fgedu_test”)
print(f”Read result: {read_result}”)
学习交流加群风哥QQ113257174

# 1. 安装HAProxy
[root@fgedu.net.cn ~]# yum install -y haproxy

# 2. 配置HAProxy
[root@fgedu.net.cn ~]# cat /etc/haproxy/haproxy.cfg
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
daemon

defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000

# 主节点（处理写操作）
frontend write_frontend
bind *:5433
default_backend write_backend

backend write_backend
balance roundrobin
server master 192.168.1.101:5432 check

# 读节点（处理读操作）
frontend read_frontend
bind *:5434
default_backend read_backend

backend read_backend
balance roundrobin
server standby1 192.168.1.102:5432 check
server standby2 192.168.1.103:5432 check

# 3. 启动HAProxy
[root@fgedu.net.cn ~]# systemctl start haproxy
[root@fgedu.net.cn ~]# systemctl enable haproxy

# 4. 测试连接
# 测试写操作（连接到5433端口）
[fgedu@fgedu.net.cn ~]$ psql -h 192.168.1.100 -p 5433 -U fgedu -d fgedudb -c “INSERT INTO fgedu_test (name, value) VALUES (‘test2’, 456)”
INSERT 0 1

# 测试读操作（连接到5434端口）
[fgedu@fgedu.net.cn ~]$ psql -h 192.168.1.100 -p 5434 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_test”
id | name | value
—-+——-+——-
1 | test | 123
2 | test2 | 456
(2 rows)

#!/bin/bash
# rw_separation_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 测试写操作性能
echo “Testing write performance…”
start_time=$(date +%s)

for i in {1..1000}; do
psql -h 192.168.1.100 -p 5433 -U fgedu -d fgedudb -c “INSERT INTO fgedu_test (name, value) VALUES (‘test$i’, $i)” > /dev/null 2>&1
done

end_time=$(date +%s)
write_time=$((end_time – start_time))
echo “Write test completed in $write_time seconds”

# 测试读操作性能（单节点）
echo “Testing read performance (single node)…”
start_time=$(date +%s)

for i in {1..1000}; do
psql -h 192.168.1.101 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_test WHERE value = $i” > /dev/null 2>&1
done

end_time=$(date +%s)
single_read_time=$((end_time – start_time))
echo “Single node read test completed in $single_read_time seconds”

# 测试读操作性能（读写分离）
echo “Testing read performance (read-write separation)…”
start_time=$(date +%s)

for i in {1..1000}; do
psql -h 192.168.1.100 -p 5434 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_test WHERE value = $i” > /dev/null 2>&1
done

end_time=$(date +%s)
rw_sep_read_time=$((end_time – start_time))
echo “Read-write separation read test completed in $rw_sep_read_time seconds”

# 输出测试结果
echo “\nTest Results:”
echo “Write operations (1000): $write_time seconds”
echo “Single node read operations (1000): $single_read_time seconds”
echo “Read-write separation read operations (1000): $rw_sep_read_time seconds”

improvement=$(( (single_read_time – rw_sep_read_time) * 100 / single_read_time ))
echo “\nPerformance improvement: $improvement%”

运行性能测试：

[fgedu@fgedu.net.cn ~]$ chmod +x rw_separation_test.sh
[fgedu@fgedu.net.cn ~]$ ./rw_separation_test.sh
Testing write performance…
Write test completed in 15 seconds
Testing read performance (single node)…
Single node read test completed in 25 seconds
Testing read performance (read-write separation)…
Read-write separation read test completed in 12 seconds

Test Results:
Write operations (1000): 15 seconds
Single node read operations (1000): 25 seconds
Read-write separation read operations (1000): 12 seconds

Performance improvement: 52%