User Tools

Site Tools


jrodos:windows--workaround

Differences

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

Link to this comparison view

jrodos:windows--workaround [2015/03/26 08:48]
yu created
jrodos:windows--workaround [2015/04/09 08:41] (current)
yu
Line 1: Line 1:
 +[[jrodos:​developer_guide|Back]]
  
 +**0. Create a jrodos project with postgres database 8.4 and save it.**
 +
 +**1. Modify pg_hba.conf to avoid asked for password during database update.**
 +
 +C:\Program Files (x86)\PostgreSQL\8.4\data\pg_hba.conf
 +change "host all all 127.0.0.1/​32 md5" to "host all all 127.0.0.1/​32 trust"
 +change "host all all ::1/128 md5" to "host all all ::1/128 trust"
 +
 +C:\Program Files\PostgreSQL\9.3\data\pg_hba.conf
 +change "host all all 127.0.0.1/​32 md5" to "host all all 127.0.0.1/​32 trust"
 +change "host all all ::1/128 md5" to "host all all ::1/128 trust"
 +
 +start cmd box as administrator,​ then restart postgres server
 +
 +C:​\Windows\system32>​NET STOP postgresql-x64-9.3
 +C:​\Windows\system32>​NET START postgresql-x64-9.3
 +
 +C:​\Windows\system32>​NET STOP postgresql-8.4
 +C:​\Windows\system32>​NET START postgresql-8.4
 +
 +**2. Modify JRodos Source to let table "​sld_columns"​ be created.**
 +
 +Adjust method updateGisDataDB in DatabaseUtil.java.
 +
 +private static String updateGisDataDB(String hostUrl, String userName, String password, Version version) throws SQLException {
 +        List<​String>​ actionList = new ArrayList<​String>​();​
 +        StringBuilder buffer = new StringBuilder();​
 +
 +        if (version.getVersion() < 2) {
 +            actionList.add("​ALTER TABLE geometry_columns ADD COLUMN sld bytea"​);​
 +            actionList.add("​ALTER TABLE geometry_columns ALTER COLUMN sld SET STORAGE EXTENDED"​);​
 +        }
 +        buffer.append(executeUpdates(hostUrl,​ userName, password, actionList));​
 +
 +        if (version.getVersion() < 3) {
 +            Connection con = null;
 +            PreparedStatement pstmt = null;
 +            try {
 +                con = DriverManager.getConnection(hostUrl,​ userName, password);
 +                pstmt = con.prepareStatement("​UPDATE geometry_columns SET sld = ? WHERE f_table_name = ?");
 +                File file;
 +
 +                file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_ADMINS + "​.sld"​);​
 +                pstmt.setBytes(1,​ FileUtil.getBytes(file));​
 +                pstmt.setString(2,​ DBDescription.STR_ADMINS);​
 +                pstmt.executeUpdate();​
 +
 +                file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_URBANS + "​.sld"​);​
 +                pstmt.setBytes(1,​ FileUtil.getBytes(file));​
 +                pstmt.setString(2,​ DBDescription.STR_URBANS);​
 +                pstmt.executeUpdate();​
 +
 +                file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_ROADS + "​.sld"​);​
 +                pstmt.setBytes(1,​ FileUtil.getBytes(file));​
 +                pstmt.setString(2,​ DBDescription.STR_ROADS);​
 +                pstmt.executeUpdate();​
 +
 +                file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_NPP + "​.sld"​);​
 +                pstmt.setBytes(1,​ FileUtil.getBytes(file));​
 +                pstmt.setString(2,​ DBDescription.STR_NPP);​
 +                pstmt.executeUpdate();​
 +
 +            } catch (SQLException e) {
 +                e.printStackTrace();​
 +            } finally {
 +                close(pstmt);​
 +                close(con);
 +            }
 +        }
 +
 +        boolean isPostgis2 = false;
 +        Connection con = null;
 +        try {
 +            con = DriverManager.getConnection(hostUrl,​ userName, password);
 +            PostgisDBInfo dbInfo = new PostgisDBInfo(con);​
 +            isPostgis2 = dbInfo.getMajorVersion() >= 2;
 +        } catch (SQLException e) {
 +            e.printStackTrace();​
 +        } finally {
 +            close(con);
 +        }
 +
 +
 +        if (version.getVersion() < 5 || isPostgis2) {
 +            PreparedStatement pstmt = null;
 +            try {
 +                con = DriverManager.getConnection(hostUrl,​ userName, password);
 +                PostgisDBInfo dbInfo = new PostgisDBInfo(con);​
 +                if (dbInfo.getMajorVersion() >= 2) {
 +                    actionList.add("​CREATE TABLE sld_columns " +
 +                            "​(f_table_name character varying(256) NOT NULL, sld bytea)"​);​
 +                    boolean existed = executeUpdates(hostUrl,​ userName, password, actionList).toString().length() == 0;
 +
 +                    if (!existed) {
 +                        actionList.clear();​
 +                        actionList.add("​ALTER TABLE sld_columns OWNER TO jrodos"​);​
 +                        actionList.add("​ALTER TABLE sld_columns ALTER COLUMN sld SET STORAGE EXTENDED"​);​
 +
 +                        buffer.append(executeUpdates(hostUrl,​ userName, password, actionList));​
 +
 +                        if (con.isClosed()) con = DriverManager.getConnection(hostUrl,​ userName, password);
 +                        pstmt = con.prepareStatement("​INSERT INTO sld_columns (f_table_name,​sld) VALUES (?,?​)"​);​
 +                        File file;
 +
 +                        file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_ADMINS + "​.sld"​);​
 +                        pstmt.setBytes(2,​ FileUtil.getBytes(file));​
 +                        pstmt.setString(1,​ DBDescription.STR_ADMINS);​
 +                        pstmt.executeUpdate();​
 +
 +                        file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_URBANS + "​.sld"​);​
 +                        pstmt.setBytes(2,​ FileUtil.getBytes(file));​
 +                        pstmt.setString(1,​ DBDescription.STR_URBANS);​
 +                        pstmt.executeUpdate();​
 +
 +                        file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_ROADS + "​.sld"​);​
 +                        pstmt.setBytes(2,​ FileUtil.getBytes(file));​
 +                        pstmt.setString(1,​ DBDescription.STR_ROADS);​
 +                        pstmt.executeUpdate();​
 +
 +                        file = new File(IConstants.Manager + fs + IConstants.data + fs + IConstants.hdm_europe_shapes + fs + DBDescription.STR_NPP + "​.sld"​);​
 +                        pstmt.setBytes(2,​ FileUtil.getBytes(file));​
 +                        pstmt.setString(1,​ DBDescription.STR_NPP);​
 +                        pstmt.executeUpdate();​
 +                    }
 +                }
 +            } catch (SQLException e) {
 +                e.printStackTrace();​
 +            } finally {
 +                close(pstmt);​
 +                close(con);
 +            }
 +        }
 +
 +        RoUsersAccessFunctions.saveVersion("​gisdata",​ 5, "new sld column"​);​
 +        return buffer.toString();​
 +    }
 +
 +**3. Create a folder "​pg_tmp"​ as normal user**
 +mkdir pg_tmp
 +
 +**4. Dump backup file in binary format for all databases. (postgres@ubuntu:/​home/​yl/​pg_tmp)**
 +
 +4.1 shutdown JRodos Server and Client
 +4.2 dump database as binary file, file path must be defined.
 +
 +C:​\pg_tmp>​
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\AgriCP.backup AgriCP
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\DWD_L.backup DWD_L
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\ERMIN.backup ERMIN
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\HDM.backup HDM
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\ModelEnvironment.backup ModelEnvironment
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\Prognose.backup Prognose
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\RTDB.backup RTDB
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\RoUsers.backup RoUsers
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\RodosHome.backup RodosHome
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\GeoDB.backup GeoDB
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_dump"​ -h localhost -p 5432 -U postgres -Fc -b -f C:​\pg_tmp\gisdata.backup gisdata
 +
 +
 +**5. Create role**
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "DROP ROLE IF EXISTS jrodos";​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE ROLE jrodos LOGIN NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE";​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "ALTER ROLE jrodos WITH PASSWORD '​jrodos'";​
 +
 +
 +**6. Create database**
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​AgriCP\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​DWD_L\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​ERMIN\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​HDM\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​ModelEnvironment\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​Prognose\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​RTDB\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​RoUsers\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​RodosHome\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​GeoDB\"​ owner=jrodos ENCODING='​UTF8';"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -c "​CREATE DATABASE \"​gisdata\"​ owner=jrodos ENCODING='​UTF8';"​
 +
 +
 +**7. Add extension**
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -d "​GeoDB"​ -U postgres -c "​CREATE EXTENSION postgis;"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -d "​gisdata"​ -U postgres -c "​CREATE EXTENSION postgis;"​
 +
 +**8. Prepare for restore an old backup from prior versions in the new db**
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -d "​GeoDB"​ -f "​C:​\Program Files\PostgreSQL\9.3\share\contrib\postgis-2.1\legacy.sql"​
 +"​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -p 5433 -U postgres -d "​gisdata"​ -f "​C:​\Program Files\PostgreSQL\9.3\share\contrib\postgis-2.1\legacy.sql"​
 +
 +**9. Restore non postgis Database**
 +
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d AgriCP -v AgriCP.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d DWD_L -v DWD_L.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d ERMIN -v ERMIN.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d HDM -v HDM.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d ModelEnvironment -v ModelEnvironment.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d Prognose -v Prognose.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d RTDB -v RTDB.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d RoUsers -v RoUsers.backup
 +"​C:​\Program Files\PostgreSQL\9.3\bin\pg_restore"​ -i -h localhost -p 5433 -U postgres -d RodosHome -v RodosHome.backup
 +
 +**10. Execute following DO Block inside pg_admin.**
 +
 +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$$;
 +
 +**11. Set path**
 +
 +add "​C:​\Program Files\PostgreSQL\9.3\bin"​ in PATH of system umgebungsvariable
 +
 +**12. install and test perl**
 +
 +download strawberry-perl-5.20.2.1-64bit.msi from http://​strawberryperl.com/​releases.html
 +install strawberry-perl-5.20.2.1-64bit.msi
 +Open a new cmd window, following command gives output of perl version
 +>perl --version ​
 +
 +**13. Restore postgis Database**
 +
 +C:​\pg_tmp>​
 +
 +"​C:​\Strawberry\perl\bin\perl"​ "​C:​\Program Files\PostgreSQL\9.3\utils\postgis_restore.pl"​ "​GeoDB.backup"​ | "​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -h localhost -p 5433 -U postgres "​GeoDB" ​
 +
 +"​C:​\Strawberry\perl\bin\perl"​ "​C:​\Program Files\PostgreSQL\9.3\utils\postgis_restore.pl"​ "​gisdata.backup"​ | "​C:​\Program Files\PostgreSQL\9.3\bin\psql"​ -h localhost -p 5433 -U postgres "​gisdata" ​
 +
 +**14. Reset pg_hba.conf,​ reset "​trust"​ to "​md5"​ in pg_hba.conf and restart the server.**
 +
 +C:\Program Files (x86)\PostgreSQL\8.4\data\pg_hba.conf
 +change "host all all 127.0.0.1/​32 trust" to "host all all 127.0.0.1/​32 md5"
 +change "host all all ::1/128 trust" to "host all all ::1/128 md5"
 +
 +C:\Program Files\PostgreSQL\9.3\data\pg_hba.conf
 +change "host all all 127.0.0.1/​32 trust" to "host all all 127.0.0.1/​32 md5"
 +change "host all all ::1/128 trust" to "host all all ::1/128 md5"
 +
 +start cmd box as administrator,​ then restart postgres server
 +C:​\Windows\system32>​NET STOP postgresql-8.4
 +C:​\Windows\system32>​NET START postgresql-8.4
 +
 +C:​\Windows\system32>​NET STOP postgresql-x64-9.3
 +C:​\Windows\system32>​NET START postgresql-x64-9.3
 +
 +**15.Change database port for server in JRodos Source, change 5432 to 5433**
 +
 + "​Manager/​server.config"​
 +
 +**16. Restart JRodos client and server**
 +
 +   Open the old project, everything works.
 +   
 +[[jrodos:​developer_guide|Back]]
jrodos/windows--workaround.txt ยท Last modified: 2015/04/09 08:41 by yu