This shows you the differences between two versions of the page.
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]] |