nacos2.3.0实现接入pgsql或其他数据库

<postgresql.version>42.3.8</postgresql.version>
org.postgresql postgresql ${postgresql.version}

org.postgresql postgresql

com.alibaba.nacos.plugin.datasource.impl.postgresql.configinfoaggrmapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.configinfobetamapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.configinfomapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.configinfotagmapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.configtagsrelationmapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.historyconfiginfomapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.tenantinfomapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.tenantcapacitymapperbypostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.groupcapacitymapperbypostgresql

    public static final string postgresql = "postgresql";

    public static final string postgresql = "postgresql";

/*
 * copyright 1999-2023 alibaba group holding ltd.
 *
 * licensed under the apache license, version 2.0 (the "license");
 * you may not use this file except in compliance with the license.
 * you may obtain a copy of the license at
 *
 *      http://www.apache.org/licenses/license-2.0
 *
 * unless required by applicable law or agreed to in writing, software
 * distributed under the license is distributed on an "as is" basis,
 * without warranties or conditions of any kind, either express or implied.
 * see the license for the specific language governing permissions and
 * limitations under the license.
 */

package com.alibaba.nacos.persistence.datasource;

import com.alibaba.nacos.common.utils.collectionutils;
import com.alibaba.nacos.common.utils.preconditions;
import com.alibaba.nacos.common.utils.stringutils;
import com.zaxxer.hikari.hikaridatasource;
import org.springframework.boot.context.properties.bind.bindable;
import org.springframework.boot.context.properties.bind.binder;
import org.springframework.core.env.environment;

import java.util.arraylist;
import java.util.list;
import java.util.objects;

import static com.alibaba.nacos.common.utils.collectionutils.getordefault;

/**
 * properties of external datasource.
 *
 * @author nacos
 */
public class externaldatasourceproperties {
    
    private static final string jdbc_driver_name = "com.mysql.cj.jdbc.driver";

    private static final string test_query = "select 1";
    
    private integer num;
    
    private list<string> url = new arraylist<>();
    
    private list<string> user = new arraylist<>();
    
    private list<string> password = new arraylist<>();
    private string jdbcdrivername;

    public void setjdbcdrivername(string jdbcdrivername) {
        if (stringutils.isblank(jdbcdrivername)) {
            this.jdbcdrivername = jdbc_driver_name;
        } else {
            this.jdbcdrivername = jdbcdrivername;
        }
    }
    
    public void setnum(integer num) {
        this.num = num;
    }
    
    public void seturl(list<string> url) {
        this.url = url;
    }
    
    public void setuser(list<string> user) {
        this.user = user;
    }
    
    public void setpassword(list<string> password) {
        this.password = password;
    }

    /**
     * build serveral hikaridatasource.
     *
     * @param environment {@link environment}
     * @param callback    callback function when constructing data source
     * @return list of {@link hikaridatasource}
     */
    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);
            if (stringutils.isempty(poolproperties.getdatasource().getdriverclassname())) {
                poolproperties.setdriverclassname(jdbcdrivername);
            }
            poolproperties.setjdbcurl(url.get(index).trim());
            poolproperties.setusername(getordefault(user, index, user.get(0)).trim());
            poolproperties.setpassword(getordefault(password, index, password.get(0)).trim());
            hikaridatasource ds = poolproperties.getdatasource();
            if (stringutils.isempty(ds.getconnectiontestquery())) {
                ds.setconnectiontestquery(test_query);
            }
            
            datasources.add(ds);
            callback.accept(ds);
        }
        preconditions.checkargument(collectionutils.isnotempty(datasources), "no datasource available");
        return datasources;
    }
    
    interface callback<d> {
        
        /**
         * perform custom logic.
         *
         * @param datasource datasource.
         */
        void accept(d datasource);
    }
}

    public static final string offset_limit = "offset ? limit ?";

package com.alibaba.nacos.plugin.auth.impl.persistence.handler.support;

import com.alibaba.nacos.config.server.service.dump.dumpservice;
import com.alibaba.nacos.persistence.constants.persistenceconstant;
import com.alibaba.nacos.plugin.auth.impl.constant.authpageconstant;
import com.alibaba.nacos.plugin.auth.impl.model.offsetfetchresult;
import com.alibaba.nacos.plugin.auth.impl.persistence.handler.pagehandleradapter;
import org.slf4j.logger;
import org.slf4j.loggerfactory;

import java.util.arraylist;
import java.util.arrays;
import java.util.list;

/**
 * @author w
 */
