# 正式环境部署 ## 安装gp客户端 **top用户信息是通过gpload到数据库的，是在192.168.70.125上安装gp客户端还是把同步top用户信息的代码抽出来部署到192.168.70.130？** 把同步top用户信息的代码抽出来部署到192.168.70.130 ## 投诉web入口前端部署位置 70.125 要加白名单 ## 安装minio **minio 部署位置？** 10.7 **操作日志和登录日志导出文件永久保存还是过期删除？** 14天过期删除 ```sh docker load < minio docker run \ --name minio \ --restart=always \ -p 19000:19000 \ -p 19001:19001 \ -v minio-data:/data \ -v /etc/timezone:/etc/timezone:ro \ -v /etc/localtime:/etc/localtime:ro \ -e "MINIO_ROOT_USER=admin" \ -e "MINIO_ROOT_PASSWORD=8#tGE3Y)ZTPinQCbX,ZY" \ -d minio/minio:RELEASE.2022-12-12T19-27-27Z server /data --address ":19000" --console-address ":19001" ``` nginx配置 ```nginx #新页面（投诉web入口） server { listen 12041 ssl; server_name 192.168.70.125; #listen 443 ssl; ssl_certificate /usr/local/ssl/server-nopassword.crt; ssl_certificate_key /usr/local/ssl/server-nopassword.key; ssl_ciphers AESGCM:ALL:!DH:!EXPORT:!RC4:+HIGH:!MEDIUM:!LOW:!aNULL:!eNULL; ssl_prefer_server_ciphers on; ssl_protocols TLSv1 TLSv1.1 TLSv1.2; # 页面 location / { root /data/doweb_new/dist; index index.html index.htm; } # 后端 location /api { proxy_buffer_size 1024k; proxy_buffers 16 1024k; proxy_busy_buffers_size 2048k; proxy_temp_file_write_size 10m; #rewrite ^/api/(.*)$ /$1 break; proxy_pass http://192.168.70.125:12128; } # minio location /oss { proxy_pass http://ip:19000; proxy_http_version 1.1; proxy_redirect off; proxy_set_header Host $http_host; proxy_set_header X-Forwarded-Proto $scheme; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; } error_page 500 502 503 504 /50x.html; location = /50x.html { root html; } } ``` 文件桶访问策略配置，public文件夹公共读 ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": [ "*" ] }, "Action": [ "s3:GetObject" ], "Resource": [ "arn:aws:s3:::oss-acl-tousu-prod/public/*" ] } ] } ``` ## 停止程序 ```shell sh stop.sh ``` ## 备份数据库 ```sql create table sqmdb_rpt.acl_area_bak as select * from sqmdb_rpt.acl_area; create table sqmdb_rpt.acl_function_bak as select * from sqmdb_rpt.acl_function; create table sqmdb_rpt.acl_role_bak as select * from sqmdb_rpt.acl_role; create table backups.acl_user_202310311520 as select * from sqmdb_rpt.acl_user; create table backups.acl_user_function_202310311520 as select * from sqmdb_rpt.acl_user_function; create table backups.acl_user_role_city_202310311520 as select * from sqmdb_rpt.acl_user_role_city; create table sqmdb_rpt.acl_verification_log_bak as select * from sqmdb_rpt.acl_verification_log; ``` ## 更新数据库 ```sql -- 创建操作日志表 CREATE TABLE sqmdb_rpt.acl_operation_log ( id bigserial NOT NULL, -- 主键 operation_time timestamp NOT NULL, -- 操作时间 operator_id int4 NOT NULL, -- 操作人id operator_name varchar NOT NULL, -- 操作人姓名 operator_account varchar NOT NULL, -- 操作人账号 operation_name varchar NOT NULL, -- 操作名称 operation_content varchar NOT NULL, -- 操作内容 CONSTRAINT acl_operation_log_pk PRIMARY KEY (id) ); COMMENT ON TABLE sqmdb_rpt.acl_operation_log IS '操作日志'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.id IS '主键'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operation_time IS '操作时间'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operator_id IS '操作人id'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operator_name IS '操作人姓名'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operator_account IS '操作人账号'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operation_name IS '操作名称'; COMMENT ON COLUMN sqmdb_rpt.acl_operation_log.operation_content IS '操作内容'; ``` **公告内容怎么填？** 先空着 ```sql -- 创建公告表 CREATE TABLE sqmdb_rpt.acl_notice ( id serial4 NOT NULL, -- 主键 create_time date NOT NULL, -- 创建时间 "content" varchar NOT NULL, -- 内容 CONSTRAINT acl_notice_pk PRIMARY KEY (id) ); COMMENT ON TABLE sqmdb_rpt.acl_notice IS '公告'; COMMENT ON COLUMN sqmdb_rpt.acl_notice.id IS '主键'; COMMENT ON COLUMN sqmdb_rpt.acl_notice.create_time IS '创建时间'; COMMENT ON COLUMN sqmdb_rpt.acl_notice."content" IS '内容'; -- 插入公告 INSERT INTO sqmdb_rpt.acl_notice (create_time,"content") VALUES ('2023-01-01','公告1'), ('2023-01-02','公告2'), ('2023-01-03','公告3'), ('2023-01-04','公告4'), ('2023-01-05','公告5'), ('2023-01-06','公告6'); ``` ```sql -- 创建客户端表 CREATE TABLE sqmdb_rpt.acl_client ( client int4 NOT NULL, -- 主键 client_name varchar NOT NULL, -- 客户端名称 CONSTRAINT acl_client_pk PRIMARY KEY (client) ); COMMENT ON TABLE sqmdb_rpt.acl_client IS '客户端'; COMMENT ON COLUMN sqmdb_rpt.acl_client.client IS '主键'; COMMENT ON COLUMN sqmdb_rpt.acl_client.client_name IS '客户端名称'; -- 插入客户端 INSERT INTO sqmdb_rpt.acl_client (client,client_name) VALUES (0,'所有'), (1,'app'), (2,'web'); ``` ```sql -- 创建系统表 CREATE TABLE sqmdb_rpt.acl_system ( "system" varchar NOT NULL, -- system id system_name varchar NOT NULL, -- system中文名称 CONSTRAINT acl_system_pk PRIMARY KEY (system) ); COMMENT ON TABLE sqmdb_rpt.acl_system IS 'acl系统'; COMMENT ON COLUMN sqmdb_rpt.acl_system."system" IS 'system id'; COMMENT ON COLUMN sqmdb_rpt.acl_system.system_name IS 'system中文名称'; -- 插入系统 INSERT INTO sqmdb_rpt.acl_system ("system",system_name) VALUES ('flow','流程'), ('heidianku','黑点库'), ('fenxi','投诉分析'), ('yuce','投诉预测'), ('daping','投诉大屏'), ('permission','权限'), ('order_manage','工单处理'), ('app','app'), ('web','web'), ('luyin','工单录音'), ('volte_t2','volte语音分析'), ('order_query','工单查询'), ('order_reply','工单答复'); ``` ```sql -- 创建top用户信息表 CREATE TABLE sqmdb_rpt.acl_top_user ( login_name varchar NOT NULL, -- 账号 org_id int8 NULL, -- 部门id org_name varchar NULL, -- 部门名称 user_id varchar NULL, -- 人员id user_name varchar NULL, -- 姓名 phone varchar NULL, -- 电话 employee_code varchar NULL, -- 人员编码 province_id int4 NULL, -- 省份id city_id int4 NULL, -- 地市id area_id int4 NULL, -- 区县id CONSTRAINT acl_top_user_pk PRIMARY KEY (login_name) ); COMMENT ON TABLE sqmdb_rpt.acl_top_user IS 'top用户'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.login_name IS '账号'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.org_id IS '部门id'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.org_name IS '部门名称'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.user_id IS '人员id'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.user_name IS '姓名'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.phone IS '电话'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.employee_code IS '人员编码'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.province_id IS '省份id'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.city_id IS '地市id'; COMMENT ON COLUMN sqmdb_rpt.acl_top_user.area_id IS '区县id'; ``` **function_description怎么填？** ```sql -- 删除模块表 DROP TABLE sqmdb_rpt.acl_function; -- 创建模块表 CREATE TABLE sqmdb_rpt.acl_function ( id serial4 NOT NULL, -- 主键 "name" varchar NOT NULL, -- 功能名称 "system" varchar NOT NULL, -- 系统模块 client int4 NOT NULL, -- 展示的客户端 app_url varchar NOT NULL DEFAULT ''::character varying, -- app链接 app_icon varchar NOT NULL DEFAULT ''::character varying, -- app图标链接 app_priority int4 NOT NULL DEFAULT 0, -- app显示优先级，越大越靠前 web_url varchar NOT NULL DEFAULT ''::character varying, -- web链接 web_icon varchar NOT NULL DEFAULT ''::character varying, -- web图标链接 web_priority int4 NOT NULL DEFAULT 0, -- web显示优先级，越大越靠前 function_description varchar NOT NULL DEFAULT ''::character varying, -- 功能说明 CONSTRAINT acl_function_pk PRIMARY KEY (id) ); COMMENT ON TABLE sqmdb_rpt.acl_function IS '功能'; COMMENT ON COLUMN sqmdb_rpt.acl_function.id IS '主键'; COMMENT ON COLUMN sqmdb_rpt.acl_function."name" IS '功能名称'; COMMENT ON COLUMN sqmdb_rpt.acl_function."system" IS '系统模块'; COMMENT ON COLUMN sqmdb_rpt.acl_function.client IS '展示的客户端'; COMMENT ON COLUMN sqmdb_rpt.acl_function.app_url IS 'app链接'; COMMENT ON COLUMN sqmdb_rpt.acl_function.app_icon IS 'app图标链接'; COMMENT ON COLUMN sqmdb_rpt.acl_function.app_priority IS 'app显示优先级，越大越靠前'; COMMENT ON COLUMN sqmdb_rpt.acl_function.web_url IS 'web链接'; COMMENT ON COLUMN sqmdb_rpt.acl_function.web_icon IS 'web图标链接'; COMMENT ON COLUMN sqmdb_rpt.acl_function.web_priority IS 'web显示优先级，越大越靠前'; COMMENT ON COLUMN sqmdb_rpt.acl_function.function_description IS '功能说明'; -- 插入模块 INSERT INTO sqmdb_rpt.acl_function (id, "name","system",client,app_url,app_icon,app_priority,web_url,web_icon,web_priority,function_description) VALUES (-1,'权限和日志管理','permission',2,'','',0,'/permissions/role','/public/images/permission.png',9999,''), (1,'工单处理','flow',0,'/pages/index/index?type=dispose','icon1',88,'https://133.96.94.108:8081/ease-flow-console-v2/#/taskmanagement','/public/images/order-manage.png',88,'描述1'), (2,'工单查询','flow',0,'/pages/index/index?type=query','icon2',77,'https://133.96.94.108:8081/ease-flow-console-v2/#/orderSearch','/public/images/order-query.png',77,'描述2'), (3,'工单答复','flow',0,'/pages/index/index?type=reply','icon3',99,'https://133.96.94.108:8081/ease-flow-console-v2/#/customerResponse','/public/images/order-reply.png',99,''), (4,'黑点库','heidianku',2,'','',0,'http://133.96.94.108:12129','/public/images/heidianku.png',0,''), (5,'投诉大屏','daping',-1,'','',0,'http://133.96.94.108:12011','',0,''), (6,'投诉分析','fenxi',2,'','',0,'https://133.96.94.108:12031/service2/optimization/mainAction','/public/images/fenxi.png',0,''), (7,'投诉预测','yuce',2,'','',0,'https://133.96.94.108:12001','/public/images/yuce.png',0,''), (8,'volte语音分析','volte_t2',2,'','',0,'https://133.96.94.108:8288','/public/images/fenxi.png',0,''), (9,'工单录音','luyin',2,'','',0,'https://133.96.94.108:8081/ease-flow-console-v2/#/recording','/public/images/fenxi.png',0,''); ``` **role_description怎么填？** ```sql -- 删除角色表 DROP TABLE sqmdb_rpt.acl_role; -- 创建角色表 CREATE TABLE sqmdb_rpt.acl_role ( role_id serial4 NOT NULL, -- 角色id role_name varchar NULL, -- 角色名称 "system" varchar NULL, -- 系统 function_id int4 NULL, -- 功能id role_description varchar NULL DEFAULT ''::character varying, -- 角色说明 CONSTRAINT role_pk PRIMARY KEY (role_id) ); COMMENT ON TABLE sqmdb_rpt.acl_role IS '角色'; COMMENT ON COLUMN sqmdb_rpt.acl_role.role_id IS '角色id'; COMMENT ON COLUMN sqmdb_rpt.acl_role.role_name IS '角色名称'; COMMENT ON COLUMN sqmdb_rpt.acl_role."system" IS '系统'; COMMENT ON COLUMN sqmdb_rpt.acl_role.function_id IS '功能id'; COMMENT ON COLUMN sqmdb_rpt.acl_role.role_description IS '角色说明'; -- 插入角色 INSERT INTO sqmdb_rpt.acl_role (role_id,role_name,"system",function_id,role_description) VALUES (-1,'权限和日志管理','permission',-1,''), (3,'工单查询','flow',2,''), (4,'工单处理(投诉分析)','flow',1,''), (5,'工单处理(优化处理)','flow',1,''), (6,'工单处理(维护处理)','flow',1,''), (7,'工单处理(建设处理)','flow',1,''), (8,'工单处理(归档审核)','flow',1,''), (12,'工单答复','flow',3,''), (13,'工单处理(处理核查)','flow',1,''), (14,'黑点库管理','heidianku',4,''), (15,'黑点库查询','heidianku',4,''), (16,'投诉分析','fenxi',6,'描述'), (17,'投诉预测','yuce',7,'描述2'), (18,'投诉大屏','daping',5,''), (19,'volte语音分析','volte_t2',8,''), (20,'工单录音','flow',9,''); ``` ```sql -- 添加用户逻辑删除字段，默认0未删除 ALTER TABLE sqmdb_rpt.acl_user ADD deleted int4 NOT NULL DEFAULT 0; COMMENT ON COLUMN sqmdb_rpt.acl_user.deleted IS '是否已删除'; -- 添加是否测试用户字段，默认0非测试用户 ALTER TABLE sqmdb_rpt.acl_user ADD test_user int4 NOT NULL DEFAULT 0; COMMENT ON COLUMN sqmdb_rpt.acl_user.test_user IS '是否测试用户'; -- 标记测试用户 update sqmdb_rpt.acl_user set test_user = 1 where login_name like 'test%' or user_id = 1; -- 给test_hebei添加管理权限 INSERT INTO sqmdb_rpt.acl_user_role_city (user_id,role_id,city_id) VALUES (10,-1,-1); ``` ```sql -- 清空用户功能入口表 TRUNCATE TABLE sqmdb_rpt.acl_user_function CONTINUE IDENTITY RESTRICT; -- 根据用户角色插入功能入口 insert into sqmdb_rpt.acl_user_function (user_id, function_id) select distinct aurc.user_id, ar.function_id from sqmdb_rpt.acl_user_role_city aurc inner join sqmdb_rpt.acl_role ar on aurc.role_id = ar.role_id inner join sqmdb_rpt.acl_function af on ar.function_id = af.id order by aurc.user_id, ar.function_id; ``` ## 更新jar 部署位置：192.168.70.125/data1/acl ```sh sh update.sh ``` ## 回滚 数据库回滚 ```sql alter table sqmdb_rpt.acl_area rename to acl_area_back; alter table sqmdb_rpt.acl_function rename to acl_function_back; alter table sqmdb_rpt.acl_role rename to acl_role_back; alter table sqmdb_rpt.acl_user rename to acl_user_back; alter table sqmdb_rpt.acl_user_function rename to acl_user_function_back; alter table sqmdb_rpt.acl_user_role_city rename to acl_user_role_city_back; alter table sqmdb_rpt.acl_verification_log rename to acl_verification_log_back; alter table sqmdb_rpt.acl_area_bak rename to acl_area; alter table sqmdb_rpt.acl_function_bak rename to acl_function; alter table sqmdb_rpt.acl_role_bak rename to acl_role; alter table sqmdb_rpt.acl_user_bak rename to acl_user; alter table sqmdb_rpt.acl_user_function_bak rename to acl_user_function; alter table sqmdb_rpt.acl_user_role_city_bak rename to acl_user_role_city; alter table sqmdb_rpt.acl_verification_log_bak rename to acl_verification_log; ``` jar回滚 ```sh sh rollback.sh ```