签到成功

知道了

CNDBA社区CNDBA社区

openGauss 逻辑备份与恢复 操作示例

2023-04-13 21:52 1010 0 原创 openGauss
作者： dave
本文链接：http://www.cndba.cn/dave/article/116549

在之前的博客，我们了解了openGauss 的备份恢复概念，如下：

openGauss 备份恢复 说明
https://www.cndba.cn/dave/article/116548
openGauss 闪回恢复 操作示例
https://www.cndba.cn/dave/article/116546

逻辑备份只能基于备份时刻进行数据转储，恢复时也只能恢复到备份时保存的数据。逻辑备份恢复适合于数据量小的场景。可以备份单表和多表，单database和所有database。备份后的数据需要使用gsql或者gs_restore工具恢复。

1 gs_dump / gs_restore工具

gs_dump支持将数据库信息导出至纯文本格式的SQL脚本文件或其他归档文件中。

  1. 纯文本格式的SQL脚本文件：包含将数据库恢复为其保存时的状态所需的SQL语句。通过gsql运行该SQL脚本文件，可以恢复数据库。即使在其他主机和其他数据库产品上，只要对SQL脚本文件稍作修改，也可以用来重建数据库。
  2. 归档格式文件：包含将数据库恢复为其保存时的状态所需的数据，可以是tar格式、目录归档格式或自定义归档格式。该导出结果必须与gs_restore配合使用来恢复数据库，gs_restore工具在导入时，系统允许用户选择需要导入的内容，甚至可以在导入之前对等待导入的内容进行排序。

1.1 查看命令帮助

命令帮助如下：http://www.cndba.cn/dave/article/116549

[dave@www.cndba.cn bin]$ gs_dump --help
gs_dump dumps a database as a text file or to other formats.

Usage:
  gs_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME                         output file or directory name
  -F, --format=c|d|t|p                        output file format (custom, directory, tar,
                                              plain text (default))
  -v, --verbose                               verbose mode
  -V, --version                               output version information, then exit
  -Z, --compress=0-9                          compression level for compressed formats
  --lock-wait-timeout=TIMEOUT                 fail after waiting TIMEOUT for a table lock
  -?, --help                                  show this help, then exit

Options controlling the output content:
  -a, --data-only                             dump only the data, not the schema
  -b, --blobs                                 include large objects in dump
  -c, --clean                                 clean (drop) database objects before recreating
  -C, --create                                include commands to create database in dump
  -E, --encoding=ENCODING                     dump the data in encoding ENCODING
  -g, --exclude-guc=GUC_PARAM                 do NOT dump the GUC_PARAM set
  -n, --schema=SCHEMA                         dump the named schema(s) only
  -N, --exclude-schema=SCHEMA                 do NOT dump the named schema(s)
  -o, --oids                                  include OIDs in dump
  -O, --no-owner                              skip restoration of object ownership in
                                              plain-text format
  -s, --schema-only                           dump only the schema, no data
  -q, --target=VERSION                        dump data format can compatible Gaussdb version (v1 or ..)
  -S, --sysadmin=NAME                         system admin user name to use in plain-text format
  -t, --table=TABLE                           dump the named table(s) only
  -T, --exclude-table=TABLE                   do NOT dump the named table(s)
  --include-table-file=FileName               dump the named table(s) only
  --exclude-table-file=FileName               do NOT dump the named table(s)
  --pipeline                                  use pipeline to pass the password,
                                              forbidden to use in terminal
  -x, --no-privileges/--no-acl                do not dump privileges (grant/revoke)
  --column-inserts/--attribute-inserts        dump data as INSERT commands with column names
  --disable-dollar-quoting                    disable dollar quoting, use SQL standard quoting
  --disable-triggers                          disable triggers during data-only restore
  --exclude-table-data=TABLE                  do NOT dump data for the named table(s)
  --exclude-with                              do NOT dump WITH() of table(s)
  --inserts                                   dump data as INSERT commands, rather than COPY
  --no-publications                           do not dump publications
  --no-security-labels                        do not dump security label assignments
  --no-subscriptions                          do not dump subscriptions
  --no-tablespaces                            do not dump tablespace assignments
  --no-unlogged-table-data                    do not dump unlogged table data
  --include-alter-table                       dump the table delete column
  --quote-all-identifiers                     quote all identifiers, even if not key words
  --section=SECTION                           dump named section (pre-data, data, or post-data)
  --serializable-deferrable                   wait until the dump can run without anomalies
  --dont-overwrite-file                       do not overwrite the existing file in case of plain, tar and custom format
  --use-set-session-authorization
                                              use SET SESSION AUTHORIZATION commands instead of
                                              ALTER OWNER commands to set ownership
  --exclude-function                          do not dump function and procedure
  --with-encryption=AES128                    dump data is encrypted using AES128
  --with-key=KEY                              AES128 encryption key, must be 16 bytes in length
  --with-salt=RANDVALUES                      used by gs_dumpall, pass rand value array
  --include-extensions                        include extensions in dump
  --binary-upgrade                            for use by upgrade utilities only
  --binary-upgrade-usermap="USER1=USER2"      to be used only by upgrade utility for mapping usernames
  --non-lock-table                            for use by OM tools utilities only
  --include-depend-objs                       dump the object which depends on the input object
  --exclude-self                              do not dump the input object

