vertica的这些事lt;十五gt;—— vertica备份元数据信息 -尊龙游戏旗舰厅官网

—备份资源池

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_pools where not is_internal order by name;

—备份角色

select '-- create roles'; select 'create role ' || name || ' ;' as txt_cr from v_catalog.roles where 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.users where user_name not in ('dbadmin') order by 1;

–备份schema

select '-- create schema'; select 'create schema ' || schema_name || ';' from schemata where 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.users where 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_gb from (select ps.anchor_table_schema,sum(used_bytes) / ( 1024^3 ) as used_compressed_gbfrom v_catalog.projections pjoin v_monitor.projection_storage pson ps.projection_id = p.projection_idwhere p.is_super_projection = 't'group by ps.anchor_table_schema) pjcross join (select (select database_size_bytesfrom v_catalog.license_auditsorder by audit_start_timestamp desclimit 1) / (select sum(used_bytes)from v_monitor.projection_storage) as ratio) la order 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')"

总结

以上是尊龙游戏旗舰厅官网为你收集整理的vertica的这些事lt;十五gt;—— vertica备份元数据信息的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得尊龙游戏旗舰厅官网网站内容还不错，欢迎将尊龙游戏旗舰厅官网推荐给好友。