Wiki of Accidents Consequences Group

Back

@ECHO off

SET dumphost=localhost

SET dumpport=5432

SET restorehost=localhost

SET restoreport=5433

SET user=postgres

SET legacysql=“C:\Program Files\PostgreSQL\9.3\share\contrib\postgis-2.1\legacy.sql”

SET restorepl=“C:\Program Files\PostgreSQL\9.3\utils\postgis_restore.pl”

SET pgdump=“C:\Program Files\PostgreSQL\9.3\bin\pg_dump”

SET psqlcmd=“C:\Program Files\PostgreSQL\9.3\bin\psql”

SET pgrestore=“C:\Program Files\PostgreSQL\9.3\bin\pg_restore”

SET perlcmd=“C:\Strawberry\perl\bin\perl”

SET perlbin=“C:\Strawberry\perl\bin”

SET dumpfolderfiles=“C:\Jrodos_Updatedb_8xTo9x\*.*”

SET dumpfolder=C:\Jrodos_Updatedb_8xTo9x

SET fileseparator=\

SET dbnopostgis=AgriCP.backup,DWD_L.backup,ERMIN.backup,HDM.backup,ModelEnvironment.backup,Prognose.backup,RTDB.backup,RoUsers.backup,RodosHome.backup

SET dbseperator=,

SET dbwithpostgis=GeoDB.backup,gisdata.backup

SET dumpfilenames=%dbnopostgisdbseperatordbwithpostgis%

SET PATH=%PATH%;%perlbin%

SETLOCAL ENABLEDELAYEDEXPANSION

ECHO Create a folder for backup files

CD /D C:\

IF EXIST %dumpfolder% (

del /F /Q /S %dumpfolderfiles%
ECHO Folder has been cleared

) ELSE (

MKDIR %dumpfolder%

)

CD /D %dumpfolder%

ECHO Dump Databases

FOR x IN ("%dumpfilenames:,=" "%") DO ( set dumpfilename=!dumpfilename!x

 set dbname=!dumpfilename:~1,-8!
 %pgdump% -h %dumphost% -p %dumpport% -U %user% -Fc -b -f !dumpfilename! !dbname!
 SET dumpfilepath=
 SET dumpfilename=

)

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 x IN ("%dumpfilenames:,=" "%") DO ( set dumpfilename=!dumpfilename!x

 set dbname=!dumpfilename:~1,-8!
 %psqlcmd% -h %restorehost% -p %restoreport% -U postgres -c "CREATE DATABASE \"!dbname!\"  owner=jrodos ENCODING='UTF8';"
 SET dumpfilepath=
 SET dumpfilename=

)

ECHO ADD EXTENSION ECHO Execute Legacy.sql for postgis database

FOR x IN ("%dbwithpostgis:,=" "%") DO ( set dumpfilename=!dumpfilename!x

 set dbname=!dumpfilename:~1,-8!
 %psqlcmd% -h %restorehost% -p %restoreport% -d "!dbname!" -U postgres -c "CREATE EXTENSION postgis;"
 %psqlcmd% -h %restorehost% -p %restoreport% -U postgres -d "!dbname!" -f %legacysql%
 SET dumpfilepath=
 SET dumpfilename=

)

ECHO Restore none postgis databases

FOR x IN ("%dbnopostgis:,=" "%") DO ( set dumpfilename=!dumpfilename!x

 set dbname=!dumpfilename:~1,-8!
 %pgrestore% -i -h %restorehost% -p %restoreport% -U postgres -d !dbname! -v !dumpfilename!
 SET dumpfilepath=
 SET dumpfilename=

)

ECHO Allow Large Object in postgresql database 9.x

rem %psqlcmd% -h %restorehost% -p %restoreport% -U -d “ModelEnvironment” postgres -f “allow_jrodos_lo.sql” rem %psqlcmd% -h %restorehost% -p %restoreport% -U postgres -c “SELECT allow_jrodos_lo();” %psqlcmd% -h %restorehost% -p %restoreport% -U postgres -d “ModelEnvironment” -c “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$$;”

ECHO Restore postgis databases

FOR x IN ("%dbwithpostgis:,=" "%") DO ( set dumpfilename=!dumpfilename!x

 set dbname=!dumpfilename:~1,-8!
 %perlcmd% %restorepl% !dumpfilename! | %psqlcmd% -h %restorehost% -p %restoreport% -U postgres "!dbname!"
 SET dumpfilepath=
 SET dumpfilename=

)

ECHO Database Update finished.

Back