Connection options:
  -h, --host=HOSTNAME                         database server host or socket directory
  -p, --port=PORT                             database server port number
  -U, --username=NAME                         connect as specified database user
  -w, --no-password                           never prompt for password
  -W, --password=PASSWORD                     the password of specified database user
  --role=ROLENAME                             do SET ROLE before dump
  --rolepassword=ROLEPASSWORD                 the password for role

If no database name is supplied, then the PGDATABASE environment
variable value is used.

[dave@www.cndba.cn bin]$

具体说明可以参考官方手册：

https://docs.opengauss.org/zh/docs/5.0.0/docs/DatabaseOMGuide/%E9%80%BB%E8%BE%91%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D.htmlhttp://www.cndba.cn/dave/article/116549

注意事项：

http://www.cndba.cn/dave/article/116549

  1. 禁止修改-F c/d/t 格式导出的文件和内容，否则可能无法恢复成功。对于-F p 格式导出的文件，如有需要，可根据需要谨慎编辑导出文件。
  2. 为了保证数据一致性和完整性，gs_dump会对需要转储的表设置共享锁。如果表在别的事务中设置了共享锁，gs_dump会等待锁释放后锁定表。如果无法在指定时间内锁定某个表，转储会失败。用户可以通过指定–lock-wait-timeout选项，自定义等待锁超时时间。
  3. 不支持加密导出存储过程和函数。

1.2 操作示例

查看当前数据库信息：

[dave@www.cndba.cn ~]$ gsql -d postgres -p 15500 -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# /l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 cndba     | omm   | UTF8      | C       | C     | 
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(4 rows)

openGauss=# 

cndba=# /dt
                                          List of relations
 Schema |         Name         | Type  | Owner |                       Storage                        
--------+----------------------+-------+-------+------------------------------------------------------
 public | cndba                | table | omm   | {orientation=row,storage_type=ustore,compression=no}
 public | customer_address_p1  | table | omm   | {orientation=row,compression=no}
 public | employees_table      | table | omm   | {orientation=row,compression=no}
 public | graderecord          | table | omm   | {orientation=row,compression=no}
 public | hash_partition_table | table | omm   | {orientation=row,compression=no}
 public | metro_ride_record    | table | omm   | {orientation=row,compression=no}
 public | sales_table          | table | omm   | {orientation=row,compression=no}
 public | t2                   | table | omm   | {orientation=row,storage_type=astore,compression=no}
 public | ustc                 | table | omm   | {orientation=row,storage_type=ustore,compression=no}
(9 rows)

cndba=# /dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(11 rows)

cndba=#

示例1：导出cndba数据库全量信息，导出的backup.sql文件格式为纯文本格式。

[dave@www.cndba.cn ~]$ gs_dump -h localhost -p 15500 cndba -U omm -W omm@123456 -f /data/backup/cndba.sql -F p
gs_dump[port='15500'][cndba][2023-04-13 20:31:29]: The total objects number is 449.
gs_dump[port='15500'][cndba][2023-04-13 20:31:29]: [100.00%] 449 objects have been dumped.
gs_dump[port='15500'][cndba][2023-04-13 20:31:30]: dump database cndba successfully
gs_dump[port='15500'][cndba][2023-04-13 20:31:30]: total time: 4114  ms

[dave@www.cndba.cn ~]$ ll /data/backup/cndba.sql 
-rw------- 1 omm dbgrp 10224 Apr 13 20:31 /data/backup/cndba.sql

