在bash中有可能有一个持久的psql连接吗

Question

我有一个非常大的bash脚本，我在其中进行了数千次psql查询，我怀疑这会减慢速度。我创建了这个MWE，它证明了我的理论。我认为这种速度减慢是因为必须反复连接到数据库。有没有一种方法可以在bash中保持与psql的连接？

#!/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))"

输出：

$ ./test_psql.sh
Multiple Call Duration (s): 64
Single Call Duration (s): 12

Answer 1

根据您的评论，尝试运行以下命令：

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