This shows you the differences between two versions of the page.
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]] |