Tutorial: Get started with geodatabases in PostgreSQL

Available with Standard or Advanced license.

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 on a Microsoft Windows server. This tutorial assumes that you have already installed ArcGIS Server and either ArcGIS Desktop or ArcGIS Pro, and that you have connection information for both the database and geodatabase administrator logins.

Install and configure PostgreSQL

For this tutorial, you can run the PostgreSQL installation provided on My Esri or obtain a supported PostgreSQL version from another source. After PostgreSQL is installed, configure the database cluster to accept remote connections by altering the pg_hba.conf file.

Download and install PostgreSQL.

1. Download the PostgreSQL installation from My Esri.
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.

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\<PostgreSQL version>\data.

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

   In the following example, all machines connecting from orgnetwor.com are allowed access to all databases on the database cluster:

   #TYPE  DATABASE     USER    ADDRESS            METHOD
   
   host     all        all     .orgnetwork.com     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 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 ArcMap or ArcGIS Server client installation or can be downloaded from My Esri.

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

1. In Windows Explorer, navigate to the location of the Windows ST_Geometry library in your ArcGIS client installation directory or download the file from My Esri.
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\<PostgreSQL version>\lib.

Create a geodatabase

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

1. Start an ArcGIS Desktop client or ArcGIS Pro and open the Create Enterprise Geodatabase tool.
2. 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 Server site.
3. Click OK (ArcMap) or Run (ArcGIS Pro).

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, you can connect as the postgres superuser or the sde user, as the Create Enterprise Geodatabase tool granted the sde user superuser status.

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

1. Connect to the database. In ArcMap, expand the Database Connections node in the Catalog tree in ArcMap and double-click Add Database Connection. In ArcGIS Pro, right-click the Databases folder in the Catalog pane and click New Database Connection.

   The Database Connection dialog box opens.

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

   Since you will use 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 a database administrator.

3. Click OK to create the connection.

A new connection file appears under Database Connections.

Now you can run the Create Database User tool.

1. Open the Create Database User.
2. Drag your new connection file into the Input database workspace text box on the Create Database User tool.
3. 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.
4. Click OK (ArcMap) or Run (ArcGIS Pro).

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.

1. Right-click your database connection and click Connection Properties.
2. Change the User name and Password values to those of your new user.
3. Click OK to connect as the new user.

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