Differences

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

--- jrodos:linux--workaround [2015/03/26 14:16]
yu
+++ jrodos:linux--workaround [2015/04/09 08:40] (current)
yu
@@ Line 1: / Line 1: @@
+====== Linux Workaround ======
+[[jrodos:developer_guide|Back]]
+**0. Create a jrodos project with postgres database 8.4.**
+**1. Modify pg_hba.conf to avoid asked for password during database update.**
+yl@ubuntu:~$ su postgres
+postgres@ubuntu:/home/yl$ nano /opt/PostgreSQL/8.4/data/pg_hba.conf
+change "local all all md5" to "local all all trust"
+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"
+postgres@ubuntu:/home/yl$ nano /opt/PostgreSQL/9.3/data/pg_hba.conf
+change "local all all md5" to "local all all trust"
+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"
+restart postgres server
+yl@ubuntu:~$ sudo /opt/PostgreSQL/8.4/scripts/launchsvrctl.sh restart
+yl@ubuntu:~$ sudo /opt/PostgreSQL/9.3/scripts/launchsvrctl.sh restart
+**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" and change permission to postgres**
+mkdir pg_tmp
+chown postgres.postgres pg_tmp
+**4. Dump backup file in binary format for all databases. (postgres@ubuntu:/home/yl/pg_tmp)**
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "AgriCP" -v -f AgriCP.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "DWD_L" -v -f DWD_L.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "ERMIN" -v -f ERMIN.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "HDM" -v -f HDM.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "ModelEnvironment" -v -f ModelEnvironment.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "Prognose" -v -f Prognose.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "RTDB" -v -f RTDB.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "RoUsers" -v -f RoUsers.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "RodosHome" -v -f RodosHome.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "GeoDB" -v -f GeoDB.backup
+/opt/PostgreSQL/9.3/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b "gisdata" -v -f gisdata.backup
+**5. Create role**
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "DROP ROLE IF EXISTS jrodos";
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE ROLE jrodos LOGIN NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE";
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "ALTER ROLE jrodos WITH PASSWORD 'jrodos'";
+**6. Create database**
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"AgriCP\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"DWD_L\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"ERMIN\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"HDM\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"ModelEnvironment\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"Prognose\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"RTDB\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"RoUsers\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"RodosHome\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"GeoDB\" owner=jrodos  ENCODING='UTF8';"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -c "CREATE DATABASE \"gisdata\" owner=jrodos  ENCODING='UTF8';"
+**7. Add extension**
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -d "GeoDB" -U postgres -c 'CREATE EXTENSION postgis;'
+/opt/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**
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -d "GeoDB" -f "/opt/PostgreSQL/9.3/share/postgresql/contrib/postgis/legacy.sql"
+/opt/PostgreSQL/9.3/bin/psql -p 5433 -U postgres -d "gisdata" -f "/opt/PostgreSQL/9.3/share/postgresql/contrib/postgis/legacy.sql"
+**9. Restore non postgis Database**
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d AgriCP -v AgriCP.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d DWD_L -v DWD_L.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d ERMIN -v ERMIN.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d HDM -v HDM.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d ModelEnvironment -v ModelEnvironment.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d Prognose -v Prognose.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d RTDB -v RTDB.backup
+/opt/PostgreSQL/9.3/bin/pg_restore -i -h localhost -p 5433 -U postgres -d RoUsers -v RoUsers.backup
+/opt/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. Restore postgis Database**
+export PATH=/opt/PostgreSQL/9.3/bin:$PATH
+perl /opt/PostgreSQL/9.3/PostGIS/utils/postgis_restore.pl "GeoDB.backup" | /opt/PostgreSQL/9.3/bin/psql -h localhost -p 5433 -U postgres "GeoDB"
+perl /opt/PostgreSQL/9.3/PostGIS/utils/postgis_restore.pl "gisdata.backup" | /opt/PostgreSQL/9.3/bin/psql -h localhost -p 5433 -U postgres "gisdata"
+**12. Reset pg_hba.conf, reset "trust" to "md5" in pg_hba.conf and restart the server.**
+postgres@ubuntu:/home/yl$ nano /opt/PostgreSQL/8.4/data/pg_hba.conf
+change "local all all trust" to "local all all md5"
+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"
+postgres@ubuntu:/home/yl$ nano /opt/PostgreSQL/9.3/data/pg_hba.conf
+change "local all all trust" to "local all all md5"
+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"
+restart postgres server
+yl@ubuntu:~$ sudo /opt/PostgreSQL/8.4/scripts/launchsvrctl.sh restart
+yl@ubuntu:~$ sudo /opt/PostgreSQL/9.3/scripts/launchsvrctl.sh restart
+**13.Change database port for server, change 5432 to 5433**
+/JRodosSErverJuly2014/Manager/server.config
+**14. Restart JRodos client and server**
+   Open the old project, everything works.
+[[jrodos:developer_guide|Back]]

Wiki of Accidents Consequences Group

User Tools

Site Tools

Differences

Page Tools