分布式数据库表结构以id 为自增分布键 采用 hash 方式分布如何查看某个数据所在的存储位置 

1.将 log_min_error_statement 参数的值设置为 PANIC，可以避免将出错的sql语句记录在系统日志中gaussdb=> show log_min_error_statement; log_min_error_statement ------------------------- error(1 row) gaussdb=> set log_min_error_statement=PANIC;SETgaussdb=> show log_min_error_statement; log_min_error_statement ------------------------- panic(1 row)2、设置日志级别gaussdb=> show log_min_messages; log_min_messages ------------------ warning(1 row) --日志级别的可取值为:debug, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic--因为日志级别（log_min_messages）设置为 DEBUGx（x为DEBUG级别，取值范围为1~5）时，--调试日志中记录的信息可能包含用户的个人数据。

 1.执行命令查看数据库实例中SQL语句执行信息  \x  select * from dbe_perf.get_global_full_sql_by_timestamp('2024-04-07 20:00:00', '2024-04-08 11:00:00');  2.执行命令查看数据库实例中慢SQL语句执行信息  \x select * from dbe_perf.get_global_slow_sql_by_timestamp('2024-04-07 20:00:00', '2024-04-08 11:00:00');  3.查看当前主节点SQL语句执行信息  select * from statement_history; 4.查看当前备节点SQL语句执行信息  --备节点执行。主节点执行会报错。 --select * from dbe_perf.standby_statement_history(is_only_slow, start_timestamp, end_timestamp); --例如： select * from dbe_perf.standby_statement_history(true, '2024-04-07 20:00:00', '2024-04-08 11:00:00');                          原文链接：https://blog.csdn.net/weixin_43346403/article/details/137516746 

1、检查GaussRoach备份生成的备份集。drwx------ 3 omm omm 4096 Apr 8 15:59 20240408_155812drwx------ 3 omm omm 4096 Apr 8 16:25 20240408_162446drwx------ 3 omm omm 4096 Apr 8 16:30 20240408_162921drwx------ 3 omm omm 4096 Apr 8 16:33 20240408_163223drwx------ 3 omm omm 4096 Apr 8 16:39 20240408_1638052、挨个删除备份的备份集cd /data/cluster/tools/script/python3 GaussRoach.py -t delete --media-type disk --media-destination /data/backup \--master-port 3000 --metadata-destination /data/backup2 \--backup-key 20240408_155812 --cascade python3 GaussRoach.py -t delete --media-type disk --media-destination /data/backup \--master-port 3000 --metadata-destination /data/backup2 \--backup-key 20240408_162446 --cascade python3 GaussRoach.py -t delete --media-type disk --media-destination /data/backup \--master-port 3000 --metadata-destination /data/backup2 \--backup-key 20240408_162921 --cascade python3 GaussRoach.py -t delete --media-type disk --media-destination /data/backup \--master-port 3000 --metadata-destination /data/backup2 \--backup-key 20240408_163223 --cascade python3 GaussRoach.py -t delete --media-type disk --media-destination /data/backup \--master-port 3000 --metadata-destination /data/backup2 \--backup-key 20240408_163805 --cascade3、检查备份集[omm@gauss001 roach]$ lltotal 0作者：薛双奇

