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