当前位置: 首页 > postgresql > 正文

bash和psql结合处理批量任务

文中的例子来自http://www.manniwood.com/postgresql_and_bash_stuff/index.html,经过了翻译整理。

通常,我们想要批量的执行sql,或者想要在数据库中处理一些逻辑时,会使用plsql,或者在命令行中一步一步的执行单条sql。

单步执行sql的方式肯定不是我们希望的;而对于plsql,如果不熟悉plsql的语法,也会比较纠结的;况且,目前调试函数或者存储过程也比较耗时间;如果我们的批量操作只进行一次的话,调试存储过程的代价难免会比较大。

下面,我们就来探索一种bash和psql共同工作的方式来实现这些功能;of cource,收益者就只限于熟悉bash语法和sql语法的同学了。

1. 最简单的批量sql执行方式,(paql 的 -f 参数)
#!/bin/sh

if [ $# != 2 ]; then
    echo "please enter a db host and a table suffix"
    exit 1
fi

export DBHOST=$1
export TSUFF=$2

psql \
    -X \
    -U user \
    -h $DBHOST \
    -f /path/to/sql/file.sql \
    --echo-all \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --set TSUFF=$TSUFF \ --set QTSUFF=\'$TSUFF\' \
    mydatabase

psql_exit_status=$?

if [ $psql_exit_status != 0 ]; then
    echo "psql failed while trying to run this sql script" 1>&2
    exit $psql_exit_status
fi

echo "sql script successful"
exit 0
  1. 在命令行设置了两个变量 TSUFF 和 QTSUFF,进而,可以在sql文件中应用变量,增加了sql的灵活性
  2. 使用 -X 选项来阻止 psql 读取 .psqlrc 文件
  3. 使用 –echo-all 来观察所有的执行日志
  4. 使用 ON_ERROR_STOP, 在出错的时候,停止执行;当然,有时候我们可能不想要这个参数
  5. 关闭 AUTOCOMMIT 来阻止sql的自动提交,在批量sql时,这个参数很重要;当然,有时我们可以考率一下 -l 参数来将所有的sql放在一个事务中运行;

下面是一个可能的sql文件:(注意,其中用到了之前SET的变量)

begin;
drop index this_index_:TSUFF;
commit;

begin;
create table new_table_:TSUFF (
    greeting text not null default '');
commit;

begin;
insert into new_table_:TSUFF (greeting)
values ('Hello from table ' || :QTSUFF);
commit;
2. 熟练使用psql的 < 操作

可能有些人还不知道,使用<重定向符号,可以将文件中的sql发送给psql来执行,下面,我们就来试试这种方式

#!/bin/bash

set -e

RUN_ON_MYDB="psql -X -U myuser -h myhost --set ON_ERROR_STOP=on --set AUTOCOMMIT=off mydb"

$RUN_ON_MYDB &lt;

甚至,我们可以将多个事务进行拆分和叠加操作

--注意,下面的句子还在上个bash脚本中哦
CREATE_MY_TABLE_SQL=$(cat &lt;
3. 将select执行出单独的结果赋值给shell变量,然后在shell中使用这个值怎么样?(我们又离存储过程的功能近了一步)
CURRENT_ID=`$PSQL -X -U $PROD_USER -h myhost -P t -P format=unaligned $PROD_DB -c "select max(id) from users"`
let NEXT_ID=CURRENT_ID+1
echo "next user.id is $NEXT_ID"

echo "about to reset user id sequence on other database"
$PSQL -X -U $DEV_USER $DEV_DB -c "alter sequence user_ids restart with $NEXT_ID"

神奇的-c参数将select的结果拿了出来,在shell中进行您喜欢的计算后,又放回postgresql中,怎么样?

有人会问,select出来的结果一般是一个结果集,或着,至少是多个列吧,要怎么处理呢?ok,看这个神奇的数组

4. 使用数组来处理select出来单条记录的多个列
#!/bin/bash

set -e

declare -a ROW=(`psql \
    -X \
    -h myhost \
    -U myuser \
    -c "select username, first_name, last_name from users where id = 5489" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    mydb`)

username=${ROW[0]}
first_name=${ROW[1]}
last_name=${ROW[2]}

echo "username: $username, first_name: $first_name, last_name: $last_name"

梦想又近了一步啦,下面看怎么处理一个结果集

5. 使用数组+循环来处理select出来包含多个列的结果集
#!/bin/bash

PSQL=/usr/bin/psql

DB_USER=myuser
DB_HOST=myhost
DB_NAME=mydb

$PSQL \
    -X \
    -h $DB_HOST \
    -U $DB_USER \
    -c "select username, password, first_name, last_name from users" \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    $DB_NAME | while read -a Record ; do

    username=${Record[0]}
    password=${Record[1]}
    first_name=${Record[2]}
    last_name=${Record[3]}

    echo "USER: $username $password $first_name $last_name"
done

ok,大工告成,以上5种 sql + bash 的方式,已经足够我们应付在工作中遇到的大部分需要plsql才可以解决的问题了

以下是一些bash+psql处理事情的具体例子,和上面的五种方式没有深层的联系

附录A:使用独立表来控制事务的处理

假如我们有好多事情要干(当然,仅限于数据库中的数据处理),我们知道干活的具体办法,只有一个唯一的限制:这些事情必须是串行执行的。

呆在电脑前,一个一个的处理事情肯定不是我们想要的方式,那我们何不做一个表来控制事情的进行呢?结束的工作标记为done,下次在没有done的事情中挑出一件来做,直到所有的事情都为done状态;如下:

#!/bin/bash

set -e

PSQL="/u99/pgsql-9.1/bin/psql"
DNL_TABLE="items_to_process"
#DNL_TABLE="test"
FETCH_QUERY="select item_id from my_schema.${DNL_TABLE} where done is false order by item_id limit 1"

process_item() {
    local item_id=$1
    local dt=`date`
    echo "[${dt}] processing item_id $item_id"
    $PSQL -X -U myuser -h myhost -c "insert into my_schema.thingies select thingie_id, salutation, name, ddr from thingies where item_id = $item_id and salutation like 'Mr.%'" mydb
    #我们可以将处理具体事情的操作放在这里, ^ ^
}

item_id=`$PSQL -X -U myuser -h myhost -P t -P format=unaligned -c "${FETCH_QUERY}" mydb`
dt=`date`
while [ -n "$item_id" ]; do
    process_item $item_id
    echo "[${dt}] marking item_id $item_id as done..." #随时记录日志是好孩子
    $PSQL -X -U myuser -h myhost -c "update my_schema.${DNL_TABLE} set done = true where item_id = $item_id" mydb
    item_id=`$PSQL -X -U myuser -h myhost -P t -P format=unaligned -c "${FETCH_QUERY}" mydb`
    dt=`date`
done
附录B不同库之间的数据传输

数据导入导出的方式简直是太多了,也不差再多这一种,或许会有意想不到的好处呢,如下:

psql \
    -X \
    -U user \
    -h oldhost \
    -c "\\copy users to stdout" \
    olddb \
| \
psql \
    -X \
    -U user \
    -h $DBHOST \
    -c "\\copy users from stdin" \
    newdb \

如何?如果旧表和新表的列数不一样呢?(这也是超过pg_dump的地方)

  • first_name
  • middle_name
  • last_name

新表如下:

  • first_name
  • last_name

依然,我们可以使用如下的方式来实现:

psql \
    -X \
    -U user \
    -h oldhost \
    -c "\\copy (select first_name, last_name from users) to stdout" \
    olddb \
| \
psql \
    -X \
    -U user \
    -h $DBHOST \
    -c "\\copy users from stdin" \
    newdb \
    分享到:

本文固定链接: http://klwang.info/bash-psql-combination/ | 数据库|Linux|软件开发

该日志由 klwang 于2013年03月14日发表在 postgresql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: bash和psql结合处理批量任务 | 数据库|Linux|软件开发
关键字: ,

bash和psql结合处理批量任务:等您坐沙发呢!

发表评论

*
快捷键:Ctrl+Enter