Pages

Tuesday, July 14, 2015

simple parallel run statement in every database in PostgreSQL cluster

When I need to execute some statement in every database of some PostgreSQL cluster, I am using a script:
for db in `psql -At -c "select datname from pg_database where datname not in ('template0','template1')"`; 
do 
  psql -At -c "select current_database(), pg_size_pretty(pg_table_size('pg_attribute')) where pg_table_size('pg_attribute') > 100 * 1024 * 1024" $db; 
done
Today I needed to run VACUUM statement for selected databases. So I needed to find a way, how to run this slow statement. I was to surprised how it is simple task due strong xarg command. And nice bonus - I can run this slow queries parallel - because xargs can run entered command in more workers (-P option):
# find databases with bloated pg_attribute table, and enforce VACUUM
for db in `psql -At -c "select datname from pg_database where datname not in ('template0','template1')"`; 
do 
  psql -At -c "select current_database() where pg_table_size('pg_attribute') > 100 * 1024 * 1024" $db; 
done | xargs -P 3 -I % psql % -c "vacuum full  verbose analyze pg_attribute"

Update

Previous example can be little bit simplified
psql postgres -At -c "select datname from pg_database where datname not in ('template0','template1')" |
xargs -P 3 -I % psql % -c "select current_database() where pg_table_size('pg_attribute') > 100 * 1024 * 1024" |
xargs -P 3 -I % psql % -c "vacuum full analyze pg_attribute"

No comments:

Post a Comment