public class postgresqlpagehandleradapter implements pagehandleradapter {

    @override
    public boolean supports(string datasourcetype) {
        return persistenceconstant.postgresql.equals(datasourcetype);
    }

    @override
    public offsetfetchresult addoffsetandfetchnext(string fetchsql, object[] arg, int pageno, int pagesize) {
        if (!fetchsql.contains(authpageconstant.limit)) {
            fetchsql += " " + authpageconstant.offset_limit;
            list<object> newargslist = new arraylist<>(arrays.aslist(arg));
            newargslist.add((pageno - 1) * pagesize);
            newargslist.add(pagesize);

            object[] newargs = newargslist.toarray(new object[newargslist.size()]);
            return new offsetfetchresult(fetchsql, newargs);
        }

        return new offsetfetchresult(fetchsql, arg);
    }
}

mvn -prelease-nacos clean install -dmaven.test.skip=true -dcheckstyle.skip=true -dpmd.skip=true -drat.skip=true -u

spring.datasource.platform=postgresql
db.num=1
db.url.0=jdbc:postgresql://xxxx:5432/nacos?currentschema=public&rewritebatchedinserts=true&useunicode=true&characterencoding=utf8&servertimezone=asia/shanghai
db.user.0=xxxx
db.password.0=xxxx
db.pool.config.driverclassname=org.postgresql.driver
nacos.core.auth.plugin.nacos.token.secret.key=secretkey012345678901234567890123456789012345678901234567890123456789


nacos.core.auth.enabled=true
nacos.core.auth.caching.enabled=false
nacos.core.auth.server.identity.key=nacos
nacos.core.auth.server.identity.value=nacos

/*
 * copyright 1999-2018 alibaba group holding ltd.
 *
 * licensed under the apache license, version 2.0 (the "license");
 * you may not use this file except in compliance with the license.
 * you may obtain a copy of the license at
 *
 *      http://www.apache.org/licenses/license-2.0
 *
 * unless required by applicable law or agreed to in writing, software
 * distributed under the license is distributed on an "as is" basis,
 * without warranties or conditions of any kind, either express or implied.
 * see the license for the specific language governing permissions and
 * limitations under the license.
 */

-- ----------------------------
-- table structure for config_info
-- ----------------------------
drop table if exists "config_info";
create table "config_info" (
  "id" bigserial not null,
  "data_id" varchar(255)  not null,
  "group_id" varchar(255) ,
  "content" text  not null,
  "md5" varchar(32) ,
  "gmt_create" timestamp(6) not null,
  "gmt_modified" timestamp(6) not null,
  "src_user" text ,
  "src_ip" varchar(20) ,
  "app_name" varchar(128) ,
  "tenant_id" varchar(128) ,
  "c_desc" varchar(256) ,
  "c_use" varchar(64) ,
  "effect" varchar(64) ,
  "type" varchar(64) ,
  "c_schema" text ,
  "encrypted_data_key" text  not null
)
;

comment on column "config_info"."id" is 'id';
comment on column "config_info"."data_id" is 'data_id';
comment on column "config_info"."content" is 'content';
comment on column "config_info"."md5" is 'md5';
comment on column "config_info"."gmt_create" is '创建时间';
comment on column "config_info"."gmt_modified" is '修改时间';
comment on column "config_info"."src_user" is 'source user';
comment on column "config_info"."src_ip" is 'source ip';
comment on column "config_info"."tenant_id" is '租户字段';
comment on column "config_info"."encrypted_data_key" is '秘钥';
comment on table "config_info" is 'config_info';


-- ----------------------------
-- table structure for config_info_aggr
-- ----------------------------
drop table if exists "config_info_aggr";
create table "config_info_aggr" (
  "id" bigserial not null,
  "data_id" varchar(255)  not null,
  "group_id" varchar(255)  not null,
  "datum_id" varchar(255)  not null,
  "content" text  not null,
  "gmt_modified" timestamp(6) not null,
  "app_name" varchar(128) ,
  "tenant_id" varchar(128) 
)
;
comment on column "config_info_aggr"."id" is 'id';
comment on column "config_info_aggr"."data_id" is 'data_id';
comment on column "config_info_aggr"."group_id" is 'group_id';
comment on column "config_info_aggr"."datum_id" is 'datum_id';
comment on column "config_info_aggr"."content" is '内容';
comment on column "config_info_aggr"."gmt_modified" is '修改时间';
comment on column "config_info_aggr"."tenant_id" is '租户字段';
comment on table "config_info_aggr" is '增加租户字段';

