The goal is to save time for gpcheckcat, especially for large clusters.
0. For master and all segments:
This option can drop temp schemas for master and all segments, if you go for this option, you can skip below 2 options.psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a}; doneSample output:
check for postgres check for template1 check for gpperfmon check for testdb drop schema if exists pg_temp_316 cascade; drop schema if exists pg_temp_3 cascade;
1. For master:
psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'drop schema ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo;" ${a}; doneSample output:
check for postgres check for template1 check for gpperfmon check for testdb drop schema pg_temp_316 cascade;
2. For segments:
psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'PGOPTIONS=''-c gp_session_role=utility'' psql -h ' || g.hostname || ' -p '||g.port || ' -c ' || '''drop schema '||p.nspname||' cascade;'' ${a}' from gp_dist_random('pg_namespace') p,gp_segment_configuration g where p.nspname like 'pg_temp%' and g.role='p' and g.content=p.gp_segment_id and p.nspname not in (select 'pg_temp_' || sess_id::varchar from pg_stat_activity);" ${a}; doneSample output:
check for postgres check for template1 check for gpperfmon check for testdb PGOPTIONS='-c gp_session_role=utility' psql -h sdw6 -p 40070 -c 'drop schema pg_temp_3 cascade;' testdb
No comments:
Post a Comment