本文作者：薛双奇1、背景闪回查询可以查询过去某个时间点表的某个 snapshot 数据，这一特性可用于查看和逻辑重建意外删除或更改的受损数据。闪回查询基于MVCC多版本机制，通过检索查询旧版本，获取指定老版本数据。 前提条件undo_retention_time 参数用于设置undo旧版本的保留时间。gaussdb=> show undo_retention_time; undo_retention_time --------------------- 0(1 row)2、参数说明•TIMESTAMP◾指要查询某个表在TIMESTAMP这个时间点上的数据，TIMESTAMP指一个具体的历史时间。 •CSN◾指要查询整个数据库逻辑提交序下某个CSN点的数据，CSN指一个具体逻辑提交时间点，数据库中的CSN为写一致性点，每个CSN代表整个数据库的一个一致性点，查询某个CSN下的数据代表SQL查询数据库在该一致性点的相关数据。 注意:使用时间点进行闪回时，可能会有3s的误差。想要闪回到精确的操作点，需要使用CSN进行闪回。3、开启闪回功能 gs_guc set -N all -I all -c "enable_default_ustore_table=on" (默认打开)--## 开启默认支持Ustore存储引擎gs_guc set -N all -I all -c "version_retention_age=10000" (默认0) --## 旧版本保留的事务数，超过该事务数的旧版本将被回收清理gs_guc set -N all -I all -c "enable_recyclebin=on" (默认关闭) --## 打开回收站gs_guc set -N all -I all -c "recyclebin_retention_time=15min" (默认15分钟)--## 置回收站对象保留时间，超过该时间的回收站对象将被自动清理gs_guc set -N all -I all -c "undo_retention_time=86400" (默认0)--## 该参数表示回滚段中旧版本数据的保留时间，等同于允许闪回查询的时间跨度, 该参数单位是秒，86400 表示是 24 小时。 回收站保留15分钟。Undo,保留24小时。修改如下三个参数即可。gs_guc set -N all -I all -c "undo_retention_time=86400" gs_guc set -N all -I all -c "enable_recyclebin=on" gs_guc set -N all -I all -c "version_retention_age=10000" --重启数据库。cm_ctl stop && cm_ctl start--或者:gs_om -t stop && gs_om -t start4、闪回功能测试(1)当前CSN:48968--查看时间和CSN的对应关系。select snptime,snpcsn from gs_txn_snapshot; 2024-04-09 11:03:44.160392+08 | 24218 2024-04-09 11:03:47.193712+08 | 24221 2024-04-09 11:03:50.226168+08 | 24224 由此可知，当前的CSN=24224 (2)查询数据。gaussdb=> \c mydb1Password for user root: SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)You are now connected to database "mydb1" as user "root".mydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+-------+------------------------------------------------------------------ public | test1 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test2 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(3 rows)mydb1=> select * from test1; id ----(0 rows)mydb1=> insert into test1 values(1),(2),(3);INSERT 0 3mydb1=> select * from test1; id ---- 1 2 3(3 rows)--没有数据，我们插入3条数据。 (3)查看当前的CSN; select snptime,snpcsn from gs_txn_snapshot where snpcsn>=24224; snptime | snpcsn -------------------------------+-------- 2024-04-09 11:03:50.226168+08 | 24224 2024-04-09 11:03:53.258814+08 | 24228 2024-04-09 11:03:56.290929+08 | 24231 2024-04-09 11:03:59.323217+08 | 24235 2024-04-09 11:04:02.357751+08 | 24238 2024-04-09 11:04:05.386135+08 | 24242 2024-04-09 11:04:08.419512+08 | 24247 2024-04-09 11:04:11.453046+08 | 24250 2024-04-09 11:04:14.485619+08 | 24253 2024-04-09 11:04:17.521973+08 | 24256 2024-04-09 11:04:20.553198+08 | 24259 2024-04-09 11:04:23.585653+08 | 24263 2024-04-09 11:04:26.618872+08 | 24268 2024-04-09 11:04:29.650457+08 | 24271 2024-04-09 11:04:32.681014+08 | 24274 2024-04-09 11:04:35.715687+08 | 24277 2024-04-09 11:04:38.748587+08 | 24280 2024-04-09 11:04:41.77907+08 | 24283 2024-04-09 11:04:44.808649+08 | 24287 2024-04-09 11:04:47.840255+08 | 24290 2024-04-09 11:04:50.870112+08 | 24293 2024-04-09 11:04:53.90244+08 | 24297 2024-04-09 11:04:56.935384+08 | 24301 2024-04-09 11:04:59.965591+08 | 24304 2024-04-09 11:05:03.003129+08 | 24307 2024-04-09 11:05:06.033566+08 | 24311 2024-04-09 11:05:09.068603+08 | 24316 2024-04-09 11:05:12.09756+08 | 24319 2024-04-09 11:05:15.130536+08 | 24322 2024-04-09 11:05:18.162522+08 | 24325 2024-04-09 11:05:21.195804+08 | 24329 2024-04-09 11:05:24.22857+08 | 24332 2024-04-09 11:05:27.262362+08 | 24337 2024-04-09 11:05:30.297972+08 | 24340 2024-04-09 11:05:33.325973+08 | 24343 2024-04-09 11:05:36.357083+08 | 24346 2024-04-09 11:05:39.390689+08 | 24349 2024-04-09 11:05:42.418839+08 | 24352 2024-04-09 11:05:45.454785+08 | 24355 2024-04-09 11:05:48.484713+08 | 24358 2024-04-09 11:05:51.518014+08 | 24362 (4)现在我闪回到前面一个CSN=24224;SELECT * FROM test1 TIMECAPSULE CSN 24224;--应该没有数据才对。mydb1=> SELECT * FROM test1 TIMECAPSULE CSN 24224; id ----(0 rows) --果然没有查到数据，我们成功查询闪回到了CSN=24224的时间点。

 1.基于时间的闪回  使用时间点进行闪回时，可能会有3s的误差。想要闪回到精确的操作点，需要使用CSN进行闪回。 2.查询当前时间   select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:08' order by snptime desc;mydb1=> select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:08' order by snptime desc; snptime | snpcsn -------------------------------+-------- 2024-04-09 11:09:35.923647+08 | 24618 2024-04-09 11:09:32.892835+08 | 24615 2024-04-09 11:09:29.861663+08 | 24612 2024-04-09 11:09:26.831202+08 | 24609 2024-04-09 11:09:23.795395+08 | 24604 2024-04-09 11:09:20.767963+08 | 24600 2024-04-09 11:09:17.731541+08 | 24597 --我们等会闪回查询取这个时间点。2024-04-09 11:09:17  3.删除数据。  mydb1=> select * from test1; id ---- 1 2 3(3 rows) mydb1=> delete from test1; DELETE 3mydb1=> select * from test1; id ----(0 rows)  4.查看当前时间。  mydb1=> SELECT CURRENT_TIMESTAMP;        pg_systimestamp         ------------------------------  2024-04-09 11:11:48.42958+08 (1 row) 5.我们闪回到上一个时间点:2024-04-09 11:09:17 SELECT * FROM test1 TIMECAPSULE TIMESTAMP to_timestamp ('2024-04-09 11:09:17', 'YYYY-MM-DD HH24:MI:SS');mydb1=> SELECT * FROM test1 TIMECAPSULE TIMESTAMP to_timestamp ('2024-04-09 11:09:17', 'YYYY-MM-DD HH24:MI:SS'); id ---- 1 2 3(3 rows) 果然我们又闪回成功。

