通过KDTS实现Oracle11.2.0.4全量数据迁移到PG14数据库

一.迁移需求

xxx项目本来是Oracle库之间互相进行数据迁移，源库的备份文件都导出完了，结果领导一拍板让把Oracle库迁移到PG库，源端和目标端服务器还不联网，幸好数据量才1.2G。Ora2PG工具看着较复杂时间紧断然放弃，Navicat数据传输过程中报错退出放弃，MTK工具报错较多放弃，在群里大佬的介绍下，使用了人大金仓的KDTS工具，先前也用过该工具只注意到了其他数据库往人大金仓数据库迁移，如果使用该工具请及时联系厂商获取新版本，新版本已支持源端和目标端非人大金仓数据库之间迁移，详细内容请移步社区官网。

采用思路：

1. 本地分别部署Oracle11.2.0.4和PG14.2版本的数据库，字符集都是UTF8，具体步骤省略

1. 将导出的Oracle dmp文件还原至本地Oracle11.2.0.4，具体步骤省略

1. 使用KDTS工具实现迁移

二.数据库信息

三.迁移前准备

3.1.查数据库版本

3.1.1.源库Oracle

sqlplus -V
或
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';

3.1.2.目标库PG

--查看数据库版本
select version(); --PostgreSQL 14.2

或
--查进程
-bash-4.2$ ps -ef | grep postmaster
postgres 1504 1 0 2023 ? 00:09:48 /usr/pgsql-14/bin/postmaster -D /opt/postgreSQL/pgsqlData
postgres 15048 14988 0 13:53 pts/2 00:00:00 grep --color=auto postmaster

--查版本
-bash-4.2$ psql -V
psql (PostgreSQL) 14.2

3.2.查字符集

3.2.1.源库Oracle

-- 查看数据库字符集
set linesize 999
col value for a60
select * from nls_database_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8

select userenv('language') from dual;

3.2.2.目标库PG

topicfeshk=# \l+ topicfeshk1
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------+------------+----------+-------------+-------------+---------------------------+---------+------------+-------------
topicfeshk1 | topicfeshk | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/topicfeshk +| 8561 kB | pg_default |
| | | | | topicfeshk=CTc/topicfeshk | | |
(1 row)

3.3.查源库数据量Oracle

--查看总大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments
where owner in ('TOPICFESHK') ;

ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
-----------------------------------------
2.02G

3.4.统计源库业务数据

--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('ENTSERVICE')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------ ----------
ENTSERVICE 227

--每个业务用户下的各个对象类别的数量校验
SQL> set pagesize 999
SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('ENTSERVICE') group by OBJECT_TYPE,owner,status order by 1,3,2;

OWNER OBJECT_TYPE COUNT(OBJECT_NAME)
------------------------------ ------------------- ------------------
ENTSERVICE SEQUENCE 6
ENTSERVICE LOB 7
ENTSERVICE INDEX 91
ENTSERVICE TABLE 123

--查询回收站lob对象数量(可选),lob数量不一致排查
select owner,type,count(*) from dba_recyclebin group by owner,type order by owner;