-- ----------------------------
-- records of config_info_aggr
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for config_info_beta
-- ----------------------------
drop table if exists "config_info_beta";
create table "config_info_beta" (
  "id" bigserial not null,
  "data_id" varchar(255)  not null,
  "group_id" varchar(128)  not null,
  "app_name" varchar(128) ,
  "content" text  not null,
  "beta_ips" varchar(1024) ,
  "md5" varchar(32) ,
  "gmt_create" timestamp(6) not null,
  "gmt_modified" timestamp(6) not null,
  "src_user" text ,
  "src_ip" varchar(20) ,
  "tenant_id" varchar(128) ,
  "encrypted_data_key" text  not null
)
;
comment on column "config_info_beta"."id" is 'id';
comment on column "config_info_beta"."data_id" is 'data_id';
comment on column "config_info_beta"."group_id" is 'group_id';
comment on column "config_info_beta"."app_name" is 'app_name';
comment on column "config_info_beta"."content" is 'content';
comment on column "config_info_beta"."beta_ips" is 'betaips';
comment on column "config_info_beta"."md5" is 'md5';
comment on column "config_info_beta"."gmt_create" is '创建时间';
comment on column "config_info_beta"."gmt_modified" is '修改时间';
comment on column "config_info_beta"."src_user" is 'source user';
comment on column "config_info_beta"."src_ip" is 'source ip';
comment on column "config_info_beta"."tenant_id" is '租户字段';
comment on column "config_info_beta"."encrypted_data_key" is '秘钥';
comment on table "config_info_beta" is 'config_info_beta';

-- ----------------------------
-- records of config_info_beta
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for config_info_tag
-- ----------------------------
drop table if exists "config_info_tag";
create table "config_info_tag" (
  "id" bigserial not null,
  "data_id" varchar(255)  not null,
  "group_id" varchar(128)  not null,
  "tenant_id" varchar(128) ,
  "tag_id" varchar(128)  not null,
  "app_name" varchar(128) ,
  "content" text  not null,
  "md5" varchar(32) ,
  "gmt_create" timestamp(6) not null,
  "gmt_modified" timestamp(6) not null,
  "src_user" text ,
  "src_ip" varchar(20) 
)
;
comment on column "config_info_tag"."id" is 'id';
comment on column "config_info_tag"."data_id" is 'data_id';
comment on column "config_info_tag"."group_id" is 'group_id';
comment on column "config_info_tag"."tenant_id" is 'tenant_id';
comment on column "config_info_tag"."tag_id" is 'tag_id';
comment on column "config_info_tag"."app_name" is 'app_name';
comment on column "config_info_tag"."content" is 'content';
comment on column "config_info_tag"."md5" is 'md5';
comment on column "config_info_tag"."gmt_create" is '创建时间';
comment on column "config_info_tag"."gmt_modified" is '修改时间';
comment on column "config_info_tag"."src_user" is 'source user';
comment on column "config_info_tag"."src_ip" is 'source ip';
comment on table "config_info_tag" is 'config_info_tag';

-- ----------------------------
-- records of config_info_tag
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for config_tags_relation
-- ----------------------------
drop table if exists "config_tags_relation";
create table "config_tags_relation" (
  "id" bigserial not null,
  "tag_name" varchar(128)  not null,
  "tag_type" varchar(64) ,
  "data_id" varchar(255)  not null,
  "group_id" varchar(128)  not null,
  "tenant_id" varchar(128) ,
  "nid" bigserial not null
)
;
comment on column "config_tags_relation"."id" is 'id';
comment on column "config_tags_relation"."tag_name" is 'tag_name';
comment on column "config_tags_relation"."tag_type" is 'tag_type';
comment on column "config_tags_relation"."data_id" is 'data_id';
comment on column "config_tags_relation"."group_id" is 'group_id';
comment on column "config_tags_relation"."tenant_id" is 'tenant_id';
comment on table "config_tags_relation" is 'config_tag_relation';

