PostgerSQL实例的创建:
创建pg实例:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl addinst myinst -D database/5432/
启动实例:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl start myinst
在SequoiaDB中创建集合空间和集合:
创建 company_domain 数据域:db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间:db.createCS("company", { Domain: "company_domain" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
在 PostgreSQL 实例创建数据库 company: /opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company myinst
查看数据库:/opt/sequoiasql/postgresql/bin/psql -l
配置PostgreSQL实例:
加载SequoiaDB连接驱动:
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载 SequoiaDB 连接驱动: CREATE EXTENSION sdb_fdw;
配置与 SequoiaDB 连接参数
在 PostgreSQL 实例中配置 SequoiaDB 连接参数:
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw
OPTIONS
(
address '127.0.0.1',
service '11810',
user '',
password '',
preferedinstance 'A',
transaction 'on'
);
创建employee表:
CREATE FOREIGN TABLE employee
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );
PG实例操作与事务管理:
检查 PostgreSQL 实例进程:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl listinst
在SequoiaDB中创建集合空间和集合:
创建 company_domain 数据域:db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间:db.createCS("company", { Domain: "company_domain" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
创建manager集合:db.company.createCL("manager", { "ShardingKey": { "empno": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
配置PostgreSQL实例:
加载SequoiaDB连接驱动:
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载 SequoiaDB 连接驱动: CREATE EXTENSION sdb_fdw;
配置与 SequoiaDB 连接参数
在 PostgreSQL 实例中配置 SequoiaDB 连接参数:
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw
OPTIONS
(
address '127.0.0.1',
service '11810',
user '',
password '',
preferedinstance 'A',
transaction 'on'
);
创建employee表:
CREATE FOREIGN TABLE employee
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );
创建manager表:
CREATE FOREIGN TABLE manager
(
empno INTEGER,
department TEXT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'manager', decimal 'on' );
事务管理:
验证回滚能力:
开始事务:begin;
写入数据:INSERT INTO manager VALUES (2, 'Product Department');
回滚事务操作:rollback;
验证提交能力:
开始事务:begin;
写入数据:INSERT INTO manager VALUES (2, 'Product Department');
提交事务操作:commit;;
查看 PostgreSQL 是否打开事务功能:\des+ sdb_server;
操作截图中 transaction 的参数值为 on,说明 PostgreSQL 的事务功能已经打开。
关闭PostgreSQL事务功能:ALTER SERVER sdb_server OPTIONS ( SET transaction 'off' );
创建视图:
CREATE VIEW manager_view AS
SELECT
e.ename, m.department
FROM
employee AS e, manager AS m
WHERE
e.empno = m.empno;
查询视图数据:SELECT * FROM manager_view;
创建自定义函数:
CREATE OR REPLACE FUNCTION totalRecords () RETURNS INTEGER AS $total$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(*) INTO total FROM employee;
RETURN total;
END;
$total$ language plpgsql;
调用函数:SELECT totalRecords();
PG数据导入导出
创建 company_domain 数据域:db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间:db.createCS("company", { Domain: "company_domain" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
写入测试数据:
for (var i = 0; i < 1000; i++)
{
db.company.employee.insert( { "empno": i, "ename": "TEST", "age": 20 } )
};
在PostgreSQL实例创建数据库company:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company myinst
配置PostgreSQL实例:
加载SequoiaDB连接驱动:
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载 SequoiaDB 连接驱动: CREATE EXTENSION sdb_fdw;
配置与 SequoiaDB 连接参数
在 PostgreSQL 实例中配置 SequoiaDB 连接参数:
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw
OPTIONS
(
address '127.0.0.1',
service '11810',
user '',
password '',
preferedinstance 'A',
transaction 'on'
);
创建employee表:
CREATE FOREIGN TABLE employee
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );
数据导出:
通过COPY(SELECT)命令将外部表employee的数据导出为.csv
导出数据到.csv文件:COPY (SELECT * FROM employee) TO '/opt/sequoiasql/postgresql/employee.csv' with delimiter ',' csv;
查看 csv 文件内容: \! more /opt/sequoiasql/postgresql/employee.csv
!前包含反斜线“\”,退出查看按 ctrl + c
数据导入:
清空 employee 集合内数据:db.company.employee.truncate();
通过 sdbimprt 工具从备份文件中恢复数据到 employee 数据集合中:sdbimprt --hosts=localhost:11810 --type=csv --file=/opt/sequoiasql/postgresql/employee.csv --fields="empno int, ename string, age int" -c company -l employee
PG执行计划
创建 company_domain 数据域:db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间:db.createCS("company", { Domain: "company_domain" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
创建 manager 集合:db.company.createCL("manager", { "ShardingKey": { "empno": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
employee写入测试数据:
for (var i = 0; i < 1000; i++)
{
db.company.employee.insert( { "empno": i, "ename": "TEST", "age": 20 } )
};
manager写入测试数据
for (var i = 0; i < 1000; i++)
{
db.company.manager.insert( { "empno": i, "department": "Sales Department" } );
}
显示访问计划(explain select * from employee where empno = 1):
db.company.employee.find( { "empno": 1 } ).explain( { "Run": true } );
在 employee 表的 empno 字段上创建唯一索引: db.company.employee.createIndex("empno_Idx", { "empno": 1 }, true );
在PostgreSQL实例创建数据库company:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company myinst
配置PostgreSQL实例:
加载SequoiaDB连接驱动:
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载 SequoiaDB 连接驱动: CREATE EXTENSION sdb_fdw;
配置与 SequoiaDB 连接参数
在 PostgreSQL 实例中配置 SequoiaDB 连接参数:
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw
OPTIONS
(
address '127.0.0.1',
service '11810',
user '',
password '',
preferedinstance 'A',
transaction 'on'
);
创建employee表:
CREATE FOREIGN TABLE employee
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );
创建 employee2 表(把 pushdownsort 和 pushdownlimit 参数设置为关闭):
CREATE FOREIGN TABLE employee2
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on', pushdownsort 'off', pushdownlimit 'off' );
创建manager表:
CREATE FOREIGN TABLE manager
(
empno INTEGER,
department TEXT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'manager', decimal 'on' );
更新表的统计信息:
ANALYZE employee;
ANALYZE employee2;
ANALYZE manager;
在 PostgreSQL 中执行条件查询语句,并查看执行计划: EXPLAIN ANALYZE SELECT * FROM employee a INNER JOIN manager b ON a.empno = b.empno WHERE a.empno = 1;
在 PostgreSQL 中执行分页查询语句,并查看执行计划:
EXPLAIN ANALYZE SELECT * FROM employee ORDER BY empno ASC LIMIT 5 OFFSET 0;
EXPLAIN ANALYZE SELECT * FROM employee2 ORDER BY empno ASC LIMIT 5 OFFSET 0;
PG元数据同步
添加实例 myinst(作为主 PostgreSQL 实例): /opt/sequoiasql/postgresql/bin/sdb_sql_ctl addinst myinst -D database/5432/ -p 5432
添加实例 myinst1(作为备 PostgreSQL 实例): /opt/sequoiasql/postgresql/bin/sdb_sql_ctl addinst myinst1 -D database/5432/ -p 5433
配置PostgreSQL实例免密登录:
进入 sdbadmin 的 home 目录: cd /home/sdbadmin
同步工具已提前放置在 sdbadmin 的 home 目录,解压同步工具: tar xvf SsqlDisseminateDDL.tar
进入解压目录:cd SsqlDisseminateDDL
拷贝免密文件到 home 目录: cp .pgpass /home/sdbadmin/
将文件权限设置为0600: chmod 0600 /home/sdbadmin/.pgpass
进入 myinst 实例的数据目录,修改部分配置: cd /opt/sequoiasql/postgresql/database/5432
为了配合元数据同步,需要对应修改 postgresql.conf 配置文件的参数:
echo "log_destination = 'csvlog'" >> postgresql.conf
echo "logging_collector = on" >> postgresql.conf
echo "log_directory = '/opt/sequoiasql/postgresql/database/5432/pg_log'" >> postgresql.conf
echo "log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'" >> postgresql.conf
echo "log_rotation_age = 28d" >> postgresql.conf
echo "log_rotation_size = 20MB" >> postgresql.conf
echo "log_statement = 'ddl'" >> postgresql.conf
拷贝元数据同步工具到数据目录: cp /home/sdbadmin/SsqlDisseminateDDL/SsqlDisseminateDDL ./
拷贝元数据同步工具配置到数据目录: cp /home/sdbadmin/SsqlDisseminateDDL/config ./
创建日志目录:
mkdir /opt/sequoiasql/postgresql/database/5432/pg_log
mkdir /opt/sequoiasql/postgresql/database/5432/log
重启myinst实例:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl restart myinst
启动元数据同步工具:
元数据同步工具在启动后会一直常驻后台运行,主要用于定时解析主 PostgreSQL 实例日志中新增的 DDL 操作语句并下发到各备 PostgreSQL 实例中执行:nohup python /opt/sequoiasql/postgresql/database/5432/SsqlDisseminateDDL &
创建 company_domain 数据域:db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
创建 company 集合空间:db.createCS("company", { Domain: "company_domain" } );
创建 employee 集合: db.company.createCL("employee", { "ShardingKey": { "_id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );
employee写入测试数据:db.company.employee.insert( { "empno": 1, "ename": "Jack", "age": 35 } );
在PostgreSQL实例创建数据库company:/opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company myinst
配置PostgreSQL实例:
加载SequoiaDB连接驱动:
登录到 PostgreSQL 实例 Shell: /opt/sequoiasql/postgresql/bin/psql -p 5432 company
加载 SequoiaDB 连接驱动: CREATE EXTENSION sdb_fdw;
配置与 SequoiaDB 连接参数
在 PostgreSQL 实例中配置 SequoiaDB 连接参数:
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw
OPTIONS
(
address '127.0.0.1',
service '11810',
user '',
password '',
preferedinstance 'A',
transaction 'on'
);
创建employee表:
CREATE FOREIGN TABLE employee
(
empno INT,
ename VARCHAR(128),
age INT
)
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );