Vertica的这些事（十一）——|Vertica的这些事（十一）—— Vertica备份元数据信息 Vertica的这些事（十一）——

---备份资源池

SELECT'CREATE RESOURCE POOL ' || name || CASE WHEN memorysizeIS 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 runtimepriorityIS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY '||runtimepriorityEND || CASE WHEN runtimeprioritythresholdIS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD '||runtimeprioritythresholdEND || CASE WHEN queuetimeoutIS NULL THEN ' ' ELSE ' QUEUETIMEOUT '||queuetimeoutEND || CASE WHEN maxconcurrencyIS NULL THEN ' ' ELSE ' MAXCONCURRENCY '||maxconcurrencyEND || CASE WHEN runtimecapIS 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_gb FROMv_catalog.projections p JOIN v_monitor.projection_storage ps ON ps.projection_id = p.projection_id WHEREp.is_super_projection = 't' GROUPBY ps.anchor_table_schema) pj CROSS JOIN (SELECT (SELECT database_size_bytes FROMv_catalog.license_audits ORDERBY audit_start_timestamp DESC LIMIT1) / (SELECT SUM(used_bytes) FROMV_MONITOR.projection_storage) AS ratio) la ORDERBY pj.used_compressed_gb DESC;