Getting Postgres data into a shell script

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;</p>

When I do that, I get nicely formatted data back

[scottm@scottsmac : ~/test1 : 13:30:46]
$ psql
psql (9.5.2)
Type &quot;help&quot; for help.</p>

<p>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)</p>

<p>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 &quot;select host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM hosts;&quot;
 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)</p>

<p>[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 &quot;select host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM hosts;&quot;
 /infra/keys/vagrantkey | 2222 | vagrant | 127.0.0.1
 /infra/keys/vagrantkey | 2201 | vagrant | 127.0.0.1</p>

<p>[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:

</p>

<h1>!/bin/bash</h1>

<p>for host in <code>psql -A -t -c &amp;quot;select host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM hosts;&amp;quot;</code>
do
 printf &quot;$host\n&quot;
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:

</p>

<h1>!/bin/bash</h1>

<p>for host in <code>psql -A -t -c &amp;quot;select host_ssh_key_path, host_ssh_port, host_ssh_user, host_ip FROM hosts;&amp;quot;</code>
do
 key=<code>echo $host | awk -F'|' '{print $1}'</code>
 port=<code>echo $host | awk -F'|' '{print $2}'</code>
 user=<code>echo $host | awk -F'|' '{print $3}'</code>
 ip=<code>echo $host | awk -F'|' '{print $4}'</code></p>

<p>printf &quot;Connecting to $ip ... &quot;
 ssh -i $key -p $port $user@$ip &quot;uptime&quot;
 printf &quot;\n&quot;</p>

<p>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</p>

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

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

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

Happy PostgreSQL-ing!