7.13.1 Upgrading to PostGIS 2.0

Table of Contents Previous Next


7 Advanced Server Supporting Components : 7.13 PostGIS : 7.13.1 Upgrading to PostGIS 2.0

If you are upgrading an existing Advanced Server installation (version 9.1 or prior) that contains PostGIS to a later version of Advanced Server, you must perform a manual upgrade. The pg_upgrade tool does not support upgrades of spatially enabled databases (a database that contains PostGIS database objects (functions, types, operators, tables, etc.).
To upgrade a database using PostGIS 1.x to PostGIS 2.x, use the pg_dump and pg_restore commands to perform a dump and restore of the PostGIS database. Please note: the host must contain an installation of Perl to perform this upgrade.
In your current Advanced Server cluster, use pg_dump to create a custom-format backup of each PostGIS 1.x database.
Perform the upgrade to Advanced Server 9.4 using pg_upgrade as described in Section 8.3, Upgrading to Advanced Server 9.4. This upgrades all non-PostGIS databases to Advanced Server 9.4.
The roadmaps PostGIS database was created in Advanced Server 9.1 using the following command given in EDB-PSQL:
In the roadmaps database, table roads was created and loaded with some rows:
CREATE TABLE roads (id INTEGER, name VARCHAR2(128));

SELECT AddGeometryColumn('roads', 'geom', -1, 'GEOMETRY', 2);

INSERT INTO roads (id, geom, name) VALUES (1, GeomFromText('LINESTRING(0 10,0 0)', -1), 'Beacon Road');
INSERT INTO roads (id, geom, name) VALUES (2, GeomFromText('LINESTRING(0 0,0 10)', -1), 'Violet Road');
INSERT INTO roads (id, geom, name) VALUES (3, GeomFromText('LINESTRING(0 0,10 0)', -1), 'Skelton Street');
INSERT INTO roads (id, geom, name) VALUES (4, GeomFromText('LINESTRING(0 0,10 10)', -1), 'Fifth Avenue');
INSERT INTO roads (id, geom, name) VALUES (5, GeomFromText('LINESTRING(10 0,0 0)', -1), 'Lipton Street');

CREATE INDEX roads_index ON roads USING GIST(geom);
For purposes of this example, roadmaps is assumed to be the only PostGIS database in the cluster.
Following the directions for performing a hard upgrade, run pg_dump to create a backup file for the roadmaps database using the following command:
export PATH=$PATH:/opt/PostgresPlus/9.1AS/bin

pg_dump -U enterprisedb -Fc -b -v -f
"/tmp/roadmaps.backup" roadmaps
Please Note: The backup file must be in custom-format as specified by the -Fc option. This is required by the Perl script you will later use to load the new PostGIS 2.0 database.
While connected to database template1 as a superuser, drop the database template template_postgis as shown by the following:
Perform steps 1 through 7 described in Section 8.3 to upgrade to Advanced Server 9.4.
Empty the edb database by dropping it and creating it.
Perform a consistency check using the --check option of pg_upgrade.
Please Note: At this time, skip step 8 of Section 8.3; do not restore the authentication settings in the pg_hba.conf file; the server must use trust authentication when loading the new PostGIS 2.0 databases from the backup files.
Create the PostGIS database with the template template_postgis as shown by the following:
Run the Perl script postgis_restore.pl and pipe the output to EDB-PSQL to load the database from the backup file. This script contains functionality to skip objects known to PostGIS (since updated versions of these objects have been created in the new database you are loading) as well as convert certain old PostGIS constructs into new ones.
Be sure trust authentication mode is set in pg_hba.conf before running this script.
The following query verifies the content of the roads table in the roadmaps database:
Update the contents of the pg_hba.conf file to reflect your preferred authentication settings.

7 Advanced Server Supporting Components : 7.13 PostGIS : 7.13.1 Upgrading to PostGIS 2.0

Table of Contents Previous Next