1.需求说明

客户需求,对Mogdb测试库数据从老的单机,迁移到主从集群上来。测试库的名称及用户都会进行修改。

2.数据库迁移

新环境安装事项

老环境是B兼容性Mysql,UTF8字符集,新环境安装也选择兼容模式B,UTF8字符集

[omm@mogdb ~]$ gsql
gsql ((MogDB 5.0.5 build b77f1a82) 
MogDB=# \l+
                                                                   List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility |  Size   | Tablespace |            
    Description                 
-----------+-------+----------+---------+-------+-------------------+---------------+---------+------------+------------
--------------------------------
 mysql     | mysql | UTF8     | C       | C     |                   | B             | 20 GB   | pg_default | 
 postgres  | omm   | UTF8     | C       | C     |                   | A             | 9983 MB | pg_default | default adm
inistrative connection database
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| A             | 25 MB   | pg_default | default tem
plate for new databases
           |       |          |         |       | omm=CTc/omm       |               |         |            | 
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| A             | 25 MB   | pg_default | unmodifiabl
e empty database
           |       |          |         |       | omm=CTc/omm       |               |         |            | 
(4 rows)

源端导出

gs_dump --file=/data/backup_dir/dump_xxx_20240702_backup_01.dump \
--clean \
--format=c \
--username=xxx \
--password=xxxx \
 mysql
 
gs_dump[port='26000'][mysql][2024-07-02 11:27:09]: The total objects number is 5835.
gs_dump[port='26000'][mysql][2024-07-02 11:27:12]: [100.00%] 5835 objects have been dumped.
gs_dump: [port='26000'] [mysql] [archiver] [2024-07-02 11:27:12] WARNING: archive items not in correct section order
gs_dump[port='26000'][mysql][2024-07-02 11:30:27]: dump database xxx successfully
gs_dump[port='26000'][mysql][2024-07-02 11:30:27]: total time: 220644  ms

目标端导入

$ gs_restore --dbname=imxxx \
--username=imxxx \
--password=Imxxxxx \
--no-owner /dump_xxxxx_20240702_backup_03.dump


14100 SQL statements read in !
14200 SQL statements read in !
Error while PROCESSING TOC:
Error from TOC entry 26015; 3546 19191 SYNONYM dual omm
could not execute query: ERROR:  schema "sys" does not exist
    Command was: CREATE OR REPLACE SYNONYM "sys"."dual" FOR "pg_catalog"."sys_dummy";

Finish reading 14202 SQL statements!
end restore operation ...
WARNING: errors ignored on restore: 1
restore operation successful
total time: 5382689  ms

可以发现,提示导入成功，但是有告警信息，提示我们同义词创建失败。

check 表数量对比

SELECT table_catalog,table_schema,count(*) from 
 information_schema.tables
 group by table_catalog,table_schema
  order by 3;
  table_catalog |    table_schema    | count 
---------------+--------------------+-------

check indexes

select schemaname,tablename,count(*) 
 from pg_indexes
  group by schemaname,tablename
   order by 1,2;

select schemaname,count(*) 
 from pg_indexes
  group by schemaname
   order by 2;

check views

select schemaname,viewowner,count(*) 
from pg_views group by schemaname,viewowner
 order by 1,2;
     schemaname     | viewowner | count 
--------------------+-----------+-------

check sequence

https://support.enmotech.com/article/search/1457

\set get_table_name_by_seq 'select ts.nspname as object_schema,tbl.relname as table_name, col.attname as column_name,s.relname   as sequence_name from pg_class s  join pg_namespace sn on sn.oid = s.relnamespace  join pg_depend d on d.refobjid = s.oid and d.refclassid=''pg_class''::regclass  join pg_attrdef ad on ad.oid = d.objid and d.classid = ''pg_attrdef''::regclass  join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum  join pg_class tbl on tbl.oid = ad.adrelid  join pg_namespace ts on ts.oid = tbl.relnamespace where s.relkind = ''S'' and d.deptype in (''a'', ''n'');'
mysql=# :get_table_name_by_seq
   object_schema    |  table_name  | column_name |      sequence_name       
--------------------+--------------+-------------+--------------------------

check 存储过程

--mysql=# select proname,prosrc from pg_proc ;
mysql=# select proname from pg_proc ;
(5472 rows)

check 同义词

mysql=# select 1 from dual;
 ?column? 
----------
        1
mysql=# select 1 from "pg_catalog"."sys_dummy";
 ?column? 
----------
        1
mysql=# 
mysql=# select * from PG_SYNONYM limit 10;
 synname | synnamespace | synowner | synobjschema | synobjname 
---------+--------------+----------+--------------+------------
 dual    |        19190 |       10 | pg_catalog   | sys_dummy
 
--pg_restore err sql
 CREATE OR REPLACE SYNONYM "sys"."dual" FOR "pg_catalog"."sys_dummy";
--change exec
 CREATE OR REPLACE SYNONYM "imss"."dual" FOR "pg_catalog"."sys_dummy";
imss=#  CREATE OR REPLACE SYNONYM "imss"."dual" FOR "pg_catalog"."sys_dummy";
CREATE SYNONYM
imss=# 
imss=#  select * from PG_SYNONYM limit 10;
 synname | synnamespace | synowner | synobjschema | synobjname 
---------+--------------+----------+--------------+------------
 dual    |        32699 |       10 | pg_catalog   | sys_dummy
(1 row)

check triggers

select * from information_schema.triggers;

迁移遇到的问题

导入报错-保留字

gs_dump: [port='26000'] [mysql] [archiver (db)] [2024-07-02 11:09:08] query failed: ERROR:  syntax error at or near "index"
LINE 1: ... create_user_id, length, package_id, handle_time, index, sec...

解决办法：
1.业务修改index字段名称,修改后，再次导入再次报错，客户使用非常多的index作为字段名称;
mysql=> ALTER TABLE xx.xx RENAME COLUMN "index" TO index_id;

2.导入时添加参数--quote-all-identifiers 

导入报错-Schema不存在

Error from TOC entry 542; 1259 19619 TABLE xxx mysql
could not execute query: ERROR:  role "mysql" does not exist
    Command was: ALTER TABLE xx.xxOWNER TO mysql;

由于source mysql,target db 和schema不是mysql,但是dump文件里面是导出source db里面记录owner to mysql; !!! 所以报错
1.新环境与源端保持一致,database,schema不合理的解决方案；
2.导入时参数指定--no-owner

业务反馈SQL变慢

迁移后，建议对整个schema收集统计信息
ANALYZE；