Tutorial: Getting started with geodatabases in PostgreSQL

Complexity: Beginner Data Requirement: Use your own data

This tutorial presents an example of how you can get started using an enterprise geodatabase in PostgreSQL 9.0.2-1 on a Windows server. This tutorial assumes that you have already installed ArcGIS for Desktop and ArcGIS for Server.

Install and configure PostgreSQL

For this tutorial, run the PostgreSQL installation provided on the Esri Customer Care portal. After PostgreSQL is installed, configure the database cluster to accept remote connections by altering the pg_hba.conf file.

Download and install PostgreSQL.

Steps:
  1. Download the PostgreSQL installation (in this case, postgresql 9.0.2-1 windows x64) from the Esri Customer Care portal.
  2. Run the setup executable on the Windows server where you want to install PostgreSQL.

    Other than setting the locale and collation for non-English language sites, you can use the default values provided in the installation wizard.

    You will specify a password for the postgres superuser during the installation. Be sure to remember this password, as it is the database administrator password for the database cluster and will be needed later in this tutorial.

When PostgreSQL is first installed, you can only connect to it from the local server. To allow other machines on your network to connect, you must alter the pg_hba.conf file.

Steps:
  1. In a text editor, open the pg_hba.conf file found in the PostgreSQL data directory.

    The default location is C:\Program Files\PostgreSQL\9.0\data.

  2. Specify the client IP addresses you want to allow access to your database cluster.

    In the following example, all addresses and all connecting users on your network are allowed access to all databases on the database cluster:

    #  TYPE  DATABASE     USER     CIDR-ADDRESS    METHOD
    
    # IPV4 local connections:
    host     all          all      0.0.0.0/0       md5
    
    # IPV6 local connections:
    host     all          all      ::/0            md5

    To make the database cluster more secure, you can restrict access to specific IP addresses or a range of addresses, specify a database or list of databases to which you want to grant access, or specify which users can connect. You can even explicitly disallow access to an IP address or range of IP addresses. See the PostgreSQL documentation for more information and examples.

  3. Restart the PostgreSQL service.

    You can do this by right-clicking the postgresql-x64 service in the Windows Services list and clicking Restart.

Place the ST_Geometry libraries in the PostgreSQL lib directory

Geodatabase creation in PostgreSQL relies on the presence of the ST_Geometry library. You must place the appropriate library in the lib directory where PostgreSQL is installed before you can create a geodatabase. ST_Geometry libraries can be found in the DatabaseSupport folder of your ArcGIS client installation.

Since this tutorial assumes your PostgreSQL installation is on a Windows server, you will use the ST_Geometry library found in the Windows64 folder.

Steps:
  1. In Windows Explorer, navigate to the location of the Windows ST_Geometry library in your ArcGIS client installation directory.

    The default location for ArcGIS for Desktop on 64-bit Windows operating systems is C:\Program Files (x86)\ArcGIS\Desktop<release#>\DatabaseSupport\PostgreSQL\Windows64; on 32-bit Windows operating systems, it is C:\Program Files\ArcGIS\Desktop<release#>\DatabaseSupport\PostgreSQL\Windows64.

  2. Copy st_geometry.dll from this location.
  3. Navigate to the PostgreSQL lib directory on the database server and paste the library to that location.

    On Windows, the default location is C:\Program Files\PostgreSQL\9.0\lib.

Place the PostgreSQL client files on your ArcGIS client machine

To connect from ArcGIS to create a geodatabase, you must have the PostgreSQL client libraries on your client machine.

