### Start example cluster and enter opentenbaseCN container

Source: https://docs.opentenbase.org/guide/15-docker-deploy

Starts the example 1C2D cluster services and enters the opentenbaseCN container. Run from the example/1c_2d_cluster directory.

```bash
cd ${SOURCECODE_PATH}/example/1c_2d_cluster
docker-compose up -d
docker-compose exec opentenbaseCN /bin/bash
```

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

### Initialize database and create shard table

Source: https://docs.opentenbase.org/guide/15-docker-deploy

SQL statements to set up the default node group, create a test database and user, and create a shard table. Detailed explanations are in the Quick Start guide.

```sql
-- 使用 dn001, dn002 存储节点组成默认存储组
create default node group default_group  with (dn001,dn002); 
create sharding group to group default_group;      -- 设置 shard 类型的表使用的存储组
create database test;                              -- 创建 test 数据库
create user test with password 'test';             -- 创建一个 test 用户密码为 test
alter database test owner to test;                 -- 修改 test 数据库 owner 为 test 用户
\c test test                                       -- 切换到 test 数据库
-- 创建一个 shard 表 foo, 使用 id 作为分布键
create table foo(id bigint, str text) distribute by shard(id);
insert into foo values(1, 'tencent'), (2, 'shenzhen');
select * from foo;
```

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

### Compile and install OpenTenBase from source

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Compile and install OpenTenBase from source. This includes cloning the repository, configuring with the specified options, building, and installing the main and contrib components.

```bash
su - opentenbase
cd /data/opentenbase/
git clone https://github.com/OpenTenBase/OpenTenBase

export SOURCECODE_PATH=/data/opentenbase/OpenTenBase
export INSTALL_PATH=/data/opentenbase/install/

cd ${SOURCECODE_PATH}
rm -rf ${INSTALL_PATH}/opentenbase_bin_v5.0
chmod +x configure*
./configure --prefix=${INSTALL_PATH}/opentenbase_bin_v5.0 --enable-user-switch --with-libxml --disable-license --with-openssl --with-ossp-uuid CFLAGS="-g"
make clean
make -sj
make install
chmod +x contrib/pgxc_ctl/make_signature
cd contrib
make -sj
make install

```

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

### Start TXSQL test server with ./mtr --start

Source: https://docs.opentenbase.org/guide/18-txsql_deploy?q=

Starts a MySQL test server using the MTR (MySQL Test Run) framework. Run this in the mysql-test directory after building TXSQL; it launches mysqld and initializes the test environment.

```bash
cd bld-release/mysql-test
./mtr --start

```

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

### Install OpenTenBase cluster with opentenbase_ctl install

Source: https://docs.opentenbase.org/guide/01-quickstart

Run the opentenbase_ctl install command with the configuration file to install the cluster. The output shows the installation steps and success messages. After seeing 'Installation completed successfully', the installation is complete.

```shell
export LD_LIBRARY_PATH=/data/opentenbase/install/opentenbase_bin_v5.0/lib
./opentenbase_bin_v5.0/bin/opentenbase_ctl install  -c opentenbase_config.ini

====== Start to Install Opentenbase test_cluster01  ====== 

step 1: Make *.tar.gz pkg ...
    Make opentenbase-5.21.8-i.x86_64.tar.gz successfully.

step 2: Transfer and extract pkg to servers ...
    Package_path: /data/opentenbase/opentenbase_ctl/opentenbase-5.21.8-i.x86_64.tar.gz
    Transfer and extract pkg to servers successfully.

step 3: Install gtm master node ...
    Install gtm0001(172.16.16.49) ...
    Install gtm0001(172.16.16.49) successfully
    Success to install  gtm master node. 

step 4: Install cn/dn master node ...
    Install cn0001(172.16.16.49) ...
    Install dn0001(172.16.16.49) ...
    Install dn0002(172.16.16.131) ...
    Install cn0001(172.16.16.49) successfully
    Install dn0001(172.16.16.49) successfully
    Install dn0002(172.16.16.131) successfully
    Success to install all cn/dn master nodes. 

step 5: Install slave nodes ...
    Install gtm0002(172.16.16.131) ...
    Install cn0001(172.16.16.131) ...
    Install dn0002(172.16.16.49) ...
    Install gtm0002(172.16.16.131) successfully
    Install dn0002(172.16.16.49) successfully
    Install dn0001(172.16.16.131) successfully
    Install cn0001(172.16.16.131) successfully
    Success to install all slave nodes. 

step 6: Create node group ...
    Create node group successfully. 

====== Installation completed successfully  ====== 

```

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

### Install opentenbase_subscription extension

Source: https://docs.opentenbase.org/guide/12-opentenbase_subscription?q=

Install the opentenbase_subscription extension on the CN of each cluster participating in multi-active replication. Execute on any CN of each cluster; both clusters need installation.