--数据量统计
----新增count_rows 函数
CREATE OR REPLACE FUNCTION count_rows (
table_name IN varchar2,
owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
num_rows number;
stmt varchar2(2000);
BEGIN
IF owner IS NULL THEN
stmt := 'select count(*) from "' || table_name || '"';
ELSE
stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
END IF;
EXECUTE IMMEDIATE stmt INTO num_rows;
RETURN num_rows;
END;
/
---调用count_rows 函数，统计各种表的记录数
select table_name, count_rows(table_name) nrows from user_tables where tablespace_name='ENTSERVICE'
order by nrows desc;

3.5.目录库创建用户和数据库

--创建新用户
create user topicfeshk with encrypted password 'topicfeshk';
alter user topicfeshk with superuser;
--创建数据库
create database topicfeshk1 owner topicfeshk;
grant all privileges on database topicfeshk1 to topicfeshk;
grant all privileges on all tables in schema public to topicfeshk;

四.迁移过程

4.1.运行迁移web程序

注意：联系厂商获取新版迁移工具，安装包自带的迁移工具版本过旧，迁移过程中会存在问题。

该迁移场景是在windows电脑上启动人大金仓自带的迁移工具web端

位置：G:\KingbaseV8R6_迁移工具\kdts-plus-bs-20240717\kdts-plus-bs-20240717\bin

双击启动startup.bat

启动过程如下：

web登录地址http://localhost:54523/ 账号密码默认，直接点登录即可

4.2.配置数据源

4.2.1.源数据库数据源

在列表中查看已创建数据源，如下：

4.2.2.目标库数据源

配置目标数据库的数据库信息，虽然web页面显示PG版本最高12版本，其实PG14也是支持的。

在列表中查看已创建数据源，如下：

4.3.创建迁移任务

4.3.1.选择数据源

4.3.2.选择模式

注意目标模式的选择，我这里是public模式，如果是自定义的模式，请注意更改

全选所有对象

4.3.3.选择迁移对象

4.3.4.配置迁移参数

这个地方务必注意

4.3.5.查看迁移结果

4.4.处理迁移报错

失败的脚本位置：安装目录下的\results\2024-09-13_13-06-58\TOPICFESHK\FailedScript

点开详情，查看迁移结果，对报错的进行处理

4.4.1.表结构报错处理

4.4.1.1.查看报错日志

单击失败数查看详情

TOPICFESHK.SIS_COLLECT fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：348
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.PUB_BENEFIT_RECORD fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：206
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.DATA_FLOW_MONITOR_HIEDJ_HIST fail:
org.postgresql.util.PSQLException: ERROR: function sys_guid() does not exist
建议：No function matches the given name and argument types. You might need to add explicit type casts.
位置：104
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.PUB_BUSIDATAREPAIR fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：163
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.INC_KETTLE_ETL_SJDZ fail:
org.postgresql.util.PSQLException: ERROR: function sys_guid() does not exist
建议：No function matches the given name and argument types. You might need to add explicit type casts.
位置：91
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.PUB_ENTUSER_LOGOUT fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：1275
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.REG_BUSMAIINF_FILE fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：511
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.INC_KETTLE_ETL fail:
org.postgresql.util.PSQLException: ERROR: function sys_guid() does not exist
建议：No function matches the given name and argument types. You might need to add explicit type casts.
位置：85
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.PUB_ENTUSER fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：1275
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.RISK_EVENT_LOG fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：318
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.REG_NAMEPREAUDITINFO fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：406
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
TOPICFESHK.SIS_INTERFACEINFO fail:
org.postgresql.util.PSQLException: ERROR: cannot use column reference in DEFAULT expression
位置：442
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.ah.bf(TableStructureWriteJob.java:197)
at com.kingbase.kdts.job.migration.ah.call(TableStructureWriteJob.java:30)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)

4.4.1.2.sysdate报错及处理办法

PG中无sysdate函数，更改成current_timestamp(6)函数执行成功。

修改方法一：命令窗口手动更改执行

以表"public"."SIS_COLLECT"为例。该方法不会使迁移结果界面失败数发生变化。

--迁移后语句
CREATE TABLE "public"."SIS_COLLECT" (
"ID" varchar(32) NOT NULL,
"USER_ID" DECIMAL NOT NULL,
"COLLECT_TYPE" varchar(30) NOT NULL,
"COLLECT_TABLE_ID" varchar(50) NOT NULL,
"COLLECT_URL" varchar(100) NOT NULL,
"COLLECT_CONTENT" varchar(500) NOT NULL,
"CREATE_TIME" TIMESTAMP(6) DEFAULT sysdate,
"DEL_FLAG" varchar(1) DEFAULT '0'
)

粘贴后在数据库连接工具中执行提示如下报错：
ERROR: cannot use column reference in DEFAULT expression
LINE 8: "CREATE_TIME" TIMESTAMP(6) DEFAULT sysdate,

--更改后执行
CREATE TABLE "public"."SIS_COLLECT" (
"ID" varchar(32) NOT NULL,
"USER_ID" DECIMAL NOT NULL,
"COLLECT_TYPE" varchar(30) NOT NULL,
"COLLECT_TABLE_ID" varchar(50) NOT NULL,
"COLLECT_URL" varchar(100) NOT NULL,
"COLLECT_CONTENT" varchar(500) NOT NULL,
"CREATE_TIME" TIMESTAMP(6) DEFAULT current_timestamp(6),
"DEL_FLAG" varchar(1) DEFAULT '0'
)