-- ----------------------------
-- records of config_tags_relation
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for group_capacity
-- ----------------------------
drop table if exists "group_capacity";
create table "group_capacity" (
  "id" bigserial not null,
  "group_id" varchar(128)  not null,
  "quota" int4 not null,
  "usage" int4 not null,
  "max_size" int4 not null,
  "max_aggr_count" int4 not null,
  "max_aggr_size" int4 not null,
  "max_history_count" int4 not null,
  "gmt_create" timestamp(6) not null,
  "gmt_modified" timestamp(6) not null
)
;
comment on column "group_capacity"."id" is '主键id';
comment on column "group_capacity"."group_id" is 'group id，空字符表示整个集群';
comment on column "group_capacity"."quota" is '配额，0表示使用默认值';
comment on column "group_capacity"."usage" is '使用量';
comment on column "group_capacity"."max_size" is '单个配置大小上限，单位为字节，0表示使用默认值';
comment on column "group_capacity"."max_aggr_count" is '聚合子配置最大个数，，0表示使用默认值';
comment on column "group_capacity"."max_aggr_size" is '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值';
comment on column "group_capacity"."max_history_count" is '最大变更历史数量';
comment on column "group_capacity"."gmt_create" is '创建时间';
comment on column "group_capacity"."gmt_modified" is '修改时间';
comment on table "group_capacity" is '集群、各group容量信息表';

-- ----------------------------
-- records of group_capacity
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for his_config_info
-- ----------------------------
drop table if exists "his_config_info";
create table "his_config_info" (
  "id" int8 not null,
  "nid" bigserial not null,
  "data_id" varchar(255)  not null,
  "group_id" varchar(128)  not null,
  "app_name" varchar(128) ,
  "content" text  not null,
  "md5" varchar(32) ,
  "gmt_create" timestamp(6) not null  default '2010-05-05 00:00:00',
  "gmt_modified" timestamp(6) not null,
  "src_user" text ,
  "src_ip" varchar(20) ,
  "op_type" char(10) ,
  "tenant_id" varchar(128) ,
  "encrypted_data_key" text  not null
)
;
comment on column "his_config_info"."app_name" is 'app_name';
comment on column "his_config_info"."tenant_id" is '租户字段';
comment on column "his_config_info"."encrypted_data_key" is '秘钥';
comment on table "his_config_info" is '多租户改造';


-- ----------------------------
-- table structure for permissions
-- ----------------------------
drop table if exists "permissions";
create table "permissions" (
  "role" varchar(50)  not null,
  "resource" varchar(512)  not null,
  "action" varchar(8)  not null
)
;

-- ----------------------------
-- records of permissions
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for roles
-- ----------------------------
drop table if exists "roles";
create table "roles" (
  "username" varchar(50)  not null,
  "role" varchar(50)  not null
)
;

-- ----------------------------
-- records of roles
-- ----------------------------
begin;
insert into "roles" values ('nacos', 'role_admin');
commit;

-- ----------------------------
-- table structure for tenant_capacity
-- ----------------------------
drop table if exists "tenant_capacity";
create table "tenant_capacity" (
  "id" bigserial not null,
  "tenant_id" varchar(128)  not null,
  "quota" int4 not null,
  "usage" int4 not null,
  "max_size" int4 not null,
  "max_aggr_count" int4 not null,
  "max_aggr_size" int4 not null,
  "max_history_count" int4 not null,
  "gmt_create" timestamp(6) not null,
  "gmt_modified" timestamp(6) not null
)
;
comment on column "tenant_capacity"."id" is '主键id';
comment on column "tenant_capacity"."tenant_id" is 'tenant id';
comment on column "tenant_capacity"."quota" is '配额，0表示使用默认值';
comment on column "tenant_capacity"."usage" is '使用量';
comment on column "tenant_capacity"."max_size" is '单个配置大小上限，单位为字节，0表示使用默认值';
comment on column "tenant_capacity"."max_aggr_count" is '聚合子配置最大个数';
comment on column "tenant_capacity"."max_aggr_size" is '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值';
comment on column "tenant_capacity"."max_history_count" is '最大变更历史数量';
comment on column "tenant_capacity"."gmt_create" is '创建时间';
comment on column "tenant_capacity"."gmt_modified" is '修改时间';
comment on table "tenant_capacity" is '租户容量信息表';

-- ----------------------------
-- records of tenant_capacity
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for tenant_info
-- ----------------------------
drop table if exists "tenant_info";
create table "tenant_info" (
  "id" bigserial not null,
  "kp" varchar(128)  not null,
  "tenant_id" varchar(128) ,
  "tenant_name" varchar(128) ,
  "tenant_desc" varchar(256) ,
  "create_source" varchar(32) ,
  "gmt_create" int8 not null,
  "gmt_modified" int8 not null
)
;
comment on column "tenant_info"."id" is 'id';
comment on column "tenant_info"."kp" is 'kp';
comment on column "tenant_info"."tenant_id" is 'tenant_id';
comment on column "tenant_info"."tenant_name" is 'tenant_name';
comment on column "tenant_info"."tenant_desc" is 'tenant_desc';
comment on column "tenant_info"."create_source" is 'create_source';
comment on column "tenant_info"."gmt_create" is '创建时间';
comment on column "tenant_info"."gmt_modified" is '修改时间';
comment on table "tenant_info" is 'tenant_info';

