Setting up a geodatabase in PostgreSQL

This topic applies to ArcGIS for Desktop Standard and ArcGIS for Desktop Advanced only.

In most cases, you can use the Create Enterprise Geodatabase geoprocessing tool or Python script to create a geodatabase in a PostgreSQL database management system.

The Create Enterprise Geodatabase tool creates a database using the PostgreSQL default template; creates an sde login role and schema; grants the sde login role privileges to create a geodatabase; and creates a geodatabase. If you want to use a different template to create the database, create the database in the database cluster first, then run the Create Enterprise Geodatabase tool or script, specifying the name of your pre-created database.

You could also use the Enable Enterprise Geodatabase geoprocessing tool or Python script to create a geodatabase in a PostgreSQL database. The only common reason for using the Enable Enterprise Geodatabase geoprocessing tool or script is if the person creating the geodatabase does not have access to the postgres superuser password. In that case, the database administrator can create the sde login role, database, and sde schema, and grant the sde user the privileges required to create a geodatabase. Then the sde user can connect to the database and enable geodatabase functionality in it using the Enable Enterprise Geodatabase geoprocessing tool or script.

Check the system requirements on the Resource Center before you set up a geodatabase to be sure the database, ArcGIS, and hardware combinations you want to use are supported.

Setting up a geodatabase in PostgreSQL on a Linux server

The following is a summary of the steps you take to create a geodatabase in PostgreSQL on Linux:

  1. Obtain and install a supported release of PostgreSQL and configure the database cluster.

    You can download PostgreSQL installations from the Esri Customer Care portal. If you use this installation, no specific nondefault settings are needed to use PostgreSQL with ArcGIS. Just be sure to change the locale and location settings if you want to use those other than the default.

    Alternatively, you can obtain the PostgreSQL installation yourself. See the documentation on the PostgreSQL website for instructions on installing and configuring PostgreSQL.

  2. Configure the database cluster to accept connections.

    This includes altering the pg_hba.conf file to allow the database cluster to accept remote connections.

  3. Set the following environment variables for the postgres user if they are not already set properly:
    • PGDATA: This should have been set by the PostgreSQL installation. If not, set it to the location of the data directory under the PostgreSQL installation directory.
    • LD_LIBRARY_PATH: Set the LD_LIBRARY_PATH variable to the PostgreSQL lib directory. The location of the lib directory can vary depending on how you installed PostgreSQL. To determine the correct location for your PostgreSQL installation, execute pg_config as the postgres user. Set LD_LIBRARY_PATH to the value that is returned for PKGLIBDIR.
    • PATH: Set the PATH variable to contain postgresql/bin.
  4. Install the ArcGIS client from which you will create the geodatabase.
  5. Download the PostgreSQL client libraries (if your ArcGIS client is on Windows) or RPM (if your ArcGIS client is on Linux) from the Esri Customer Care portal and place them in the bin directory of the ArcGIS client you installed in the last step. See Setting up a connection to PostgreSQL for more information.
  6. If you will be using ArcGIS for Server with your geodatabase in PostgreSQL, set or alter the following variables for the ArcGIS account:
    • SDEHOME: Set this variable to /home/ags/arcgis/server.

      export SDEHOME=/home/ags/arcgis/server

    • PGHOME: Set PGHOME to /opt/PostgreSQL/9.0.

      export PGHOME=/opt/PostgreSQL/9.0

    • PATH: Add $PATH:$PGHOME/bin:$SDEHOME/bin to the PATH variable.

      export PATH=$PATH:$PGHOME/bin:$SDEHOME/bin

    • LD_LIBRARY_PATH: Add $LD_LIBRARY_PATH:$PGHOME/lib:$SDEHOME/lib to the LD_LIBRARY_PATH variable.

      export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib:$SDEHOME/lib

  7. Move the st_geometry.so library to the PostgreSQL installation directory.

    The st_geometry.so library for PostgreSQL 9.0 can be found in the DatabaseSupport/PostgreSQL/Linux64 directory of your ArcGIS client installation directory. Move the file from this location on the ArcGIS client, log in to the PostgreSQL server as the root user, and place the st_geometry.so file in the PostgreSQL lib directory. (This is the same location to which you set the LD_LIBRARY_PATH environment variable.)

    Beginning with ArcGIS 10.1 service pack 1, the DatabaseSupport directory contains a directory named 9.1. If you will be connecting to a PostgreSQL 9.1 database, use the st_geometry.so library found in this directory instead.

    Beginning with ArcGIS 10.2, the DatabaseSupport directory also contains a directory named 9.2. If you will be connecting to a PostgreSQL 9.2 database, use the st_geometry.so library found in this directory instead.

    If you do not place the correct st_geometry.so library in the PostgreSQL lib directory, you will not be able to create a geodatabase.

    CautionCaution:

    The ArcGIS for Server DatabaseSupport directory is created with permissions set to 700. When you log in as root to move the st_geometry.so file to the PostgreSQL lib directory, change the permissions on the file to 755. If you do not, the postgres user will not be able to access it to create a geodatabase.

  8. Create a tablespace in the database cluster in which to store your database. If you do not create a tablespace, the pg_default tablespace will be used.
  9. Create a database, sde user and schema, and geodatabase by doing one of the following:
    • Run the Create Enterprise Geodatabase geoprocessing tool from ArcGIS for Desktop (Standard or Advanced) on a remote Windows computer.
    • Run a Python script from a computer running ArcGIS for Desktop (Standard or Advanced), ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server Enterprise (Standard or Advanced). An example script can be copied from Scripting database and geodatabase creation in PostgreSQL and altered to run at your site.

    Alternatively, you can create a database, sde user, and schema manually, then run the Enable Enterprise Geodatabase geoprocessing tool from a remote Windows computer, or run a Python script that calls EnableEnterpriseGeodatabase on the Linux computer where ArcGIS Engine with the Geodatabase Update extension or ArcGIS for Server Enterprise (Standard or Advanced) are installed.

