This tutorial presents an example of how you can get started using an enterprise geodatabase in PostgreSQL on a Windows server. This tutorial assumes that you have already installed ArcGIS for Desktop and ArcGIS for Server, and that you have connection information for both the database and geodatabase administrator logins.
Install and configure PostgreSQL
For this tutorial, run the PostgreSQL installation provided on My Esri. After PostgreSQL is installed, configure the database cluster to accept remote connections by altering the pg_hba.conf file.
Download and install PostgreSQL.
- Download the PostgreSQL installation from My Esri.
- 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.
- 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.
- 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's documentation for more information and examples.
- 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.
- 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\<PostgreSQL version>\Windows64; on 32-bit Windows operating systems, it is C:\Program Files\ArcGIS\Desktop<release#>\DatabaseSupport\PostgreSQL\<PostgreSQL version>\Windows64.
- Copy st_geometry.dll from this location.
- 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.
- Start ArcMap and open the Search window.
- Click the Tools filter.
- Type Create Enterprise Geodatabase in the search box and click the Search button.
- Click the Create Enterprise Geodatabase (Data Management) link to open the tool.
- 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.
- 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, you can connect as the postgres superuser or the sde user, as the Create Enterprise Geodatabase tool granted the sde user superuser authority.
Create a database connection, logging in as the postgres or sde user.
- Expand the Database Connections node in the Catalog tree in ArcMap and double-click Add Database Connection.
The Database Connection dialog box opens.
- Provide the information necessary to connect to your new database as a superuser.
In this example, a connection is made to the new fstutorial geodatabase as the postgres user:
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.
- 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.
- Click the Tools filter in the Search window in ArcMap.
- Type Create Database User in the search box and click the Search button.
- Click the Create Database User (Data Management) link to open the tool.
- Drag your new connection file from the Catalog tree into the Input database workspace text box on the Create Database User tool.
- 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.
- 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.
- Right-click your database connection and click Connection Properties.
- Change the User name and Password values to those of your new user.
- Click OK to connect as the new user.
You can now use this connection to add data to your geodatabase.