Escheduler调度系统修改数据库MySQL为Postgres_psqlexception: error: function last_insert_id() do

为了统一使用的数据库为postgres，所以现在对开源项目Escheduler做一些修改，把MySQL数据库改为Postgres。

具体步骤，和遇到的问题及解决办法记录如下：

说一下思路，直接改代码也不清楚改哪里，我是在已经安装好的情况下，修改了配置文件，然后重启服务，看日志，根据日志报错情况再具体问题具体解决。解决问题的话是改源代码，然后再编译安装，再查看日志有没有报错，中间反复很多次。测试了所有基本功能都正常才行。

前置步骤

Escheduler前端和后台服务已经安装启动成功，然后公司的DBA同事帮忙把MySQL数据库的所有表迁移到Postgres数据库。这里也可以自己使用Navicat工具迁移表。Navicat下载安装参考文档：https://www.jianshu.com/p/4e93b48f9f63

具体步骤

1、修改配置文件 quartz.properties，项目用到了quartz定时器

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
 
org.quartz.dataSource.myDs.driver = org.postgresql.Driver
org.quartz.dataSource.myDs.URL = jdbc:postgresql://192.168.3.111:5432/escheduler

2、修改配置文件 data_source.properties

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://192.168.3.103:5432/escheduler

3、修改安装脚本 install.sh

sed -i ${txt} "s#spring.datasource.url.*#spring.datasource.url=jdbc:postgresql://${mysqlHost}/${mysqlDb}#g" conf/dao/data_source.properties
 
sed -i ${txt} "s#org.quartz.dataSource.myDs.URL.*#org.quartz.dataSource.myDs.URL=jdbc:postgresql://${mysqlHost}/${mysqlDb}#g" conf/quartz.properties
 

报错问题及解决办法

1、nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer

解决办法：

对比了一下MySQL和Postgres中的表结构，发现把 tinyint转成了boolean类型，所以会报错；

修改Postgres中的数据类型为int4之后解决问题。

2、org.postgresql.util.PSQLException: ERROR: function last_insert_id() does not exist

解决办法：

Mysql中有last_insert_id()函数获取刚刚插入的一条数据的主键值，Postgres中没有对应的方法。最后改成查询每个序列的上次值，具体语句：SELECT last_value as id from t_escheduler_alertgroup_id_seq

3、源代码中SQL语句的字段名称用``引起来，也报错，去掉之后解决

4、Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: LIMIT #,# syntax is not supported
  建议：Use separate LIMIT and OFFSET clauses.

解决办法：报错中提示了使用limit offset，修改源代码即可

5、Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: column "tu.user_name" must appear in the GROUP BY clause or be used in an aggregate function

SELECT td.user_id as user_id, tu.user_name as user_name, count(0) as count 
FROM t_escheduler_process_definition td JOIN t_escheduler_user tu 
on tu.id=td.user_id 
WHERE (td.project_id in 
(select id as project_id 
from t_escheduler_project tp 
where tp.user_id= ? union select project_id 
from t_escheduler_relation_project_user tr 
where tr.user_id= ? )) GROUP BY td.user_id

解决办法：把tu.user_name放到GROUP BY语句后面解决问题。

6、Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: function unix_timestamp(timestamp with time zone) does not exist

解决办法：

Postgres中没有对应的unix_timestamp，改成下面方法就可以了

SELECT("*, EXTRACT( epoch from end_time)-EXTRACT( epoch from start_time) as duration");

7、还有一些表的字段名是desc，这个是关键字，需要加引号，否则也报错

例如：VALUES("\"desc\"", "#{alertGroup.desc}");

8、最后还有页面中Mysql监控部分

修改部分如下：

Class.forName(Constants.JDBC_POSTGRESQL_CLASS_NAME); 
 
ResultSet rs1 = pstmt.executeQuery("show max_connections");
            while(rs1.next()){
                maxConnections= Long.parseLong(rs1.getString("max_connections"));
 
            }
 
            ResultSet rs2 = pstmt.executeQuery("SELECT count(*) as cnt from pg_stat_activity");
            while(rs2.next()){
                maxUsedConnections = Long.parseLong(rs2.getString("cnt"));
                threadsConnections = Long.parseLong(rs2.getString("cnt"));
            }
            ResultSet rs3 = pstmt.executeQuery("SELECT count(*) as cnt from pg_stat_activity where state=\'active\'");
            while(rs3.next()){
                threadsRunningConnections= Long.parseLong(rs3.getString("cnt"));
 
            }