transfer EDA2.0 to postgis 2.4 on the server

[cedric]

The restoration from the old version of postgis 1.5 didn't go well. Below notes on how to do it, in the end really manually to rebuild the structure of some tables and rebuild it.
On the server the version is still (postgres 9.1 postgis 1.5), now in local we have two working versions which we will test before finalizing the database transfer

[cedric]

​http://www.postgis.org/docs/postgis_installation.html

Open postgres9.6 on local network

• Manual edition of pg_hba.conf (the editor from pgaminIII does not accept changes
• Opening port of the new server on firewall
• Trusting postgres on new port in local firewall
• change path to new psql version (currently pointing to 9.1), will take effect when we restart the server

Change database password

"C:\Program Files\PostgreSQL\9.6\bin\psql" -U superuser-h localhost -p 5434  maintenance_database

{{{!sql
ALTER USER user_name WITH PASSWORD 'new_password';
}}}

Hard upgrade of the trac

following instructions ​on postgis, I need a hard updgrade to reinstall the database to the new server

There are specific instructions for windows

CLEAN HARD UPGRADE ON WINDOWS

adapted from ​osgeo_script

Need to do a hard upgrade when upgrading from one major version to another, such as 1.* to 2.*.
Create a backup of your postgis database as usual. You can do this with pg_dump

Created a "custom-format" dump of the database . Include binary blobs (-b) and verbose (-v) output. Used admin account. The psql version is the one from my computer 9.6.3

F:
cd F:/base
"pg_dump" -h 1.100.1.6 -p 5432 -U postgres -Fc -b -v -f "dbeel.backup" dbeel

Do a fresh install of PostGIS in a new database (on the 9.6 server).

createdb -h 1.100.1.6 -p 5434 -U postgres dbeel
psql -h 1.100.1.6 -p 5434 -U postgres dbeel
dbeel=# CREATE EXTENSION postgis
dbeel=# CREATE EXTENSION postgis_topology
dbeel=#\q

You may also want to install legacy.sql if you have functions that use deprecated PostGIS functions.

for me it is :

psql -h 1.100.1.6 -p 5434 -U postgres -c 'psql -h 1.100.1.6 -p 5434 -U postgres -f "C:\Program Files\PostgreSQL\9.6\share\contrib\postgis-2.4\legacy.sql" dbeel

I'm getting a lot of return like

psql:C:/Program Files/PostgreSQL/9.6/share/contrib/postgis-2.4/legacy.sql:1585: NOTICE:  le type de retour geometry est seulement un shell

If you don't have perl on your windows box, download ​ActivePerl? and install. You can use the 64-bit or 32-bit version even if you are using 32-bit PostgreSQL. ​>>​http://www.activestate.com/activeperl/downloads.
When prompted to do so, choose to add Perl to your environment path and map perl to .pl scripts.

perl is installed on my computer

Create an upgrade.bat file with contents something like this and save in same folder as your database backup:

set PGPORT=5434
set PGHOST=w3
set PGUSER=postgres
set PGPASSWORD=yourpasswordhere
set THEDB=dbeel
set BACKUPFILE=dbeel.backup
set PGINSTALL=C:\Program Files\PostgreSQL\9.6
set PATH=%PATH%;"%PGINSTALL%\bin"
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt
pause

Edit the script accordingly to your path settings. Note that utils folder is packaged with the PostGIS tar balls
if not present in your PostGIS install. Download the tar ball for the PostGIS you want to upgrade to and copy the utils folder to the same folder as your backup. ​​http://postgis.net/source

Right mouse click on the .bat file (you may have to choose -- Run as Administrator) or run if run as administrator is not available.
If all is working -- you should see a screen something like:
ALTER TABLE
ALTER TABLE
SET
Clicking enter after the process is done will close the batch script.

When all is done, check the errors.txt file to see if there are any serious errors. Serious errors are tables that did not come back, or some of your own functions that did not come back, your specialty constraints and indexes. PostGIS function failures should not show, but if they do, you can ignore them.

[cedric]

MANUAL RESTORATION EDA2.0

none of other scripts have worked I have to do it the hard way for the ccm21 scheme. Then I can apply the hard upgrade on other tables
the manual save is as following :
Save structure of table
Edit manually
Launch structure
save content of tables using -a options
restore it

1 ccm21

source:eda/EDACCM/sql/script_restore_v15to24.sql

savingd schema ccm21

 pg_dump -U postgres -h 1.100.1.6 -W -s --schema ccm21 -f "ccm21.catchments_str.sql" eda2

remove reference to public.geometry
verify base functions OK

catchments

 pg_dump -U postgres -h 1.100.1.6 -a -W --verbose -f "ccm21.catchments.sql" --table ccm21.catchments eda2
psql -U postgres -h localhost -f "ccm21.catchments.sql" eda2.0

riversegments

pg_dump -U postgres -h 1.100.1.6 -a -W --verbose -f "ccm21.riversegments.sql" --table ccm21.riversegments eda2
psql -U postgres -h localhost -f "ccm21.riversegments.sql" eda2.0

all other tables

pg_dump -U postgres -h 1.100.1.6 -a -W --schema ccm21 --verbose -f "ccm21.other.sql" --exclude-table-data=ccm21.riversegments --exclude-table-data=ccm21.catchments  eda2
psql -U postgres -h localhost -f "ccm21.other.sql" eda2.0

2 dbeel

Pour le schema dbeel

 CREATE EXTENSION "uuid-ossp"

set SCHEMA=dbeel_nomenclature
set BACKUPFILE=dbeel_nomenclature.backup
set PGPORT=5432
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=?
set THEDBDESTINATION=eda2.0
set PGINSTALL=C:\Program Files\PostgreSQL\9.6
set PATH=%PATH%;"%PGINSTALL%\bin"
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDBDESTINATION% 2> errors.txt
set BACKUPFILE=dbeel.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDBDESTINATION% 2> errors.txt

3 others

set THEDBD=eda2.0
set BACKUPFILE=anglian.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=bd_map.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=oria.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=belge.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=dmeer2003.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=ecoregion2003.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=europe.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

set BACKUPFILE=european_wise2008.backup
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

[cedric]

MANUAL SAVE AND RESTORATION DBEEL

F:
cd F:/base
set PGPORT=5432
set PGHOST=w3....
set PGUSER=postgres
set PGPASSWORD=?
set THEDBSOURCE=dbeel
set PGINSTALL=C:\Program Files\PostgreSQL\9.6
set PATH=%PATH%;"%PGINSTALL%\bin"
set BACKUPFILE=dbeel_base.backup
pg_dump --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM CREATION D'UNE BASE PROPRE DBEEL AVEC EXTENSION (VERFIER)
set PGHOST=localhost
set PGPASSWORD=?
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

[maria]

Restoration notes in localhost Maria

pb with

create extension postgis_topology

pb with perl
saving from localhost cedric

pg_dump -U postgres -h localhost -W --schema anglian --schema oria --schema belge --schema ireland -f "other1.sql" eda2.0
pg_dump -U postgres -h localhost -W --schema bd_map --schema dmeer2003 --schema ecoregion2003 --schema europe -f "other2.sql" eda2.0
pg_dump -U postgres -h localhost -W --schema european_wise2008 -f "european_wise2008.sql" eda2.0
pg_dump -U postgres -h localhost -W --schema dbeel_nomenclature --schema dbeel -f "dbeel.all.sql" eda2.0

psql -U postgres -h localhost -f "other1.sql" eda2.0
psql  -U postgres -h localhost -f "other2.sql" eda2.0
psql  -U postgres -h localhost -f "dbeel.all.sql" eda2.0 
psql  -U postgres -h localhost -f "european_wise2008.sql" eda2.0