ArcSDE 10 for PostgreSQL Install Guide - Upgrading from PostgreSQL 8.3.0 to 8.4.1 on Windows

Upgrades from PostgreSQL 8.3.x to 8.4.x are considered hard upgrades and require you to back up your existing databases, install the new PostgreSQL version, and restore the databases to the new database cluster.

You should follow instructions in the PostgreSQL documentation for upgrading from PostgreSQL 8.3.0 to 8.4.1, but there are some steps you must take that are specific to using PostgreSQL with ArcSDE. Those steps are included here along with a summary of the PostgreSQL upgrade process.

Tip:

Most of the PostgreSQL and SQL commands shown in the examples are run as the postgres superuser.

Steps:

Back up each of your databases using the pg_dump command rather than using the pg_dumpall command.
You must include the –F option to back up to a custom format archive file to allow a hard upgrade. In this example, a custom format archive dump file (spdatabu.dump) of the database spdata is created by the postgres super user.
```
pg_dump -U postgres -F c spdata > spdatabu.dump
```
Stop the PostgreSQL 8.3.0 server.
If you customized your pg_hba.conf and postgresql.conf files, copy these files to another directory so you have them for your new PostgreSQL server.
Uninstall PostgreSQL if you are replacing 8.3.0 with 8.4.1 and delete the old PostgreSQL installation directory.
Install PostgreSQL 8.4.1 and configure a database cluster.
Be sure to make the custom settings you had in the pg_hba.conf and postgresql.conf files. If you were using PostGIS with PostgreSQL 8.3.0, be sure to install PostGIS.
Create directories on the server to store tablespaces.
The postgres operating system user must have read/write access to the directory.
Create the tablespace or tablespaces you will use for your databases.
```
CREATE TABLESPACE gists
OWNER sde
LOCATION 'D:/pgtablespaces/gists';
```
Create the sde log in role.
The sde user requires superuser and createdb permission to create the ArcSDE geodatabase schema. The superuser permission can be revoked after geodatabase creation but must be regranted before the geodatabase can be upgraded.
This example shows the creation of an sde login role and password. Sde is being granted superuser permissions and the permissions to create database objects and roles. Noinherit is specified because the sde user is not usually placed in a login group.
```
CREATE ROLE sde LOGIN
PASSWORD averturis
SUPERUSER NOINHERIT CREATEDB CREATEROLE;
```
Create databases on the new database cluster to which you will restore your old databases.
The new databases must have the same properties as the old databases, including name, encoding, and owner. If you were using PostGIS in the old database, the new database must also be able to use PostGIS.
In this example, using the createdb command, the new database is owned by sde and has an encoding of UTF8 and a default tablespace of gistbs.
```
createdb -U postgres -E UTF8 -D gistbs 
-O sde spdata
```
Here is the same command in SQL:
```
CREATE DATABASE spdata
WITH OWNER sde
ENCODING = 'UTF8'
TABLESPACE = 'gistbs';
```
For each database that contains a geodatabase, set the search_path variable to include the sde schema.
```
ALTER DATABASE spdata
SET search_path = "$user",public,sde;
```
Re-create the login and group roles you had on your old PostgreSQL server.
Restore each database using the pg_restore command.
Caution:
You must run the pg_restore command twice for each database that contains a geodatabase: first, restore the contents of the public schema, then restore the whole database. The public schema contains the sde_spatial_references table. This table must be present in the database before you restore your spatial data.
Restore the public schema.
```
pg_restore -U postgres -n public -d spdata spdatabu.dump
```
Restore the entire database.
```
pg_restore -U postgres -d spdata spdatabu.dump
```

Your databases are now upgraded.

Now you must upgrade your geodatabases. See Preparing to upgrade a geodatabase in PostgreSQL to get started.

10/4/2012