GBase 8c 学习笔记 012 —— GBase 8c 备份恢复
逻辑备份
数据准备
--创建用户（会自动创建schema）
postgres=# create user test_use1 identified by 'Gbase_123';
CREATE ROLE
--授DBA权限
postgres=# grant all privileges to test_use1;
ALTER ROLE
--切换模式schema
postgres=# set search_path=test_use1;
SET
--建表及插入数据
create table ora_alter_table
( pid number,
did number ,
lsn varchar2(24),
name varchar2(200),
pno number,
cnt number,
amount number,
sale_time date default sysdate
);
postgres=# create table ora_alter_table
postgres-# ( pid number,
postgres(# did number ,
postgres(# lsn varchar2(24),
postgres(# name varchar2(200),
postgres(# pno number,
postgres(# cnt number,
postgres(# amount number,
postgres(# sale_time date default sysdate
postgres(# );
CREATE TABLE
--加载测试数据
begin
for i in 1..10000 loop
insert into ora_alter_table(pid,did,lsn,name,pno,cnt,amount,sale_time) values(i,i+100,i+9,'name' ||i,i+1000,i+10,i+10000,sysdate);
end loop;
commit;
end;
/
postgres=# begin
postgres$# for i in 1..10000 loop
postgres$# insert into ora_alter_table(pid,did,lsn,name,pno,cnt,amount,sale_time) values(i,i+100,i+9,'name' ||i,i+1000,i+10,i+10000,sysdate);
postgres$# end loop;
postgres$# commit;
postgres$# end;
postgres$# /
ANONYMOUS BLOCK EXECUTE
gs_dump --导出postgres数据库全量信息
格式：纯文本格式、自定义归档格式、目录归档格式、tar归档格式
[gbase@gbase8c_5_151 ~]$ gs_dump -U gbase -W Gbase_123 -f backup/MPPDB_backup.sql -p 15432 postgres -F p
gs_dump[port='15432'][postgres][2023-04-01 21:09:53]: The total objects number is 416.
gs_dump[port='15432'][postgres][2023-04-01 21:09:53]: [100.00%] 416 objects have been dumped.
gs_dump: [port='15432'] [postgres] [archiver] [2023-04-01 21:09:53] WARNING: archive items not in correct section order
gs_dump[port='15432'][postgres][2023-04-01 21:09:53]: dump database postgres successfully
gs_dump[port='15432'][postgres][2023-04-01 21:09:53]: total time: 2106 ms
gs_dump --导出指定schema（模式）
格式：纯文本格式、自定义归档格式、目录归档格式、tar归档格式
gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -n test_use1 -F p
-- -U：指定数据库用户名
-- -W：指定用户的密码
-- -f：指定的备份文件名
-- -p：指定数据库的端口号
-- postges：数据库名称
-- -n：指定的模式的名称（多个模式可以指定多个n）
-- -F：指定备份文件的格式，后面的p代表纯文本格式
-- -N：排除指定模式（指定下的模式不会导出）
[gbase@gbase8c_5_151 ~]$ gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -n test_use1 -F p
gs_dump[port='5432'][postgres][2023-04-01 20:12:40]: The total objects number is 403.
gs_dump[port='5432'][postgres][2023-04-01 20:12:40]: [100.00%] 403 objects have been dumped.
gs_dump[port='5432'][postgres][2023-04-01 20:12:40]: dump database postgres successfully
gs_dump[port='5432'][postgres][2023-04-01 20:12:40]: total time: 2235 ms
[gbase@gbase8c_5_151 ~]$ vim backup/test_use1_backup.sql
gs_dump --导出指定的表
gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -t test_use1.ora_alter_table -F p
-- -t：指定表名，多张表可以指定多个-t；
-- 注意：
-- -T：代表不导出的表名，多张表可以指定多个-T；
-- --include-table-file=FILENAME：代表需要备份的表文件
-- --exclude-table-file=FileName：代表不需要备份的表文件
-- FILENAME文件中格式：
-- schema1.table
-- schema2.table2
-- ……
[gbase@gbase8c_5_151 ~]$ gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -t test_use1.ora_alter_table -F p
gs_dump[port='5432'][postgres][2023-04-01 20:16:04]: The total objects number is 402.
gs_dump[port='5432'][postgres][2023-04-01 20:16:04]: [100.00%] 402 objects have been dumped.
gs_dump[port='5432'][postgres][2023-04-01 20:16:04]: dump database postgres successfully
gs_dump[port='5432'][postgres][2023-04-01 20:16:04]: total time: 1978 ms
gs_dump --只导出表定义，不导出数据
gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -t test_use1.ora_alter_table -s -F p
-- -t：代表指定表名，多张表可以指定多个-t
-- -s：代表只导出表定义，不导出数据
[gbase@gbase8c_5_151 ~]$ gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.sql -p 5432 postgres -t test_use1.ora_alter_table -s -F p
gs_dump[port='5432'][postgres][2023-04-01 20:17:45]: The total objects number is 401.
gs_dump[port='5432'][postgres][2023-04-01 20:17:45]: [100.00%] 401 objects have been dumped.
gs_dump[port='5432'][postgres][2023-04-01 20:17:45]: dump database postgres successfully
gs_dump[port='5432'][postgres][2023-04-01 20:17:45]: total time: 1961 ms
恢复
gsql -f 指定备份文件执行（备份文件的格式仅支持p纯文本格式）
[gbase@gbase8c_5_151 ~]$ gsql -r postgres
gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# set search_path=test_use1;
SET
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
-----------+-----------------+-------+-----------+--------+----------------------------------+-------------
test_use1 | ora_alter_table | table | test_use1 | 888 kB | {orientation=row,compression=no} |
(1 row)
postgres=# drop table ora_alter_table;
DROP TABLE
postgres=# \d+
No relations found.
[gbase@gbase8c_5_151 ~]$ gsql -U gbase -W Gbase_123 -f backup/MPPDB_backup.sql -p 5432 postgres
SET
SET
SET
SET
SET
SET
COMMENT
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
SET
CREATE SYNONYM
ALTER SYNONYM
…………………………………………………………………………………………………………………………………………………………………………………………
[gbase@gbase8c_5_151 ~]$ gsql -r postgres
gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+------+-------+---------+---------+-------------
public | dual | view | gbase | 0 bytes | |
(1 row)
postgres=# \q
[gbase@gbase8c_5_151 ~]$ gsql -r postgres
gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# set search_path=test_use1;
SET
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
-----------+-----------------+-------+-----------+--------+----------------------------------+-------------
test_use1 | ora_alter_table | table | test_use1 | 448 kB | {orientation=row,compression=no} |
(1 row)
gs_restore（以上备份文件均支持）
[gbase@gbase8c_5_151 ~]$ gs_dump -U gbase -W Gbase_123 -f backup/test_use1_backup.dmp -p 5432 postgres -t test_use1.ora_alter_table -F c
gs_dump[port='5432'][postgres][2023-04-01 21:16:40]: The total objects number is 402.
gs_dump[port='5432'][postgres][2023-04-01 21:16:40]: [100.00%] 402 objects have been dumped.
gs_dump[port='5432'][postgres][2023-04-01 21:16:40]: dump database postgres successfully
gs_dump[port='5432'][postgres][2023-04-01 21:16:40]: total time: 2019 ms
[gbase@gbase8c_5_151 ~]$ gsql -r postgres
gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# set search_path=test_use1;
SET
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
-----------+-----------------+-------+-----------+--------+----------------------------------+-------------
test_use1 | ora_alter_table | table | test_use1 | 888 kB | {orientation=row,compression=no} |
(1 row)
postgres=# drop table ora_alter_table;
DROP TABLE
postgres=# \d+
No relations found.
[gbase@gbase8c_5_151 ~]$ gs_restore backup/test_use1_backup.dmp -p 5432 -d postgres
start restore operation ...
table ora_alter_table complete data imported !
Finish reading 4 SQL statements!
end restore operation ...
restore operation successful
total time: 93 ms
[gbase@gbase8c_5_151 ~]$ gsql -r postgres
gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# set search_path=test_use1;
SET
postgres=#
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
-----------+-----------------+-------+-----------+--------+----------------------------------+-------------
test_use1 | ora_alter_table | table | test_use1 | 888 kB | {orientation=row,compression=no} |
(1 row)
postgres=# select count(*) from ora_alter_table;
count
-------
10000
(1 row)
数据导入/导出
数据导出
GBase 8c 通过copy to 把一个表的数据拷贝到一个文件
--将表test_use1.ora_alter_table的数据导出scv格式
postgres=# COPY test_use1.ora_alter_table TO '/home/gbase/backup/ora_alter_table.csv';
COPY 4948
postgres=# \q
[gbase@gbase8c_5_151 ~]$ cat /home/gbase/backup/ora_alter_table.csv
1 101 10 name1 1001 11 10001 2023-04-01 20:03:59
2 102 11 name2 1002 12 10002 2023-04-01 20:03:59
4 104 13 name4 1004 14 10004 2023-04-01 20:03:59
5 105 14 name5 1005 15 10005 2023-04-01 20:03:59
6 106 15 name6 1006 16 10006 2023-04-01 20:03:59
9 109 18 name9 1009 19 10009 2023-04-01 20:03:59
10 110 19 name10 1010 20 10010 2023-04-01 20:03:59
11 111 20 name11 1011 21 10011 2023-04-01 20:03:59
12 112 21 name12 1012 22 10012 2023-04-01 20:03:59
13 113 22 name13 1013 23 10013 2023-04-01 20:03:59
14 114 23 name14 1014 24 10014 2023-04-01 20:03:59
16 116 25 name16 1016 26 10016 2023-04-01 20:03:59
18 118 27 name18 1018 28 10018 2023-04-01 20:03:59
24 124 33 name24 1024 34 10024 2023-04-01 20:03:59
28 128 37 name28 1028 38 10028 2023-04-01 20:03:59
31 131 40 name31 1031 41 10031 2023-04-01 20:03:59
32 132 41 name32 1032 42 10032 2023-04-01 20:03:59
34 134 43 name34 1034 44 10034 2023-04-01 20:03:59
36 136 45 name36 1036 46 10036 2023-04-01 20:03:59
41 141 50 name41 1041 51 10041 2023-04-01 20:03:59
…………………………………………………………………………………………
数据导入
目前有三种方式进行数据的导出
- insert 通过insert语句插入一行或多行数据，及从指定表插入数据
postgres=# CREATE TABLE customer_t2
postgres-# (
postgres(# c_customer_sk integer,
postgres(# c_customer_id char(5),
postgres(# c_first_name char(6),
postgres(# c_last_name char(8)
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into customer_t2 values
postgres-# (68, 'a1', 'zhou','wang'),
postgres-# (43, 'b1', 'wu', 'zhao'),
postgres-# (95, 'c1', 'zheng', 'qian');
INSERT 0 3
postgres=# select * from customer_t2;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
68 | a1 | zhou | wang
43 | b1 | wu | zhao
95 | c1 | zheng | qian
(3 rows)
- 使用insert批量插入
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+-------------+-------+-------+---------+----------------------------------+-------------
public | customer_t2 | table | gbase | 16 kB | {orientation=row,compression=no} |
public | dual | view | gbase | 0 bytes | |
(2 rows)
postgres=# CREATE TABLE customer_t3
postgres-# (
postgres(# c_customer_sk integer,
postgres(# c_customer_id char(5),
postgres(# c_first_name char(6),
postgres(# c_last_name char(8)
postgres(# );
CREATE TABLE
postgres=# INSERT INTO customer_t3 SELECT * FROM customer_t2;
INSERT 0 3
postgres=#
postgres=# CREATE TABLE customer_t1 AS SELECT * FROM customer_t2;
INSERT 0 3
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+-------------+-------+-------+---------+----------------------------------+-------------
public | customer_t1 | table | gbase | 16 kB | {orientation=row,compression=no} |
public | customer_t2 | table | gbase | 16 kB | {orientation=row,compression=no} |
public | customer_t3 | table | gbase | 16 kB | {orientation=row,compression=no} |
public | dual | view | gbase | 0 bytes | |
(4 rows)
- 使用copy 表名 [(列名[,…])] from {‘文件名’}
postgres=# select count(*) from table1;
count
-------
0
(1 row)
postgres=# COPY table1 FROM '/home/gbase/backup/ora_alter_table.csv';
COPY 4948
postgres=#
postgres=# select count(*) from table1;
count
-------
4948
(1 row)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
