Using open database connection to PostgreSQL in BASH?

Question

I have to use BASH to connect to our PostgreSQL 9.1 database server to execute various SQL statements.

We have a performance issue caused by repeatedly opening/closing too many database connections (right now, we send each statement to a psql command).

I am looking at the possibility of maintaining an open database connection for a block of SQL statements using named pipes.

The problem I have is that once I open a connection and execute a SQL statement, I don't know when to stop reading from the psql. I've thought about parsing the output to look for a prompt, although I don't know if that is safe considering the possibility that the character may be embedded in a SELECT output.

Does anyone have a suggestion?

Here's a simplified example of what I have thus far...

#!/bin/bash

PIPE_IN=/tmp/pipe.in
PIPE_OUT=/tmp/pipe.out

mkfifo $PIPE_IN $PIPE_OUT
psql -A -t jkim_edr_md_xxx_db < $PIPE_IN > $PIPE_OUT &
exec 5> $PIPE_IN; rm -f $PIPE_IN
exec 4< $PIPE_OUT; rm -f $PIPE_OUT

echo 'SELECT * FROM some_table' >&5

# unfortunately, this loop blocks
while read -u 4 LINE
do
    echo LINE=$LINE
done

PostgreSQL supports persistent connections. You don't need to open and close connections all the time. — Xeoncross
– Xeoncross, Commented Feb 8, 2012 at 19:21

Milen A. Radev · Accepted Answer · 2012-02-08 19:29:56Z

1

Use --file=filename for a batch execution.

Depending on your need for flow control you may want to use another language with a more flexible DB API (Python would be my choice here but use whatever works).

answered Feb 8, 2012 at 19:29

Milen A. Radev

63k22 gold badges110 silver badges112 bronze badges

I need to process the results from the SQL statements individually so I don't think batch execution will work for me.

Jin Kim
– Jin Kim

02/08/2012 19:33:30
Commented Feb 8, 2012 at 19:33

Add a comment |

BRPocock · Accepted Answer · 2012-02-08 19:39:18Z

            echo >&5 "SELECT * FROM some_table"

should read

            echo 'SELECT * FROM some_table' >&5

The redirection operator >& comes after the parameters to echo; and also, if you use "" quotes, some punctuation may be treated specially by the shell, causing foul and mysterious bugs later. On the other hand, quoting ' will be … ugly. SELECT * FROM some_table WHERE foo=\'Can\'\'t read\'' …

You probably want to also create these pipes someplace safer than /tmp. There's a big security-hole race condition where someone else on host could hijack your connection. Try creating a folder like /var/run/yournamehere/ with 0700 privileges, and create the pipes there, ideally with names like PIPE_IN=/var/run/jinkimsqltool/sql.pipe.in.$$ — $$ will be your process ID, so simulataneously-executed scripts won't clobber one another. (To exacerbate the security hole, rm -rf should not be needed for a pipe, but a clever cracker could use that excalation of privileges to abuse the -r there. Just rm -f is sufficient.)

Thanks I have updated my post with some of your recommendations. — Jin Kim
– Jin Kim, Commented Feb 8, 2012 at 19:49

maniek · Accepted Answer · 2012-02-09 22:51:37Z

0

in psql You can use

\o YOUR_PIPE
SELECT whatever;
\o

which will open, write and close the pipe. Your BASH-fu seems quite a lot stronger than mine, so I'll let You work out the details :)

answered Feb 9, 2012 at 22:51

maniek

7,3152 gold badges23 silver badges44 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Using open database connection to PostgreSQL in BASH?

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related