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.

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

up vote 1 down vote

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 yesterday

Milen A. Radev
9,61011931

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

feedback

BRPocock · Answer 2 · 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.

asked	yesterday
viewed	26 times
active	yesterday

Using open database connection to PostgreSQL in BASH?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged bash postgresql named-pipes postgresql-9.1 or ask your own question.

Using open database connection to PostgreSQL in BASH?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged bash postgresql named-pipes postgresql-9.1 or ask your own question.

Hello World!

Related