修改方法2：KDTS创建编辑保存执行

以表"public"."PUB_BUSIDATAREPAIR"为例，该方法会使迁移结果界面的失败数发生变化

更改后如下

状态变成了已修改

再次单击“查看详情”，单机“执行”

最后查看迁移结果，失败数少了一个。

剩余一样的报错按以上办法处理即可，建议使用修复方法2。

4.4.1.3.sys_guid()函数报错及处理办法

--创建sys_guid ()函数
CREATE FUNCTION sys_guid ()
RETURNS VARCHAR
LANGUAGE plpgsql
AS
$$
DECLARE
uuid VARCHAR;
BEGIN
SELECT REPLACE
( CAST ( uuid_generate_v4 () AS VARCHAR ), '-', '' ) INTO uuid;
RETURN uuid;
END;
$$;

修改方法一：命令窗口手动更改执行

以表"public"."DATA_FLOW_MONITOR_HIEDJ_HIST"为例。该方法不会使迁移结果界面失败数发生变化。

--迁移后语句
CREATE TABLE "public"."DATA_FLOW_MONITOR_HIEDJ_HIST" (
"ID_HIST" varchar(32) NOT NULL DEFAULT sys_guid(),
"ID" varchar(4),
"SOURCE_SYSTEM" CHAR(4),
"TARGET_SYSTEM" varchar(5),
"PUSH_TABLE_NUM" DECIMAL,
"PUSH_NUM" DECIMAL,
"FREQUENCY" DECIMAL,
"RECEIVE_TABLE_NUM" DECIMAL,
"RECEIVE_NUM" DECIMAL,
"RECEIVE_TIME" TIMESTAMP,
"PUSH_TIME" TIMESTAMP
)

修改方法2：KDTS创建编辑保存执行

以表"public"."DATA_FLOW_MONITOR_HIEDJ_HIST"为例，该方法会使迁移结果界面的失败数发生变化

查看失败数

点击失败数的数字，在弹出的界面中单击”查看详情“

直接单击”执行“

相关的报错按以上方式处理即可，建议选择修改方法2 执行。

4.4.2.表数据忽略处理

查看错误日志

本文档采用逐个在navicat工具中导出每个表数据为sql脚本，插入到目标库

注意：记得将sql中的双引号都去掉，不然执行语句报错。

4.4.3.表数据失败处理

本文档采用逐个在navicat工具中导出每个表数据为sql脚本，插入到目标库

注意：记得将sql中的双引号都去掉，不然执行语句报错。

--源库Oracle生成DDL语句
CREATE TABLE "TOPICFESHK"."SYS_TEMP_FBT" (
"SCHEMA" VARCHAR2(32 BYTE),
"OBJECT_NAME" VARCHAR2(32 BYTE),
"OBJECT#" NUMBER,
"RID" UROWID(4000),
"ACTION" CHAR(1 BYTE)
)
NOLOGGING
ROW STORE COMPRESS BASIC
PCTFREE 10
INITRANS 1
STORAGE (
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;

--PG库中创建SYS_TEMP_FBT 因为字段object#中包含#所以采用双引号括起来
CREATE TABLE sys_temp_fbt (
schema VARCHAR,
object_name VARCHAR,
"object#" NUMERIC,
rid VARCHAR(4000),
action CHAR
);

扩展：

如果表数据较多，采用上传到服务器上进行sql文件处理然后再数据库中执行

--处理sql文件
vim工具将双引号都替换为空

--数据库中执行
psql -Utopicfeshk -dtopicfeshk1 -f /home/postgres/CODE_SELF_HELP_IDCARD.sql

4.4.4.主键约束忽略处理

查看错误日志

在以下目录中找到文件，复制粘贴到sql界面执行

4.4.5.索引忽略处理

查看错误日志

如果数量少，复制粘贴到sql界面执行，如果数量多，可下载excel表复制粘贴到sql界面手动执行。

注意：勾对勾，导出的excel只包含本页，可选择每页显示多一些再导出.

这个地方有bug，导出后excel表中显示为空。可在以下目录中找到，复制粘贴到sql界面手动执行。

G:\KingbaseV8R6_迁移工具\kdts-plus-bs-20240717\kdts-plus-bs-20240717\results\2024-09-13_14-37-24\TOPICFESHK\IgnoredScript

4.4.6.唯一约束忽略处理

查看错误日志

点开复制粘贴到库里执行即可

或者在以下目录中找到复制粘贴到sql界面执行。

4.4.7.函数报错处理

4.4.7.1.查看报错日志

TOPICFESHK.FUN_GET_JSSC fail:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "v_real_startTime"
位置：124
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at com.kingbase.kdts.dboperator.b.r.executeSql(DbOperatorPostgreSql.java:88)
at com.kingbase.kdts.job.migration.g.bf(FunctionWriteJob.java:82)
at com.kingbase.kdts.job.migration.g.call(FunctionWriteJob.java:26)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)

