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数据库;