人大金仓常用命令

数据库备份为dmp文件（用system用户）

su - kingbase
sys_dump -h ip -U system  -p54321 -d库名   -Fc -v -f /home/kingbase/库名.dmp

还原dmp：
sys_restore -Usystem -p54321 -v -d库名 /home/kingbase/库名.dmp

数据库备份为sql文件（用system用户）

su - kingbase
sys_dump -h ip -U system  -p54321 -d库名   -Fp -v -f /home/kingbase/库名.sql

备份单表
sys_dump -U system -d test -t 表名 -f /home/kingbase/test.sql --inserts

还原sql：
ksql -Usystem -p54321 -d库名 -f /home/kingbase/库名.sql

修改数据库的属主

SELECT
'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to 用户名;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( '模式名' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;

修改数据表权限

grant ALL ON all tables in schema 模式名 TO 用户名;

人大金仓主备模式配置读写分离

jdbc:kingbase8://主库IP:端口/数据库名?USEDISPATCH=true&SLAVE_ADD=备机1ip&SLAVE_PORT=端口1&nodeList=node1,node2&HOSTLOADRATE=30&currentSchema=public
#50是表示主节点读负载率

统计数据

SELECT
    nspname AS schema_name,
    c.relname AS table_name,
    pg_relation_size(c.oid) AS size,
    pg_stat_user_tables.n_live_tup AS row_count,
    COUNT(a.attnum) AS column_count
FROM
    pg_class c
JOIN
    pg_namespace n ON n.oid = c.relnamespace
JOIN
    pg_stat_user_tables ON pg_stat_user_tables.relid = c.oid
LEFT JOIN
    pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE
    c.relkind = 'r' -- 只选择普通表
GROUP BY
    nspname, c.relname, c.oid, pg_stat_user_tables.n_live_tup
ORDER BY
    nspname, c.relname;

查询当前活跃的数据库连接和具体sql

select * from sys_stat_activity where state <> 'idle';


查询指定库当前活跃的连接
SELECT pid, usename, client_addr, application_name FROM sys_stat_activity WHERE datname = "test"    AND pid <> pg_backend_pid();

断开所有活跃的连接
SELECT pg_terminate_backend(pid) FROM sys_stat_activity WHERE datname = 'test'  AND pid <> pg_backend_pid();

数据库修改名字
ALTER DATABASE test RENAME TO test1;