[dave@www.cndba.cn ~]$ head /data/backup/cndba.sql 
--
-- openGauss database dump
--

SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET session_replication_role = replica;
[dave@www.cndba.cn ~]$

这里要注意，导出的sql里纯文本默认使用的copy命令，如下：http://www.cndba.cn/dave/article/116549http://www.cndba.cn/dave/article/116549

http://www.cndba.cn/dave/article/116549 

COPY employees_table (employee_id, employee_name, onboarding_date, "position") FROM stdin;
1       SMITH                   1997-01-10 00:00:00     Manager             
2       JONES                   2001-05-06 00:00:00     Supervisor          
3       WILLIAMS                2011-09-17 00:00:00     Engineer            
/.
;

可以在导出时加参数：–inserts，改成INSERT命令（而非COPY命令）转储数据。但这会导致恢复缓慢。

恢复时直接使用gsql 导入sql：我们这里直接恢复到cndba 库，会提示我们对象已经存在：

[dave@www.cndba.cn ~]$ gsql -h localhost -p 15500 -d cndba -U omm -W omm@123456 -r -f /data/backup/cndba.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
gsql:/data/backup/cndba.sql:27: ERROR:  relation "cndba" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:57: ERROR:  relation "customer_address_p1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:81: ERROR:  relation "employees_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:104: ERROR:  relation "graderecord" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:122: ERROR:  relation "hash_partition_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:149: ERROR:  relation "metro_ride_record" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:172: ERROR:  relation "sales_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:185: ERROR:  relation "t2" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:198: ERROR:  relation "ustc" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/cndba.sql:316: ERROR:  relation "ds_customer_address_p1_index1" already exists
gsql:/data/backup/cndba.sql:323: ERROR:  relation "ds_customer_address_p1_index2" already exists
gsql:/data/backup/cndba.sql:330: ERROR:  relation "ds_customer_address_p1_index3" already exists
gsql:/data/backup/cndba.sql:337: ERROR:  relation "ds_customer_address_p1_index4" already exists
REVOKE
REVOKE
GRANT
GRANT
total time: 113  ms
[dave@www.cndba.cn ~]$

我们这里创建一个新库来恢复：

cndba=# create database ustc;

[dave@www.cndba.cn ~]$ gsql -h localhost -p 15500 -d ustc -U omm -W omm@123456 -r -f /data/backup/cndba.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
total time: 172  ms
[dave@www.cndba.cn ~]$

恢复成功：

[dave@www.cndba.cn ~]$ gsql -h localhost -p 15500 -d ustc -U omm -W omm@123456 -r -c "/dt"
                                          List of relations
 Schema |         Name         | Type  | Owner |                       Storage                        
--------+----------------------+-------+-------+------------------------------------------------------
 public | cndba                | table | omm   | {orientation=row,storage_type=ustore,compression=no}
 public | customer_address_p1  | table | omm   | {orientation=row,compression=no}
 public | employees_table      | table | omm   | {orientation=row,compression=no}
 public | graderecord          | table | omm   | {orientation=row,compression=no}
 public | hash_partition_table | table | omm   | {orientation=row,compression=no}
 public | metro_ride_record    | table | omm   | {orientation=row,compression=no}
 public | sales_table          | table | omm   | {orientation=row,compression=no}
 public | t2                   | table | omm   | {orientation=row,storage_type=astore,compression=no}
 public | ustc                 | table | omm   | {orientation=row,storage_type=ustore,compression=no}
(9 rows)

示例2：执行gs_dump，导出cndba数据库全量信息，导出backup.tar文件格式为tar格式。

[dave@www.cndba.cn ~]$ gs_dump -h localhost -p 15500 cndba -U omm -W omm@123456 -f /data/backup/cndba.tar -F t
gs_dump[port='15500'][cndba][2023-04-13 20:49:19]: The total objects number is 449.
gs_dump[port='15500'][cndba][2023-04-13 20:49:19]: [100.00%] 449 objects have been dumped.
gs_dump[port='15500'][cndba][2023-04-13 20:49:19]: dump database cndba successfully
gs_dump[port='15500'][cndba][2023-04-13 20:49:19]: total time: 3889  ms
[dave@www.cndba.cn ~]$

示例3：执行gs_dump，导出cndba数据库全量信息，导出的backup.dmp文件格式为自定义归档格式。