-- ----------------------------
-- records of tenant_info
-- ----------------------------
begin;
commit;

-- ----------------------------
-- table structure for users
-- ----------------------------
drop table if exists "users";
create table "users" (
  "username" varchar(50)  not null,
  "password" varchar(500)  not null,
  "enabled" boolean not null
)
;

-- ----------------------------
-- records of users
-- ----------------------------
begin;
insert into "users" values ('nacos', '$2a$10$euwpzhzz32djn7jexm34moeyirddfazm2kuwj7veojhhzkdrxfvuu', true);
commit;

-- ----------------------------
-- indexes structure for table config_info
-- ----------------------------
create unique index "uk_configinfo_datagrouptenant" on "config_info" ("data_id","group_id","tenant_id");

-- ----------------------------
-- primary key structure for table config_info
-- ----------------------------
alter table "config_info" add constraint "config_info_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table config_info_aggr
-- ----------------------------
create unique index "uk_configinfoaggr_datagrouptenantdatum" on "config_info_aggr" using btree ("data_id","group_id","tenant_id","datum_id");

-- ----------------------------
-- primary key structure for table config_info_aggr
-- ----------------------------
alter table "config_info_aggr" add constraint "config_info_aggr_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table config_info_beta
-- ----------------------------
create unique index "uk_configinfobeta_datagrouptenant" on "config_info_beta" using btree ("data_id","group_id","tenant_id");

-- ----------------------------
-- primary key structure for table config_info_beta
-- ----------------------------
alter table "config_info_beta" add constraint "config_info_beta_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table config_info_tag
-- ----------------------------
create unique index "uk_configinfotag_datagrouptenanttag" on "config_info_tag" using btree ("data_id","group_id","tenant_id","tag_id");

-- ----------------------------
-- primary key structure for table config_info_tag
-- ----------------------------
alter table "config_info_tag" add constraint "config_info_tag_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table config_tags_relation
-- ----------------------------
create index "idx_tenant_id" on "config_tags_relation" using btree (
  "tenant_id"
);
create unique index "uk_configtagrelation_configidtag" on "config_tags_relation" using btree (
  "id",
  "tag_name",
  "tag_type"
);

-- ----------------------------
-- primary key structure for table config_tags_relation
-- ----------------------------
alter table "config_tags_relation" add constraint "config_tags_relation_pkey" primary key ("nid");

-- ----------------------------
-- indexes structure for table group_capacity
-- ----------------------------
create unique index "uk_group_id" on "group_capacity" using btree (
  "group_id"
);

-- ----------------------------
-- primary key structure for table group_capacity
-- ----------------------------
alter table "group_capacity" add constraint "group_capacity_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table his_config_info
-- ----------------------------
create index "idx_did" on "his_config_info" using btree (
  "data_id"
);
create index "idx_gmt_create" on "his_config_info" using btree (
  "gmt_create"
);
create index "idx_gmt_modified" on "his_config_info" using btree (
  "gmt_modified"
);

-- ----------------------------
-- primary key structure for table his_config_info
-- ----------------------------
alter table "his_config_info" add constraint "his_config_info_pkey" primary key ("nid");

-- ----------------------------
-- indexes structure for table permissions
-- ----------------------------
create unique index "uk_role_permission" on "permissions" using btree (
  "role",
  "resource",
  "action"
);

-- ----------------------------
-- indexes structure for table roles
-- ----------------------------
create unique index "uk_username_role" on "roles" using btree (
  "username",
  "role"
);

-- ----------------------------
-- indexes structure for table tenant_capacity
-- ----------------------------
create unique index "uk_tenant_id" on "tenant_capacity" using btree (
  "tenant_id"
);

-- ----------------------------
-- primary key structure for table tenant_capacity
-- ----------------------------
alter table "tenant_capacity" add constraint "tenant_capacity_pkey" primary key ("id");

-- ----------------------------
-- indexes structure for table tenant_info
-- ----------------------------
create unique index "uk_tenant_info_kptenantid" on "tenant_info" using btree (
  "kp",
  "tenant_id"
);