@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.