ArcSDE 10 for PostgreSQL Install Guide - Creating a geodatabase in PostgreSQL 8.4.1 on Windows

The procedure to set up a geodatabase in PostgreSQL 8.4.1 is different than setting up a geodatabase in PostgreSQL 8.3.8 on Windows.

You obtain and install PostgreSQL 8.4.1 for Windows. (ESRI does not distribute it.)
Be sure the PL/pgsql language is installed. If it is not, you will not be able to create a geodatabase. You can execute the following SQL command to discover if the language is installed:
```
SELECT true
FROM pg_catalog.pg_language
WHERE lanname = 'plpgsql';
```
If t is returned, the language is installed; if f is returned, it is not and you must install it.
When you run the ArcSDE installation wizard, uncheck PostgreSQL 8.3.8 on the ArcSDE for PostgreSQL Installation Options dialog box.
Copy the st_geometry.dll file from the pg841_st_lib directory in SDEHOME to the PostgreSQL library directory.
Do not use the ArcSDE for PostgreSQL Post Installation wizard. Instead, you must manually create a tablespace, database, and sde login role in the PostgreSQL database cluster; grant the sde role superuser permission in the database cluster; create an sde schema in the database and grant roles access to it; add the sde schema to the database's search_path variable; and use the ArcSDE administrative tools to set up a geodatabase in the database.

Steps:

Configure the PostgreSQL database cluster to accept client connections.
Configure the postgresql.conf file.
Install ArcSDE for PostgreSQL.
Copy the st_geometry.dll file (and libst_raster_pg.dll file, if you want to use ST_Raster storage) from %SDEHOME%/pg841_st_lib to the PostgreSQL library directory.
By default, the PostgreSQL library directory is located at Program Files\PostgreSQL\8.4\lib on Windows 32-bit operating systems and Program Files (x86)\PostgreSQL\8.4\lib on Windows 64-bit operating systems.
Create a folder on the server to store your tablespace.
Tablespaces must be created in empty folders.
Log in to a SQL client as a PostgreSQL superuser.
Create a tablespace.
In this example, the tablespace gis is created in a folder named gis and is owned by the sde superuser.
```
CREATE TABLESPACE gis
OWNER sde
LOCATION 'D:/tablespaces/gis';
```
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 a database.
In this example, the database spdata uses an encoding of UTF8 and the gis tablespace as its default tablespace. Also, it is owned by the sde superuser.
```
CREATE DATABASE spdata
WITH OWNER = sde
ENCODING = 'UTF8'
TABLESPACE = 'gis';
```
Create the sde role's schema in the database and grant the sde user full permissions on the schema.
The schema name must be sde.
Note:
The CREATE SCHEMA command runs against the database to which you are currently connected. If you are executing the SQL in the pgAdmin III Query window, be sure to use the drop-down list to connect to your new database. If you are executing the SQL in psql, you must log out and back in, connecting to your new database.
```
CREATE SCHEMA sde
AUTHORIZATION sde;
GRANT ALL ON SCHEMA sde TO sde;
```
Grant access to the sde schema to all users connecting to the geodatabase.
You can grant access to public or to specific groups or roles you create. If you grant access to specific groups or roles, you must first create those roles in the database cluster.
In this example, access is granted to the public group.
```
GRANT USAGE ON SCHEMA sde TO public;
```

Now your database is ready to store a geodatabase.

Request an authorization file from ESRI.
The site from which you downloaded the ArcSDE installation files has instructions for obtaining your authorization file. They include filling out an online form or running the Software Authorization portion of the Post Installation wizard.
Once you have an authorization file, open an MS-DOS command prompt and use the ArcSDE sdesetup command with the install operation to create a geodatabase in your database.
Here, the sdesetup command is running against the spdata database on the server, server1.
```
sdesetup -o install -d POSTGRESQL 
-s server1 -D spdata -u sde -p averturis
```
Tip:
If you copied the wrong libraries to the PostgreSQL lib folder in step 4, you will receive an error message indicating there is a version mismatch between the server and the library when you run sdesetup.
You will need to run the Software Authorization portion of the Post Installation wizard to receive an authorization file and authorize the geodatabase.
1. Start the ArcSDE for PostgreSQL Post Installation wizard: Start > All Programs > ArcGIS > ArcSDE > ArcSDE for PostgreSQL Post Installation.
2. Choose Custom on the first dialog box, then see Authorizing ArcSDE geodatabases in PostgreSQL on Windows for options and instructions on authorizing a geodatabase.
If you want to use an ArcSDE service to connect to the geodatabase, use the sdeservice command to create the service and the sdemon command to start it. Do not use the Post Installation wizard to create and start the service; it will not work.
1. Open the ArcSDE services file (services.sde) located in the %SDEHOME%/etc folder.
2. Either uncomment the line for the default service (esri_sde, 5151/tcp) or add an entry for your new service.
  For example:
```
arcsdesvc      2244/tcp
```
3. Copy the service entry, save your changes, then close the services.sde file.
4. Open the Windows services file.
  The default location of this file is Windows > System32 > drivers > etc.
5. Paste the service entry at the bottom of the Windows services file, then save and close the file.
6. Open an MS-DOS command prompt and change directories to %SDEHOME%/bin.
7. Run the sdeservice command with the create operation to create the service.
  For example:
  sdeservice -o create -d postgresql,postgresql-8.4 -p averturis -i arcsdesvc
8. Register your database with the service using the sdeservice command with the register operation.
  sdeservice -o register -r ADMIN_DATABASE -v spdata -d postgresql -p averturis -i arcsdesvc
9. Run the sdemon command with the start operation to start the service.
  sdemon -o start -i arcsdesvc -p averturis

When your geodatabase is created, you can proceed with adding users to the database cluster and their schemas to the database and creating connections to the geodatabase.

10/4/2012