1.导出工具gs_dump 

GaussDB提供的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 DATABASE
gaussdb=> create user user1 with password 'User01#123';
CREATE ROLE
gaussdb=> grant all privileges on database db1 to user1; 
GRANT
gaussdb=> 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 -r
gsql ((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 3
db1=> create table test2(id int,name varchar2(20));
CREATE TABLE
db1=> insert into test2 values(1,'薛双奇1'),(2,'薛双奇2');
INSERT 0 2
db1=> 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 superuser
gs_dump[user='user1'][localhost][port='30100'][db1][2024-04-09 12:44:08]: WARNING: directory not dumped because current user is not a superuser
gs_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 superuser
gs_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 successfully
gs_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 ROLE

5.恢复 

[omm@gauss001 backup]$ mkdir user1 
[omm@gauss001 backup]$ tar xvf user1.tar -C user1
toc.dat
6845.dat
6846.dat
restore.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 directory
gsql:/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.sql
SET
SET
SET
SET
SET
SET
SET
SET
DROP TABLE
DROP TABLE
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 3
COPY 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文件里面的：(\.) (;)(空行)