上篇写到用pg_bulkload来导入数据,但是实际的环境是solaris,pg_bulkload还不支持,于是用copy的方式,下面附上脚本
-bash-4.1$ cat copy.sh
#!/bin/sh
#$1 data fil ename
file=$1
if [ ! -f $file ]
then
echo "File is not exist"
exit 1
fi
echo "-----------------------------------------------------------------"
tbname=$( echo $file |cut -d . -f1 )
echo "Table name is : "$tbname
echo "set table unlogging model"
psql -c "update pg_class set relpersistence='u' where relname = '$tbname'" -d sgdw
zcat $file|psql -c "copy $tbname from stdin with (format csv, delimiter ',')" -d sgdw
#zcat $file|psql -c "copy $tbname from stdin with (format csv, delimiter ',', null '\n', encoding 'utf8' quote '"', force_quote *)" -d sgdw
echo "set table logging model"
psql -c "update pg_class set relpersistence='p' where relname = '$tbname'" -d sgdw
echo "load $tbname complete"
echo "-----------------------------------------------------------------"
批量导入:
-bash-4.1$ cat copy_all.sh
#!/bin/sh
files=$(ls *.gz)
for file in $files;
do
echo "-----------------------------------------------------------------"
echo "Load file : "$file
starttime=$(date +"%F %X")
startseconde=$(date +%s)
echo "Start time is :" $starttime
./copy.sh $file
endtime=$(date +"%F %X")
echo "End time is :" $endtime
endseconde=$(date +%s)
echo "Total time is :" $(($endseconde-$startseconde))
echo "-----------------------------------------------------------------"
echo ""
echo ""
done
后台执行:
nohup ./copy_all.sh > copy_all.log 2>&1 &
加入新的用法,一次搞定:
for i in $(ls *.csv);do psql -c "copy ${i/.csv/} from '/var/lib/pgsql/tpc-h/tpc-h-orcale/$i' with delimiter '|'" tpch; echo $i;done