```sql
CREATE EXTENSION opentenbase_subscription;

```

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

### Install opentenbase_ai with CASCADE

Source: https://docs.opentenbase.org/guide/23-opentenbase_ai?q=

Installs opentenbase_ai and its dependency pgsql-http in one command using CASCADE.

```sql
CREATE EXTENSION opentenbase_ai CASCADE;

```

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

### Install and check OpenTenBase cluster with opentenbase_ctl

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Install the OpenTenBase cluster using opentenbase_ctl install with the configuration file opentenbase_config.ini. The output shows the installation steps and success message. After installation, check the cluster status with opentenbase_ctl status -c opentenbase_config.ini; the output lists all nodes as Running and provides the master CN connection info.

```shell
export LD_LIBRARY_PATH=/data/opentenbase/install/opentenbase_bin_v5.0/lib
./opentenbase_bin_v5.0/bin/opentenbase_ctl install  -c opentenbase_config.ini

====== Start to Install Opentenbase test_cluster01  ====== 

step 1: Make *.tar.gz pkg ...
    Make opentenbase-5.21.8-i.x86_64.tar.gz successfully.

step 2: Transfer and extract pkg to servers ...
    Package_path: /data/opentenbase/opentenbase_ctl/opentenbase-5.21.8-i.x86_64.tar.gz
    Transfer and extract pkg to servers successfully.

step 3: Install gtm master node ...
    Install gtm0001(172.16.16.49) ...
    Install gtm0001(172.16.16.49) successfully
    Success to install  gtm master node. 

step 4: Install cn/dn master node ...
    Install cn0001(172.16.16.49) ...
    Install dn0001(172.16.16.49) ...
    Install dn0002(172.16.16.131) ...
    Install cn0001(172.16.16.49) successfully
    Install dn0001(172.16.16.49) successfully
    Install dn0002(172.16.16.131) successfully
    Success to install all cn/dn master nodes. 

step 5: Install slave nodes ...
    Install gtm0002(172.16.16.131) ...
    Install cn0001(172.16.16.131) ...
    Install dn0001(172.16.16.131) ...
    Install dn0002(172.16.16.49) ...
    Install gtm0002(172.16.16.131) successfully
    Install dn0002(172.16.16.49) successfully
    Install dn0001(172.16.16.131) successfully
    Install cn0001(172.16.16.131) successfully
    Success to install all slave nodes. 

step 6: Create node group ...
    Create node group successfully. 

====== Installation completed successfully  ====== 

```

```shell
[opentenbase@VM-16-49-tencentos opentenbase_ctl]$ ./opentenbase_bin_v5.0/bin/opentenbase_ctl status -c opentenbase_config.ini

------------- Instance status -----------  
Instance name: test_cluster01
Version: 5.21.8

-------------- Node status --------------  
Node gtm0001(172.16.16.49) is Running 
Node dn0001(172.16.16.49) is Running 
Node dn0002(172.16.16.49) is Running 
Node cn0001(172.16.16.49) is Running 
Node dn0002(172.16.16.131) is Running 
Node cn0001(172.16.16.131) is Running 
Node gtm0002(172.16.16.131) is Running 
Node dn0001(172.16.16.131) is Running 
[Result] Total: 8, Running: 8, Stopped: 0, Unknown: 0

------- Master CN Connection Info -------  
[1] cn0001(172.16.16.49)  
Environment variable: export LD_LIBRARY_PATH=/data/opentenbase/install/opentenbase/5.21.8/lib  && export PATH=/data/opentenbase/install/opentenbase/5.21.8/bin:${PATH} 
PSQL connection: psql -h 172.16.16.49 -p 11000 -U opentenbase postgres 

```

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

### Distribution key join with LIMIT optimization example

Source: https://docs.opentenbase.org/guide/09-problem-performance

Shows the setup and execution of a join with limit on distribution key columns in a sharded PostgreSQL environment. The query plan reveals a Merge Join with remote subquery scans on all data nodes, and the execution time is reported as 2976.008 ms. The example includes table creation, data insertion, analyze, and the EXPLAIN ANALYZE output.

```sql
--数据准备  
postgres=# CREATE TABLE t1(f1 serial not null unique,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# CREATE TABLE t2(f1 serial not null unique,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t1 select t,md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text) from generate_series(1,1000000) as t;
INSERT 0 1000000
postgres=# insert into t2 select t,md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text) from generate_series(1,1000000) as t; 
INSERT 0 1000000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
postgres=# 

postgres=# \timing 
Timing is on.
postgres=# explain  select t1.* from t1,t2 where t1.f1=t2.f1 limit 10;       
                                                                                         QUERY PLAN                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..1.65 rows=10 width=367) (actual time=2675.437..2948.199 rows=10 loops=1)
   ->  Merge Join  (cost=0.25..140446.26 rows=1000000 width=367) (actual time=2675.431..2675.508 rows=10 loops=1)
         Merge Cond: (t1.f1 = t2.f1)
         ->  Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10)  (cost=100.12..434823.13 rows=1000000 width=367) (actual time=1.661..1.704 rows=10 loops=1)
         ->  Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10)  (cost=100.12..71823.13 rows=1000000 width=4) (actual time=2673.761..2673.783 rows=10 loops=1)
 Planning time: 0.358 ms
 Execution time: 2973.948 ms
(7 rows)

Time: 2976.008 ms (00:02.976)
postgres=# 

```

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

