Wednesday, July 15, 2015

aliasing psql

When you run psql without arguments, then the psql tries to connect to database named like current user. I dislike this behave. Surely I have not database "pavel". There is trivial solution - add to your .bashrc code:
function psql { 
if [[ $# -eq 0 ]]; then
   env psql postgres
else
  env psql "$@"
fi
}

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"

Saturday, July 11, 2015

New Orafce and Plpgsql_check extensions released

I released a new versions of these packages: Orafce and Plpgsql_check. Its mostly bugfix releases with PostgreSQL 9.5 support.