A quick tour of setting up a geodatabase in PostgreSQL

Before you can create a geodatabase in a PostgreSQL database management system (DBMS), you first install the PostgreSQL software, configure the DBMS, initialize a database cluster, and create a database. When that is complete, you can install ArcSDE, create a geodatabase, then add users and schemas to the database.

Setting up PostgreSQL and creating a geodatabase on Linux

You can alter and use the script, create_pgdb.sde, to complete steps 1 and 2. You can alter and use the script, setup_pgdb.sde, to complete steps 7 through 10, and 12. These scripts can be found in the pg directory of the installation media.

1. Install PostgreSQL. You can install using the RPM installation packages on Red Hat Linux.
2. Copy the st_geometry.so file to the usr/lib/pgsql directory on 32-bit servers and the usr/lib64/pgsql on 64-bit servers.
3. Login as the postgres user and initialize the database cluster.
4. If storing the tablespace in a location other than the default, create a directory to store the tablespace for the database that will house the geodatabase.
5. Prepare a shell by defining variables in the postgres user's shell file.
6. Configure the database cluster to accept connections by altering the pg_hba.conf and postgresql.conf files.
7. Create the PostgreSQL database, tablespace, sde user, and sde schema.
8. Grant the sde user superuser privileges for geodatabase setup.
9. Grant the sde user full access to the database.
10. Grant USAGE to PUBLIC on the sde schema.
11. If using PostGIS, grant SELECT, INSERT, UPDATE, and DELETE privileges on the public.geometry_columns table and SELECT on the public.spatial_ref_sys table to the sde user.
12. Install PL/pgSQL.
13. Login as the sde OS user and install ArcSDE.
14. If you install ArcSDE on a different server than the PostgreSQL DBMS, you must copy or send (via FTP) certain libraries from the PostgreSQL lib (or lib64 on 64-bit servers) directory to SDEHOME > bin on the server where ArcSDE is installed so that the ST_Geometry type can access them. For Linux, you need to run the RPM after it is placed on the Linux server. See Installing PostgreSQL and ArcSDE on separate servers for instructions.
15. Modify the files in $SDEHOME/etc and the Linux/etc directories.
16. Run the sdesetup command to create the geodatabase and authorize it with an ArcGIS Server Enterprise license. See Creating a geodatabase in PostgreSQL on Linux.
17. Run the sdemon command to start a service, if you are going to use one. See Starting a local ArcSDE service on Linux or UNIX for instructions.
18. Create other users and their schemas and grant permissions, including permissions on schemas.
19. If using PostGIS, grant SELECT, INSERT, UPDATE, and DELETE privileges on the public.geometry_columns table and SELECT privileges on the public.spatial_ref_sys table to any users who will create data in the database.

Setting up PostgreSQL and creating a geodatabase on Windows

1. Install PostgreSQL using the wizard. Install StackBuilder if you intend to use other PostgreSQL components, such Slony-I.
2. Configure the PostgreSQL database cluster to accept client connections by altering the pg_hba.conf file. See Configuring the PostgreSQL database cluster on Windows to accept client connections.
3. Install ArcSDE using the wizard. See Installing ArcSDE for PostgreSQL on Windows for instructions.
4. If you install ArcSDE on a different server than the PostgreSQL DBMS, you must copy or send (via FTP) certain libraries from the PostgreSQL lib directory to SDEHOME > bin on the server where ArcSDE is installed so that the ST_Geometry type can access them. See Installing PostgreSQL and ArcSDE on separate servers for instructions.
5. Connect as the postgres superuser. Create a tablespace, database, sde user, and sde schema; and grant the sde user superuser privileges for the installation. This can be done with the ArcSDE for PostgreSQL Post Installation wizard. If you are using PostGIS, be sure to specify the PostGIS-enabled database created when PostGIS was installed in step 2. See Creating a geodatabase in PostgreSQL on Windows for instructions on using the Post Installation wizard.
6. If you plan to use an ArcSDE service to make connections to the geodatabase, use the Post Installation wizard to create and start the service.
7. If using PostGIS, grant SELECT, INSERT, UPDATE, and DELETE privileges on the public.geometry_columns table and SELECT on the public.spatial_ref_sys table to the sde user.
8. Create other users and their schemas and grant permissions, including permissions on schemas.