4.4.7.2.查看迁移后语句

复制粘贴到本地进行修改

CREATE OR REPLACE FUNCTION "public"."fun_get_jssc"
(i_startTime in date,i_endTime in date)
return number
is
v_real_startTime date;--开始时间 变量
v_real_endTime date;--结束时间 变量
--v_hours number(18,0);--计算结果
v_zssb number(18,2);
v_zsxb number(18,2);
v_xwsb number(18,2);
v_xwxb number(18,2);
v_gzsc number(18,2);
v_number number(18,4);
begin
--上下班时间赋值
v_zssb := 8;--早上上班时间，比如8:30上班，就填写8.5
v_zsxb := 12;--早上下班时间
v_xwsb := 14.5;--下午上班时间
v_xwxb := 17.5;--下午下班时间

v_gzsc := (v_xwxb-v_zssb)-(v_xwsb-v_zsxb);--一天工作时长

--开始结束时间赋值
v_real_startTime := i_startTime;
v_real_endTime := i_endTime;
--开始时间及结束时间转换
if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间，将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
end if;

-- 如果小于当天9点，将其置为当天9点(因为你是9点上班)
if v_real_startTime<trunc(v_real_startTime,'dd')+v_zssb/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+v_zssb/24;
-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)
elsif v_real_startTime>trunc(v_real_startTime,'dd')+v_zsxb/24 and v_real_startTime<trunc(v_real_startTime,'dd')+v_xwsb/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+v_xwsb/24;
-- 如果大于当天18点，将其置为当天18天(因为你是18点下班)
elsif v_real_startTime>trunc(v_real_startTime,'dd')+v_xwxb/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+v_xwxb/24;
end if;

-- 如果小于当天9点，将其置为当天9点(因为你是9点上班)
if v_real_endTime<trunc(v_real_endTime,'dd')+v_zssb/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+v_zssb/24;
-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)
elsif v_real_endTime>trunc(v_real_endTime,'dd')+v_zsxb/24 and v_real_endTime<trunc(v_real_endTime,'dd')+v_xwsb/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+v_xwsb/24;
-- 如果大于当天18点，将其置为当天18天(因为你是18点下班)
elsif v_real_endTime>trunc(v_real_endTime,'dd')+v_xwxb/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+v_xwxb/24;
end if;


