说明：通过python3每次读取文件的前三行传递到shell脚本执行迁移任务

脚本如下

python脚本

[root@node-251 aws-uat-prep]# cat pg-move.py 
#!/usr/bin/python3
import sys,os

with open('pg.txt','r') as f:
    content=f.readlines()

content=[content[i].strip('\n') for i in range(len(content))]
a=0
b=3
while content[a:b]:
      result=content[a:b]
      result=' '.join(result) 
      content[a:b]=''
      os.system("/tmp/aws-uat-prep/pg-mv.sh {}".format(result))

shell脚本：通过配置/root/.pgpass来让psql不用手动输入密码执行数据迁入

[root@node-251 aws-uat-prep]# cat pg-mv.sh 
#!/bin/bash
#将cdp_edge上面schema的数据复制一份到cdp_edge_prep对应的schema上面
cd /tmp/aws-uat-prep
rm -f *.sql
schema="cml"  #postgres的schema
yy=$*   #接收python传过来的字符串，也就是三个表
for i in $yy;do
      echo $i
      echo $i >>info.txt
      pg_dump "host=cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com port=5432 user=dbadmin password=G-PsscYdl1qmNh2bw|9X  dbname=cdp_edge"  -t ${schema}.$i --no-tablespaces -f ${i}.sql
      echo "alter table ${schema}.${i} owner to cdp_2c" >> ${i}.sql
      sed -i "/CREATE TABLE/i\ drop table if exists  ${schema}.${i};" ${i}.sql
      #sed -i "s/public\./${schema}\./g" ${i}.sql
      #sed -i 's/OWNER TO dbadmin/OWNER TO cdp_2c/g' ${i}.sql
done
# for i in `ls *.sql`;do psql --host=cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com --port=5432 --username=dbadmin  --dbname=cdp_edge_prep  -f  $i;done
 for i in `ls *.sql`;do psql -h cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com -p 5432 cdp_edge_prep dbadmin  -f  $i;done

 免密执行操作

[root@node-251 aws-uat-prep]# cat ~/.pgpass 
cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com:5432:cdp_edge_prep:dbadmin:密码

上面配置后下面的命令可以免密执行

psql -h cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com -p 5432 cdp_edge_prep dbadmin  -f 文件 ##免密执行命令
psql -h cdpsupportquotation-uat-pg.cojemua46qv2.us-east-1.rds.amazonaws.com -p 5432 cdp_edge_prep dbadmin   ## 登陆

注意使用特殊符号的处理比如冒号：  htZul;+0NNb\:UK12Ip

 

其他