[dave@www.cndba.cn ~]$ gs_dump -h localhost -p 15500 cndba -U omm -W omm@123456 -f /data/backup/cndba.dmp -F c
gs_dump[port='15500'][cndba][2023-04-13 20:56:10]: The total objects number is 449.
gs_dump[port='15500'][cndba][2023-04-13 20:56:10]: [100.00%] 449 objects have been dumped.
gs_dump[port='15500'][cndba][2023-04-13 20:56:10]: dump database cndba successfully
gs_dump[port='15500'][cndba][2023-04-13 20:56:10]: total time: 3866  ms
[dave@www.cndba.cn ~]$

示例4：执行gs_dump，导出postgres数据库全量信息，导出的cndba_backup文件格式为目录格式。

[dave@www.cndba.cn ~]$ gs_dump -h localhost -p 15500 cndba -U omm -W omm@123456 -f /data/backup/cndba_backup -F d
gs_dump[port='15500'][cndba][2023-04-13 20:57:22]: The total objects number is 449.
gs_dump[port='15500'][cndba][2023-04-13 20:57:22]: [100.00%] 449 objects have been dumped.
gs_dump[port='15500'][cndba][2023-04-13 20:57:22]: dump database cndba successfully
gs_dump[port='15500'][cndba][2023-04-13 20:57:22]: total time: 4128  ms
[dave@www.cndba.cn ~]$ 
[dave@www.cndba.cn backup]$ cd cndba_backup/
[dave@www.cndba.cn cndba_backup]$ ll
total 48
-rw------- 1 omm dbgrp  351 Apr 13 20:57 4842.dat.gz
-rw------- 1 omm dbgrp  229 Apr 13 20:57 4843.dat.gz
-rw------- 1 omm dbgrp  169 Apr 13 20:57 4844.dat.gz
-rw------- 1 omm dbgrp   46 Apr 13 20:57 4845.dat.gz
-rw------- 1 omm dbgrp  139 Apr 13 20:57 4846.dat.gz
-rw------- 1 omm dbgrp   26 Apr 13 20:57 4847.dat.gz
-rw------- 1 omm dbgrp   26 Apr 13 20:57 4848.dat.gz
-rw------- 1 omm dbgrp   26 Apr 13 20:57 4849.dat.gz
-rw------- 1 omm dbgrp   50 Apr 13 20:57 4850.dat.gz
-rw------- 1 omm dbgrp    0 Apr 13 20:57 dir.lock
-rw------- 1 omm dbgrp 9582 Apr 13 20:57 toc.dat
[dave@www.cndba.cn cndba_backup]$

除了纯文本使用gsql 恢复之外，其他的三种格式：tar 、自定归档的格式、目录的恢复都需要使用gs_restore命令。另外tar格式不支持压缩且对于单独表有8GB的大小限制。

gs_restore 命令的具体用法直接看帮助，我们这里直接看示例：http://www.cndba.cn/dave/article/116549

[dave@www.cndba.cn cndba_backup]$ gs_restore -h localhost -p 15500 -d cndba -U omm -W omm@123456 -c  /data/backup/cndba.dmp
start restore operation ...
table cndba complete data imported !
table customer_address_p1 complete data imported !
table employees_table complete data imported !
table graderecord complete data imported !
table hash_partition_table complete data imported !
table metro_ride_record complete data imported !
table sales_table complete data imported !
table t2 complete data imported !
table ustc complete data imported !
Finish reading 26 SQL statements!
end restore operation ...
restore operation successful
total time: 366  ms

[dave@www.cndba.cn cndba_backup]$ gs_restore -h localhost -p 15500 -d cndba -U omm -W omm@123456 -c  /data/backup/cndba.tar
start restore operation ...
table cndba complete data imported !
table customer_address_p1 complete data imported !
table employees_table complete data imported !
table graderecord complete data imported !
table hash_partition_table complete data imported !
table metro_ride_record complete data imported !
table sales_table complete data imported !
table t2 complete data imported !
table ustc complete data imported !
Finish reading 26 SQL statements!
end restore operation ...
restore operation successful
total time: 366  ms
[dave@www.cndba.cn cndba_backup]$ 

