Nacos 2.5.0 深度适配PostgreSQL全流程

最新推荐文章于 2025-07-04 16:41:55 发布

原创最新推荐文章于 2025-07-04 16:41:55 发布 · 1.6k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #java

1.依赖配置阶段

在项目根目录(nacos-all)的pom.xml中引入PostgreSQL驱动依赖

为nacos-config-plugin和nacos-persistence模块添加PostgreSQL相关依赖

2.数据源适配改造

修改nacos-datasource-plugin模块下的DataSourceConstant常量类，新增PostgreSQL相关常量

在impl目录下新建postgresql包，基于MySQL实现类复制改造：
- 重命名类后缀为Postgresql
- 调整SQL分页语法为PostgreSQL特有的OFFSET ? LIMIT ?格式
- 将where.limit方法改造为where.limitPG

3.持久层配置优化

在nacos-datasource-plugin模块下的resource/mapper目录下新增PostgreSQL专属的Mapper文件

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.ConfigInfoGrayMapperByPostgresql
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

nacos-persistence模块下修改PersistenceConstant类补充PostgreSQL支持

nacos-persistence模块下重构ExternalDataSourceProperties类：
新增driverName属性及setter方法
将静态的JDBC_DRIVER_NAME改为动态获取驱动名称

4.分页处理适配

在default-auth-plugin模块中：
- 扩展AuthPageConstant分页常量
- 新增PostgresqlPageHandlerAdapter（基于MySQL适配器改造）
- 在PageHandlerAdapterFactory中注册PostgreSQL处理器

5.打包

mvn clean -Prelease-nacos install -U -DskipTests -Drat.skip=true

打包成功后在distribution下可以找到打包好的文件

配置application.properties：
- 替换为实际的PostgreSQL连接信息
- 特别注意schema名称配置
调整start.cmd启动脚本参数

spring.datasource.platform=postgresql
db.num=1
db.url.0=jdbc:postgresql://localhost:54321/mydb?currentSchema=public&reWriteBatchedInserts=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
db.user.0=user
db.password.0=password

db.pool.config.driverClassName=org.postgresql.Driver

最后附赠PG建表语句，祝大家都成功！！！

-- 创建 config_info 表
CREATE TABLE config_info (
    id BIGSERIAL PRIMARY KEY,
    data_id VARCHAR(255) NOT NULL,
    group_id VARCHAR(128),
    content TEXT NOT NULL,
    md5 VARCHAR(32),
    gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    src_user TEXT,
    src_ip VARCHAR(50),
    app_name VARCHAR(128),
    tenant_id VARCHAR(128) DEFAULT '',
    c_desc VARCHAR(256),
    c_use VARCHAR(64),
    effect VARCHAR(64),
    type VARCHAR(64),
    c_schema TEXT,
    encrypted_data_key VARCHAR(1024) NOT NULL DEFAULT ''
);

-- 添加唯一约束
ALTER TABLE config_info ADD CONSTRAINT uk_configinfo_datagrouptenant 
UNIQUE (data_id, group_id, tenant_id);

-- 添加表注释
COMMENT ON TABLE config_info IS '配置信息表';

-- 添加列注释
COMMENT ON COLUMN config_info.id IS 'id';
COMMENT ON COLUMN config_info.data_id IS 'data_id';
COMMENT ON COLUMN config_info.group_id IS 'group_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.app_name IS 'app_name';
COMMENT ON COLUMN config_info.tenant_id IS '租户字段';
COMMENT ON COLUMN config_info.c_desc IS 'configuration description';
COMMENT ON COLUMN config_info.c_use IS 'configuration usage';
COMMENT ON COLUMN config_info.effect IS '配置生效的描述';
COMMENT ON COLUMN config_info.type IS '配置的类型';
COMMENT ON COLUMN config_info.c_schema IS '配置的模式';
COMMENT ON COLUMN config_info.encrypted_data_key IS '密钥';

-- 创建 config_info_gray 表
CREATE TABLE config_info_gray (
    id BIGSERIAL PRIMARY KEY,
    data_id VARCHAR(255) NOT NULL,
    group_id VARCHAR(128) NOT NULL,
    content TEXT NOT NULL,
    md5 VARCHAR(32),
    src_user TEXT,
    src_ip VARCHAR(100),
    gmt_create TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    app_name VARCHAR(128),
    tenant_id VARCHAR(128) DEFAULT '',
    gray_name VARCHAR(128) NOT NULL,
    gray_rule TEXT NOT NULL,
    encrypted_data_key VARCHAR(256) NOT NULL DEFAULT ''
);

-- 添加唯一约束
ALTER TABLE config_info_gray ADD CONSTRAINT uk_configinfogray_datagrouptenantgray 
UNIQUE (data_id, group_id, tenant_id, gray_name);

-- 添加索引
CREATE INDEX idx_dataid_gmt_modified ON config_info_gray (data_id, gmt_modified);
CREATE INDEX idx_gmt_modified ON config_info_gray (gmt_modified);

