Vertica的这些事<十五>—— Vertica备份元数据信息
来源:互联网 发布:淘宝网餐桌 编辑:程序博客网 时间:2024/06/05 07:59
—备份资源池
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 runtimecap IS NULL THEN ' ' ELSE ' RUNTIMECAP ' || '''' || runtimecap || '''' END || ' ; 'FROM v_catalog.resource_poolsWHERE NOT is_internalORDER BY name;
—备份角色
SELECT '-- Create Roles';SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CRFROM v_catalog.rolesWHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser')ORDER BY 1;
SELECT '-- Add users to roles';SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';'FROM v_catalog.usersWHERE user_name NOT IN ('dbadmin')ORDER BY 1;
–备份schema
SELECT '-- Create Schema';SELECT 'CREATE SCHEMA ' || schema_name || ';'FROM schemataWHERE schema_name NOT IN ('v_internal','v_catalog','v_monitor','TxtIndex')ORDER BY 1;
–备份用户
SELECT '-- Create Users';SELECT 'CREATE USER ' || user_name || ' RESOURCE POOL ' || resource_pool || ' ;'FROM v_catalog.usersWHERE user_name NOT IN ('dbadmin')ORDER BY 1;
—各手shcema大小
SELECT /*+(estimated_raw_size)*/ pj.anchor_table_schema, pj.used_compressed_gb, pj.used_compressed_gb * la.ratio AS raw_estimate_gbFROM (SELECT ps.anchor_table_schema, SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb FROM v_catalog.projections p JOIN v_monitor.projection_storage ps ON ps.projection_id = p.projection_id WHERE p.is_super_projection = 't' GROUP BY ps.anchor_table_schema) pj CROSS JOIN (SELECT (SELECT database_size_bytes FROM v_catalog.license_audits ORDER BY audit_start_timestamp DESC LIMIT 1) / (SELECT SUM(used_bytes) FROM V_MONITOR.projection_storage) AS ratio) laORDER BY pj.used_compressed_gb DESC;
–备份赋权语句
–backup grants
select 'grant '|| privileges_description || ' on '|| object_name || ' to '|| grantee||';' from grants where grantor<>grantee order by object_name;
备份建表语句以及schema语句
SELECT EXPORT_CATALOG('','DESIGN_ALL')"
1 0
- Vertica的这些事<十五>—— Vertica备份元数据信息
- Vertica的这些事<八>—— vertica加密数据
- Vertica的这些事<十六>—— vertica备份与恢复
- Vertica的这些事<十四>—— vertica优化
- Vertica的这些事<十一>—— Vertica 管理
- Vertica的这些事<十二>—— vertica存储统计信息
- Vertica的这些事<二>—— vertica建表的一些规则
- Vertica的这些事<三>—— 谈谈vertica的flex table
- Vertica的这些事<九>—— 关于vertica的Connection Failover
- Vertica的这些事<十三>—— Vertica停止数据库的操作步骤
- Vertica的这些事<十>—— vertica中group by 和join 语句的优化
- Vertica的这些事<五>—— 关于vertica常用函数介绍(持续更新ing)
- Vertica的这些事<七>—— Vertica中实现Oracle中的ws_concat功能
- Vertica的这些事<四>—— VSQL常用命令
- Vertica的这些事<六>—— SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
- 《Vertica的这些事》系列文章
- vertica-备份的恢复
- Vertia的这些事<一>—— 关于vertica的常用操作
- [Caffe]:关于diff = 0/nan
- 系统回调 CmpCallback
- 02jquery01-05jquery选择器
- 已知某年某月,请输出这个月共有多少天(switch语句)
- javascript获取AJAX对象(DOM方式)
- Vertica的这些事<十五>—— Vertica备份元数据信息
- 自定义View获取dimen值得三个方法:getDimension,getDimensionPixelOffset和getDimensionPixelSize
- 如何使虚拟机VMware workstation连接局域网
- MySQL的安装与卸载
- 隐马尔科夫模型攻略
- 【二分】【前缀和】【数学归纳法】【优化】NOIP模拟测试题"nan"
- 解决开机提示High Severity Error问题
- 猴子吃桃问题
- Java基于AES的加密与解密