Vertica的这些事(十八)—— Vertica备份元数据信息
发布日期:2021-06-29 20:31:16
浏览次数:2
分类:技术文章
本文共 3238 字,大约阅读时间需要 10 分钟。
---备份资源池
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')"
转载地址:https://dataclub.blog.csdn.net/article/details/103160766 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
逛到本站,mark一下
[***.202.152.39]2024年04月15日 18时05分37秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
python中时间日期相减并转化为秒
2019-04-30
django中迁移表失败怎么办
2019-04-30
python在得到绝对路径时候 \ 转化/
2019-04-30
python中二维码得生成
2019-04-30
Odoo 14 初级入门学习
2019-04-30
python drf 小程序 微信支付 退款 逻辑加代码
2019-04-30
odoo14 rpc 链接报错 容易出错怎样解决
2019-04-30
python os 怎样使用 读取一个文件中所有图片的名称
2019-04-30
python 图片转化为base64
2019-04-30
小程序支付 xml拼接 和生成 sign签名 代码
2019-04-30
python pandas excle 把两列合并新的一列
2019-04-30
odoo 中 tree中的字段隐藏 排序等
2019-04-30
有道翻译 爬虫 讲解 和 代码
2019-04-30
python中 .py 文件打包成 .exe可执行文件
2019-04-30
Ubuntu 安装虚拟环境 安装python 3.7配置等
2019-04-30
【使用技巧】VS2017避免全部安装至C盘
2019-04-30
【使用技巧】Pycharm需要配置脚本路径(Script Path)
2019-04-30