-- 添加表注释
COMMENT ON TABLE config_info_gray IS 'config_info_gray';

-- 添加列注释
COMMENT ON COLUMN config_info_gray.id IS 'id';
COMMENT ON COLUMN config_info_gray.data_id IS 'data_id';
COMMENT ON COLUMN config_info_gray.group_id IS 'group_id';
COMMENT ON COLUMN config_info_gray.content IS 'content';
COMMENT ON COLUMN config_info_gray.md5 IS 'md5';
COMMENT ON COLUMN config_info_gray.src_user IS 'src_user';
COMMENT ON COLUMN config_info_gray.src_ip IS 'src_ip';
COMMENT ON COLUMN config_info_gray.gmt_create IS 'gmt_create';
COMMENT ON COLUMN config_info_gray.gmt_modified IS 'gmt_modified';
COMMENT ON COLUMN config_info_gray.app_name IS 'app_name';
COMMENT ON COLUMN config_info_gray.tenant_id IS 'tenant_id';
COMMENT ON COLUMN config_info_gray.gray_name IS 'gray_name';
COMMENT ON COLUMN config_info_gray.gray_rule IS 'gray_rule';
COMMENT ON COLUMN config_info_gray.encrypted_data_key IS 'encrypted_data_key';
CREATE TABLE config_info_beta (
    id BIGSERIAL PRIMARY KEY,
    data_id VARCHAR(255) NOT NULL,
    group_id VARCHAR(128) NOT NULL,
    app_name VARCHAR(128) DEFAULT NULL,
    content TEXT NOT NULL,
    beta_ips VARCHAR(1024) DEFAULT NULL,
    md5 VARCHAR(32) DEFAULT NULL,
    gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    src_user TEXT,
    src_ip VARCHAR(50) DEFAULT NULL,
    tenant_id VARCHAR(128) DEFAULT '',
    encrypted_data_key VARCHAR(1024) NOT NULL DEFAULT ''
);

-- 添加唯一约束
ALTER TABLE config_info_beta ADD CONSTRAINT uk_configinfobeta_datagrouptenant 
UNIQUE (data_id, group_id, tenant_id);

-- 添加表注释
COMMENT ON TABLE config_info_beta IS 'config_info_beta';

-- 添加列注释
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 '密钥';
-- 创建表结构
CREATE TABLE config_info_tag (
    id BIGSERIAL PRIMARY KEY,
    data_id VARCHAR(255) NOT NULL,
    group_id VARCHAR(128) NOT NULL,
    tenant_id VARCHAR(128) DEFAULT '',
    tag_id VARCHAR(128) NOT NULL,
    app_name VARCHAR(128),
    content TEXT NOT NULL,
    md5 VARCHAR(32),
    gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    src_user TEXT,
    src_ip VARCHAR(50)
);

-- 添加唯一约束
ALTER TABLE config_info_tag ADD CONSTRAINT uk_configinfotag_datagrouptenanttag 
UNIQUE (data_id, group_id, tenant_id, tag_id);

-- 表注释
COMMENT ON TABLE config_info_tag IS 'config_info_tag';

-- 列注释
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';
-- 创建表结构
CREATE TABLE config_tags_relation (
    id BIGINT 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) DEFAULT '',
    nid BIGSERIAL
);

-- 设置主键
ALTER TABLE config_tags_relation ADD PRIMARY KEY (nid);

-- 添加唯一约束
ALTER TABLE config_tags_relation ADD CONSTRAINT uk_configtagrelation_configidtag 
UNIQUE (id, tag_name, tag_type);

-- 添加索引
CREATE INDEX idx_tenant_id ON config_tags_relation (tenant_id);

-- 表注释
COMMENT ON TABLE config_tags_relation IS 'config_tag_relation';