// SQL 执行器切换为分页模式hint FRAGMENT_SQL_QUERY_BY_PAGE = true;分页查询，报错，原先是使用的postgre(海盒)数据库，切换到guassDB出现这种问题

1.背景信息•闪回DROP：可以恢复意外删除的表，从回收站（recyclebin）中恢复被删除的表及其附属结构如索引、表约束等。闪回drop是基于回收站机制，通过还原回收站中记录的表的物理文件，实现已drop表的恢复。•闪回TRUNCATE：可以恢复误操作或意外被进行truncate的表，从回收站中恢复被truncate的表及索引的物理数据。闪回truncate基于回收站机制，通过还原回收站中记录的表的物理文件，实现已truncate表的恢复。2.开启闪回功能gs_guc set -N all -I all -c "enable_default_ustore_table=on" (默认打开)--## 开启默认支持Ustore存储引擎gs_guc set -N all -I all -c "version_retention_age=10000" (默认0) --## 旧版本保留的事务数，超过该事务数的旧版本将被回收清理gs_guc set -N all -I all -c "enable_recyclebin=on" (默认关闭) --## 打开回收站gs_guc set -N all -I all -c "recyclebin_retention_time=15min" (默认15分钟)--## 置回收站对象保留时间，超过该时间的回收站对象将被自动清理gs_guc set -N all -I all -c "undo_retention_time=86400" (默认0) gs_guc set -N all -I all -c "enable_default_ustore_table=on" gs_guc set -N all -I all -c "version_retention_age=10000"gs_guc set -N all -I all -c "enable_recyclebin=on" gs_guc set -N all -I all -c "recyclebin_retention_time=15min" gs_guc set -N all -I all -c "undo_retention_time=86400" 如果已经开启就不用重复开了。3.DROP 表。mydb1=> select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:40' order by snptime desc; snptime | snpcsn -------------------------------+-------- 2024-04-09 11:40:31.55595+08 | 26719 2024-04-09 11:40:28.526085+08 | 26716 2024-04-09 11:40:25.489872+08 | 26711 2024-04-09 11:40:22.45913+08 | 26707 2024-04-09 11:40:19.428504+08 | 26704 2024-04-09 11:40:16.397608+08 | 26701 2024-04-09 11:40:13.36746+08 | 26698 2024-04-09 11:40:10.334387+08 | 26695 2024-04-09 11:40:07.3036+08 | 26692 2024-04-09 11:40:04.270007+08 | 26687 2024-04-09 11:40:01.242144+08 | 26683(11 rows) 我们取当前的CSN:267194、删除表mydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+-------+------------------------------------------------------------------ public | test2 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(2 rows) mydb1=> select * from test2; id ---- 1 2(2 rows) mydb1=> drop table test2;DROP TABLEmydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------------------------------+-------+-------+------------------------------------------------------------------ public | BIN$600023386004$18181C88==$0 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(2 rows)5.闪回DROPmydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------------------------------+-------+-------+------------------------------------------------------------------ public | BIN$600023386004$18181C88==$0 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(2 rows) mydb1=> TIMECAPSULE TABLE "BIN$600023386004$18181C88==$0" to before drop;TimeCapsule Tablemydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+-------+------------------------------------------------------------------ public | test2 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(2 rows)6、查询 mydb1=> select * from test2; id ---- 1 2(2 rows) --果然，闪回DROP 完成。本文作者：薛双奇