### Install pg_clean extension

Source: https://docs.opentenbase.org/guide/10-pg_clean

Installs the pg_clean extension on any primary CN. Run this before using the pg_clean functions.

```sql
CREATE EXTENSION pg_clean;

```

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

### Install opentenbase_ai extension

Source: https://docs.opentenbase.org/guide/23-opentenbase_ai?q=

Installs the opentenbase_ai extension. Requires the HTTP extension to be installed first.

```sql
CREATE EXTENSION opentenbase_ai;

```

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

### Join query example: p_cpu join server_physical_info

Source: https://docs.opentenbase.org/guide/17-txsql_architecture

Example query used to illustrate how the optimizer chooses between different join orders and index usage. The same logical result can be obtained by different execution plans, and the optimizer selects based on cost estimation.

```sql
select * from p_cpu join server_physical_info using(SvrAssetId) where p_cpu.SN = 'sn1' and server_physical_info.SvrIsSpecial=1;
```

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

### 部署 OpenTenBase 插件

Source: https://docs.opentenbase.org/guide/14-kubeblocks-support?q=

使用 helm install 命令部署 OpenTenBase 插件。

```bash
$ helm install opentenbase ./opentenbase

```

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

### 创建 OpenTenBase 集群

Source: https://docs.opentenbase.org/guide/14-kubeblocks-support?q=

使用 helm install 命令创建一个包含一个 GTM、一个 Coordinator 和两个 Datanode 的 OpenTenBase 集群。

```bash
$ helm install otb ./opentenbase-cluster

```

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

### Start datanode with pg_ctl

Source: https://docs.opentenbase.org/guide/05-component?q=

Starts the datanode service using pg_ctl with the datanode role and the specified data directory.

```shell
[opentenbase@VM_0_29_centos ~]# pg_ctl -Z datanode -D /data/opentenbase/data/pgxz/dn01 start

```

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

### Create user user1

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that creates a user named user1, allowing connection from any host with password mypassword.

```sql
CREATE USER 'user1'@'%' IDENTIFIED BY 'mypassword';
```

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

### Start a transaction with BEGIN

Source: https://docs.opentenbase.org/guide/04-advanced-use?q=

Starts a transaction in OpenTenBase. The first two forms are equivalent; the third additionally sets the isolation level to READ COMMITTED. Each command returns BEGIN when successful.

```sql
postgres=# begin;
BEGIN


```

```sql
postgres=# begin TRANSACTION ; 
BEGIN


```

```sql
postgres=# begin transaction isolation level read committed ;
BEGIN


```

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

### Create database mydatabase

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Creates a database named mydatabase as a concrete example of the CREATE DATABASE statement.

```sql
CREATE DATABASE mydatabase;

```

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

### Go 安装 gorm (go get -u gorm.io/gorm)

Source: https://docs.opentenbase.org/guide/19-txsql_access?q=

安装 GORM 库，用于 Go 连接 TXSQL。

```bash
go get -u gorm.io/gorm

```

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

### Create OpenTenBase tar.gz package

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Creates a .tar.gz package of the OpenTenBase installation. Requires PG_HOME and INSTALL_PATH to be set.

```bash
cd ${PG_HOME}
tar -zcf ${INSTALL_PATH}/opentenbase-5.21.8-i.x86_64.tar.gz *
cd ${INSTALL_PATH}

```

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

### Connect to OpenTenBase coordinator and run basic SQL

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Connects to the OpenTenBase coordinator node via psql, then creates a database, user, and table, inserts data, and queries it. The session output shows each command's result, including the COPY 2 confirmation and the final SELECT result.

```shell
export LD_LIBRARY_PATH=/home/opentenbase/install/opentenbase/5.21.8/lib  && export PATH=/home/opentenbase/install/opentenbase/5.21.8/bin:${PATH} 
$ psql -h ${CoordinateNode_IP} -p ${CoordinateNode_PORT} -U opentenbase -d postgres

postgres=# create database test;
CREATE DATABASE
postgres=# create user test with password 'test';
CREATE ROLE
postgres=# alter database test owner to test;
ALTER DATABASE
postgres=# \c test test
You are now connected to database "test" as user "test".
test=> create table foo(id bigint, str text) distribute by shard(id);
CREATE TABLE
test=> insert into foo values(1, 'tencent'), (2, 'shenzhen');
COPY 2
test=> select * from foo;
 id |   str    
----+----------
  1 | tencent
  2 | shenzhen
(2 rows)


```

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

