Moving To PostgreSQL (Part 2)

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. 

pg10

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

pg10b

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.

pg10c

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.

pg11

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.

pg12

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:

pg12b

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:

pg12c

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

pg13

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.

pg14

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!

pg15

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.

pg16

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.

pg17

Then move to your PostgreSQL lib directory.

pg18

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.

pg19

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.

pg20

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s