初始化实例
- 通过pgxc_ctl新建一个双CN、双DN的实例，并开启服务。
- 通过telesql连接到CN节点。
- 执行sql “create default node group default_group with(dn01, dn02); create sharding group to group default_group;”。
创建插件
- 通过telesql连接到CN节点。
- 执行sql “create extension teledbx_core;”
- 执行telesql命令\dx，查看插件teledbx_core是否存在。
创建枚举类型
- 通过telesql连接到CN节点。
- 执行sql“create type week as enum('Sun','Mon','Tues','Wed','Thur','Fri','Sat');”。
创建表
执行sql “CREATE TABLE basic_types_table (
id INT PRIMARY KEY,
boolean_col BOOLEAN,
smallint_col SMALLINT,
integer_col INTEGER,
bigint_col BIGINT,
real_col REAL,
double_col DOUBLE PRECISION,
numeric_col NUMERIC(10,2),
decimal_col DECIMAL(10,2),
char_col CHAR(10),
varchar_col VARCHAR(50),
text_col TEXT,
date_col DATE,
time_col TIME,
timestamp_col TIMESTAMP,
interval_col INTERVAL,
binary_col BYTEA
);”
执行sql “create table duty(
person text,
weekday week
);”
执行sql “CREATE TABLE complextest ( id serial PRIMARY KEY, complex_column complex_type );“
创建索引
执行sql “CREATE INDEX integer_index ON basic_types_table(integer_col);” 创建索引。
创建统计对象
执行sql “CREATE STATISTICS basic_types_stats ON boolean_col, integer_col, double_col, numeric_col, char_col, timestamp_colFROM basic_types_table;“
插入数据
1.执行sql “DO $$
DECLARE
i INT := 1;
BEGIN
WHILE i <= 1000 LOOP
INSERT INTO basic_types_table (id, boolean_col, smallint_col, integer_col, bigint_col, real_col, double_col, numeric_col, decimal_col, char_col, varchar_col, text_col, date_col, time_col, timestamp_col, interval_col, binary_col)
VALUES (
i,
CASE WHEN random() < 0.5 THEN TRUE ELSE FALSE END,
trunc(random() * 65536 - 32768)::SMALLINT,
trunc(random() * 2147483647)::INTEGER,
trunc(random() * 9223372036854775807)::BIGINT,
random() * 1000,
random() * 1000,
trunc(random() * 1000 * random() * 100) / 100,
trunc(random() * 1000 * random() * 100) / 100,
substr(md5(random()::text), 1, 10),
substr(md5(random()::text), 1, 50),
md5(random()::text),
CURRENT_DATE - (trunc(random() * 3650) || ' days')::INTERVAL,
CURRENT_TIME - (trunc(random() * 86400) || ' seconds')::INTERVAL,
CURRENT_TIMESTAMP - (trunc(random() * 3650) || ' days')::INTERVAL,
(trunc(random() * 3650) || ' days')::INTERVAL,
decode(md5(random()::text), 'hex')
);
i := i + 1;
END LOOP;
END $$;”插入数据。
2.执行sql “select count(*) from basic_types_table;”查询表内数据行数。
3.执行sql “insert into duty values('April','Sun');
insert into duty values('Harris','Mon');
insert into duty values('Dave','Wed');”插入数据。
4.执行sql “select count(*) from duty; “查询表内数据行数
创建复杂类型
1.使用普通用户权限telesql连接到CN节点。
2.执行sql “CREATE OR REPLACE FUNCTION add_numbers(a FLOAT, b FLOAT)
RETURNS FLOAT AS $$
SELECT a + b;
$$ LANGUAGE SQL;“
3.执行sql“CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
SELECT a + b;
$$ LANGUAGE SQL;”
4.执行sql“CREATE OR REPLACE FUNCTION insert_complextest_with_oid(oid_value oid) RETURNS void AS $$
BEGIN
INSERT INTO complextest (complex_column) VALUES (ROW('Sun', oid_value::regproc));
END;
$$ LANGUAGE plpgsql; “
5.执行sql“SELECT insert_complextest_with_oid(oid)
FROM pg_proc
WHERE proname = 'add_numbers'; “
执行analyze
1.使用普通用户权限telesql连接到CN节点。
2.执行sql “ANALYZE ;”生成统计信息。
3.执行sql “\o first_pg_stats_result.csv
EXECUTE DIRECT ON (coord1) 'SELECT * FROM pg_stats order by (schemaname,tablename,attname)';
\o second_pg_stats_result.csv
EXECUTE DIRECT ON (coord2) 'SELECT * FROM pg_stats order by (schemaname,tablename,attname)';
\o
\! diff first_pg_stats_result.csv second_pg_stats_result.csv“ 比较cn之间的pg_stats是否相同
4.执行sql“\o first_pg_statistic_ext_result.csv
EXECUTE DIRECT ON (coord1) 'SELECT * FROM pg_statistic_ext where stxname =''basic_types_stats''';
\o second_pg_statistic_ext_result.csv
EXECUTE DIRECT ON (coord2) 'SELECT * FROM pg_statistic_ext where stxname =''basic_types_stats''';
\o
\! diff first_pg_statistic_ext_result.csv second_pg_statistic_ext_result.csv” 比较cn之间的pg_statistic_ext 是否相同
5.执行sql“ \o first_pg_class_result.csv
EXECUTE DIRECT ON (coord1) '
SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relhaspkey, relhasrules, relhastriggers
FROM pg_class
WHERE relname IN (''basic_types_table'', ''integer_index'')';
\o second_pg_class_result.csv
EXECUTE DIRECT ON (coord2) '
SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relhaspkey, relhasrules, relhastriggers
FROM pg_class
WHERE relname IN (''basic_types_table'', ''integer_index'')';
\o
\! diff first_pg_class_result.csv second_pg_class_result.csv”比较pg_class是否相同