### Install PyMySQL for Python

Source: https://docs.opentenbase.org/guide/19-txsql_access

Installs PyMySQL for Python 3 (pip3) or Python 2 (pip). Required before using the Python PyMySQL example to connect to TXSQL.

```bash
## python3 
pip3 install PyMySQL
## python2
pip install pymysql


```

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

### Install TXSQL build dependencies (yum)

Source: https://docs.opentenbase.org/guide/18-txsql_deploy?q=

Installs the build dependencies for compiling TXSQL on CentOS 7.8/7.9 or TencentOS Server. The first command installs general packages; the second installs the GCC 7 toolchain via Software Collections (SCL). Run these as root before starting the build.

```bash
yum install -y git ncurses-devel bison cmake3 libaio-devel openssl openssl-devel cyrus-sasl-devel openldap-devel gtest libtirpc-devel 

### gcc
yum install centos-release-scl -y
yum install devtoolset-7-gcc devtoolset-7-gcc-c++ devtoolset-7-binutils -y

```

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

### 编译连接数据库程序

Source: https://docs.opentenbase.org/guide/02-access

编译conn.c并链接libpq库。需要指定OpenTenBase的include和lib路径。

```bash
gcc -c -I /usr/local/install/opentenbase_pgxz/include/ conn.c  
gcc -o conn conn.o -L /usr/local/install/opentenbase_pgxz/lib/ -lpq  

```

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

### 创建数据表运行输出（go run createtable.go）

Source: https://docs.opentenbase.org/guide/02-access

运行创建数据表示例的输出，显示连接成功和创建数据表成功日志。

```bash
[root@VM_0_29_centos opentenbase]# go run createtable.go 
访问时间： 2018-04-03 20:50:24
日志级别： Log
详细信息： 连接数据库成功
访问时间： 2018-04-03 20:50:24
日志级别： Log
详细信息： 创建数据表成功
```

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

### Create opentenbase user and configure sudo

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Create the 'opentenbase' user, set its password, add it to the appropriate sudo group (wheel for RedHat, sudo for Debian), and enable sudo via visudo.

```bash
# 1. 创建目录 /data
mkdir -p /data

# 2. 添加用户
useradd -d /data/opentenbase -s /bin/bash -m opentenbase # 添加用户 opentenbase

# 3. 设置密码
passwd opentenbase # 设置密码

# 4. 将用户添加到 wheel 组
# 对于 RedHat
usermod -aG wheel opentenbase
# 对于 Debian
usermod -aG sudo opentenbase

# 5. 为 wheel 组启用 sudo 权限（通过 visudo）
visudo 
# 然后取消注释 "% wheel" 行，保存并退出

```

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

### 编译并运行连接数据库示例（go build conn.go）

Source: https://docs.opentenbase.org/guide/02-access

编译并运行连接数据库示例，输出与直接运行相同。

```bash
[root@VM_0_29_centos opentenbase]# go build conn.go 
[root@VM_0_29_centos opentenbase]# ./conn 
访问时间： 2018-04-03 20:40:48
日志级别： Log
详细信息： 连接数据库成功
```

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

### 查看 pgxz 应用程序目录结构

Source: https://docs.opentenbase.org/guide/06-switching?q=

使用 ll 命令查看 /data/opentenbase/install/pgxz 目录下的内容，确认 bin、include、lib、share 子目录存在。

```bash
[opentenbase@VM_0_29_centos pgxz]$ ll /data/opentenbase/install/pgxz
total 4
drwxrwxr-x 2 pgxz pgxz 4096 Nov 10 04:33 bin
drwxrwxr-x 4 pgxz pgxz  189 Nov 10 04:33 include
drwxrwxr-x 4 pgxz pgxz  172 Nov 10 04:33 lib
drwxrwxr-x 3 pgxz pgxz   24 Oct  1 14:54 share
[opentenbase@VM_0_29_centos pgxz]$ 

```

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

### Start dn02 datanode with pg_ctl

Source: https://docs.opentenbase.org/guide/05-component

Starts the dn02 datanode service using pg_ctl with the datanode role.

```shell
[opentenbase@VM_0_47_centos ~]# pg_ctl -Z datanode -D /data/opentenbase/data/pgxz/dn02 start

```

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

### 编译建立数据表程序

Source: https://docs.opentenbase.org/guide/02-access

编译createtable.c并链接libpq库。

```bash
gcc -c -I /usr/local/install/opentenbase_pgxz/include/ createtable.c  
gcc -o createtable createtable.o -L /usr/local/install/opentenbase_pgxz/lib/ -lpq  

```

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

### 创建数据表（Go + pgx）

Source: https://docs.opentenbase.org/guide/02-access

