Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to have a persistent psql connection in bash?

I have a very large bash script and I make thousands of psql queries throughout it which I suspect is slowing it down. I have created this MWE which proves my theory. I am assuming this slow down is because of having to connect to the db repeatedly. Is there a way to remain connected to psql in bash?

#!/bin/bash

 DB_NAME=testdb
 DB_USER=user1  #UPDATE THIS

 #DROP DB
 SQL_QUERY="DROP DATABASE IF EXISTS $DB_NAME;"
 echo $SQL_QUERY | sudo -u $DB_USER psql >/dev/null

 #CREATE DB
 SQL_QUERY="CREATE DATABASE $DB_NAME;"
 echo $SQL_QUERY | sudo -u $DB_USER psql >/dev/null

 #CREATE TABLE
 SQL_QUERY="CREATE TABLE foo
 (
     id BIGSERIAL PRIMARY KEY,
     problems INTEGER
 );"
 echo $SQL_QUERY | sudo -u $DB_USER psql -d $DB_NAME >/dev/null

 TARGET_ITERATIONS=1000

 #MULTIPLE DB CALLS
 SQL_QUERY="INSERT INTO foo
 (
     problems
 )
 VALUES
 (
     99
 );"

 START_TIME=$(date +%s)
 for ITERATION in $(seq $TARGET_ITERATIONS)
 do
     echo $SQL_QUERY | sudo -u $DB_USER psql -d $DB_NAME >/dev/null
 done
 STOP_TIME=$(date +%s)

 echo "Multiple Call Duration (s): $((STOP_TIME-START_TIME))"

 #Single db call
 SQL_QUERY=""

 for ITERATION in $(seq $TARGET_ITERATIONS)
 do
     SQL_QUERY+="INSERT INTO foo
     (
         problems
     )
     VALUES
     (
         99
     );"

 done

 START_TIME=$(date +%s)
 echo $SQL_QUERY | sudo -u postgres psql -d $DB_NAME >/dev/null
 STOP_TIME=$(date +%s)
 echo "Single Call Duration (s): $((STOP_TIME-START_TIME))"

Output:

$ ./test_psql.sh
Multiple Call Duration (s): 64
Single Call Duration (s): 12
like image 899
puk Avatar asked Oct 20 '25 08:10

puk


1 Answers

Per your comment, try running this:

#/bin/bash

mkfifo /tmp/mypipe
chmod a+r /tmp/mypipe
sleep 10000 > /tmp/mypipe &
psql -f /tmp/mypipe &
echo "\pset pager off" > /tmp/mypipe
echo "select count(*) from information_schema.columns;" > /tmp/mypipe
echo "select count(*) from information_schema.columns;" > /tmp/mypipe
sleep 3
echo "select count(*) from information_schema.columns;" > /tmp/mypipe
echo "\q" > /tmp/mypipe
rm /tmp/mypipe

like image 115
Mike Organek Avatar answered Oct 21 '25 22:10

Mike Organek



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!