1、开启闪回功能gs_guc set -N all -I all -c "enable_default_ustore_table=on" (默认打开)--## 开启默认支持Ustore存储引擎gs_guc set -N all -I all -c "version_retention_age=10000" (默认0) --## 旧版本保留的事务数，超过该事务数的旧版本将被回收清理gs_guc set -N all -I all -c "enable_recyclebin=on" (默认关闭) --## 打开回收站gs_guc set -N all -I all -c "recyclebin_retention_time=15min" (默认15分钟)--## 置回收站对象保留时间，超过该时间的回收站对象将被自动清理gs_guc set -N all -I all -c "undo_retention_time=86400" (默认0) gs_guc set -N all -I all -c "enable_default_ustore_table=on" gs_guc set -N all -I all -c "version_retention_age=10000"gs_guc set -N all -I all -c "enable_recyclebin=on" gs_guc set -N all -I all -c "recyclebin_retention_time=15min" gs_guc set -N all -I all -c "undo_retention_time=86400" 如果已经开启就不用重复开了。2.TRUNCATE表 mydb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+-------+------------------------------------------------------------------ public | test2 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | test3 | table | root | {orientation=row,compression=no,storage_type=USTORE,segment=off}(2 rows) mydb1=> select * from test2; id ---- 1 2(2 rows) mydb1=> TRUNCATE TABLE TEST2; TRUNCATE TABLEmydb1=> select * from test2; id ----(0 rows)3.闪回TRUNCATE 时。mydb1=> TIMECAPSULE TABLE test2 to before TRUNCATE;TimeCapsule Tablemydb1=> select * from test2; id ---- 1 2(2 rows)本文作者：薛双奇

1、开启闪回功能gs_guc set -N all -I all -c "enable_default_ustore_table=on" (默认打开)--## 开启默认支持Ustore存储引擎gs_guc set -N all -I all -c "version_retention_age=10000" (默认0) --## 旧版本保留的事务数，超过该事务数的旧版本将被回收清理gs_guc set -N all -I all -c "enable_recyclebin=on" (默认关闭) --## 打开回收站gs_guc set -N all -I all -c "recyclebin_retention_time=15min" (默认15分钟)--## 置回收站对象保留时间，超过该时间的回收站对象将被自动清理gs_guc set -N all -I all -c "undo_retention_time=86400" (默认0) gs_guc set -N all -I all -c "enable_default_ustore_table=on" gs_guc set -N all -I all -c "version_retention_age=10000"gs_guc set -N all -I all -c "enable_recyclebin=on" gs_guc set -N all -I all -c "recyclebin_retention_time=15min" gs_guc set -N all -I all -c "undo_retention_time=86400" 如果已经开启就不用重复开了。2、操作数据并记录时间select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 12:00' order by snptime desc; snptime | snpcsn -------------------------------+-------- 2024-04-09 11:58:40.135915+08 | 28126 2024-04-09 11:58:37.10279+08 | 28123 2024-04-09 11:58:34.072052+08 | 28120 2024-04-09 11:58:31.040367+08 | 28117 2024-04-09 11:58:28.009364+08 | 28112 2024-04-09 11:58:24.973027+08 | 28108 2024-04-09 11:58:21.945881+08 | 28105 2024-04-09 11:58:18.909798+08 | 28102 2024-04-09 11:58:15.879746+08 | 28099 2024-04-09 11:58:12.84645+08 | 28096 2024-04-09 11:58:09.815601+08 | 28093 2024-04-09 11:58:06.782726+08 | 28090 2024-04-09 11:58:03.74962+08 | 28084 2024-04-09 11:58:00.722211+08 | 28081(14 rows) (1)插入前时间:28126插入数据:mydb1=> insert into test2 values(3),(4),(5),(6);INSERT 0 4mydb1=> select * from test3; id | name ----+--------- 1 | 雪霜去 2 | 薛双奇2(2 rows) mydb1=> select * from test2; id ---- 1 2 3 4 5 6(6 rows) (2)第二次插入:28275 snptime | snpcsn -------------------------------+-------- 2024-04-09 12:00:41.424479+08 | 28275 2024-04-09 12:00:38.394161+08 | 28272 2024-04-09 12:00:35.366127+08 | 28269 2024-04-09 12:00:32.333742+08 | 28266 2024-04-09 12:00:29.301718+08 | 28263 2024-04-09 12:00:26.266639+08 | 28258 2024-04-09 12:00:23.236084+08 | 28254 2024-04-09 12:00:20.205659+08 | 28251 2024-04-09 12:00:17.175269+08 | 28248 2024-04-09 12:00:14.14343+08 | 28245 2024-04-09 12:00:11.110262+08 | 28242 2024-04-09 12:00:08.078396+08 | 28239 2024-04-09 12:00:05.047515+08 | 28234 2024-04-09 12:00:02.009359+08 | 28230(14 rows) --当前时间:2024-04-09 12:00:41insert into test2 values(7),(8);insert into test3 values(3,'薛双奇2'),(3,'薛双奇3');mydb1=> select * from test2; id ---- 1 2 3 4 5 6 7 8(8 rows) mydb1=> select * from test3; id | name ----+--------- 1 | 雪霜去 2 | 薛双奇2 3 | 薛双奇2 3 | 薛双奇3(4 rows)3.闪回TEST2 到:28126mydb1=> TIMECAPSULE TABLE test2 TO CSN 28126;TimeCapsule Tablemydb1=> select * from test2; id ---- 1 2(2 rows) mydb1=> select * from test3; id | name ----+--------- 1 | 雪霜去 2 | 薛双奇2 3 | 薛双奇2 3 | 薛双奇3(4 rows) 由此可见表TEST2已经闪回到插入前的状态，但TEST3却没有任何变化。即闪回表不会对其他表产生影响。4.基于TIMESTAMP 闪回test3; mydb1=> select * from test3; id | name ----+--------- 1 | 雪霜去 2 | 薛双奇2 3 | 薛双奇2 3 | 薛双奇3(4 rows) mydb1=> TIMECAPSULE TABLE test3 TO TIMESTAMP to_timestamp('2024-04-09 12:00:41','YYYY-MM-DD HH24:MI:SS');TimeCapsule Tablemydb1=> select * from test3; id | name ----+--------- 1 | 雪霜去 2 | 薛双奇2(2 rows) --果然基于TIMESTAMP 闪回到之前的时间点了。本文作者：薛双奇

