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]

The following details steps for hard updgrade which did not work for two reasons :

1 psql fails to create the extension, it works in pgadmin
2 the script did not get rid of a public.geometry in the ccm which I had to get rid off manually.

psql -h localhost -p 5432 -W -U postgres eda2.0
dbeel=# CREATE EXTENSION postgis
dbeel=# CREATE EXTENSION postgis_topology

First set if trials (see manual below for the version that works)

​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

ALTER USER user_name WITH PASSWORD 'new_password';

Hard upgrade

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 "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.

---

SECOND set of trials, using legacy.sql did not solve the problem

---

CREATE database EDA2.0 on the new postgres 9.6 server

-W prompts for password,

createdb -U postgres -W -p 5434 -h 1.100.1.6 eda2.0
psql -d eda2.0 -h 1.100.1.6 -p 5434 -W -c "CREATE EXTENSION postgis;"
psql -d eda2.0 -h 1.100.1.6 -p 5434 -W -c "CREATE EXTENSION postgis_topology;"

If you plan to restore an old backup from prior versions in this new db, run:

psql -d [yourdatabase] -f legacy.sql

You can later run uninstall_legacy.sql to get rid of the deprecated functions after you are done with restoring and cleanup.

Let's see if that works before

The .bat file, change schema and backupfile

set PGPORT=5432
set PGHOST=1.100.1.6
set PGUSER=postgres
set PGPASSWORD=?
set THEDBSOURCE=eda2
set THEDEBDESTINATION=eda2.0
set PGHOST=1.100.1.6
set BACKUPFILE=anglian.backup
set SCHEMA = anglian
set PGINSTALL=C:\Program Files\PostgreSQL\9.6
set PATH=%PATH%;"%PGINSTALL%\bin"
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
set PGPORT=5434
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

list schema

psql -U postgres -h w3.eptb-vilaine.fr eda2
eda2=# \dn

anglian
bd_map
belge
ccm21
clc
dbeel
dbeel_nomenclature
dmeer2003
ecoregion2003
europe
european_wise2008
france
geobs2010
ia
ireland
onema
oria
public
temperature
tidal
uga2010
uk
vilaine
wrbd

EDA2.0

F:
cd F:/base
set PGPORT=5432
set PGHOST=w3
set PGUSER=postgres
set PGPASSWORD=?
set THEDBSOURCE=eda2
set PGINSTALL=C:\Program Files\PostgreSQL\9.6
set PATH=%PATH%;"%PGINSTALL%\bin"

set SCHEMA=anglian
set BACKUPFILE=anglian.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA=bd_map
set BACKUPFILE=bd_map.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA=belge
set BACKUPFILE=belge.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

REM OK
set SCHEMA=ccm21
set BACKUPFILE=ccm21.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA=clc
set BACKUPFILE=clc.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA=dbeel
set BACKUPFILE=dbeel.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA=dbeel_nomenclature
set BACKUPFILE=dbeel_nomenclature.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA=dmeer2003
set BACKUPFILE=dmeer2003.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA=ecoregion2003
set BACKUPFILE=ecoregion2003.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA= europe
set BACKUPFILE= europe.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA= european_wise2008
set BACKUPFILE= european_wise2008.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA= france
set BACKUPFILE= france.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA= geobs2010
set BACKUPFILE= geobs2010.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= ia
set BACKUPFILE= ia.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= ireland
set BACKUPFILE= ireland.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM OK
set SCHEMA= onema
set BACKUPFILE= onema.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= oria
set BACKUPFILE= oria.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM TODO
set SCHEMA= temperature
set BACKUPFILE= temperature.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= tidal
set BACKUPFILE= tidal.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= uk
set BACKUPFILE= uk.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

set SCHEMA= vilaine
set BACKUPFILE= vilaine.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%
REM TODO
set SCHEMA= wrbd
set BACKUPFILE= wrbd.backup
pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE%

The first trials didn't work. I'll have to split my databases in several components.
Size of databases

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

| table_name | size |
| eda2|10 GB|
|eda2.1|3305 MB|

[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