Differences

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

--- 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]]

Wiki of Accidents Consequences Group

User Tools

Site Tools

Differences

Page Tools