We have migrated to new Google sites!
Post date: Oct 05, 2012 2:46:34 PM
Segment script for modifying all segments with one SQL command. The scripted database maintenance needs to be run through utility mode on segments.
In Greenplum Database 4.x versions Greenplum introduced the gpconfig utility to modify any GUC settings in segment databases or master database. Prior to 4.x there is no easy way of making these changes.
The following script is designed to execute seg_fix.sql on all segment databases.
seg_fix.sql can contain anything SQL related. This is just an example:
echo 'drop table if exists prod_mis_seg.nunn_segment_sme cascade;' > seg_fix.sql
V3.x:
psql template1 -Atc "select 'echo dbid:' || dbid || E'\\n' || 'PGOPTIONS=''-c gp_session_role=utility'' psql -e -p '|| port || ' -h ' || hostname || E' -f seg_fix.sql ' from gp_configuration where content != -1" | bash
V4.x:
psql template1 -Atc "select 'echo dbid:' || dbid || E'\\n' || 'PGOPTIONS=''-c gp_session_role=utility'' psql -e -p '|| port || ' -h ' || hostname || E' -f seg_fix.sql ' from gp_segment_configuration where role='p'" | bash
- or -
psql template1 -Atc "select 'PGOPTIONS=''-c gp_session_role=utility'' psql -h '|| hostname||' -p '|| port||' gpadmin -c "'"'drop table if exists prod_mis_seg.nunn_segment_sme cascade;"'"' from gp_segment_configuration where role='p'" > drop_table.sh
sh drop_table.sh