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' );
注意事項1,注意事項2
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' );