demo_insert.expect

#!/usr/bin/env expect
#
# Copyright (c) YMatrix Inc.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the “Software”),
# to deal in the Software without restriction, including without limitation
# the rights to use, copy, modify, merge, publish, distribute, sublicense,
# and/or sell copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
# DEALINGS IN THE SOFTWARE.

# 演示 YMatrix 5.0 的 master 故障自动转移功能。
#
# 本脚本需要在已经部署了 YMatrix 5.0 数据库集群的 master 机器上用 mxadmin 用户执行。
# 本脚本使用 psql 客户端用 multiple hosts 连接方式连接到 YMatrix 5.0 数据库集群，
# 并持续向数据库的一个普通表中插入当前的时间和当前连接的 postgresql 数据库实例的 IP 地址。
#
# 当发生 master 故障自动转移时，
# - 插入行为不会中断。这表明数据库支持了故障自动转移功能。
# - 连接的 postgresql 数据库实例的地址会从 master 机器变为standby 机器。
#   这表明客户端也能够支持故障自动转移功能。

# 本脚本有两个命令行参数
# - argv[0] - master 主机的 postgresql 连接地址，格式为 host:port
# - argv[1] - standby 主机的 postgresql 连接地址，格式为 host:port

# 设置运行参数
set master_host_port [lindex $argv 0]
set master [split $master_host_port ":"]
set master_host [lindex $master 0]
set master_port [lindex $master 1]

set standby_host_port [lindex $argv 1]
set standby [split $standby_host_port ":"]
set standby_host [lindex $standby 0]
set standby_port [lindex $standby 1]

set user "mxadmin"
set database "postgres"

# 输出连接信息
send_user "Using psql to connect to cluster:\n"

# 启动 psql 连接到 YMatrix 5.0 集群
spawn psql -d "user=$user host=$master_host,$standby_host port=$master_port,$standby_port dbname=$database"

expect "postgres=# "
sleep 2

# 创建用于测试的表 demo_auto_failover
# 表的列如下
# - insert_id      - 主键，serial类型
# - ts             - 插入的时间戳
# - connected_host - 当前插入命令连接的 postgresql 数据库实例所在的机器 IP
#                    因为集群 master 和 standby 数据库实例分别部署在不同的机器上，它们的 IP 不同。
send -- "DROP TABLE IF EXISTS demo_auto_failover;\n"

expect "postgres=# "
sleep 2

send -- "CREATE TABLE demo_auto_failover(
    insert_id SERIAL PRIMARY KEY,
    ts TIMESTAMP WITH TIME ZONE,
    connected_host INET
) DISTRIBUTED BY (insert_id);\n"


expect "postgres=# "
sleep 2

# 循环运行插入操作
# 该循环插入的操作应该不会被 master 数据库实例被杀死而中断。
while { true } {
    # 插入当前时间和连接的数据库实例的 IP 地址。
    send -- "INSERT INTO demo_auto_failover(ts, connected_host)
        SELECT now() AS ts, inet_server_addr() AS connected_host;\n"
    expect {
        # 插入成功
        "postgres=# " {
            # 显示插入的最新一行的结果
            send -- "SELECT * FROM demo_auto_failover WHERE insert_id = (SELECT max(insert_id) FROM demo_auto_failover);\n"

            expect {
                # 显示成功，进入下一次循环
                "postgres=# " {
                    sleep 1
                    continue
                }

                # psql connection reset 发生重新连接数据库集群时的异常处理
                "Attempting reset: Failed." {
                    send_user "Failed to connect to cluster. Now reconnect.\n"
                    send -- "\\q\n"

                    expect "\$"
                    sleep 2

                    send_user "Reconnect with psql:"
                    spawn psql -d "user=$user host=$master_host,$standby_host port=$master_port,$standby_port dbname=$database"

                    expect "postgres=# "
                    sleep 1
                    continue
                }
            }
        }

        # psql connection reset 发生重新连接数据库集群时的异常处理
        "Attempting reset: Failed." {
            send_user "Failed to connect to cluster. Now reconnect.\n"
            send -- "\\q\n"

            expect "\$"
            sleep 2

            send_user "Reconnect with psql:"
            spawn psql -d "user=$user host=$master_host,$standby_host port=$master_port,$standby_port dbname=$database"

            expect "postgres=# "
            sleep 1
            continue
        }
    }
}