在连接数据库后，执行CREATE TABLE语句创建分布式数据表，并输出创建成功日志。运行示例：go run createtable.go。

```go
package main

import (
    "fmt"
    "time"

    "github.com/jackc/pgx"
)

func main() {
    var error_msg string
    var sql string

    //连接数据库
    conn, err := db_connect()
    if err != nil {
        error_msg = "连接数据库失败，详情：" + err.Error()
        write_log("Error", error_msg)
        return
    }
    //程序运行结束时关闭连接
    defer conn.Close()
    write_log("Log", "连接数据库成功")

    //建立数据表
    sql = "create table public.opentenbase(id varchar(20),nickname varchar(100)) distribute by shard(id) to group  default_group;"
    _, err = conn.Exec(sql)
    if err != nil {
        error_msg = "创建数据表失败,详情：" + err.Error()
        write_log("Error", error_msg)
        return
    } else {
        write_log("Log", "创建数据表成功")
    }
}

/*
功能描述：写入日志处理

参数说明：
log_level -- 日志级别，只能是`Error`或`Log`
error_msg -- 日志内容

返回值说明：无
*/

func write_log(log_level string, error_msg string) {
    //打印错误信息
    fmt.Println("访问时间：", time.Now().Format("2006-01-02 15:04:05"))
    fmt.Println("日志级别：", log_level)
    fmt.Println("详细信息：", error_msg)
}

/*
功能描述：连接数据库

参数说明：无

返回值说明：
conn *pgx.Conn -- 连接信息
err error --错误信息

*/

func db_connect() (conn *pgx.Conn, err error) {
    var config pgx.ConnConfig
    config.Host = "127.0.0.1"    //数据库主机host或ip
    config.User = "opentenbase"         //连接用户
    config.Password = "pgsql"    //用户密码
    config.Database = "postgres" //连接数据库名
    config.Port = 15432          //端口号
    conn, err = pgx.Connect(config)
    return conn, err
}
```

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

### Start cn02 coordinator service with pg_ctl

Source: https://docs.opentenbase.org/guide/05-component?q=

Starts the coordinator service for cn02 using pg_ctl with the coordinator role.

```shell
[opentenbase@VM_0_47_centos ~]# pg_ctl -Z coordinator -D /data/opentenbase/data/pgxz/cn02 start

```

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

### Connect to TXSQL with Go GORM

Source: https://docs.opentenbase.org/guide/19-txsql_access

Go example using GORM with the MySQL driver to connect to TXSQL and query the first student record. Fill in the connection parameters (username, password, host, port, dbname). Note the code is incomplete (missing fmt import and port value) and uses db.close() instead of sqlDB.Close().

```go
package main

import (
"gorm.io/gorm"
"gorm.io/driver/mysql"
)

type Student struct{
    Name string
    Age int
}

func main(){

    // 配置 TXSQL 连接参数 
    username := ""  // 用户名
    password := ""  // 密码
    host := ""    // IP 地址
    port :=      // 端口
    dbname := ""  // 库名

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local", username, password, host, port, dbname)        
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) 
    if err != nil { 
        panic("连接数据库失败, error=" + err.Error()) 
    }
    defer db.close()

    var stu Student
    db.First(&stu)
}


```

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

### Drop idx_customer_date from orders

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Deletes the idx_customer_date index from the orders table. This example appears in the management examples section.

```sql
DROP INDEX idx_customer_date ON orders;
```

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

### 创建 opentenbase 应用程序运行目录

Source: https://docs.opentenbase.org/guide/06-switching?q=

切换到 opentenbase 用户并创建应用程序安装目录 /data/opentenbase/install/pgxz。

```bash
[root@VM_0_29_centos pgxz]# su opentenbase  
[opentenbase@VM_0_29_centos install]$ mkdir -p /data/opentenbase/install/pgxz

```

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

### Example: drop composite index on orders

Source: https://docs.opentenbase.org/guide/20-txsql_basic

Drops the idx_customer_date index from the orders table. This is the third example in the '示例' section.

```sql
DROP INDEX idx_customer_date ON orders;

```

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

### 编译查询数据程序

Source: https://docs.opentenbase.org/guide/02-access

编译select.c，使用C99标准，并链接libpq库。

```bash
gcc -std=c99 -c -I /usr/local/install/opentenbase_pgxz/include/ select.c  
gcc -o select select.o -L /usr/local/install/opentenbase_pgxz/lib/ -lpq

```

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

### Start dn02 data node

Source: https://docs.opentenbase.org/guide/05-component?q=

Start the dn02 data node service using pg_ctl with the datanode role and the data directory /data/opentenbase/data/pgxz/dn02.

```shell
[opentenbase@VM_0_29_centos ~]# pg_ctl -Z datanode -D /data/opentenbase/data/pgxz/dn02 start

```

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

### 连接数据库运行输出（go run conn.go）

Source: https://docs.opentenbase.org/guide/02-access