Setting up a geodatabase in PostgreSQL on a Windows server

The following is a summary of the steps you take to create a geodatabase in PostgreSQL on Windows:

  1. Obtain and install a supported release of PostgreSQL and configure the database cluster.

    You can download PostgreSQL installations from the Esri Customer Care portal. If you use this installation, no specific nondefault settings are needed to use PostgreSQL with ArcGIS. Just be sure to change the locale and location settings if you want to use those other than the default.

    Alternatively, you can obtain the PostgreSQL installation yourself. See the documentation on the PostgreSQL website for instructions on installing and configuring PostgreSQL.

  2. Configure the database cluster to accept connections.

    This includes altering the pg_hba.conf file to allow the database cluster to accept remote connections.

  3. Install the ArcGIS client from which you will create the geodatabase.
  4. Download the PostgreSQL client libraries (if your ArcGIS client is on Windows) or RPM (if your ArcGIS client is on Linux) from the Esri Customer Care portal and place them in the bin directory of the ArcGIS client you installed in the last step. See Setting up a connection to PostgreSQL for more information.
  5. Copy the st_geometry library to the PostgreSQL installation directory.

    The st_geometry.dll file for use with PostgreSQL 9.0 can be found in the DatabaseSupport directory of your ArcGIS client installation directory. Move the st_geometry.dll file from the ArcGIS client to the PostgreSQL lib directory on your PostgreSQL server.

    If you will be connecting to a PostgreSQL 9.1 database, use the st_geometry.dll library found in the 9.1 directory instead.

    If you will be connecting to a PostgreSQL 9.2 database, use the st_geometry.dll library found in the 9.2 directory instead.

    NoteNote:

    If you do not place the correct st_geometry.dll library in the PostgreSQL lib folder, you will not be able to create a geodatabase.

    Also note that the st_geometry.dll requires the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64). If this package is not present on the PostgreSQL server, download it from the Microsoft site and install it.

  6. Create a tablespace in the database cluster in which to store your database. If you do not create a tablespace, the pg_default tablespace will be used.
  7. Create a database, sde user and schema, and geodatabase by doing one of the following:

    Alternatively, you can manually create a database, sde user, and schema, then run the Enable Enterprise Geodatabase geoprocessing tool or Python script to create a geodatabase in the pre-created database.

Optional additional setup

There are some scenarios that require you to use additional tools to complete your geodatabase setup, such as when you want to use an ArcSDE service for connections or multiple geodatabases are needed in the same PostgreSQL database cluster. See Multiple geodatabases in PostgreSQL for information on creating more than one geodatabase on the same database cluster.

To use an ArcSDE service, you must install and configure the ArcSDE application server. See the installation guide provided with the ArcSDE application server for instructions for this scenario. The ArcSDE application server installation can be downloaded from the Esri Customer Care portal.

Related Topics

Setting up a connection to PostgreSQL
Create Database User
Create Role
Database connections in ArcGIS for Desktop
Configuring a PostgreSQL database cluster on Linux to accept connections
Configuring a PostgreSQL database cluster on Windows to accept connections
Tutorial: Getting started with geodatabases in PostgreSQL
Copyright © 1995-2014 Esri. All rights reserved.
11/6/2014