-- 列注释
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 COLUMN config_tags_relation.nid IS 'nid, 自增长标识';
-- 创建表结构
CREATE TABLE group_capacity (
    id BIGSERIAL PRIMARY KEY,
    group_id VARCHAR(128) NOT NULL DEFAULT '',
    quota INTEGER NOT NULL DEFAULT 0,
    usage INTEGER NOT NULL DEFAULT 0,
    max_size INTEGER NOT NULL DEFAULT 0,
    max_aggr_count INTEGER NOT NULL DEFAULT 0,
    max_aggr_size INTEGER NOT NULL DEFAULT 0,
    max_history_count INTEGER NOT NULL DEFAULT 0,
    gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加唯一约束
ALTER TABLE group_capacity ADD CONSTRAINT uk_group_id UNIQUE (group_id);

-- 表注释
COMMENT ON TABLE group_capacity IS '集群、各Group容量信息表';

-- 列注释
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 '修改时间';
-- 创建表结构
CREATE TABLE his_config_info (
    id BIGINT NOT NULL,
    nid BIGSERIAL,
    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 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    src_user TEXT,
    src_ip VARCHAR(50),
    op_type CHAR(10),
    tenant_id VARCHAR(128) DEFAULT '',
    encrypted_data_key VARCHAR(1024) NOT NULL DEFAULT '',
    publish_type VARCHAR(50) DEFAULT 'formal',
    gray_name VARCHAR(50),
    ext_info TEXT
);

-- 设置主键
ALTER TABLE his_config_info ADD PRIMARY KEY (nid);

-- 创建索引
CREATE INDEX idx_gmt_create ON his_config_info (gmt_create);
CREATE INDEX his_config_info_idx_gmt_modified ON his_config_info (gmt_modified);
CREATE INDEX idx_did ON his_config_info (data_id);

-- 表注释
COMMENT ON TABLE his_config_info IS '多租户改造';

-- 列注释
COMMENT ON COLUMN his_config_info.id IS 'id';
COMMENT ON COLUMN his_config_info.nid IS 'nid, 自增标识';
COMMENT ON COLUMN his_config_info.data_id IS 'data_id';
COMMENT ON COLUMN his_config_info.group_id IS 'group_id';
COMMENT ON COLUMN his_config_info.app_name IS 'app_name';
COMMENT ON COLUMN his_config_info.content IS 'content';
COMMENT ON COLUMN his_config_info.md5 IS 'md5';
COMMENT ON COLUMN his_config_info.gmt_create IS '创建时间';
COMMENT ON COLUMN his_config_info.gmt_modified IS '修改时间';
COMMENT ON COLUMN his_config_info.src_user IS 'source user';
COMMENT ON COLUMN his_config_info.src_ip IS 'source ip';
COMMENT ON COLUMN his_config_info.op_type IS 'operation type';
COMMENT ON COLUMN his_config_info.tenant_id IS '租户字段';
COMMENT ON COLUMN his_config_info.encrypted_data_key IS '密钥';
COMMENT ON COLUMN his_config_info.publish_type IS 'publish type gray or formal';
COMMENT ON COLUMN his_config_info.gray_name IS 'gray name';
COMMENT ON COLUMN his_config_info.ext_info IS 'ext info';
-- 创建表结构
CREATE TABLE tenant_capacity (
    id BIGSERIAL PRIMARY KEY,
    tenant_id VARCHAR(128) NOT NULL DEFAULT '',
    quota INTEGER NOT NULL DEFAULT 0,
    usage INTEGER NOT NULL DEFAULT 0,
    max_size INTEGER NOT NULL DEFAULT 0,
    max_aggr_count INTEGER NOT NULL DEFAULT 0,
    max_aggr_size INTEGER NOT NULL DEFAULT 0,
    max_history_count INTEGER NOT NULL DEFAULT 0,
    gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加唯一约束
ALTER TABLE tenant_capacity ADD CONSTRAINT uk_tenant_id UNIQUE (tenant_id);

-- 表注释
COMMENT ON TABLE tenant_capacity IS '租户容量信息表';

-- 列注释
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 '修改时间';

-- 创建表结构
CREATE TABLE tenant_info (
    id BIGSERIAL PRIMARY KEY,
    kp VARCHAR(128) NOT NULL,
    tenant_id VARCHAR(128) DEFAULT '',
    tenant_name VARCHAR(128) DEFAULT '',
    tenant_desc VARCHAR(256),
    create_source VARCHAR(32),
    gmt_create BIGINT NOT NULL,
    gmt_modified BIGINT NOT NULL
);

-- 添加唯一约束
ALTER TABLE tenant_info ADD CONSTRAINT uk_tenant_info_kptenantid UNIQUE (kp, tenant_id);

-- 添加索引
CREATE INDEX tenant_info_idx_tenant_id ON tenant_info (tenant_id);

-- 表注释
COMMENT ON TABLE tenant_info IS 'tenant_info';

-- 列注释
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 '修改时间';
-- 创建表结构
CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    password VARCHAR(500) NOT NULL,
    enabled BOOLEAN NOT NULL,
    PRIMARY KEY (username)
);

-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.username IS 'username';
COMMENT ON COLUMN users.password IS 'password';
COMMENT ON COLUMN users.enabled IS 'enabled';
-- 创建表结构
CREATE TABLE roles (
    username VARCHAR(50) NOT NULL,
    role VARCHAR(50) NOT NULL
);

-- 添加唯一索引
CREATE UNIQUE INDEX idx_user_role ON roles (username, role);

-- 添加列注释
COMMENT ON COLUMN roles.username IS 'username';
COMMENT ON COLUMN roles.role IS 'role';
-- 创建表结构
CREATE TABLE permissions (
    role VARCHAR(50) NOT NULL,
    resource VARCHAR(128) NOT NULL,
    action VARCHAR(8) NOT NULL
);

-- 添加唯一约束
ALTER TABLE permissions ADD CONSTRAINT uk_role_permission UNIQUE (role, resource, action);

-- 添加列注释
COMMENT ON COLUMN permissions.role IS 'role';
COMMENT ON COLUMN permissions.resource IS 'resource';
COMMENT ON COLUMN permissions.action IS 'action';