I use postgres for a lot of different things. Many times, it’s a full-blown application where I can directly access the database (i.e. python + psycopg2, java + jdbc, etc…). What if you’re using something a bit less formal, like a shell script?

psql

psql is the native, command-line interface that ships with a postgresql server. This is usually the first tool that most people end up using with postgres. It’s simplicity masks a very powerful ‘swiss-army’ knife that lets you very quickly work with data.

Let’s take a basic table “hosts”

CREATE TABLE hosts ( 
 host_id SERIAL, 
 hostname TEXT, 
 host_group TEXT, 
 host_ip TEXT, 
 host_ssh_port INT, 
 host_ssh_user TEXT, 
 host_ssh_key_path TEXT 
 );

There are much, much better ways to model this! This is just a simple example.

Let’s say I want to ssh to all of the my hosts and execute and ‘uptime’ command. How could I accomplish this from a shell script?

Let’s start by ssh-ing to one of the hosts

ssh -i private_key -p 22 user@host "uptime"

Okay, now, I want to do this for every host in my table. This means that somehow, I need to get the data out of the databases and into my shell script. Let’s write the query that we need:

SELECT host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM hosts;

When I do that, I get nicely formatted data back

[scottm@scottsmac : ~/test1 : 13:30:46] 
$ psql 
psql (9.5.2) 
Type "help" for help.

[scottsmac@[local]:9520]
postgres@postgres=# select host_ssh_key_path, host_ssh_port, host_ssh_user, 
host_ip FROM hosts; host_ssh_key_path | host_ssh_port | host_ssh_user | host_ip 
------------------------+---------------+---------------+----------- 
/infra/keys/vagrantkey | 2222 | vagrant | 127.0.0.1 
/infra/keys/vagrantkey | 2201 | vagrant | 127.0.0.1 (2 rows)

[scottsmac@[local]:9520]
postgres@postgres=#

Great, but, how do I get this into my shell script?

psql gives us the ‘-c’ flag (command) option. This will connect to the database, execute the SQL and print the result to stdout.

[scottm@scottsmac : ~/test1 : 13:36:24] 
$ psql -c "select host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM 
hosts;" host_ssh_key_path | host_ssh_port | host_ssh_user | host_ip 
------------------------+---------------+---------------+----------- 
/infra/keys/vagrantkey | 2222 | vagrant | 127.0.0.1 
/infra/keys/vagrantkey | 2201 | vagrant | 127.0.0.1 (2 rows)

[scottm@scottsmac : ~/test1 : 13:36:29] 
$

Great, except, there’s a lot of superfluous stuff there. We could do a lot of ‘sed’, ‘awk’, but, we don’t need to.

Now, we can use the ‘tuples-only’ option to psql (-t) to only print the results of our query:

[scottm@scottsmac : ~/test1 : 13:36:29] 
$ psql -t -c "select host_ssh_key_path, host_ssh_port, host_ssh_user, 
host_ip FROM hosts;"
/infra/keys/vagrantkey | 2222 | vagrant | 127.0.0.1 
/infra/keys/vagrantkey | 2201 | vagrant | 127.0.0.1

[scottm@scottsmac : ~/test1 : 13:39:44] 
$

We’ve removed the header and the row count from the bottom.

Now, we’ve got a bunch of extra whitespace, let’s get rid of it. We can do this by disabling ‘aligned’ mode (-A).

[scottm@scottsmac : ~/test1 : 13:41:38] 
$ psql -A -t -c "select host_ssh_key_path, host_ssh_port, host_ssh_user, 
host_ip FROM hosts;"
/infra/keys/vagrantkey|2222|vagrant|127.0.0.1                                     
/infra/keys/vagrantkey|2201|vagrant|127.0.0.1 
[scottm@scottsmac : ~/test1 : 13:41:42] 
$

Okay, now we’ve got our data to something that’s parse-able with a simple awk script. So, now we can dump this into a ‘for’ loop in a script:

# !/bin/bash
for host in `psql -A -t -c "select host_ssh_key_path, host_ssh_port, host_ssh_user, 
host_ip FROM hosts;"` 
do printf "$host\n" 
done 

The output:

[scottm@scottsmac : ~/test1 : 13:44:16] 
$ ./uptime_all_hosts.sh 
/infra/keys/vagrantkey|2222|vagrant|127.0.0.1 
/infra/keys/vagrantkey|2201|vagrant|127.0.0.1 
[scottm@scottsmac : ~/test1 : 13:44:17] 
$

Now, let’s do a quick ‘awk’ to parse these into proper variables for our ssh:

# !/bin/bash
for host in `psql -A -t -c "select host_ssh_key_path, host_ssh_port, host_ssh_user, 
host_ip FROM hosts;"` 
key=`echo $host | awk -F'|' '{print $1}'`
port=`echo $host | awk -F'|' '{print $2}'`
user=`echo $host | awk -F'|' '{print $3}'`
ip=`echo $host | awk -F'|' '{print $4}'`

printf "Connecting to $ip ... "
ssh -i $key -p $port $user@$ip "uptime"
printf "\n"

done

Now, when I run the script:

scottm@scottsmac : ~/test1 : 13:48:18]
$ ./uptime_all_hosts.sh
Connecting to 127.0.0.1 ... 18:48:20 up 23:16, 0 users, load average: 0.00, 0.01, 
0.05

Connecting to 127.0.0.1 ... 18:48:19 up 21 min, 0 users, load average: 0.00, 0.01, 
0.04

[scottm@scottsmac : ~/test1 : 13:48:20]
$

Voila! Postgres data, reliably from within your shell script.

Happy PostgreSQL-ing!