1.导出db1数据库下schema  public;gs_dump -U user1 -W User01#123 -f /home/omm/backup/user1/ -p 30100 db1 -n hr -n public -F d [omm@gauss001 backup]$ gs_dump -U user1 -W User01#123 -f /home/omm/backup/user1/ -p 30100 db1 -n hr -n public -F d gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:35]: no matching schemas were found for pattern "hr"gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:39]: The total objects number is 449.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:39]: [100.00%] 449 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:39]: dump schema hr public successfullygs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:39]: dump database db1 successfullygs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:42:39]: total time: 3819 ms [omm@gauss001 backup]$ cd user1[omm@gauss001 user1]$ lltotal 12-rw------- 1 omm omm 32 Apr 9 14:42 7604.dat.gz-rw------- 1 omm omm 44 Apr 9 14:42 7605.dat.gz-rw------- 1 omm omm 0 Apr 9 14:42 dir.lock-rw------- 1 omm omm 1594 Apr 9 14:42 toc.dat2、恢复db1=> drop table test1; DROP TABLEdb1=> drop table test2;DROP TABLE --导出的gz压缩文件通过 gs_restore的方式可以导入。-s:仅仅导入schema;[omm@gauss001 user1]$ gs_restore -U user1 -W User01#123 /home/omm/backup/user1/ -p 30100 -d db1 -e -cstart restore operation ...table test1 complete data imported !table test2 complete data imported !Finish reading 7 SQL statements!end restore operation ...restore operation successfultotal time: 45 ms --清空回收站。db1=> purge recyclebin;PURGE RECYCLEBINdb1=> \d List of relations Schema | Name | Type | Owner | Storage --------+-------+-------+-------+------------------------------------------------------------------ public | test1 | table | user1 | {orientation=row,compression=no,storage_type=ustore,segment=off} public | test2 | table | user1 | {orientation=row,compression=no,storage_type=ustore,segment=off}(2 rows)db1=> select * from test1; 1 2 3 db1=> select * from test2; 1 | 薛双奇1 2 | 薛双奇2本文作者：薛双奇

1、导出工具gs_dumpGaussDB提供的gs_dump和gs_dumpall工具，能够帮助用户导出需要的数据库对象或其相关信息。通过导入工具将导出的数据信息导入至需要的数据库，可以完成数据库信息的迁移。gs_dump支持导出单个数据库或其内的对象，而gs_dumpall支持导出数据库中所有数据库或各库的公共全局对象2.注意事项 gs_dump和gs_dumpall工具导出数据时，生成列不会被转储。注意事项•禁止修改-F c/d/t 格式导出的文件和内容，否则可能无法恢复成功。 •如果数据库中包含的对象数量（数据表、视图、索引）在50万以上，为了提高性能且避免出现内存问题，建议通过gs_guc工具设置数据库节点的如下参数（如果参数值大于如下建议值，则无需设置）。gs_guc set -N all -Z datanode -I all -c 'max_prepared_transactions = 1000'gs_guc set -N all -Z datanode -I all -c 'max_locks_per_transaction = 512' mydb1=> show max_prepared_transactions; max_prepared_transactions --------------------------- 800(1 row) mydb1=> show max_locks_per_transaction; max_locks_per_transaction --------------------------- 256(1 row) --重启生效。gs_om -t restart gaussdb=> show max_prepared_transactions; max_prepared_transactions --------------------------- 1000(1 row)gaussdb=> show max_locks_per_transaction; max_locks_per_transaction --------------------------- 512(1 row)3、造数gaussdb=> create database db1; CREATE DATABASEgaussdb=> create user user1 with password 'User01#123';CREATE ROLEgaussdb=> grant all privileges on database db1 to user1; GRANTgaussdb=> alter database db1 owner to user1; ALTER DATABASE--root用户切换到db1数据库，将public的权限授予user1; gaussdb=> \c db1 Password for user root: SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)You are now connected to database "db1" as user "root".db1=> grant create on schema public to user1; GRANT db1=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+----------+---------+-------+----------------------- db1 | user1 | UTF8 | C | C | =Tc/user1 + | | | | | user1=CTc/user1 + | | | | | user1=APm/user1 [omm@gauss001 ~]$ gsql -h 192.168.0.142 -p 30100 -d db1 -U user1 -W User01#123 -rgsql ((GaussDB Kernel 505.0.0.SPC0500 build 9500ee0b) compiled at 2023-12-28 22:56:07 commit 6847 last mr 14991 release)SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)Type "help" for help. db1=> db1=> create table test1(id int); CREATE TABLE db1=> insert into test1 values(1),(2),(3);INSERT 0 3db1=> create table test2(id int,name varchar2(20));CREATE TABLEdb1=> insert into test2 values(1,'薛双奇1'),(2,'薛双奇2');INSERT 0 2db1=> select * from test1; id ---- 1 2 3(3 rows) db1=> select * from test2; id | name ----+--------- 1 | 薛双奇1 2 | 薛双奇2(2 rows)4、导出数据[omm@gauss001 ~]$ gs_dump -U user1 -f /home/omm/backup/user1.tar -p 30100 db1 -F t [omm@gauss001 backup]$ gs_dump -U user1 -f /home/omm/backup/user1.tar -p 30100 db1 -F t Password: gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:05]: Notice: options -U is not super or sysadmin role, can only back up objects belonging to user user1.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:08]: WARNING: subscriptions not dumped because current user is not a superusergs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:08]: WARNING: directory not dumped because current user is not a superusergs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:08]: The total objects number is 449.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:09]: WARNING: synonym not dumped because current user is not a superusergs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:09]: [100.00%] 449 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:09]: dump database db1 successfullygs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:09]: total time: 10519 ms user1不是超级用户或者sysadmin,只能备份。gaussdb=> alter user user1 with SYSADMIN;ALTER ROLE5、恢复[omm@gauss001 backup]$ mkdir user1 [omm@gauss001 backup]$ tar xvf user1.tar -C user1toc.dat6845.dat6846.datrestore.sql --(1)设置参数，重启生效。允许导入数据。gs_guc set -I all -Z datanode -c "enable_copy_server_files=on"gs_om -t restart --(2)导入报错。db1=> \i /home/omm/backup/user1/restore.sql报错:gsql:/home/omm/backup/user1/restore.sql:61: ERROR: could not open file "/data/cluster/data/dn/dn_6001/$$PATH$$" for reading: No such file or directorygsql:/home/omm/backup/user1/restore.sql:69: ERROR: could not open file "/data/cluster/data/dn/dn_6001/$$PATH$$" for reading: No such file or directory --解决方法，修改路径。1)vi /home/omm/backup/user1/restore.sql:%s#\$\$PATH\$\$#/home/omm/backup/user1#g $$PATH$$ 全部替换为:/home/omm/backup/user1 gsql -h 192.168.0.142 -p 30100 -d db1 -U user1 -W User01#123 -r 2)删除文件里面的:如下两行和空行。\.; 3)重新导入db1=> \i /home/omm/backup/user1/restore.sqlSETSETSETSETSETSETSETSETDROP TABLEDROP TABLESETSETSETCREATE TABLEALTER TABLECREATE TABLEALTER TABLECOPY 3COPY 2 4)检查数据。db1=> select * from test1; id ---- 1 2 3(3 rows) db1=> select * from test2; id | name ----+--------- 1 | 薛双奇1 2 | 薛双奇2(2 rows)6、总结gs_dump 备份出来的内容，需要修改一些内容，才可以导入到数据库里面。(1)$$PATH$$ 全部替换为:/home/omm/backup/user1(2) 删除.dat文件里面的：(\.) (;)(空行) 本文作者：薛双奇

1、导出 [omm@gauss001 user1]$ gs_dump -U user1 -W User01#123 -f /home/omm/backup/user1.tar -p 30100 db1 -F t gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:53:54]: The total objects number is 449.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:53:54]: [100.00%] 449 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:53:54]: dump database db1 successfullygs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 14:53:54]: total time: 3785 ms2、恢复db1=> truncate table test1; TRUNCATE TABLEdb1=> truncate table test2;TRUNCATE TABLE--这里不做清理也是可以的。恢复时自动删除。 gs_restore -U user1 -W User01#123 /home/omm/backup/user1.tar -p 30100 -d db1 -e -c[omm@gauss001 user1]$ gs_restore -U user1 -W User01#123 /home/omm/backup/user1.tar -p 30100 -d db1 -e -cstart restore operation ...table test1 complete data imported !table test2 complete data imported !Finish reading 8 SQL statements!end restore operation ...restore operation successfultotal time: 46 ms db1=> select * from test1; 1 2 3 db1=> select * from test2; 1 | 薛双奇1 2 | 薛双奇2