select
(case

when to_char(v_real_startTime,'YYYYMMDD')=to_char(v_real_endTime,'YYYYMMDD')
then
(case
when v_real_startTime<=trunc(v_real_startTime,'dd')+v_zsxb/24 and v_real_endTime>=trunc(v_real_endTime,'dd')+v_xwsb/24
then round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)
else
round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)
end)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='0' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='0'
then (
(case
when v_real_startTime<=trunc(v_real_startTime,'dd')+v_zsxb/24 and v_real_endTime>=trunc(v_real_endTime,'dd')+v_xwsb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)+round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)
when v_real_startTime>=trunc(v_real_startTime,'dd')+v_xwsb/24 and v_real_endTime>=trunc(v_real_endTime,'dd')+v_xwsb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)+round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)
when v_real_startTime<=trunc(v_real_startTime,'dd')+v_zsxb/24 and v_real_endTime<=trunc(v_real_endTime,'dd')+v_zsxb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)+round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)
when v_real_startTime>=trunc(v_real_startTime,'dd')+v_xwsb/24 and v_real_endTime<=trunc(v_real_endTime,'dd')+v_zsxb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)+round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)
end)+
(select nvl(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+1,'yyyy-MM-dd') and to_char(v_real_endTime-1, 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='1' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='0'
then (
(case
when v_real_endTime>=trunc(v_real_endTime,'dd')+v_xwsb/24
then round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)
when v_real_endTime<=trunc(v_real_endTime,'dd')+v_zsxb/24
then round((to_date(to_char(v_real_endTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(trunc(v_real_endTime,'dd')+v_zssb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)
end)+
(select nvl(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+1,'yyyy-MM-dd') and to_char(v_real_endTime-1, 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='0' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='1'
then (
(case
when v_real_startTime<=trunc(v_real_startTime,'dd')+v_zsxb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24-(v_xwsb-v_zsxb),2)
when v_real_startTime>=trunc(v_real_startTime,'dd')+v_xwsb/24
then round((to_date(to_char(trunc(v_real_startTime,'dd')+v_xwxb/24,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')- to_date(to_char(v_real_startTime,'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss'))*24,2)
end)+
(select nvl(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+1,'yyyy-MM-dd') and to_char(v_real_endTime-1, 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='1' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='1' and (select nvl(count(1),0) from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime,'yyyy-MM-dd') and to_char(v_real_endTime, 'yyyy-MM-dd'))>=0
then (

(select nvl(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+1,'yyyy-MM-dd') and to_char(v_real_endTime-1, 'yyyy-MM-dd'))*v_gzsc
)

end) into v_number from dual;

return v_number;
end;

修改如下，执行成功

/**
上下班时间有如下两种情况：
1、如果不需要过滤工作日内非工作时间，按照如下图进行配置：
v_zssb := 0;--早上上班时间，比如8:30上班，就填写8.5
v_zsxb := 12;--早上下班时间
v_xwsb := 12;--下午上班时间
v_xwxb := 24;--下午下班时间
2、如果需要过滤工作日内非工作时间，根据现场的实际上下班时间进行配置下图红框的内容：
v_zssb := 8.5;--早上上班时间，比如8:30上班，就填写8.5
v_zsxb := 12;--早上下班时间
v_xwsb := 14;--下午上班时间
v_xwxb := 17.5;--下午下班时间
**/
CREATE OR REPLACE FUNCTION FUN_GET_JSSC
(in i_startTime timestamp,in i_endTime timestamp)
returns numeric as $$
declare v_real_startTime timestamp;--开始时间 变量
declare v_real_endTime timestamp;--结束时间 变量
--v_hours numeric(18,0);--计算结果
declare v_zssb numeric(18,2);
declare v_zsxb numeric(18,2);
declare v_xwsb numeric(18,2);
declare v_xwxb numeric(18,2);
declare v_gzsc numeric(18,2);
declare v_number numeric(18,4);
begin
--上下班时间赋值
v_zssb := 8.5;--早上上班时间，比如8:30上班，就填写8.5
v_zsxb := 12;--早上下班时间
v_xwsb := 14;--下午上班时间
v_xwxb := 18;--下午下班时间

v_gzsc := (v_xwxb-v_zssb)-(v_xwsb-v_zsxb);--一天工作时长

--开始结束时间赋值
v_real_startTime := i_startTime;
v_real_endTime := i_endTime;
--开始时间及结束时间转换
if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间，将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime;
end if;

-- 如果小于当天9点，将其置为当天9点(因为你是9点上班)
if v_real_startTime<date_trunc('day',v_real_startTime)+ v_zssb * interval '1 hour'
then
v_real_startTime:=date_trunc('day',v_real_startTime)+ v_zssb * interval '1 hour';
-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)
elsif v_real_startTime>date_trunc('day',v_real_startTime)+v_zsxb * interval '1 hour' and v_real_startTime<date_trunc('day',v_real_startTime)+v_xwsb * interval '1 hour'
then
v_real_startTime:=date_trunc('day',v_real_startTime)+v_xwsb * interval '1 hour';
-- 如果大于当天18点，将其置为当天18天(因为你是18点下班)
elsif v_real_startTime>date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour'
then
v_real_startTime:=date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour';
end if;

-- 如果小于当天9点，将其置为当天9点(因为你是9点上班)
if v_real_endTime<date_trunc('day',v_real_endTime)+ v_zssb * interval '1 hour'
then
v_real_endTime:=date_trunc('day',v_real_endTime)+ v_zssb * interval '1 hour';
-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)
elsif v_real_endTime>date_trunc('day',v_real_endTime)+v_zsxb * interval '1 hour' and v_real_endTime<date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour'
then
v_real_endTime:=date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour';
-- 如果大于当天18点，将其置为当天18天(因为你是18点下班)
elsif v_real_endTime>date_trunc('day',v_real_endTime)+v_xwxb * interval '1 hour'
then
v_real_endTime:=date_trunc('day',v_real_endTime)+v_xwxb * interval '1 hour';
end if;

select
(case

when to_char(v_real_startTime,'YYYYMMDD')=to_char(v_real_endTime,'YYYYMMDD')
then
(case
when v_real_startTime<=date_trunc('day',v_real_startTime)+v_zsxb * interval '1 hour' and v_real_endTime>=date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour'
then round(cast(date_part('epoch',v_real_endTime- v_real_startTime)/3600-(v_xwsb-v_zsxb) as numeric),2)
else
round(cast(date_part('epoch',v_real_endTime- v_real_startTime)/3600as numeric),2)
end)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='0' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='0'
then (
(case
when v_real_startTime<=date_trunc('day',v_real_startTime)+v_zsxb * interval '1 hour' and v_real_endTime>=date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')-v_real_startTime)/3600-(v_xwsb-v_zsxb) as numeric),2)+ round(cast(date_part('epoch',v_real_endTime- (date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour'))/3600-(v_xwsb-v_zsxb) as numeric),2)
when v_real_startTime>=date_trunc('day',v_real_startTime)+v_xwsb * interval '1 hour' and v_real_endTime>=date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')- v_real_startTime)/3600 as numeric),2)+round(cast(date_part('epoch',v_real_endTime- (date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour'))/3600-(v_xwsb-v_zsxb) as numeric),2)
when v_real_startTime<=date_trunc('day',v_real_startTime)+v_zsxb * interval '1 hour' and v_real_endTime<=date_trunc('day',v_real_endTime)+v_zsxb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')-v_real_startTime)/3600-(v_xwsb-v_zsxb) as numeric),2)+round(cast(date_part('epoch',v_real_endTime- (date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour'))/3600 as numeric),2)
when v_real_startTime>=date_trunc('day',v_real_startTime)+v_xwsb * interval '1 hour' and v_real_endTime<=date_trunc('day',v_real_endTime)+v_zsxb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')- v_real_startTime)/3600 as numeric),2)+round(cast(date_part('epoch',v_real_endTime- (date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour'))/3600 as numeric),2)
end)+
(select coalesce(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+interval '1 day','yyyy-MM-dd') and to_char(v_real_endTime-interval '1 day', 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='1' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='0'
then (
(case
when v_real_endTime>=date_trunc('day',v_real_endTime)+v_xwsb * interval '1 hour'
then round(cast(date_part('epoch',(v_real_endTime-(date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour')))/3600-(v_xwsb-v_zsxb) as numeric),2)
when v_real_endTime<=date_trunc('day',v_real_endTime)+v_zsxb * interval '1 hour'
then round(cast(date_part('epoch',v_real_endTime- (date_trunc('day',v_real_endTime)+v_zssb * interval '1 hour'))/3600 as numeric),2)
end)+
(select coalesce(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+interval '1 day','yyyy-MM-dd') and to_char(v_real_endTime-interval '1 day', 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='0' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='1'
then (
(case
when v_real_startTime<=date_trunc('day',v_real_startTime)+v_zsxb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')-v_real_startTime)/3600-(v_xwsb-v_zsxb) as numeric),2)
when v_real_startTime>=date_trunc('day',v_real_startTime)+v_xwsb * interval '1 hour'
then round(cast(date_part('epoch',(date_trunc('day',v_real_startTime)+v_xwxb * interval '1 hour')- v_real_startTime)/3600 as numeric),2)
end)+
(select coalesce(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+interval '1 day','yyyy-MM-dd') and to_char(v_real_endTime-interval '1 day', 'yyyy-MM-dd'))*v_gzsc
)

when to_char(v_real_startTime,'YYYYMMDD')<to_char(v_real_endTime,'YYYYMMDD') and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_startTime,'yyyy-MM-dd'))='1' and (select t.HOLIDAY_FLAG from REGISTRAR_HOLIDAY t where t.DAY=to_char(v_real_endTime,'yyyy-MM-dd'))='1' and (select coalesce(count(1),0) from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime,'yyyy-MM-dd') and to_char(v_real_endTime, 'yyyy-MM-dd'))>=0
then (

(select coalesce(count(1),0) as holiday_num from REGISTRAR_HOLIDAY t where t.HOLIDAY_FLAG='0' and t.DAY between to_char(v_real_startTime+interval '1 day','yyyy-MM-dd') and to_char(v_real_endTime-interval '1 day', 'yyyy-MM-dd'))*v_gzsc
)

end) into v_number;

return v_number;
end;
$$ language plpgsql;

4.4.8.注释忽略处理

查看报错日志：

在以下路径中找到该文件在sql界面执行

由于遇到的还有较多的存储过程、触发器、视图语法报错篇幅较长，在这儿不一一展示。

4.5.查目标库数据量PG

4.5.1.查数据库大小

[postgres@localhost ~]$ psql -U topicfeshk -W -d topicfeshk1
Password:
psql (14.2)
Type "help" for help.

topicfeshk1=# \l+ topicfeshk1
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------+------------+----------+-------------+-------------+---------------------------+---------+------------+-------------
topicfeshk1 | topicfeshk | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/topicfeshk +| 1866 MB | pg_default |
| | | | | topicfeshk=CTc/topicfeshk | | |
(1 row)


或
topicfeshk1=# select pg_size_pretty(pg_database_size('topicfeshk1'));
pg_size_pretty
----------------
1866 MB
(1 row)


或
查单个数据库大小
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid where d.datname='topicfeshk1' order by 1;

注：如果查多个库或者所有库大小，更改或去掉where 后面的d.datname='ahtjtestnew'

4.5.2.查不同对象数量

和源库进行对比。

SELECT
nsp.nspname AS SchemaName,
CASE
cls.relkind
WHEN 'r' THEN
'TABLE'
WHEN 'm' THEN
'MATERIALIZED_VIEW'
WHEN 'i' THEN
'INDEX'
WHEN 'S' THEN
'SEQUENCE'
WHEN 'v' THEN
'VIEW'
WHEN 'c' THEN
'composite type'
WHEN 't' THEN
'TOAST'
WHEN 'f' THEN
'foreign table'
WHEN 'p' THEN
'partitioned_table'
WHEN 'I' THEN
'partitioned_index' ELSE cls.relkind :: TEXT
END AS ObjectType,
COUNT ( * ) cnt
FROM
pg_class cls
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE
nsp.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and nsp.nspname='public'
AND nsp.nspname NOT LIKE'pg_toast%'
GROUP BY
nsp.nspname,
cls.relkind UNION ALL
SELECT
n.nspname AS "Schema",
CASE
P.prokind
WHEN 'a' THEN
'agg'
WHEN 'w' THEN
'window'
WHEN 'p' THEN
'proc' ELSE'func'
END AS "Type",
COUNT ( * ) cnt
FROM
pg_catalog.pg_proc
P LEFT JOIN pg_catalog.pg_namespace n ON n.oid = P.pronamespace
WHERE
pg_catalog.pg_function_is_visible ( P.oid )
AND n.nspname NOT IN ( 'information_schema', 'pg_catalog' ) and n.nspname='public'
GROUP BY
n.nspname,
P.prokind;

schemaname | objecttype | cnt
------------+------------+-----
public VIEW 21
public INDEX 1630
public TABLE 1602
public SEQUENCE 8
public func 2
(5 行记录)

4.5.3.查表记录数

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables
where schemaname='public'
ORDER BY relname;

数据记录不是特别准确，和源库记录数进行对比，记录数不一样的查询下。和源库进行对比。

4.6.备份刚迁移完成的PG库(可选)

由于网络不通，将PG库进行备份，备份完成后发给现场同事上传到目标服务器上。

注意：如果源服务器和目标服务器网络通，可省略该步骤

su - postgres
pg_dump -Utopicfeshk -W -p 5432 -Fc -v -f /home/postgres/topicfeshk1_20240913.dmp topicfeshk1

五.恢复过程(可选)

注意：如果源服务器和目标服务器网络通，可省略该步骤

5.1.恢复刚迁移完成的PG库

注意：创建用户时，注意和源服务器的用户要相同，因为pg_restore恢复时无用户映射参数，若不相同需要在迁移后将所有对象的owner更改为新owner

--创建业务用户
create user topicfeshk with encrypted password '8\VE@ksf:(NOW\~x';
alter user topicfeshk with superuser;

--创建业务数据库
create database topicfeshk owner topicfeshk;
grant all privileges on database topicfeshk to topicfeshk;
grant all privileges on all tables in schema public to topicfeshk;


--恢复
pg_restore -Utopicfeshk -W -d topicfeshk /home/postgres/topicfeshk1_20240913.dmp

5.2.扩展：恢复PG库后更改库中所有对象owner为新owner

5.2.1.更改所有表的Owner

5.2.1.1.上传一键更改所有对象owner的脚本

cat /home/postgres/generation_alterallobjectowner.sql
\a \t \o /home/postgres/grantciv.sql

select 'alter table ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.relname || '"' || ' owner to uat'||';'
from pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ()
and cls.relnamespace = nsp.oid
and cls.relkind in ('r','p')
order by nsp.nspname,
cls.relname;


select 'alter sequence ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.relname || '"' || ' owner to uat'||';'
from pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in (
)
and cls.relnamespace = nsp.oid
and cls.relkind in ('S')
order by nsp.nspname,
cls.relname;


select 'ALTER FUNCTION ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.proname || '"' || ' owner to uat'||';'
from pg_catalog.pg_proc cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ()
and cls.pronamespace = nsp.oid
order by nsp.nspname,
cls.proname;

select 'ALTER VIEW ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.table_name || '"' || ' owner to uat'||';'
from information_schema.views cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ('public')
and cls.table_schema = nsp.nspname
order by nsp.nspname,
cls.table_name;

5.2.1.2.更改一键更改所有对象owner脚本

更改一键更改所有对象owner脚本，更改内容如下：

更改grantciv.sql前目录
where nsp.nspname in ()更改为where nsp.nspname in ('public')即模式名；
owner to uat更改为owner to postgres即用户

更改后脚本内容如下：

cat /home/postgres/generation_alterallobjectowner.sql
\a \t \o /home/postgres/grantciv.sql

select 'alter table ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.relname || '"' || ' owner to postgres'||';'
from pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ('public')
and cls.relnamespace = nsp.oid
and cls.relkind in ('r','p')
order by nsp.nspname,
cls.relname;


select 'alter sequence ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.relname || '"' || ' owner to postgres'||';'
from pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ('public')
and cls.relnamespace = nsp.oid
and cls.relkind in ('S')
order by nsp.nspname,
cls.relname;

select 'ALTER FUNCTION ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.proname || '"' || ' owner to postgres'||';'
from pg_catalog.pg_proc cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ('public')
and cls.pronamespace = nsp.oid
order by nsp.nspname,
cls.proname;

select 'ALTER VIEW ' || '"' || nsp.nspname || '"' || '.' || '"' || cls.table_name || '"' || ' owner to postgres'||';'
from information_schema.views cls,
pg_catalog.pg_namespace nsp
where nsp.nspname in ('public')
and cls.table_schema = nsp.nspname
order by nsp.nspname,
cls.table_name;

5.2.1.3.执行一键更改所有对象owner脚本

[postgres@localhost ~]$ psql -Upostgres -d dxj -f /home/postgres/generation_alterallobjectowner.sql
Output format is unaligned.
Tuples only is on.

/home/postgres目录下会多生成一个grantciv.sql文件

[postgres@localhost ~]$ ls -l
total 339516
-rw-rw-r--. 1 postgres postgres 961 Sep 13 18:21 generation_alterallobjectowner.sql
-rw-rw-r--. 1 postgres postgres 97912 Sep 13 18:23 grantciv.sql
-rw-r--r--. 1 root root 68 Aug 22 10:02 pg_hba.conf
-rw-rw-r--. 1 postgres postgres 347555746 Sep 13 16:51 topicfeshk1_20240913.dmp

5.2.1.4.执行grantciv.sql脚本

[postgres@localhost ~]$ psql -Upostgres -d dxj -f /home/postgres/grantciv.sql

5.2.1.5.查看对象

[postgres@localhost ~]$ psql -Upostgres -d dxj
psql (14.2)
Type "help" for help.

--查表 \d[S+] list tables, views, and sequences
dxj=# \dS+

--查索引
dxj=# \di

补充：
\di[S+] [PATTERN] list indexes
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables

分别输出如下：

至此更改owner步骤完成