User Tools

Site Tools


jrodos:windows--workaround

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.
 

Back

jrodos/windows--workaround.txt · Last modified: 2015/04/09 08:41 by yu