运行连接数据库示例的输出，显示连接成功日志。

```bash
[root@VM_0_29_centos opentenbase]# go run conn.go 
访问时间： 2018-04-03 20:40:28
日志级别： Log
详细信息： 连接数据库成功
```

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

### Start dn01 data node

Source: https://docs.opentenbase.org/guide/05-component?q=

Start the dn01 data node service using pg_ctl with the datanode role and the data directory /data/opentenbase/data/pgxz/dn01.

```shell
[opentenbase@VM_0_47_centos ~]# pg_ctl -Z datanode -D /data/opentenbase/data/pgxz/dn01 start

```

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

### Install dependencies with yum or apt

Source: https://docs.opentenbase.org/guide/01-quickstart?q=

Install the required build dependencies for OpenTenBase. The first command is for RedHat-based systems (yum), the second for Debian-based systems (apt).

```bash
yum -y install git sudo gcc make readline-devel zlib-devel openssl-devel uuid-devel bison flex cmake postgresql-devel libssh2-devel sshpass libcurl-devel libxml2-devel

```

```bash
apt install -y git sudo gcc make libreadline-dev zlib1g-dev libssl-dev libossp-uuid-dev bison flex cmake libssh2-1-dev sshpass libxml2-dev language-pack-zh-hans

```

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

### Create composite index on orders

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Creates a composite index named idx_customer_date on the customer_id and order_date columns of the orders table. This example appears in the management examples section.

```sql
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
```

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

### 运行 select.go 的输出结果

Source: https://docs.opentenbase.org/guide/02-access

运行 go run select.go 命令后的输出，显示连接、查询成功及查询到的数据。

```bash
[root@VM_0_29_centos opentenbase]# go run select.go
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： 连接数据库成功
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： 查询数据成功
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： id：2 nickname：opentenbase
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： id：3 nickname：postgresql
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： 查询数据成功
访问时间： 2018-04-09 10:35:50
日志级别： Log
详细信息： id：1 nickname：opentenbase

```

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

### Create index on employees.email

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Creates a B-Tree index named idx_email on the email column of the employees table. This example appears in the section on using CREATE INDEX and again in the management examples.

```sql
CREATE INDEX idx_email ON employees (email);
```

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

### 编译插入数据程序

Source: https://docs.opentenbase.org/guide/02-access

编译insert.c并链接libpq库。

```bash
gcc -c -I /usr/local/install/opentenbase_pgxz/include/ insert.c
gcc -o insert insert.o -L /usr/local/install/opentenbase_pgxz/lib/ -lpq

```

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

### Deploy and initialize cluster with pgxc_ctl

Source: https://docs.opentenbase.org/guide/15-docker-deploy

Deploys and initializes the cluster using pgxc_ctl. After entering pgxc_ctl, avoid commands like ls or echo. Use exit or Ctrl+D to leave.

```bash
pgxc_ctl                                # 这一步会进入 --home 位置，默认是/home/$USER/pgxc_ctl, 使用exit退出，或者ctrl + D
deploy all                              # 会使用/home/$USER/pgxc_ctl/pgxc.conf 这个配置文件
init all

exit
```

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

### Drop user1

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that deletes the user user1.

```sql
DROP USER 'user1'@'%';
```

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

### DROP TABLE employees example

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Deletes the employees table.

```sql
DROP TABLE employees;
```

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

### 建立数据表（libpq）

Source: https://docs.opentenbase.org/guide/02-access

使用PQexec执行CREATE TABLE语句，创建分布式表opentenbase。表包含id和nickname字段，按id分片。

```c
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"   
int
main(int argc, char **argv){
    const char *conninfo;
    PGconn     *conn;      
    PGresult   *res;
    const char *sql = "create table opentenbase(id int,nickname text) distribute by shard(id) to group  default_group";
    if (argc > 1){
        conninfo = argv[1];
    }else{
        conninfo = "dbname = postgres";           
    }        
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "连接数据库失败: %s",PQerrorMessage(conn));              
    }else{
        printf("连接数据库成功！\n");
    }
    res = PQexec(conn,sql);
    if(PQresultStatus(res) != PGRES_COMMAND_OK){
        fprintf(stderr, "建立数据表失败: %s",PQresultErrorMessage(res)); 
    }else{
        printf("建立数据表成功！\n");
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

```

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

### Change user1 password

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that changes the password for user1 to newpassword123.

```sql
SET PASSWORD FOR 'user1'@'%' = PASSWORD('newpassword123');
```

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

### Connect to OpenTenBase and create partitioned table

Source: https://docs.opentenbase.org/guide/14-kubeblocks-support?q=

Connects to the OpenTenBase cluster via kubectl exec, then uses psql to create a default node group, a sharding group, and a partitioned table (t1_pt) with range partitioning and shard distribution. The output shows the resulting pgxc_node and pgxc_group views and the table definition.

