Welcome back!  Last time in Part 1 we installed PostgresSQL on a Linux server.  Now we need to do a few things to get it ready so we can create an Enterprise Geodatabase in it.

Postgres User

When PostgreSQL was installed, a postgres user was created.  The postgres user is the default “superuser” to the PostgreSQL database.  Right now the postgres user password is unknown to you.  You must change it in Linux and in the PostgreSQL database.

Log back in to the Linux server and at the Linux prompt, use the passwd command to change the postgres user password.  You might need to use the sudo command with it for it to work. 

Now change the postgres password in the PostgreSQL database using the psql tool.  First switch to the postgres user using your new password.

Then run the psql tool as shown, and use “\password” to change the password and then “\q” to exit.  You can also type “exit” at the Linux prompt to exit being the postgres user and go back to your login.

PostgreSQL Configuration Files

There are two PostgreSQL configuration files that you will need to make changes to.  Now this will be a little harder for you if you are not familiar with IP stuff and the vi text editor in Linux.

First, you need to make changes to the pg_hba.conf file so you can remotely connect to PostgreSQL from another computer on your domain or IP address ranges.  If you are not comfortable doing this, have a system admin help you out.  If you want to go for it yourself, read the ESRI help at http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/configure-postgresql-accept-connections.htm and look at the steps in the Linux section.  If you want to know more about the pg_hba.conf file, read the PostgreSQL doc https://www.postgresql.org/docs/10/auth-pg-hba-conf.html .

You will need to become the postgres user then in the postgres home directory, there is a directory named “10” and under that is a directory named “data”.  In there is the pg_hba.conf file.

If you want to see what it looks like, just issue the command “cat pg_hba.conf” to list or “more pg_hba.conf” to scroll through.

Make a backup copy of the file using the cp command, then use a text editor like vi to make the changes.  See http://www.lagmonster.org/docs/vi.html for a cheat sheet of vi commands.

After saving your changes to the pg_hba.conf file, in that same directory is the postgresql.conf file.  This file stores global configuration settings for the PostgreSQL database.  More information about the settings in postgresql.conf at https://www.postgresql.org/docs/10/runtime-config.html .

Go ahead and make a backup copy of that file too, then edit it.  You need to change the “listen_addresses” setting from “localhost” to “*”, so PostgreSQL will listen for any IP address that wants to connect to it.  You might have to remove the “#” in front of it if it was commented out.  Here is mine after I made the changes:

While you are still in there, go ahead and change the “shared_buffers” setting if you have 1GB or more of memory.  A reasonable starting value would be 25% of the memory on your system.  For example, I have 16gb of memory, so my setting would be 4gb.  Here are my changes:

After saving your changes, exit the postgres user and reboot your server.

ST_Geometry Data Type

The ST_Geometry data type extends the capabilities of databases by providing storage for point, line, and polygon objects that represent geographic features.  ESRI has implemented an ST_Geometry type in Oracle, PostgreSQL, and SQLite.  If you want to know more about it, read http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-the-st-geometry-storage-type.htm .  Bottom line, you must enable PostgreSQL to be able to use ST_Geometry for geodatabases.

To enable PostgreSQL to use ST_Geometry, you must copy the st_geometry.so file on your PC where ArcGIS Desktop 10.6 is installed to the PostgreSQL package library directory on your Linux box.  I found my st_geometry.so file on my Windows PC under C:\Program Files (x86)\ArcGIS\Desktop10.6\DatabaseSupport\PostgreSQL\10\Linux64.  Per my install of PostgreSQL, I need to copy the file to /usr/pgsql-10/lib on the Linux box.  If you are not using CentOS 7 Linux, your location may be different.  A quick way to find your PostgreSQL package library location is to issue the command “/usr/pgsql-10/bin/pg_config” as the postgres user and look for the PKGLIBDIR value that is listed.

On your Windows PC, startup WinSCP.  In the upper left, click on “New Site”, then on the right enter your Linux server name and optionally your user name and password.

This is very similar to logging in with the PuTTY program.  You can save your entry so you can click on it next time you need it.  Go ahead and click the Login button to login to your server.  Once you are in you will see two windows, on the left is your Windows PC and on the right is your home directory on the Linux server.  You now need to copy the st_geometry.so file on your PC to your home directory on Linux.  Just navigate to the file on your PC, then drag/drop it to your home directory on Linux.  Pretty easy!

Close WinSCP, then use PuTTY to login again.  Once you are in, you can see the st_geometry.so file in your home directory by issuing the ls command.

You now need to copy the file to the PostgreSQL lib directory.  As stated above, mine is /usr/pgsql-10/lib.  You need to be root (superuser) to do this, so change to user root.

Then move to your PostgreSQL lib directory.

Now use the cp command as shown to copy the file over to the current directory (note the extra . to specify “copy here”) and then use the ls command as shown to list the files that start with “st” in that directory.

There is only 1 file, the one I copied over.  You also need to change the permissions on the file to 755.  Just use the chmod command as shown and the ls command to see the change.

Note the file’s permissions changed from “-rw-r–r–” to “-rwxr-xr-x”.  R is read, W is write, and X is execute.  If you want to know more about file permissions on Linux, take a look at https://www.geeksforgeeks.org/permissions-in-linux/ .

Alright!  You are done on the Linux box!  Since you are still the root user, just issue the reboot command at the prompt to reboot the Linux server.

Now the Enterprise Geodatabase can be created.  That we will save for next time in Part 3!