[dave@www.cndba.cn cndba_backup]$ gs_restore -h localhost -p 15500 -d cndba -U omm -W omm@123456 -c  /data/backup/cndba_backup
start restore operation ...
table cndba complete data imported !
table customer_address_p1 complete data imported !
table employees_table complete data imported !
table graderecord complete data imported !
table hash_partition_table complete data imported !
table metro_ride_record complete data imported !
table sales_table complete data imported !
table t2 complete data imported !
table ustc complete data imported !
Finish reading 26 SQL statements!
end restore operation ...
restore operation successful
total time: 356  ms
[dave@www.cndba.cn cndba_backup]$

当然，也可以到导出的过程中也可以排除某些表或者模式：

1.-T, –exclude-table=TABLE，排除某些表，如果有多个表，就写多个-T
gs_dump -h host_name -p port_number postgres -f backup/bkp_shl2.sql -T table1 -T table2

2.-t, –table=TABLE：只导某些表，也可以使用通配符指定多个表对象。
gs_dump -h host_name -p port_number postgres -f backup/bkp_shl2.sql -t schema1.table1 -t schema2.table2

3.-n schemaname：只导某些模式。
gs_dump -h host_name -p port_number postgres -f backup/bkp_shl2.sql -n sch1 -n sch2

4.-N, –exclude-schema=SCHEMA，不导某些模式。
gs_dump -h host_name -p port_number postgres -f backup/bkp_shl2.sql -N sch1 -N sch2

2 gs_dumpall 工具

gs_dumpall是openGauss用于导出所有数据库相关信息工具，它可以导出openGauss数据库的所有数据，包括默认数据库postgres的数据、自定义数据库的数据以及openGauss所有数据库公共的全局对象。

gs_dumpall在导出openGauss所有数据库时分为两部分：

  1. gs_dumpall自身对所有数据库公共的全局对象进行导出，包括有关数据库用户和组、表空间以及属性（例如，适用于数据库整体的访问权限）信息。
  2. gs_dumpall通过调用gs_dump来完成openGauss中各数据库的SQL脚本文件导出，该脚本文件包含将数据库恢复为其保存时的状态所需要的全部SQL语句。

以上两部分导出的结果为纯文本格式的SQL脚本文件，使用gsql运行该脚本文件可以恢复openGauss数据库。

gs_dumpall 命令选项和gs_dump 差不多，这里不在列举，有兴趣的直接查看命令帮助。http://www.cndba.cn/dave/article/116549

[dave@www.cndba.cn cndba_backup]$ gs_dumpall -h localhost -p 15500  -U omm -W omm@123456 -f /data/backup/alldb.sql 
gs_dump[port='15500'][dbname='cndba'][2023-04-13 21:23:21]: The total objects number is 449.
gs_dump[port='15500'][dbname='cndba'][2023-04-13 21:23:21]: [100.00%] 449 objects have been dumped.
gs_dump[port='15500'][dbname='cndba'][2023-04-13 21:23:21]: dump database dbname='cndba' successfully
gs_dump[port='15500'][dbname='cndba'][2023-04-13 21:23:21]: total time: 4006  ms
gs_dump[port='15500'][dbname='postgres'][2023-04-13 21:23:25]: The total objects number is 428.
gs_dump[port='15500'][dbname='postgres'][2023-04-13 21:23:25]: [100.00%] 428 objects have been dumped.
gs_dump[port='15500'][dbname='postgres'][2023-04-13 21:23:25]: dump database dbname='postgres' successfully
gs_dump[port='15500'][dbname='postgres'][2023-04-13 21:23:25]: total time: 4126  ms
gs_dump[port='15500'][dbname='ustc'][2023-04-13 21:23:28]: The total objects number is 427.
gs_dump[port='15500'][dbname='ustc'][2023-04-13 21:23:28]: [100.00%] 427 objects have been dumped.
gs_dump[port='15500'][dbname='ustc'][2023-04-13 21:23:28]: dump database dbname='ustc' successfully
gs_dump[port='15500'][dbname='ustc'][2023-04-13 21:23:28]: total time: 3627  ms
gs_dumpall[port='15500'][2023-04-13 21:23:29]: dumpall operation successful
gs_dumpall[port='15500'][2023-04-13 21:23:29]: total time: 11974  ms
[dave@www.cndba.cn cndba_backup]$

通过操作日志，也可以看出，gs_dumpall也是调用gs_dump命令来备份每个单独的库的。

我们这里来看下恢复，先看过程，我们在总结：