```bash
$ kubectl exec -it otb-cn-0-0 -- bash

opentenbase@otb-cn-0-0:~$ psql postgres
psql (PostgreSQL 10.0 OpenTenBase V2)
Type "help" for help.

postgres=# select * from pgxc_node;
 node_name | node_type | node_port |     node_host     | nodeis_primary | nodeis_preferred |   node_id   |  node_cluster_name  
-----------+-----------+-----------+-------------------+----------------+------------------+-------------+---------------------
 a_one     | G         |     50001 | otb-gtm           | t              | f                | -1343982441 | opentenbase_cluster
 dn_0      | D         |      5432 | otb-dn-0          | f              | f                |  1485981022 | opentenbase_cluster
 dn_1      | D         |      5432 | otb-dn-1          | f              | f                | -1300059100 | opentenbase_cluster
 cn_0      | C         |      5432 | otb-cn-0          | f              | f                | -1541982360 | opentenbase_cluster
(4 rows)

postgres=# create default node group default_group with(dn_0, dn_1);
CREATE NODE GROUP
postgres=# create sharding group to group default_group;
CREATE SHARDING GROUP
postgres=# clean sharding;
CLEAN SHARDING
postgres=# select * from pgxc_group;
  group_name   | default_group | group_members 
---------------+---------------+---------------
 default_group |             1 | 16384 16385
(1 row)
postgres=# create table public.t1_pt(
f1 int not null,
f2 timestamp not null,
f3 varchar(20),
primary key(f1)) 
partition by range (f2) 
begin (timestamp without time zone '2019-01-01 0:0:0') 
step (interval '1 month') partitions (3) 
distribute by shard(f1) 
to group default_group;
CREATE TABLE
postgres=# \d+ public.t1_pt
                                             Table "public.t1_pt"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 f1     | integer                     |           | not null |         | plain    |              | 
 f2     | timestamp without time zone |           | not null |         | plain    |              | 
 f3     | character varying(20)       |           |          |         | extended |              | 
Indexes:
    "t1_pt_pkey" PRIMARY KEY, btree (f1)
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES
Partition By: RANGE(f2)
     Partitions number: 3
     Start With: 2019-01-01
     Interval Of Partition: 1 MONTH

```

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

### RENAME TABLE employees TO staff example

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Renames the employees table to staff.

```sql
RENAME TABLE employees TO staff;
```

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

### EXPLAIN 查看 join 在 CN 节点执行的查询计划

Source: https://docs.opentenbase.org/guide/09-problem-performance

使用 EXPLAIN 查看 join 在 CN 节点执行的查询计划。该查询在 CN 节点执行 Hash Join，两个表均通过 Remote Subquery Scan 从 DN 获取数据，适合少量数据 join 的 OLTP 场景。

```sql
postgres=#  explain  select opentenbase_1.* from opentenbase_1,opentenbase_2 where opentenbase_1.f1=opentenbase_2.f1 ;    
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Hash Join  (cost=29.80..186.32 rows=3872 width=40)
   Hash Cond: (opentenbase_1.f1 = opentenbase_2.f1)
   ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..158.40 rows=880 width=40)
         ->  Seq Scan on opentenbase_1  (cost=0.00..18.80 rows=880 width=40)
   ->  Hash  (cost=126.72..126.72 rows=880 width=4)
         ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..126.72 rows=880 width=4)
               ->  Seq Scan on opentenbase_2  (cost=0.00..18.80 rows=880 width=4)
(7 rows)

```

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

### Grant all privileges to user1

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that grants all privileges on the mydatabase database to user1.

```sql
GRANT ALL PRIVILEGES ON mydatabase.* TO 'user1'@'%';
```

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

### 流数据 copy from 入表（Go + pgx）

Source: https://docs.opentenbase.org/guide/02-access?q=

使用 pgx 的 CopyFrom 将 5000 行随机数据批量插入 opentenbase 表，并输出日志。运行命令为 go run copy_from.go，成功时日志显示“连接数据库成功”和“Copy 记录成功”。

