Home > PostgreSQL > Scripting within psql

Scripting within psql

In one of my previous posts I was told of using PSQL instead of shell scripting to make things much simpler. That turned out to a good tip and with some help from my colleague at work I am now so used to PSQL in these kind of scenarios.

Just today I had to get the dead tuple percentages (using pgstattuple contrib) for tables within all databases on the server and here is how I was to get it done using PSQL and a simple shell script….

#!/bin/bash
PSQL="psql -A -t"
DBLIST=$($PSQL postgres -c "select datname from pg_database
where not datistemplate and datname not in ('postgres')")
for DB in $DBLIST; do
 echo $DB;
 psql $DB  < gettupd.sql > $DB.out
done
exit 0

gettupd.sql

\a
\t
\o /tmp/tupd.sql
select 'SELECT \'' || relname || '\', * from pgstattuple('''||schemaname||
'.'||relname||''');' from pg_stat_user_tables ;
\o
\i /tmp/tupd.sql
\! rm /tmp/tupd.sql
\a
\t

PSQL is just the way to go…  🙂


Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
  1. November 3, 2009 at 2:27 am

    It’s unfortunate that PSQL commands have those ugly backslash names. It makes it look like LaTeX code — except less readable!

  2. yeestrada
    October 12, 2011 at 7:59 am

    Hello, how can I connect and insert data into a remote database using a bash script? I mean, I want to connect to another pc with postgreSQL database and insert some data there from a bash script, can anyone help me??

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: