nacos2.x 支持postgresql与mysql

 环境：

nacos-2.0.1

postgresql-12.x

mysql-8.x

正题：

nacos是一款不错的服务注册以及配置中心中间件，官网发布的nacos-server docker版本只支持mysql，不支持postgresql，但如果项目中使用的postgresql数据库，仅仅为了nacos单独去部署一个mysql实例有点得不偿失。今天要做的事就是在官网nacos2.0.1的基础上进行改造，使构建出来的nacos docker镜像同时支持mysql8以及postgresql。

1、克隆nacos的官网库，使用2.0.1 tag

git clone https://github.com/alibaba/nacos.git -b 2.0.1

2、使用IDEA或者其他工具打开nacos项目，下面是nacos 2.0.1的项目概览

3、添加postgresql依赖，需要修改的地方有3处，顶级pom.xml，nacos-config以及nacos-naming的pom.xml,顶级pom是在dependencyManagement部分添加，pg版本根据自己项目实际情况修改

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.14</version>
</dependency>

 nacos-config以及nacos-naming只需添加postgresql依赖即可，不用再关注pg版本

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

4、修改nacos-config模块的ExternalDataSourceProperties类build方法

    List<HikariDataSource> build(Environment environment, Callback<HikariDataSource> callback) {
        List<HikariDataSource> dataSources = new ArrayList<>();
        Binder.get(environment).bind("db", Bindable.ofInstance(this));
        Preconditions.checkArgument(Objects.nonNull(num), "db.num is null");
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(user), "db.user or db.user.[index] is null");
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(password), "db.password or db.password.[index] is null");
        for (int index = 0; index < num; index++) {
            int currentSize = index + 1;
            Preconditions.checkArgument(url.size() >= currentSize, "db.url.%s is null", index);
            DataSourcePoolProperties poolProperties = DataSourcePoolProperties.build(environment);
            // 支持postgresql与mysql，POSTGRES_JDBC_DRIVER_NAME为org.postgresql.Driver的常量
            String driverClassName = JDBC_DRIVER_NAME;
            if ("postgresql".equals(EnvUtil.getProperty("spring.datasource.platform"))) {
                driverClassName = POSTGRES_JDBC_DRIVER_NAME;
            }

5、修改nacos-config模块的PropertyUtil类loadSetting方法，修改后为

private void loadSetting() {
        try {
            setNotifyConnectTimeout(Integer.parseInt(EnvUtil.getProperty("notifyConnectTimeout", "100")));
            LOGGER.info("notifyConnectTimeout:{}", notifyConnectTimeout);
            setNotifySocketTimeout(Integer.parseInt(EnvUtil.getProperty("notifySocketTimeout", "200")));
            LOGGER.info("notifySocketTimeout:{}", notifySocketTimeout);
            setHealthCheck(Boolean.parseBoolean(EnvUtil.getProperty("isHealthCheck", "true")));
            LOGGER.info("isHealthCheck:{}", isHealthCheck);
            setMaxHealthCheckFailCount(Integer.parseInt(EnvUtil.getProperty("maxHealthCheckFailCount", "12")));
            LOGGER.info("maxHealthCheckFailCount:{}", maxHealthCheckFailCount);
            setMaxContent(Integer.parseInt(EnvUtil.getProperty("maxContent", String.valueOf(maxContent))));
            LOGGER.info("maxContent:{}", maxContent);
            // 容量管理
            setManageCapacity(getBoolean("isManageCapacity", isManageCapacity));
            setCapacityLimitCheck(getBoolean("isCapacityLimitCheck", isCapacityLimitCheck));
            setDefaultClusterQuota(getInt("defaultClusterQuota", defaultClusterQuota));
            setDefaultGroupQuota(getInt("defaultGroupQuota", defaultGroupQuota));
            setDefaultTenantQuota(getInt("defaultTenantQuota", defaultTenantQuota));
            setDefaultMaxSize(getInt("defaultMaxSize", defaultMaxSize));
            setDefaultMaxAggrCount(getInt("defaultMaxAggrCount", defaultMaxAggrCount));
            setDefaultMaxAggrSize(getInt("defaultMaxAggrSize", defaultMaxAggrSize));
            setCorrectUsageDelay(getInt("correctUsageDelay", correctUsageDelay));
            setInitialExpansionPercent(getInt("initialExpansionPercent", initialExpansionPercent));
            // External data sources are used by default in cluster mode
            // 支持postgresql与mysql
            String platfrom = getString("spring.datasource.platform", "");
            setUseExternalDB("mysql".equalsIgnoreCase(platfrom) || "postgresql".equalsIgnoreCase(platfrom));

6、修改nacos-core模块的StartingApplicationListener类judgeStorageMode方法，修改后如下所示：

    private void judgeStorageMode(ConfigurableEnvironment env) {

        // External data sources are used by default in cluster mode
        // 修改为支持postgresql与mysql
        String platform = env.getProperty("spring.datasource.platform", "");
        boolean useExternalStorage = ("mysql".equalsIgnoreCase(platform) || "postgresql".equalsIgnoreCase(platform));

7、修改nacos-config模块GroupCapacityPersistService类insertGroupCapacity方法，修改后如下所示：

    private boolean insertGroupCapacity(final String sql, final GroupCapacity capacity) {
        try {
            GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
            PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    //修改为支持postgresql与mysql
                    PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});

8、修改nacos-config模块TenantCapacityPersistService类的insertTenantCapacity方法，修改后如下所示：

    public boolean insertTenantCapacity(final TenantCapacity tenantCapacity) {
        final String sql =
                "INSERT INTO tenant_capacity (tenant_id, quota, `usage`, `max_size`, max_aggr_count, max_aggr_size, "
                        + "gmt_create, gmt_modified) SELECT ?, ?, count(*), ?, ?, ?, ?, ? FROM config_info WHERE tenant_id=?;";
        try {
            GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
            PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});

9、修改nacos-config模块ExternalStoragePersistServiceImpl类的addConfigInfoAtomic方法，修改后如下所示：

    public long addConfigInfoAtomic(final long configId, final String srcIp, final String srcUser,
            final ConfigInfo configInfo, final Timestamp time, Map<String, Object> configAdvanceInfo) {
        final String appNameTmp =
                StringUtils.isBlank(configInfo.getAppName()) ? StringUtils.EMPTY : configInfo.getAppName();
        final String tenantTmp =
                StringUtils.isBlank(configInfo.getTenant()) ? StringUtils.EMPTY : configInfo.getTenant();

        final String desc = configAdvanceInfo == null ? null : (String) configAdvanceInfo.get("desc");
        final String use = configAdvanceInfo == null ? null : (String) configAdvanceInfo.get("use");
        final String effect = configAdvanceInfo == null ? null : (String) configAdvanceInfo.get("effect");
        final String type = configAdvanceInfo == null ? null : (String) configAdvanceInfo.get("type");
        final String schema = configAdvanceInfo == null ? null : (String) configAdvanceInfo.get("schema");

        final String md5Tmp = MD5Utils.md5Hex(configInfo.getContent(), Constants.ENCODE);

        KeyHolder keyHolder = new GeneratedKeyHolder();

        final String sql =
                "INSERT INTO config_info(data_id,group_id,tenant_id,app_name,content,md5,src_ip,src_user,gmt_create,"
                        + "gmt_modified,c_desc,c_use,effect,type,c_schema) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {
            jt.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    // 支持postgresql与mysql
                    PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});

10、修改nacos-config模块ExternalStoragePaginationHelperImpl类的fetchPage方法，修改后如下所示：

    public Page<E> fetchPage(final String sqlCountRows, final String sqlFetchRows, final Object[] args,
            final int pageNo, final int pageSize, final Long lastMaxId, final RowMapper rowMapper) {
        if (pageNo <= 0 || pageSize <= 0) {
            throw new IllegalArgumentException("pageNo and pageSize must be greater than zero");
        }

        // Query the total number of current records.
        Integer rowCountInt = jdbcTemplate.queryForObject(sqlCountRows, args, Integer.class);
        if (rowCountInt == null) {
            throw new IllegalArgumentException("fetchPageLimit error");
        }

        // Compute pages count
        int pageCount = rowCountInt / pageSize;
        if (rowCountInt > pageSize * pageCount) {
            pageCount++;
        }

        // Create Page object
        final Page<E> page = new Page<E>();
        page.setPageNumber(pageNo);
        page.setPagesAvailable(pageCount);
        page.setTotalCount(rowCountInt);

        if (pageNo > pageCount) {
            return page;
        }

        final int startRow = (pageNo - 1) * pageSize;
        String selectSql = "";
        if (isDerby()) {
            selectSql = sqlFetchRows + " OFFSET " + startRow + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
        } else if (lastMaxId != null) {
            selectSql = sqlFetchRows + " and id > " + lastMaxId + " order by id asc" + " limit " + 0 + "," + pageSize;
        } else {
            // 修改为支持postgresql,同时兼容mysql8
            selectSql = sqlFetchRows + " limit " + pageSize + " offset " + startRow;
        }

11、修改nacos-config模块ExternalRolePersistServiceImpl类的findRolesLikeRoleName方法，修改后如下所示：

public List<String> findRolesLikeRoleName(String role) {
        // 支持postgresql与mysql
        String sql = "SELECT role FROM roles WHERE role like '%" + role + "%'";
        List<String> users = this.jt.queryForList(sql, null, String.class);
        return users;
    }

12、修改nacos-config模块ExternalUserPersistServiceImpl类的findUserLikeUsername方法，修改后如下所示：

public List<String> findUserLikeUsername(String username) {
        // 兼容postgresql、mysql
        String sql = "SELECT username FROM users WHERE username like '%" + username + "%'";
        List<String> users = this.jt.queryForList(sql, null, String.class);
        return users;
    }

13、利用IDEA的全局替换功能将所有limit ?,? 替换为 limit ? offset ?，这些sql全部分布在nacos-config模块，不要修改EmbeddedStoragePersistServiceImpl这个类，这个跟postgresql没有任何关系，由于官方nacos分支使用的sql语法传参是offset在前，limit在后，即offset ? limit ?这种形式，所以替换完limit ?,?后需要调整传参的顺序，这里就以一处修改为例来说明，

public Page<ConfigInfoWrapper> findAllConfigInfoFragment(final long lastMaxId, final int pageSize) {
    String select = "SELECT id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,type from config_info where id > ? order by id asc limit ? offset ?";
    PaginationHelper<ConfigInfoWrapper> helper = createPaginationHelper();
    try {
        // 官方传参顺序为 new Object[] {lastMaxId, 0, pageSize}，需要调整为下面的顺序
        return helper.fetchPageLimit(select, new Object[] {lastMaxId, pageSize, 0}, 1, pageSize,
                CONFIG_INFO_WRAPPER_ROW_MAPPER);
    } catch (CannotGetJdbcConnectionException e) {
        LogUtil.FATAL_LOG.error("[db-error] " + e.toString(), e);
        throw e;
    }
}

14、执行下面命令进行打包

mvn -Prelease-nacos -Dmaven.test.skip=true -Dpmd.skip=true -Dcheckstyle.skip=true -Drat.skip=true clean install -U  

15、打包成功后会创建distribution目录，该目录结构如下：

16、在postgresql创建nacos数据库，并初始化nacos的sql进行初始化。初始化脚本可以通过

nacos2.0.1postgresql初始化脚本-互联网文档类资源-CSDN下载下载

17、运行nacos，在distribution目录，修改conf目录下的application.properties文件，修改部分如下：

执行.\bin\startup.cmd -m standalone 命令启动即可，linux环境执行startup.sh脚本

Docker镜像制作：

1、使用上面打包出的nacos-server-2.0.1.tar.gz文件，使用github上的nacos-docker/build at master · nacos-group/nacos-docker · GitHub文件

2、为了减少docker镜像的大小，笔者调整了基础镜像，并修改了Dockerfile部分内容，示例如下：

FROM openjdk:8-jdk-alpine
MAINTAINER johnhuster "https://blog.csdn.net/john1337"

# set environment
ENV MODE="cluster" \
    PREFER_HOST_MODE="ip"\
    BASE_DIR="/home/nacos" \
    CLASSPATH=".:/home/nacos/conf:$CLASSPATH" \
    CLUSTER_CONF="/home/nacos/conf/cluster.conf" \
    FUNCTION_MODE="all" \
    JAVA_HOME="/usr/lib/jvm/java-1.8-openjdk" \
    NACOS_USER="nacos" \
    JAVA="/usr/lib/jvm/java-1.8-openjdk/bin/java" \
    JVM_XMS="1g" \
    JVM_XMX="1g" \
    JVM_XMN="512m" \
    JVM_MS="128m" \
    JVM_MMS="320m" \
    NACOS_DEBUG="n" \
    TOMCAT_ACCESSLOG_ENABLED="false" \
    TIME_ZONE="Asia/Shanghai"

ARG NACOS_VERSION=2.0.1
ARG HOT_FIX_FLAG=""

WORKDIR $BASE_DIR

COPY nacos-server-${NACOS_VERSION}.tar.gz /home

RUN ln -s /lib/libc.musl-x86_64.so.1 /lib/ld-linux-x86-64.so.2
 
RUN tar -xzvf /home/nacos-server-${NACOS_VERSION}.tar.gz -C /home \
    && rm -rf /home/nacos-server-${NACOS_VERSION}.tar.gz /home/nacos/bin/* /home/nacos/conf/*.properties /home/nacos/conf/*.example /home/nacos/conf/nacos-mysql.sql


ADD bin/docker-startup.sh bin/docker-startup.sh
ADD conf/application.properties conf/application.properties


# set startup log dir
RUN mkdir -p logs \
	&& cd logs \
	&& touch start.out \
	&& ln -sf /dev/stdout start.out \
	&& ln -sf /dev/stderr start.out
RUN chmod +x bin/docker-startup.sh

EXPOSE 8848
ENTRYPOINT ["bin/docker-startup.sh"]

归纳：

1、本次修改涉及三个模块：nacos-core、nacos-config以及nacos-naming

2、nacos-config模块修改文件如下所示：

 3、nacos-core模块变动部分如下所示：

PS：

1、ExternalStoragePersistServiceImpl类findAllConfigInfoForDumpAll方法有问题，调整后如下：

   public Page<ConfigInfoWrapper> findAllConfigInfoForDumpAll(final int pageNo, final int pageSize) {
        String sqlCountRows = "select count(*) from config_info";
        String sqlFetchRows = " SELECT t.id,type,data_id,group_id,tenant_id,app_name,content,type,md5,gmt_modified "
                + " FROM ( SELECT id FROM config_info   ORDER BY id limit ? offset ?  )"
                + " g, config_info t WHERE g.id = t.id ";
        PaginationHelper<ConfigInfoWrapper> helper = createPaginationHelper();

        try {
            return helper.fetchPageLimit(sqlCountRows, sqlFetchRows, new Object[] {pageSize, (pageNo - 1) * pageSize}, pageNo, pageSize,
                    CONFIG_INFO_WRAPPER_ROW_MAPPER);
        } catch (CannotGetJdbcConnectionException e) {
            LogUtil.FATAL_LOG.error("[db-error] " + e.toString(), e);
            throw e;
        }
    }

 原来的代码：

2、nacos-2.0.3按照这个方式修改应该问题也不大，有问题的话可以交流