Import and export scripts for personal postgres databases

In the work often to develop and test import and export some test database, so wrote a script for easy operation.

The company is currently using the postgres9.3 database.

#!/bin/bash# defines some variables and operation commands dbs= "DB1&NBSP;DB2&NBSP;DB3&NBSP;DB4" expdb_cmd=/usr/pgsql-9.3/bin/pg_dumpdbcmd=/usr/ pgsql-9.3/bin/psqldbuser=postgresdbsvr=mydbhost1# Export Database Function exportdb () {  read -p  " input database user name to export:  " SRCDB  read -p " input export path:  " EXPATH  for db in  ' echo  $DBS '    do     $EXPDB _cmd -u  $DBUSER  -h  $DBSVR  ${db}_${SRCDB}  >  $EXPATH/${db}_${srcdb}  done} #导入数据库function  importdb () {  read -p  "input source database user name: "  SRCDB  read -p  "Input  destination database user name:  " DSTDB  read -p " Input  source database path:  " EXPATH  read -p " confirm the  source db:  $SRCDB  / destination db:  $DSTDB  [y/n] " YorN  if [  $YorN  ==   "Y"  ] | |  [  $YorN  ==  "y"  ]; then     for db in  ' echo  $DBS '     do       echo ${db}_$dstdb        $DBCMD  -U  $DBUSER  -h  $DBSVR  -d ${db}_${dstdb } <  $EXPATH/${db}_${srcdb}    done  else      echo  "Decline or input incorrect! re-type again"      for i in  ' seq 3 '; do sleep 1; echo -n  "."; DONE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SCRN&NBSP;&NBSP;FI} #创建数据库用户function  createrole () {  read  -p  "input new database username: "  NEWROLE   $DBCMD  -u $ dbuser -h  $DBsvr -c  "create role  $NEWROLE;"    $DBCMD  -U  $DBUSER  -h  $DBSVR  -c  "alter role  $NEWROLE   login; "} #创建新的数据库function  createdb () {  read -p  "Input new database username:   " newdb  for db in $ (echo  $DBS)   do     $DBCMD  -U  $DBUSER  -h  $DBSVR  -c  "create database ${db}_$newdb  owner  $NEWDB; " &NBSP;&NBSP;DONE&NBSP;&NBSP,} #删除不用的数据库function  dropdb () {  read -p  "input  database username you want to drop:  " DRPDB  read -p " confirmed the database username  $DRPDB  you want to drop [y/n]:    YorN  if [  $YorN  ==  "Y"  ] | |  [  $YorN  ==  "y"  ]; then    for db in $ (echo  $DBS)     do       $DBCMD  -U  $DBUSER  -h  $DBSVR  -c  "drop database ${db}_$drpdb;"     done  else     echo  "decline or  Input incorrect! re-type again "    for i in  ' seq 3 ';  do sleep 1; echo -n  "."; DONE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SCRN&NBSP;&NBSP;FI} #搜索数据库function  searchdb () {  read -p   "input database username you want to search: "  SERDB   $DBCMD  -U  $DBUSER  -h  $DBSVR  -c  "\l"  | grep  $SERDB   [  $? -ne 0 ] && echo  "No results"} #查询数据库下的表function  shtbl ( ) {  read -p  "Input full database name you want to search:  " SERDB   $DBCMD  -U  $DBUSER  -h  $DBSVR  -d  $SERDB  -c   "\dt"   [ $? -ne 0 ] && echo  "No results"}# You need to modify the table owner Function chgowner () {  read -p  "Input database username" after importing another user  you want to change owner:  " newdb  for db in $ ( echo  $DBS)   do    for tbl in $ ($DBCMD  -U  $DBUSER  -h  $DBSVR  -qAt -c  "select tablename from pg_tables where  schemaname =  ' public '; &NBSP;${DB}_$NEWDB)     do         $DBCMD  -u   $DBUSER  -h  $DBSVR  -c  "alter table  $tbl  owner to  $NEWDB;"  ${db}_$newdb    done  done  } #显示菜单function  scrn () {  clear  echo  "#########################"   echo  " Welcome to  Db operation "  echo " ######################### "  echo " 1.export  Database "  echo " 2.create role "  echo " 3.create database "   echo  "4.import database"   echo  "5.drop database"   echo  "6.Search  database "  echo " 7.change database owner "  echo " 8.list  Tables of database "  echo " 9.Exit "  echo " 0.display menu "}# Start the Execution menu and select the action to perform scrnwhile truedo read -p  "input your choice: "  cho  case  "$cho"  in    "1")     exportdb      ;;    "2")     createrole    ;;    "3")     createdb    ;;    "4")     importdb    ;;    "5")     dropdb    ;;     "6")     searchdb    ;;      "7")   chgowner  ;;     "8")   shtbl  ;;   "9")   exit 0  ;;   "0")   scrn  ;;  *)    #echo   "usage: input your choice: {export|import|createrole| Createdb|drop|changeowner|search} "  echo " usage: press  ' 0 '  to display  menu or press  ' 9 '  to exit "esac [[  $cho  ==  9"  ]] & & exit 0 | |   continuedone

This article from the "efforts to" blog, reproduced please contact the author!

Import and export scripts for personal postgres databases