Nacos 2.3.2的PostgreSQL版本

亚林瓜子

于 2024-08-06 01:08:37 发布

阅读量137

点赞数 2

文章标签： postgresql 数据库 nacos

欢迎流通功德无量

本文链接：https://blog.csdn.net/fxtxz2/article/details/140939006

版权

问题

最近需要让Nacos2.3.2使用PostgreSQL数据库进行存储。

PostgreSQL

# 数据库
create database nacos;

初始化数据库ddl：

create table config_info
(
    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,
    id                 bigserial
        primary key
);

comment on table config_info is 'config_info';

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 '秘钥';


create unique index uk_configinfo_datagrouptenant
    on config_info (data_id, group_id, tenant_id);

create table config_info_aggr
(
    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),
    id           bigserial
        primary key
);

comment on table config_info_aggr is '增加租户字段';

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 '租户字段';


create unique index uk_configinfoaggr_datagrouptenantdatum
    on config_info_aggr (data_id, group_id, tenant_id, datum_id);

create table config_info_beta
(
    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,
    id                 bigserial
        primary key
);

comment on table config_info_beta is 'config_info_beta';

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 unique index uk_configinfobeta_datagrouptenant
    on config_info_beta (data_id, group_id, tenant_id);

create table config_info_tag
(
    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),
    id           bigserial
        primary key
);

comment on table config_info_tag is 'config_info_tag';

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 unique index uk_configinfotag_datagrouptenanttag
    on config_info_tag (data_id, group_id, tenant_id, tag_id);

create table config_tags_relation
(
    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),
    id        bigserial,
    nid       bigserial
        primary key
);

comment on table config_tags_relation is 'config_tag_relation';

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';

create index idx_tenant_id
    on config_tags_relation (tenant_id);

create table group_capacity
(
    group_id          varchar(128) not null,
    quota             bigint       not null,
    usage             bigint       not null,
    max_size          bigint       not null,
    max_aggr_count    bigint       not null,
    max_aggr_size     bigint       not null,
    max_history_count bigint       not null,
    gmt_create        timestamp(6) not null,
    gmt_modified      timestamp(6) not null,
    id                bigserial
        primary key
);

comment on table group_capacity is '集群、各Group容量信息表';

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 unique index uk_group_id
    on group_capacity (group_id);

create table his_config_info
(
    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) default '2010-05-05 00:00:00'::timestamp without time zone not null,
    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,
    id                 bigserial,
    nid                bigserial
        primary key
);

comment on table his_config_info is '多租户改造';

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 '秘钥';

create table permissions
(
    role     varchar(50)  not null,
    resource varchar(255) not null,
    action   varchar(8)   not null
);

create unique index uk_role_permission
    on permissions (role, resource, action);

create table roles
(
    username varchar(50) not null,
    role     varchar(50) not null
);

create unique index idx_user_role
    on roles (username, role);

create table tenant_capacity
(
    tenant_id         varchar(128) not null,
    quota             bigint       not null,
    usage             bigint       not null,
    max_size          bigint       not null,
    max_aggr_count    bigint       not null,
    max_aggr_size     bigint       not null,
    max_history_count bigint       not null,
    gmt_create        timestamp(6) not null,
    gmt_modified      timestamp(6) not null,
    id                bigserial
        primary key
);

comment on table tenant_capacity is '租户容量信息表';

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 unique index uk_tenant_id
    on tenant_capacity (tenant_id);

create table tenant_info
(
    kp            varchar(128) not null,
    tenant_id     varchar(128),
    tenant_name   varchar(128),
    tenant_desc   varchar(256),
    create_source varchar(32),
    gmt_create    bigint       not null,
    gmt_modified  bigint       not null,
    id            bigserial
        primary key
);

comment on table tenant_info is 'tenant_info';

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 unique index uk_tenant_info_kptenantid
    on tenant_info (kp, tenant_id);

create table users
(
    username varchar(50)  not null,
    password varchar(500) not null,
    enabled  smallint     not null
);

默认nacos管理员账号，初始化sql：

INSERT INTO public.users (username, password, enabled) VALUES ('nacos', '$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu', 1);

INSERT INTO public.roles (username, role) VALUES ('nacos', 'ROLE_ADMIN');