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; doneToday 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 simplifiedpsql 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"
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home