磐维数据库数据迁移
一：环境检查
1.源端：
操作系统版本：BigCloud Enterprise Linux For Euler release 21.10 (LTS-SP2)
数据库版本： gsql (PanWeiDB_V2.0-S2.0.2_B01) compiled at 2024-03-29 20:29:01 commit 2b900fc
数据库对象确认：
test_db=# select count(*) from pg_class ;
count
--------
118044
(1 row)
备份目录空间检查
[omm@test01 bak]$ df -h /intdata
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/datavg-lv_data 4.4T 796G 3.6T 18% /intdata
业务数据库，用户，schema确认
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+-------+----------+-----------+-----------+-------------------
panweidb | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
test_db | omm | GBK | zh_CN.gbk | zh_CN.gbk |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
postgres=# \du
List of roles
Role name | Attributes | Member
of
----------------------+----------------------------------------------------------------------------------------------------------------------------------+--------
u3 | Sysadmin | {}
jd | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer sso, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
oracle | Sysadmin | {ud}
pw_read_all_settings | Cannot login, Monitoradmin, Operatoradmin, Policyadmin | {}
pwadmin | Sysadmin, Create role, Create DB, Replication, UseFT | {}
pwaudit | Create role, Administer audit, UseFT | {}
pwsso | Create role, Administer sso | {}
u2 | | {}
u1 | | {}
postgres=# \dn
List of schemas
Name | Owner
-------------------------+---------
audit | pwaudit
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
dbms_alert | omm
dbms_application_info | omm
dbms_assert | omm
dbms_job | omm
dbms_lob | omm
dbms_lock | omm
dbms_pipe | omm
dbms_profiler_proctable | omm
dbms_random | omm
dbms_rowid | omm
dbms_scheduler | omm
dbms_session | omm
dbms_utility | omm
dbms_xplan | omm
oracle | oracle
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
sys | omm
wmsys | omm
xmltype | omm
(30 rows)2.目标端：
操作系统版本：gsql (PanWeiDB_V2.0-S3.1.1_B01) compiled at 2025-04-09 16:45:54 commit d653354
数据库版本 BigCloud Enterprise Linux For Euler release 21.10 (LTS-SP2)
业务数据库，用户，schema没有创建
二：gs_dump在源库导出
1.在源库导出所有的对象不包含数据，-s 表示只导出对象，-F d 表示目录归档格式目录会自动创建，-j 表示加并行
[omm@aeetest01 panweidb]$ nohup gs_dump -p 17700 test_db -U omm -s -F d -f /intdata/panweidb/bak -j 64 &
[1] 4085083
[omm@aeetest01 panweidb]$ nohup: ignoring input and appending output to 'nohup.out'
[omm@aeetest01 panweidb]$
[omm@aeetest01 panweidb]$ ll
total 408K
drwx------ 10 omm dbgrp 163 Aug 9 2024 app
drwx------ 2 omm dbgrp 296K Jun 26 14:15 archive
drwx------ 2 omm dbgrp 30 Jun 26 14:56 bak
drwx------ 26 omm dbgrp 4.0K Apr 28 09:37 data
drwx------ 3 omm dbgrp 30 Nov 5 2024 database
drwx------ 3 omm dbgrp 25 Aug 9 2024 log
-rw------- 1 omm dbgrp 0 Jun 26 14:56 nohup.out
drwxr-xr-x 2 omm dbgrp 147 Aug 15 2024 ptk
drwxr-xr-x 3 omm dbgrp 265 Nov 5 2024 soft
drwx------ 2 omm dbgrp 104 Jun 17 17:40 tmp
drwx------ 5 omm dbgrp 4.0K Aug 9 2024 tool
[omm@aeetest01 panweidb]$
[omm@aeetest01 panweidb]$ cd bak/
[omm@aeetest01 bak]$ ll
total 0
-rw------- 1 omm dbgrp 0 Jun 26 14:56 dir.lock
[omm@aeetest01 bak]$
[omm@aeetest01 bak]$ 2.查看日志
[omm@aeetest01 panweidb]$ tail -100f nohup.out
gs_dump[port='17700'][test_db][2025-06-26 15:00:21]: The total objects number is 24795.
gs_dump[port='17700'][test_db][2025-06-26 15:00:40]: [ 40.33%] 10000 objects have been dumped.
gs_dump[port='17700'][test_db][2025-06-26 15:01:02]: [ 80.66%] 20000 objects have been dumped.
gs_dump[port='17700'][test_db][2025-06-26 15:01:02]: [100.00%] 24795 objects have been dumped.
gs_dump[port='17700'][test_db][2025-06-26 15:01:03]: dump database test_db successfully
gs_dump[port='17700'][test_db][2025-06-26 15:01:03]: total time: 292079 ms
[omm@aeetest01 bak]$ ll
total 34M
-rw------- 1 omm dbgrp 34M Jun 26 15:01 toc.dat
[omm@aeetest01 bak]$ 三 gs_restore在目标库导入
1.传输dump文件
2.创建库，schema
CREATE DATABASE test_db ENCODING = 'GBK' LC_COLLATE = 'zh_CN.gbk' LC_CTYPE = 'zh_CN.gbk';
CREATE USER ad password 'test123456'; 3.gs_restore导入
[omm@test01 ~]$ nohup gs_restore /home/omm/bak/ -d test_db &
[1] 1155277
[omm@test01 ~]$ nohup: ignoring input and appending output to 'nohup.out'
[omm@test01 ~]$ ll
total 24
drwx------ 2 omm dbgrp 37 Jun 26 16:18 bak
drwx------ 3 omm dbgrp 25 Jun 25 10:51 gaussdb_tmp
-rw------- 1 omm dbgrp 21067 Jun 26 16:18 nohup.out
-rw------- 1 omm dbgrp 35537 Jun 26 16:22 nohup.out
[omm@test01 ~]$ tail -f nohup.out
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE oracle IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR RO...
Error from TOC entry 99724; 826 2284661 DEFAULT ACL DEFAULT PRIVILEGES FOR SEQUENCES oracle
could not execute query: ERROR: permission denied for schema ucr_dims1
DETAIL: N/A
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE oracle IN SCHEMA ucr_dims1 REVOKE ALL ON SEQUENCES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES ...
WARNING: errors ignored on restore: 96
restore operation successful
total time: 215881 ms4.校验数据
test_db=# select count(*) from pg_tables where schemaname in ('u1','u2');
count
--------
196737
(1 row)最后修改时间：2025-09-02 15:32:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
