User Tools

Site Tools


jrodos:linux--workaround

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
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]]
jrodos/linux--workaround.txt ยท Last modified: 2015/04/09 08:40 by yu