7.13.1 Upgrading to PostGIS 2.0
7.13.1 Upgrading to PostGIS 2.0If 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.The following summarizes the basic steps required to perform an upgrade to Advanced Server 9.4 when your current version of Advanced Server has PostGIS 1.x installed along with PostGIS databases:
• In your current Advanced Server cluster, use pg_dump to create a custom-format backup of each PostGIS 1.x database.
• Drop the PostGIS databases that were backed up, drop database template template_postgis, and uninstall PostGIS.
• Perform the upgrade to Advanced Server 9.4 using pg_upgrade as described in , Upgrading to Advanced Server 9.4. This upgrades all non-PostGIS databases to Advanced Server 9.4.
• The following example illustrates an upgrade from Advanced Server 9.1 to Advanced Server 9.4 when PostGIS 1.5 is installed in Advanced Server 9.1, and a PostGIS database named roadmaps exists in the Advanced Server 9.1 cluster.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" roadmapsPlease 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:template1=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template_postgis';
template1=# DROP DATABASE template_postgis;
DROP DATABASEBefore dropping a PostGIS database, be sure you have a backup (see Step 1). Then, drop the roadmaps database:As a superuser, run the following script located in the Advanced Server home directory to uninstall PostGIS from Advanced Server 9.1:Perform steps 1 through 7 described in to upgrade to Advanced Server 9.4.Empty the edb database by dropping it and creating it.For both database servers, set the authentication mode to trust in pg_hba.conf.Assume the identity of the cluster owner and change the current working directory to a temporary directory.Perform a consistency check using the --check option of pg_upgrade.pg_upgrade -d /opt/PostgresPlus/9.1AS/data -D /opt/PostgresPlus/9.4AS/data -u enterprisedb -b /opt/PostgresPlus/9.1AS/bin -B /opt/PostgresPlus/9.4AS/bin -p 5444 -P 5445 --checkPerform the upgrade (omit the --check option).pg_upgrade -d /opt/PostgresPlus/9.1AS/data -D /opt/PostgresPlus/9.4AS/data -u enterprisedb -b /opt/PostgresPlus/9.1AS/bin -B /opt/PostgresPlus/9.4AS/bin -p 5444 -P 5445Please Note: At this time, skip step 8 of ; 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.For information about using StackBuilder Plus, see , Using StackBuilder Plus.Create the PostGIS database with the template template_postgis as shown by the following:If your applications require legacy PostGIS functions, these can be added using the legacy.sql script:edb-psql -d roadmaps -U enterprisedb -p 5445 -f "/opt/PostgresPlus/9.4AS/share/contrib/postgis-2.0/legacy.sql"These legacy functions can later be removed with the uninstall_legacy.sql script.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.$ perl /opt/PostgresPlus/9.4AS/share/contrib/postgis-2.0/postgis_restore.pl "/tmp/roadmaps.backup" | edb-psql -U enterprisedb -p 5445 roadmaps 2> /tmp/roadmaps_loaderr.txtThe 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.