Steps:
  1. Download the PostgreSQL client files from the Esri Customer Care portal.

    To connect from ArcGIS for Desktop, you need the 32-bit files. To connect from ArcGIS for Server, you need the 64-bit files. If both ArcGIS clients are installed on the same machine, you need both sets of libraries.

  2. Paste the appropriate libraries into the bin directory of the ArcGIS client.
    • For ArcGIS for Desktop, paste the 32-bit files into the C:\Program Files (x86)\ArcGIS\Desktop<release#>\bin directory (64-bit operating systems) or C:\Program Files\ArcGIS\Desktop<release#>\bin directory (32-bit operating systems).
    • For ArcGIS for Server, paste the 64-bit files into the C:\Program Files\ArcGIS\Server\bin directory.
    NoteNote:

    Libraries must be placed on every ArcGIS client machine from which you will be connecting to the database.

  3. Restart ArcGIS for Server, if it is already running, so that it recognizes the files you placed in the bin directory.

Create a geodatabase

You can use the Create Enterprise Geodatabase geoprocessing tool to create the database, sde user, sde schema, and geodatabase in PostgreSQL.

Steps:
  1. Start ArcMap and open the Search window.
  2. Click the Tools filter.
  3. Type Create Enterprise Geodatabase in the search box and click the search button.
  4. Click the Create Enterprise Geodatabase (Data Management) link to open the tool.
  5. Provide the information required to connect to the PostgreSQL database cluster as the postgres superuser to create a database and sde user. You must also point to the keycodes file that was generated when you authorized your ArcGIS for Server instance.

    In this example, PostgreSQL is running on server mypgserver, the database to be created is fstutorial, and the authorization keycodes file is in the default location on the ArcGIS for Server machine. The password for the postgres superuser and a password for the sde user to be created have also been provided. Tablespace Name has been left blank, which means the database will be created in the pg_default tablespace.

    Example values for creating a geodatabase in PostgreSQL

  6. Click OK to run the tool.

The database, sde user, sde schema, and geodatabase are created in PostgreSQL.

Create a user to own data

Data stored in your geodatabase should be owned by users other than the sde user. Use the Create Database User geoprocessing tool to create a user in the PostgreSQL database cluster and a schema in your new database.

To create the user, though, you must first connect to the geodatabase as the database administrator (in this case, the postgres superuser).

Create a database connection, logging in as the postgres user.

Steps:
  1. Expand the Database Connections node in the Catalog tree in ArcMap and double-click Add Database Connection.

    The Database Connection dialog box opens.

  2. Provide the information necessary to connect to your new database as the postgres superuser.

    In this example, a connection is made to the new fstutorial geodatabase as the postgres user:

    Example of connecting as the postgres user

    Since you will be using this connection file in a geoprocessing tool, you must check Save user name and password. After the tool is run, though, you should either uncheck this option in the connection file, connect as a different user, or delete the connection file to prevent other users with access to this file from logging in to the geodatabase as the database administrator.

  3. Click OK to create the connection.

A new connection file appears under the Database Connections node in the Catalog tree.

Now you can run the Create Database User tool.

Steps:
  1. Click the Tools filter in the Search window in ArcMap.
  2. Type Create Database User in the search box and click the Search button.
  3. Click the Create Database User (Data Management) link to open the tool.
  4. Drag your new connection file from the Catalog tree into the Input database workspace text box on the Create Database User tool.
  5. Type a name for the new user in the Database User text box and a password for the new user in the Database User Password text box.

    In this example, the connection file that was created in the last set of steps has been added to the tool and a user name (fstuser) and password are provided. Since no database roles have been created yet, no role name is specified.

    Example values to create user

  6. Click OK to run the tool.

A new user and schema are created in PostgreSQL, and USAGE is granted automatically on the schema to the public role.

Connect as the newly created user

Now that you have a user who can add data to your new geodatabase, connect to the database as that user. The easiest way to do that is to alter the connection file you created earlier.

Open the existing database connection and change the user name and password.

Steps:
  1. Right-click your database connection and click Connection Properties.
  2. Change the User name and Password values to those of your new user.

    Example of connecting as a nonadministrator user

  3. Click OK to connect as the new user.

You can now use this connection to add data to your geodatabase.

Related Topics

11/6/2014