Back

Two shellscripts have been used.

The main.sh create the folder and assign the permission, it also change the user and then execute the second shell script dumprestore.jrodos.8to9.sh.

Following the steps below the execute the shell scripts:

1. Make the both shell scripts executable. (sudo chmod +x *.sh)
2. Login as root
3. Stop the database connection. (Stop jrodos server and client, Unchoose the database in pgadmin)
4. Execute the main.sh

#!/bin/bash

DUMPFOLDER=“/tmp/pg_jrodos_tmp” DUMPFOLDERFILES=$DUMPFOLDER“*” FILESEPARATOR=“/” SCRIPTPATH=“/data/iket/Git_JRodosSource/UpdateDB/dumprestore.jrodos.8to9.sh”

USER=postgres USERGROUP=postgres

if [ “$EUID” -ne 0 ]; then

echo "Please run as root for assign permission."
echo "Process has been aborted."

fi

echo Create a folder for backup files: echo $DUMPFOLDER

cd /tmp

if [ ! -d $DUMPFOLDER ]; then

 mkdir -p  $DUMPFOLDER

else

 rm -rf $DUMPFOLDERFILES
 mkdir -p  $DUMPFOLDER

fi

echo Assign permission for the folder

chown -R $USER.$USERGROUP $DUMPFOLDER

cd $DUMPFOLDER

echo Login as database user

su $USER -c “bash $SCRIPTPATH”

#!/bin/bash

DUMPFOLDER=“/tmp/pg_jrodos_tmp” DUMPFOLDERFILES=$DUMPFOLDER“*” FILESEPARATOR=“/”

USER=postgres USERGROUP=postgres

DUMPHOST=localhost DUMPPORT=5432 RESTOREHOST=localhost RESTOREPORT=5433

POSTGRESBIN=“/data/opt/PostgreSQL/9.3/bin” LEGACYSQL=“/data/opt/PostgreSQL/9.3/share/postgresql/contrib/postgis/legacy.sql” RESTOREPL=“/opt/PostgreSQL/9.3/PostGIS/utils/postgis_restore.pl” PGDUMP=“/opt/PostgreSQL/9.3/bin/pg_dump” PSQLCMD=“/opt/PostgreSQL/9.3/bin/psql” PGRESTORE=“/opt/PostgreSQL/9.3/bin/pg_restore”

PATH=$PATH$POSTGRESBIN

declare -a DBNOPOSTGIS=(“AgriCP” “DWD_L” “ERMIN” “HDM” “ModelEnvironment” “Prognose” “RTDB” “RoUsers” “RodosHome”) declare -a DBWITHPOSTGIS=(“GeoDB” “gisdata”)

DBNAMES=()

for DBNAME in “${DBNOPOSTGIS[@]}” “${DBWITHPOSTGIS[@]}” do

      DBNAMES+=("$DBNAME")

done

for DBNAME in “${DBNAMES[@]}” do

 echo "Dump database $DBNAME"
 $PGDUMP -h $DUMPHOST -p $DUMPPORT -U $USER -Fc -b -f $DUMPFOLDER$FILESEPARATOR$DBNAME".backup" $DBNAME

done

echo Create role jrodos

$PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -c “DROP ROLE IF EXISTS jrodos;”; $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -c “CREATE ROLE jrodos LOGIN NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;”; $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -c “ALTER ROLE jrodos WITH PASSWORD 'jrodos'”;

echo Create database

for DBNAME in “${DBNAMES[@]}” do

 echo "Create database $DBNAME"
 $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -c "CREATE DATABASE \"$DBNAME\" owner=jrodos ENCODING='UTF8';"

done

echo Add extension echo Execute Legacy.sql for postgis database

for DBNAME in “${DBWITHPOSTGIS[@]}” do

 $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -d $DBNAME  -c "CREATE EXTENSION postgis;"
 $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -d $DBNAME -f $LEGACYSQL

done

echo Restore none postgis databases

for DBNAME in “${DBNOPOSTGIS[@]}” do

 $PGRESTORE -i -h $RESTOREHOST -p $RESTOREPORT -U $USER -d $DBNAME -v $DBNAME".backup"

done

echo Allow Large Object in postgresql database 9.x

$PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER -d “ModelEnvironment” « EOF DO \$\$ DECLARE r record; BEGIN FOR r in SELECT loid FROM pg_catalog.pg_largeobject LOOP EXECUTE 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO jrodos'; END LOOP; END\$\$; EOF

echo Restore postgis databases

for DBNAME in “${DBWITHPOSTGIS[@]}” do

 perl $RESTOREPL $DBNAME".backup" | $PSQLCMD -h $RESTOREHOST -p $RESTOREPORT -U $USER $DBNAME

done

echo Database Update finished.

Back