1.导出gs_dumpall -U user1 -W User01#123 -f /home/omm/backup/MPPDB_backup.sql -p 30100 [omm@gauss001 backup]$ gs_dumpall -U user1 -W User01#123 -f /home/omm/backup/MPPDB_backup.sql -p 30100 -cgs_dump[user='user1'][localhost][port='30100'][dbname='db1'][2024-04-09 15:13:15]: The total objects number is 449.gs_dump[user='user1'][localhost][port='30100'][dbname='db1'][2024-04-09 15:13:15]: [100.00%] 449 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][dbname='db1'][2024-04-09 15:13:15]: dump database dbname='db1' successfullygs_dump[user='user1'][localhost][port='30100'][dbname='db1'][2024-04-09 15:13:15]: total time: 3791 msgs_dump[user='user1'][localhost][port='30100'][dbname='drs'][2024-04-09 15:13:20]: The total objects number is 1219.gs_dump[user='user1'][localhost][port='30100'][dbname='drs'][2024-04-09 15:13:20]: [100.00%] 1219 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][dbname='drs'][2024-04-09 15:13:20]: dump database dbname='drs' successfullygs_dump[user='user1'][localhost][port='30100'][dbname='drs'][2024-04-09 15:13:20]: total time: 5290 msgs_dump[user='user1'][localhost][port='30100'][dbname='postgres'][2024-04-09 15:13:24]: The total objects number is 451.gs_dump[user='user1'][localhost][port='30100'][dbname='postgres'][2024-04-09 15:13:24]: [100.00%] 451 objects have been dumped.gs_dump[user='user1'][localhost][port='30100'][dbname='postgres'][2024-04-09 15:13:24]: dump database dbname='postgres' successfullygs_dump[user='user1'][localhost][port='30100'][dbname='postgres'][2024-04-09 15:13:24]: total time: 4057 msgs_dumpall[user='user1'][localhost][port='30100'][2024-04-09 15:13:24]: dumpall operation successfulgs_dumpall[user='user1'][localhost][port='30100'][2024-04-09 15:13:24]: total time: 13249 ms 可以看出，导出的内容:db1/drs/postgres三个数据库。 --近导出元数据。即结构定义。gs_dumpall -U user1 -W User01#123 -f /home/omm/backup/MPPDB_backup.sql -p 30100 -s2、本地原地还原gaussdb=> \i /home/omm/backup/MPPDB_backup.sqlSETSETgsql:/home/omm/backup/MPPDB_backup.sql:12: ERROR: Database "db1" is being accessed by other users. You can stop all connections by command: "clean connection to all force for database XXXX;" or wait for the sessions to end by querying view: "pg_stat_activity".DETAIL: There is 1 other session using the database.gsql:/home/omm/backup/MPPDB_backup.sql:13: ERROR: Database "drs" is being accessed by other users. You can stop all connections by command: "clean connection to all force for database XXXX;" or wait for the sessions to end by querying view: "pg_stat_activity".DETAIL: There are 8 other sessions using the database.gsql:/home/omm/backup/MPPDB_backup.sql:22: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:23: ERROR: role "drs" cannot be dropped because some objects depend on itDETAIL: privileges for database drsowner of schema drs101 objects in database drsgsql:/home/omm/backup/MPPDB_backup.sql:24: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:25: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:26: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:27: ERROR: current user cannot be droppedgsql:/home/omm/backup/MPPDB_backup.sql:28: ERROR: role "user1" cannot be dropped because some objects depend on itDETAIL: owner of database db1owner of schema user13 objects in database db1gsql:/home/omm/backup/MPPDB_backup.sql:37: ERROR: role "backupUser" already existsgsql:/home/omm/backup/MPPDB_backup.sql:38: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:39: ERROR: role "drs" already existsALTER ROLEgsql:/home/omm/backup/MPPDB_backup.sql:41: ERROR: role "metricUser" already existsgsql:/home/omm/backup/MPPDB_backup.sql:42: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:43: ERROR: role "rdsAdmin" already existsgsql:/home/omm/backup/MPPDB_backup.sql:44: ERROR: Permission denied to change privilege of the initial account.gsql:/home/omm/backup/MPPDB_backup.sql:45: ERROR: role "repUser" already existsgsql:/home/omm/backup/MPPDB_backup.sql:46: ERROR: Permission denied.gsql:/home/omm/backup/MPPDB_backup.sql:47: ERROR: role "root" already existsALTER ROLEgsql:/home/omm/backup/MPPDB_backup.sql:49: ERROR: role "user1" already existsALTER ROLEgsql:/home/omm/backup/MPPDB_backup.sql:51: NOTICE: SET "search_path" TO "core" will only take effect after restarting session.ALTER ROLEgsql:/home/omm/backup/MPPDB_backup.sql:52: NOTICE: SET "current_schema" TO "core" will only take effect after restarting session.ALTER ROLEgsql:/home/omm/backup/MPPDB_backup.sql:59: NOTICE: role "root" is already a member of role "gs_role_account_lock"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:60: NOTICE: role "root" is already a member of role "gs_role_copy_files"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:61: NOTICE: role "root" is already a member of role "gs_role_pldebugger"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:62: NOTICE: role "root" is already a member of role "gs_role_replication"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:63: NOTICE: role "root" is already a member of role "gs_role_signal_backend"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:64: NOTICE: role "root" is already a member of role "gs_role_tablespace"GRANT ROLEgsql:/home/omm/backup/MPPDB_backup.sql:73: ERROR: database "db1" already existsREVOKEREVOKEGRANTGRANTGRANTgsql:/home/omm/backup/MPPDB_backup.sql:79: ERROR: database "drs" already existsREVOKEREVOKEGRANTGRANTGRANTGRANTREVOKEREVOKEGRANTGRANTGRANTGRANTGRANTGRANTREVOKEREVOKEGRANTGRANT作者：薛双奇

 1.密码安全策略   用户密码存储在系统表 pg_authid 中，为防止用户密码泄露，GaussDB对用户密码进行加密存储， 所采用的加密算法由配置参数 password_encryption_type 决定。 •当参数password_encryption_type设置为0时，表示采用MD5方式对密码加密。     MD5加密算法安全性低，存在安全风险，不建议使用。 •当参数password_encryption_type设置为1时，表示采用sha256和MD5方式对密码加密。     MD5加密算法安全性低，存在安全风险，不建议使用。 •当参数password_encryption_type设置为2时，表示采用sha256方式对密码加密，为默认配置。 •当参数password_encryption_type设置为3时，表示采用sm3方式对密码加密。 2.查看已配置的加密算法。  gaussdb=# SHOW password_encryption_type;  password_encryption_type --------------------------  2 (1 row) 3.修改密码加密算法。  --0/1都不安全。 gs_guc reload -Z datanode -N all -I all -c "password_encryption_type=2" 4.账户密码的复杂度及长度要求如下  ◾包含大写字母（A-Z）的最少个数（根据GUC参数password_min_uppercase配置）。 ◾包含小写字母（a-z）的最少个数（根据GUC参数password_min_lowercase配置）。 ◾包含数字（0-9）的最少个数（根据GUC参数password_min_digital配置）。 ◾包含特殊字符的最少个数（根据GUC参数password_min_special配置，特殊字符的列表请参见表1）。 ◾密码的最小长度（根据GUC参数password_min_length配置）。 ◾密码的最大长度（根据GUC参数password_max_length配置）。 5.关于若口令的说明。  ◾弱口令指的是强度较低，容易被破解的密码，对于不同的用户或群体，弱口令的定义可能会有所区别，     用户需自己添加定制化的弱口令。 ◾弱口令字典中的口令存放在 gs_global_config 系统表中，当创建用户、修改用户需要设置密码时，     将会把用户设置口令和弱口令字典中存放的口令进行对比，如果命中，则会提示用户该口令为弱口令，设置密码失败。 ◾弱口令字典默认为空，用户通过以下语法可以对弱口令字典进行增加和删除 --示例如下： CREATE WEAK PASSWORD DICTIONARY WITH VALUES ('oracle'), ('mysql123'),('gaussdb001'); DROP WEAK PASSWORD DICTIONARY;   --由此可见，我们可以通过创建弱口令字典来加强密码的管理。不让设置太简单容易被猜到的密码。 6.密码重用问题   不可重用天数默认值为60天，不可重用次数默认值是0。这两个参数值越大越安全 --查看已配置的参数。 gaussdb=# SHOW password_reuse_time;  password_reuse_time ---------------------  60 (1 row)   密码过期后，60天以内不能设置和以前相同的密码。 不建议设置为0，即使需要设置也要将所有数据库节点中的password_reuse_time都设置为0才能生效。   数据库用户的密码都有密码有效期（password_effect_time）， 当达到密码到期提醒天数（password_notify_time）时， 系统会在用户登录数据库时提示用户修改密码。     gs_guc reload -Z datanode -N all -I all -c "password_reuse_time=60"   --0表示不能重用。每次设置必须不同。 gaussdb=# SHOW password_reuse_max;  password_reuse_max --------------------  0 (1 row)   --修改密码可以重用的次数。 gs_guc reload -Z datanode -N all -I all -c "password_reuse_max = 0"  7.密码有效期   gaussdb=# SHOW password_effect_time;  password_effect_time ----------------------  90 (1 row) --设置密码有效期。 gs_guc reload -Z datanode -N all -I all -c "password_effect_time = 90"   这里需要注意账号(用户)有效期和密码有效期可以不同。从两个不同的维度进行约束。 gaussdb=# SHOW password_notify_time;  password_notify_time ----------------------  7 (1 row)   --当达到密码到期提醒天数:过期前一个礼拜通知。 gs_guc reload -Z datanode -N all -I all -c "password_notify_time = 7"  8.修改密码。  gaussdb=# ALTER USER user1 IDENTIFIED BY 'NewPasswd' REPLACE 'OldPasswd'; ALTER ROLE   gaussdb=# ALTER USER joe IDENTIFIED BY "********"; ALTER ROLE