人大金仓常用查询sql
时间：2023-01-18 21:00:00
V8r3查询license有效时间 select get_license_validdays();
select get_license_info();
copy用法 test=# copy (select * from t4 where a=1) to ‘/home/kingbase/t4.sql’;
test=# truncate t4;
test=# copy t4 from ‘/home/kingbase/t4.sql’;
CP231=# create table tt1(a int,b varchar(20));
CP231=# insert into tt1 values(1,‘abc’);
CP231=# copy (select a,b from tt1) to ‘/home/kingbase/v8r3_sigle/tt1.sql’;
CP231=# create table tt2(d int ,c int ,g varchar(20));
CP231=# copy tt2(d,g) from ‘/home/kingbase/v8r3_sigle/tt1.sql’;
CP231=# select * from tt2;
D | C | G
—±–±----
1 | | abc
复制 copy方法(时间快一倍，需要提前建好表结构)
v8r3
sys_dump -Usystem -W 123456 -h 127.0.0.1 -d test -t t4 -a | ksql -U system -W -h 127.0.0.1 -d test2
v8r6
sys_dump -Usystem -h 127.0.0.1 -d test -t t4 -a | ksql -U system -W -h 127.0.0.1 -d test2
sys_dump备份 备份指定数据库
sys_dump -Usystem -d warehouses400 -Fc -f $backup_dest/warehouses400_20201127.dmp -v >> $log_dest/db_dump.log_1 2>&1
备份指定模式schema
sys_dump -Usystem warehouses400 -n nwcs_user -Fc -f $backup_dest/warehouses400_nwcs_user.dmp -v >> $log_dest/schema_dump.log_1 2>&1
备份指定表table
sys_dump -Usystem -t ‘public.bmsql_new_order’ warehouses400 -Fc -f $backup_dest/warehouses400_public.bmsql_new_order.dmp -v >> $log_dest/table_dump.log_1 2>&1
备份指定多个表T1 / T2
sys_dump -Usystem -W 123456 -t ‘PUBLIC.T1’ -t ‘PUBLIC.T2’ -d TEST -Fc -f 2.dmp -v
备份public下的所有表
sys_dump -U SYSTEM -W kingbase -t ‘PUBLIC.*’ DB1 -f DB1.TEST001.sql
备份public以te开头的表
sys_dump -U SYSTEM -W kingbase -t ‘PUBLIC.TE*’ DB1 -f DB1.TEST002.sql
备份public以te排除开头表test001表
sys_dump -U SYSTEM -W kingbase -t ‘PUBLIC.TE*’ -T ‘PUBLIC.TEST001’ DB1 -f db1.sql
sys_restore恢复 将scott模式恢复到sc002中(需要提前创建)sc002）
sys_restore -USYSTEM -W123456 -g SCOTT G SC002 -d test /home/kingbase/test.dmp
-n, --schema=NAME 只恢复该模式的对象
还原单表：T4、T5
sys_restore -Usystem -W 123456 -d CP230 -t ‘T4’ -t ‘T5’ test.dmp
查询数据库端口 show port;
或
select name,context,unit,setting,boot_val,reset_val from sys_settings where name in (‘port’);
查询超级用户可用的连接数 select name,context,unit,setting,boot_val,reset_val from sys_settings where name in (‘superuser_reserved_connections’);
打开查询时间 \timing
创建复制槽： select * from sys_create_physical_replication_slot(‘slot_node1’);
select * from sys_create_physical_replication_slot(‘slot_node2’);
删除复制槽： select * from sys_drop_replication_slot(‘slot_node1’);
select * from sys_drop_replication_slot(‘slot_node2’);
查询表膨胀情况： SELECT relname,n_live_tup,n_dead_tup FROM sys_stat_user_tables order by n_dead_tup desc;
查询数据库启动时间： select sys_postmaster_start_time();
查看flush_location字段和当前日志位置是否相同 select * from sys_stat_replication ，select sys_current_xlog_location();
vacuum回收空间： vacuum ANALYZE VERBOSE te_vacuum;
vacuum FULL VERBOSE te_vacuum;
执行计划 \timing
explain SELECT * FROM t1;
explain analyze SELECT * FROM t1;
修改system用户密码： alter user system with password ‘123456’;
查看当前显示的内容schema下的表path： show search_path;
==
临时设置：
set search_path TO public;
永久设置：
kingbase.conf设置reload生效（sys_ctl -D /kingbase/data reload）
search_path = ‘"$user", public’ # schema names
数据库日志每天循环一次，建议保留30天 logging_collector=on
log_destination=‘stderr’
log_directory=‘sys_log’
log_filename=‘kingbase-%d.log’
log_truncate_on_rotation=on
log_rotation_age=1440
log_rotation_size=500MB
备机克隆的前提： 1.主库sys_hba.conf需要添加以下行文件。
host all SYSTEM 192.168.0.191/16 md5
host replication SYSTEM 192.168.0.191/16 md5
host all SYSTEM 192.168.0.192/16 md5
hos replication SYSTEM 192.168.0.192/16 md5
2.需要把db/etc/kingbase.conf配置内容加入到主库data/kingbase.conf文件最后
3.启动主库
./sys_ctl start -D …/data/
备机操作：
执行下面克隆
su - kingbase -c " cd /home/kingbase/cluster/clu1/db/bin/;chmod +x *; ./sys_basebackup -F p -X stream -v -P -h192.168.0.191 -p54321 -USYSTEM -W123456 -D …/data "
禁用索引：
KINGBASE=#update sys_index set indisvalid=false where indexrelid=‘ind123’ ::regclass;
检查失效的索引
KINGBASE=#select relname from sys_index,sys_class where relkind=‘i’ and sys_index.indexrelid=sys_class.oid and (sys_index.indislive=‘f’ or sys_index.indisvalid=‘f’);
设置指定用户user1最大连接数
alter USER user1 WITH CONNECTION LIMIT 50;
kill锁信息
DB1=# SELECT sys_terminate_backend(33576) FROM sys_stat_activity;
V8R6在主库手动加载vip
kbha -A loadvip
V8R6手动删除vip
kbha -A unloadvip
查询大小写敏感
22:09:30 (SYSTEM@192.168.0.210:54321)hhy=#show case_sensitive;
case_sensitive
off
(1 row)
查询表的oid
10:43:29 (u1@[local]:54321)dbb=>select ‘tb1’::regclass::oid;
oid
16495
(1 row)
DB1=# select oid,relname ,relkind from sys_class where relname in (‘TEST001’,‘TEST002’);
OID | RELNAME | RELKIND
-------±--------±--------
16413 | TEST001 | r
16416 | TEST002 | r
(2 rows)
r代表表
i代表索引
v代表视图
查询数据库oid
10:53:46 (u1@[local]:54321)dbb=>select oid from sys_database where datname=‘dbb’;
oid
16493
(1 row)
DB1=# select datid,datname from sys_stat_database order by 1;
查询表的存储路径
10:52:13 (u1@[local]:54321)dbb=>select sys_relation_filepath(‘tb1’);
sys_relation_filepath
sys_tblspc/16492/SYS_V008R002_201608131/16493/16495
/data/Kingbase/ES/tbs/tbs1/ —sys_tblspc/16492
SYS_V008R002_201608131
/格式化为挂载点/表空间的oid/SYS_版本_固定值/数据库的oid/表的oid
查看备份信息
[kingbase@host13 bak]$ sys_rman show -USYSTEM -W kingbase -d TEMPLATE2
[kingbase@host13 bak]$ sys_rman -USYSTEM -W kingbase -d TEMPLATE2 show QHYRAU
查询rman备份保留期
[kingbase@host13 wal]$ sys_rman retention show
查询参数生效方式
该视图有一列context来标识参数的修改有何限制。
mydb=# select distinct context from pg_settings ;
context
backend
user
internal
postmaster
superuser
sighup
superuser-backend
TEST=# select distinct context from sys_settings ;
CONTEXT
backend
user
internal
superuser
sighup
kingbase
superuser-backend
backend：可以在postgresql.conf中对这些设置进行更改，而无需重新启动服务器。
但新的配置值只会出现在这之后的连接中，在已有的连接中，这些值不会改变。
user：该类参数表示，普通用户可以通过set命令来更改参数的配置值。[reload生效]
internal：该类参数是内部参数，也就是说，不可以进行修改，除非重新initdb。
Postmaster/kingbase：该类参数更改配置项后，需要重启PostgreSQL实例才能生效。
superuser：该类参数可以由超级用户来改变，改变时，只会影响到自身的session，不会影响到其他的用户。
sighup：在postgresql.conf配置文件中更改这种类型的参数无须重启实例，只需要向postmaster进程发送一个SIGHUP信号，让其重新读取配置文件即可。postmaster进程收到信号后，也会向其他子进程发送SIGHUP信号，让新的参数值在其他子进程中也生效。该类参数区别于backend类参数。
superuser-backend:该类参数可以由超级用户来改变，可以在postgresql.conf中对这些设置进行更改，而无需重新启动服务器。但新的配置值只会出现在这之后的连接中，在已有的连接中，这些值不会改变。
验证：
select * from sys_settings where name=‘archive_dest’;
select * from sys_settings where name=‘shared_buffers’;
输出查询内容到文件：
TEST=# \o /tmp/a.txt
TEST=# select name, context from sys_settings ;
TEST=# \o
TEST=# \q
[kingbase@host230 data]$ more /tmp/a.txt
NAME | CONTEXT
------------------------------------------±------------------
allow_system_table_mods | kingbase
application_name | user
archive_command | sighup
集群bmj设置：
1正常安装BMJ单机版本(客户端)，安装完毕切换到V8数据库，重启服务器或者停止所有数据库相关进程后注销当前用户生效。
2停止主备机数据库，删除备机data目录，
由于单机数据库自启动会影响集群状态，关闭数据库后修改
/opt/Kingbase/ES/V8/Server/bin/kingbase8d为old_kingbase8d
3修改主机data目录下kingbase.conf
集群通讯：[两边都要开]
启动数据库通讯协议,端口8890（230机器）
cd /opt/Kingbase/ES/V8/Server/bin
./esHAmodel.sh start
检测服务是否启动 ps -ef |grep Kingbase
netstat -anp | grep es
测试通讯协议端口（231机器操作）
./es_client -U kingbase -W 123456 -p 8890 -h 192.168.0.230 -o “echo hello world”
4检查大小写设置、
TEST=# show case_sensitive ;
case_sensitive
on
选中--case-insensitive ==》 不区分大小写
不选，默认 ==》区分大小写
--case-insensitive 不区分大小写
字符集
TEST=# select datname,sys_encoding_to_char(ENCODING) from sys_database;
确认所需命令CRON、ARPING是否存在
whereis cron 、whereis arping、whereis ip
设置开机自启动
[root@host231 init.d]# more /etc/init.d/auto.sh
#!/bin/bash
#chkconfig:2345 86 10
#description:resind
service NetworkManager stop
service network restart
chkconfig --list
chkconfig --add auto.sh
其中2345是默认启动级别，级别有0-6共7个级别。
等级0表示：表示关机
等级1表示：单用户模式
等级2表示：无网络连接的多用户命令行模式
等级3表示：有网络连接的多用户命令行模式
等级4表示：不可用
等级5表示：带图形界面的多用户模式
等级6表示：重新启动
10是启动优先级，90是停止优先级，优先级范围是0－100，数字越大，优先级越低。
查询表大小：
select relname,relpages*8/1024 MB from sys_class where relname not like ‘SYS_%’ order by MB desc;
单表
select sys_size_pretty(sys_relation_size(‘TA’));
select relname,relpages*8/1024 mb from sys_class where relname = ‘TA’;