[dave@www.cndba.cn cndba_backup]$ gsql -h localhost -p 15500 -d postgres -U omm -W omm@123456 -r -f /data/backup/alldb.sql 
SET
SET
gsql:/data/backup/alldb.sql:12: ERROR:  role "dave" already exists
ALTER ROLE
gsql:/data/backup/alldb.sql:14: ERROR:  role name "gs_role_account_lock" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:15: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:16: ERROR:  role name "gs_role_copy_files" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:17: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:18: ERROR:  role name "gs_role_directory_create" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:19: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:20: ERROR:  role name "gs_role_directory_drop" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:21: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:22: ERROR:  role name "gs_role_pldebugger" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:23: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:24: ERROR:  role name "gs_role_replication" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:25: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:26: ERROR:  role name "gs_role_signal_backend" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:27: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:28: ERROR:  role name "gs_role_tablespace" is reserved
DETAIL:  Role names starting with "gs_role_" are reserved.
gsql:/data/backup/alldb.sql:29: ERROR:  Permission denied to alter predefined roles.
gsql:/data/backup/alldb.sql:30: ERROR:  role "omm" already exists
gsql:/data/backup/alldb.sql:31: ERROR:  Permission denied to change privilege of the initial account.
ALTER ROLE
gsql:/data/backup/alldb.sql:43: ERROR:  database "cndba" already exists
ALTER DATABASE
REVOKE
REVOKE
GRANT
GRANT
gsql:/data/backup/alldb.sql:49: ERROR:  database "ustc" already exists
Password for user omm: 
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
You are now connected to database "cndba" as user "omm".
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
gsql:/data/backup/alldb.sql:80: ERROR:  relation "cndba" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:110: ERROR:  relation "customer_address_p1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:134: ERROR:  relation "employees_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:157: ERROR:  relation "graderecord" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:175: ERROR:  relation "hash_partition_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:202: ERROR:  relation "metro_ride_record" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:225: ERROR:  relation "sales_table" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:238: ERROR:  relation "t2" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:251: ERROR:  relation "ustc" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/data/backup/alldb.sql:406: ERROR:  relation "ds_customer_address_p1_index1" already exists
gsql:/data/backup/alldb.sql:413: ERROR:  relation "ds_customer_address_p1_index2" already exists
gsql:/data/backup/alldb.sql:420: ERROR:  relation "ds_customer_address_p1_index3" already exists
gsql:/data/backup/alldb.sql:427: ERROR:  relation "ds_customer_address_p1_index4" already exists
REVOKE
REVOKE
GRANT
GRANT
Password for user omm: 
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
You are now connected to database "postgres" as user "omm".
SET
SET
SET
SET
SET
SET
SET
COMMENT
gsql:/data/backup/alldb.sql:469: ERROR:  schema "dave" already exists
ALTER SCHEMA
REVOKE
REVOKE
GRANT
GRANT
Password for user omm: 
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
You are now connected to database "ustc" as user "omm".
SET
SET
SET
SET
SET
SET
SET
REVOKE
REVOKE
GRANT
GRANT
total time: 20919  ms
[dave@www.cndba.cn cndba_backup]$

从执行过程看，实际上，gsql在执行的时候，是先在第一个数据库，也就是我们指定的-d postgres库上执行，然后切换到其他库上执行操作的，我们查看导出的sql文件，就会发现他们的逻辑，先创建角色，在建库：

http://www.cndba.cn/dave/article/116549
http://www.cndba.cn/dave/article/116549 

--
-- Database creation
--

CREATE DATABASE cndba WITH TEMPLATE = template0 OWNER = omm ENCODING = 'UTF8' dbcompatibility = 'A';
ALTER DATABASE postgres SET explain_perf_mode TO 'normal';
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM omm;
GRANT CREATE,CONNECT,TEMPORARY ON DATABASE template1 TO omm;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE ustc WITH TEMPLATE = template0 OWNER = omm dbcompatibility = 'A';

所以gs_dumpall得恢复，建议是到一个新的环境上，或者drop 之前已经存在的库，指定gsql时，指定-d postgres 来进行导入，并且在逐个导每个子库时，导每个子库时都需要输入一次密码。 从操作上看，并不是十分方便，还不如直接使用gs_dump 来导单库更方便一些。

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2239
    原创
  • 3
    翻译
  • 546
    转载
  • 186
    评论
  • 访问：6602684次
  • 积分：4248
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