企业模式迁移EON【非MIGRATE_ENTERPRISE_TO_EON】

本文链接：https://blog.csdn.net/liquanaddnewer/article/details/126599962

1.备份企业模式数据

备份资源池，用户，权限等

将下面语句创建一个sql文件会自动生成多个不同sql 文件，一个一个在EON环境运行即可

--集群资源池备份
\o 1.create_resource_pools.sql
\echo --集群资源池备份
SELECT 'CREATE RESOURCE POOL ' || NAME
    || CASE WHEN MEMORYSIZE IS NULL THEN ' ' ELSE ' MEMORYSIZE ' || '''' || MEMORYSIZE || '''' END
    || CASE WHEN MAXMEMORYSIZE = '' THEN ' ' ELSE ' MAXMEMORYSIZE ' || '''' || MAXMEMORYSIZE || '''' END
    || CASE WHEN EXECUTIONPARALLELISM = 'AUTO' THEN ' ' ELSE ' EXECUTIONPARALLELISM ' || EXECUTIONPARALLELISM END
    || CASE WHEN NULLIFZERO(PRIORITY) IS NULL THEN ' ' ELSE ' PRIORITY ' || PRIORITY END
    || CASE WHEN RUNTIMEPRIORITY IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY ' || RUNTIMEPRIORITY END
    || CASE WHEN RUNTIMEPRIORITYTHRESHOLD  IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD '   || RUNTIMEPRIORITYTHRESHOLD END
    || CASE WHEN QUEUETIMEOUT IS NULL THEN ' ' ELSE ' QUEUETIMEOUT ''' || QUEUETIMEOUT ||'''' END
    || CASE WHEN MAXCONCURRENCY IS NULL THEN ' ' ELSE ' MAXCONCURRENCY ' || MAXCONCURRENCY END
    || CASE WHEN PLANNEDCONCURRENCY IS NULL THEN ' ' ELSE ' PLANNEDCONCURRENCY ' || PLANNEDCONCURRENCY END
    || CASE WHEN RUNTIMECAP IS NULL THEN ' ' ELSE ' RUNTIMECAP ' || '''' || RUNTIMECAP || '''' END
    || CASE WHEN SINGLEINITIATOR IS TRUE THEN ' SINGLEINITIATOR TRUE' ELSE ' SINGLEINITIATOR FALSE' END
    || CASE WHEN CASCADETO IS NULL THEN ' ' ELSE ' CASCADE TO ' || CASCADETO END
    || ';'
FROM V_CATALOG.RESOURCE_POOLS
WHERE NOT IS_INTERNAL
ORDER BY NAME;

--角色备份
\o 2.create_roles.sql
\echo --角色备份
SELECT 'CREATE ROLE ' || name || ';' AS CREATE_ROLE_TXT
FROM v_catalog.roles
WHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser','sysmonitor')
ORDER BY 1;

--用户备份
\o 3.create_users.sql
\echo --用户备份
SELECT 'CREATE USER ' || user_name
    || case when is_locked then ' ACCOUNT LOCK' WHEN NOT is_locked then ' ACCOUNT UNLOCK' END
    || case when grace_period='undefined' then '' else ' GRACEPERIOD ' || grace_period end
    || ' IDENTIFIED BY ' || '''PASSWORD'''
    || case when idle_session_timeout='unlimited' then '' else ' IDLESESSIONTIMEOUT ' || idle_session_timeout end
    || case when max_connections='unlimited' then '' else ' MAXCONNECTIONS ' || max_connections end
    || case when memory_cap_kb='unlimited' then '' else ' MEMORYCAP ' || memory_cap_kb end
    || ' PASSWORD EXPIRE '
    || ' PROFILE ' || profile_name
    || case when run_time_cap='unlimited' then '' else ' RUNTIMECAP ' || run_time_cap end
    || ' SEARCH_PATH ' || search_path
    || case when temp_space_cap_kb='unlimited' then '' else ' TEMPSPACECAP ' || temp_space_cap_kb end
    || ';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--用户角色赋权备份
\o 4.grant_roles2user.sql
\echo --用户角色赋权备份
SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
and all_roles <> ''
ORDER BY 1;

--用户默认角色备份
\o 5.alter_user_default_role.sql
\echo --用户默认角色备份
SELECT 'ALTER USER ' || user_name 
    || ' DEFAULT ROLE '|| default_roles 
    ||';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
and default_roles <> ''
ORDER BY 1;

--用户默认资源池备份
\o 6.alter_user_default_resourcepool.sql
\echo --用户默认资源池备份
SELECT 'ALTER USER ' || user_name 
    || ' RESOURCE POOL '|| resource_pool 
    ||';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--数据库对象备份
\o 7.create_objects.sql
\echo --数据库对象备份
--导出schemas, 表, 约束, 视图, projections
SELECT export_objects('','');

--认证方式
\o 8.create_authentication.sql
\echo --认证方式
select 'create authentication ' || auth_name || ' method ''' || auth_method || ''' host ''' || auth_host_address || ''';' from client_auth;

select 
case 
when is_auth_enabled = 'True'
then 'alter authentication ' || auth_name || ' enable;' 
when is_auth_enabled = 'False'
then 'alter authentication ' || auth_name || ' disable;'
end alter_authentication
from client_auth;

select 'alter authentication ' || auth_name || ' priority ' || auth_priority ||';' from client_auth;

--权限备份
\o 9.grants.sql
\echo --权限备份
SELECT
    CASE
        WHEN object_type = 'ROLE'
        THEN 'GRANT ' || object_name || ' TO '|| grantee||';'
        WHEN object_type = 'SCHEMA'
        THEN 'GRANT '|| privileges_description || ' ON SCHEMA ' || object_name || ' TO '|| grantee||';'
        WHEN object_type = 'RESOURCEPOOL'
        THEN 'GRANT '|| privileges_description || ' ON RESOURCE POOL ' || object_name || ' TO '|| grantee||';'
        WHEN object_type = 'TABLE'
        THEN 'GRANT '|| privileges_description || ' ON TABLE ' || object_schema || '.' || object_name || ' TO '|| grantee||';'
        WHEN object_type = 'CLIENTAUTHENTICATION'
        THEN 'GRANT AUTHENTICATION '||object_name||' TO '||grantee||';'
        WHEN object_type = 'SEQUENCE'
        THEN 'GRANT '|| privileges_description ||' ON SEQUENCE '||object_schema||'.'||object_name||' TO '||grantee||';'
        WHEN object_type = 'VIEW'
        THEN 'GRANT '|| privileges_description ||' ON '||object_schema||'.'||object_name||' TO '||grantee||';'
    END TXT
 FROM grants 
WHERE grantor<>grantee 
  AND object_type not in ('PROCEDURE','DATABASE','STORAGELOCATION')
ORDER BY object_type, object_name;

--修改schema的owner
\o 10.alter_schema_owner.sql
\echo --修改schema的owner
select 'alter schema '||schema_name||' owner to '||schema_owner||' cascade;' from schemata where not is_system_schema;

--修改表的owner
\o 11.alter_table_owner.sql
\echo --修改表的owner
select 'alter table '||table_schema||'.'||table_name||' owner to '||owner_name||';' from tables where not is_system_table ;

--访问策略备份
\o 12.create_access_policy.sql
\echo --访问策略备份
select 
case when upper(policy_type) = 'COLUMN POLICY' 
     then 'CREATE ACCESS POLICY ON ' || table_name || ' FOR COLUMN ' || column_name || ' ' || expression || ' ' || REPLACE(is_policy_enabled, 'abled', 'able')||';'
     when upper(policy_type) = 'ROW POLICY' 
     then 'CREATE ACCESS POLICY ON ' || table_name || ' FOR ROWS WHERE ' || expression || ' ' || REPLACE(is_policy_enabled, 'abled', 'able')||';'
end TXT
 from v_catalog.access_policy 
order by table_name;

--storage location
\o 13.storage_location.sql
\echo --storage location
select * from storage_locations;
SELECT node_name, projection_name, location_label FROM v_monitor.storage_containers;
select * from v_monitor.storage_policies;


-- \o 14.迁移数用户和口令: Migrate user/password between databases:
-- \echo --Migrate user/password between databases:
-- SELECT 'ALTER USER ' || user_name || ' IDENTIFIED BY ''' || password || '''' || ' SALT ''' || salt || '''' || ';' 
--   FROM passwords 
--  WHERE password <> '' 
--    AND user_name <> 'dbadmin' ORDER BY 1 ;

备份表结构及projections

将下面sql 结果保存成sql文件用作EON创建表结构及projection

select export_catalog()

2.安装eon模式集群

安装依赖（所有节点上运行）

yum install -y dialog gdb mcelog sysstat

设置时区（所有节点上运行）

yum update -y tzdata
echo 'export TZ="Asia/Shanghai"' >>/etc/profile
source /etc/profile

创建HOME目录（所有节点上运行）

mkdir /data/home

关闭防火墙（所有节点上运行）

systemctl mask firewalld
systemctl disable firewalld
systemctl stop firewalld

准备工作（各个节点都运行）

vi /etc/selinux/config
SELINUX=disabled
setenforce 0
vim /etc/sysctl.conf
vm.swappiness = 1
echo 1 > /proc/sys/vm/swappiness

安装vertica一个节点运行（安装之前确保删除干净vertia）

/opt/vertica/sbin/install_vertica --hosts 192.168.21.1 --rpm /data/vertica-11.0.2-7.x86_64.RHEL6.rpm -T -d /data/home/dbadmin --failure-threshold=NONE

创建共享存储配置文件

vim ~/oss.conf
awsauth = key:secret
awsendpoint = oss-cn-zhangjiakou-internal.aliyuncs.com 
s3enablevirtualaddressing = 1

创建数据库

admintools --debug -t create_db -x ~/oss.conf --communal-storage-location=s3://bucket/ -D /data/home/vertica/ -c /data/home/vertica/ --depot-path=/data/home/vertica/ --shard-count=3 -s 172.16.222.90 -d databaseName --force-removal-at-creation --force-cleanup-on-failure

集群中增加节点

/opt/vertica/sbin/update_vertica --add-hosts 192.168.21.3 --rpm /opt/vertica-11.0.2-2.x86_64.RHEL6.rpm --failure-threshold=NONE

扩节点：

/opt/vertica/sbin/update_vertica --add-hosts 192.168.21.4 --rpm /opt/vertica-11.0.2-2.x86_64.RHEL6.rpm --dba-user dba账号 -l /data/home/dbadmin -p 密码  --failure-threshold=NONE

节点创建目录：

mkdir /data/home/vertica
chown -R verticadba:verticadba /data/home/vertica

remove节点

/opt/vertica/sbin/install_vertica --point-to-point  --remove-hosts  192.168.21.4  --rpm /opt/vertica-11.0.2-2.x86_64.RHEL6.rpm

迁移表结构

select export_catalog()执行这个运行完的结果

vsql -w密码 -f 文件.sql

迁移用户及权限

多个sql文件都运行一遍

vsql -w密码 -f 1.create_resource_pools.sql

俩库之前迁移数据

在企业模式运行

CONNECT TO VERTICA EON数据库 USER   xxx     PASSWORD 'xxx' ON 'EONip', 5433;

EXPORT TO VERTICA EON数据库.表名称 AS     SELECT *  from  企业表名称;

EXPORT TO VERTICA EON数据库.表名称 AS SELECT     * from 企业模式表 where 天=20180611 and 小时=0;

DISCONNECT EON数据库;