User Tools

Site Tools


jrodos:batchscript

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
jrodos:batchscript [2015/03/26 08:53]
yu
jrodos:batchscript [2015/04/09 08:41] (current)
yu
Line 1: Line 1:
 +[[jrodos:​developer_guide|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=%dbnopostgis%%dbseperator%%dbwithpostgis%
 +
 +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.
 +
 +[[jrodos:​developer_guide|Back]]
jrodos/batchscript.txt ยท Last modified: 2015/04/09 08:41 by yu