```go
package main

import (
    "fmt"
    "math/rand"
    "time"

    "github.com/jackc/pgx"
)

func main() {
    var error_msg string

    //连接数据库
    conn, err := db_connect()
    if err != nil {
        error_msg = "连接数据库失败，详情：" + err.Error()
        write_log("Error", error_msg)
        return
    }
    //程序运行结束时关闭连接
    defer conn.Close()
    write_log("Log", "连接数据库成功")

    //构造5000行数据
    inputRows := [][]interface{}{}
    var id string
    var nickname string
    for i := 0; i < 5000; i++ {
        id = fmt.Sprintf("%d", rand.Intn(10000))
        nickname = fmt.Sprintf("%d", rand.Intn(10000))
        inputRows = append(inputRows, []interface{}{id, nickname})
    }
    copyCount, err := conn.CopyFrom(pgx.Identifier{"opentenbase"}, []string{"id", "nickname"}, pgx.CopyFromRows(inputRows))
    if err != nil {
        error_msg = "执行copyFrom失败,详情：" + err.Error()
        write_log("Error", error_msg)
        return
    }
    if copyCount != len(inputRows) {
        error_msg = fmt.Sprintf("执行copyFrom失败，copy行数：%d 返回行数为：%d", len(inputRows), copyCount)
        write_log("Error", error_msg)
        return
    } else {
        error_msg = "Copy 记录成功"
        write_log("Log", error_msg)
    }

}

/*
功能描述：写入日志处理

参数说明：
log_level -- 日志级别，只能是是Error或Log
error_msg -- 日志内容

返回值说明：无
*/

func write_log(log_level string, error_msg string) {
    //打印错误信息
    fmt.Println("访问时间：", time.Now().Format("2006-01-02 15:04:05"))
    fmt.Println("日志级别：", log_level)
    fmt.Println("详细信息：", error_msg)
}

/*
功能描述：连接数据库

参数说明：无

返回值说明：
conn *pgx.Conn -- 连接信息
err error --错误信息

*/

func db_connect() (conn *pgx.Conn, err error) {
    var config pgx.ConnConfig
    config.Host = "127.0.0.1"    //数据库主机host或ip
    config.User = "opentenbase"         //连接用户
    config.Password = "pgsql"    //用户密码
    config.Database = "postgres" //连接数据库名
    config.Port = 15432          //端口号
    conn, err = pgx.Connect(config)
    return conn, err
}

```

```bash
[root@VM_0_29_centos opentenbase]# go run copy_from.go 
访问时间： 2018-04-09 10:36:40
日志级别： Log
详细信息： 连接数据库成功
访问时间： 2018-04-09 10:36:40
日志级别： Log
详细信息： Copy 记录成功

```

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

### INSERT employee record

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that inserts a new employee record into the employees table.

```sql
INSERT INTO employees (name, email, salary) VALUES ('Jane Doe', 'jane.doe@example.com', 50000);
```

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

### 运行输出示例（go run insert.go）

Source: https://docs.opentenbase.org/guide/02-access

运行 insert.go 后的输出，显示连接数据库成功和三次插入数据成功。

```bash
[root@VM_0_29_centos opentenbase]# go run insert.go 
访问时间： 2018-04-03 21:05:51
日志级别： Log
详细信息： 连接数据库成功
访问时间： 2018-04-03 21:05:51
日志级别： Log
详细信息： 插入数据成功
访问时间： 2018-04-03 21:05:51
日志级别： Log
详细信息： 插入数据成功
访问时间： 2018-04-03 21:05:51
日志级别： Log
详细信息： 插入数据成功
```

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

### Select database mydatabase

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Selects the mydatabase database as a concrete example of the USE statement.

```sql
USE mydatabase;

```

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

### Set up postgres_exporter user permissions

Source: https://docs.opentenbase.org/guide/13-opentenbase_monitoring?q=

SQL statements to execute the creation function, drop it, set a password, configure search_path, grant CONNECT, and grant pg_monitor role.

```sql
SELECT __tmp_create_user();

DROP FUNCTION __tmp_create_user();

ALTER USER postgres_exporter WITH PASSWORD 'password';

ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

-- OpenTenBase中集成的PostgreSQL版本是10，所以可以执行以下语句，历史版本可前往开源地址进行查看。
GRANT pg_monitor to postgres_exporter;

```

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

### Run Grafana Docker container

Source: https://docs.opentenbase.org/guide/13-opentenbase_monitoring?q=

Starts Grafana in a Docker container with a persistent volume for configuration.

```bash
docker run -d -p 3000:3000 --name=grafana   --volume grafana-storage:/var/lib/grafana  grafana/grafana-enterprise
```

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

### 通过mtr启动mysqld测试

Source: https://docs.opentenbase.org/guide/18-txsql_deploy

在mysql-test目录下执行./mtr --start拉起mysqld进行测试。

```bash
cd bld-release/mysql-test
./mtr --start
```

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

### Generate with specified return type

Source: https://docs.opentenbase.org/guide/23-opentenbase_ai?q=

Generates a response with a specified return type (integer in this example).

```sql
SELECT ai.generate('9*9 = ?',NULL::integer);

```

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

### DELETE employee record

Source: https://docs.opentenbase.org/guide/20-txsql_basic?q=

Example that deletes the employee record for John Doe from the employees table.

```sql
DELETE FROM employees WHERE name = 'John Doe';
```

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

### 运行创建数据表脚本

Source: https://docs.opentenbase.org/guide/02-access

运行createtable.py脚本，输出连接数据库成功和建立数据表成功。

```bash
[opentenbase@VM_0_29_centos python]$ python createtable.py   
连接数据库成功
建立数据表成功

```