«

»

Feb 01

PostgreSQL Series, Part 1: Installation and Configuration

Geographic Database Consulting is running a multi-part series on utilizing PostgreSQL for your Geodatabase storage needs. This chapter will focus on the installation and configuration of PostgreSQL for hosting a Geodatabase.

For this blog, I will be installing PostgreSQL on a Virtual Machine running Oracle Linux 64-bit, which is binary compatible with Redhat. You can find the ESRI ArcSDE 10.1 System Requirements here:

http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000

PostgreSQL is a free and open-source Relational DataBase Management System (RDBMS). We highly recommend the version available from EnterpriseDB.com, as commercial support is available should you ever need it. You can find the Downloads available at the following URL:

http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

After selecting the appropriate PostgreSQL download, you should save the file…

Go into the Downloads folder, and you will need to make the file “executable”:

# chmod 744 postgresql-9.1.6-1-linux-x64.run

Then run the installer with the following command:

# ./postgresql-9.1.6-1-linux-x64.run

Walking thru the setup program is pretty simple.

Select an appropriate path, and hit Next>

Select an appropriate path for data files, and hit Next>

Type in a password for the POSTGRES operating system user, and hit Next>

The default port number 5432 should be fine. Hit Next>

Default locale should be fine. Hit Next>

Hit Next> to perform the installation.

The installer goes thru a typical progression, and will present with a “Finish” screen…

The “Stack Builder” is a nice tool that will help you install PostGIS, and any other add-ons you might want. For this guide, I will run the Stack Builder, to install the PostGIS extension. Hit Finish.

In some cases, the Stack Builder won’t run correctly, depending on who’s logged in, and who’s running the installer, etc. If it doesn’t start correctly, you can always start it thru the Menus, but selecting Applications->

 

When the Stack Builder starts, select your new PostgreSQL 9.1 on Port 5432. Hit Next>

Open Categories->Spatial Extensions. Highlight PostGIS. Hit Next>

Select an appropriate Download directory, and hit Next>

The Stack Builder will download the PostGIS installer, and give you the option to run the installer…

If you want to run the installer, leave the checkbox empty, and hit Next>

This kicks off the PostGIS installer.

Select Next>

Since this is a clean install, leave the box unchecked, and hit Next>

Type in your postgres user password (as entered above), and hit Next>

Select Next>

The installer will go thru a few steps, including creating a template database.

When complete, it will present you with a Finish dialog.

Select Finish. Also select Finish on the Stack Builder dialog box. This ends the PostgreSQL installation.

Now it’s time to configure PostgreSQL, and create an empty Geodatabase.

Open a command prompt. As root, run the following command:

# source /opt/PostgreSQL/9.1/pg_env.sh

This will set the appropriate environment variables.

 

Next, you will need to modify your “pg_hba.conf” configuration file. By default, PostgreSQL won’t allow network connections to it, so we need to change that. The “pg_hba.conf” file is usually located in your data directory. So following our example, it’s under /opt/PostgreSQL/9.1/data/pg_hba.conf. I like using “vi” (no flames, please), so as root:

vi /opt/PostgreSQL/9.1/data/pg_hba.conf

Add the following line to the end of the file:

host all all 192.168.0.0/24 trust

Change the values as appropriate for your network. This line will allow access to all databases from all 192.168.0.X IP addresses.

Restart PostgreSQL for the change to take affect…

# service postgresql-9.1 restart

Should give the following output:

Restarting PostgreSQL 9.1:

waiting for server to shut down…. done

server stopped

waiting for server to start…. done

server started

PostgreSQL 9.1 restarted successfully

 

Next, start pgAdmin3.

Double click on the PostgreSQL 9.1 (localhost). Enter the password, and if you want, select the checkbox to store password.

Expand the PostgreSQL 9.1 on Localhost. Right click on Databases, and select New Database…

We’re going to create a new database called Geodatabase. Set the owner to postgres. Go to the Definition tab.

Set the Template to “template_postgis”. This will allow us to use the POSTGIS geometry storage if we want.

Hit OK to create the database.

Back in pgAdmin III, we want to right click the Login Roles, and select New Login Role…

Create a role named SDE. Go to the Definition tab.

Set the password. (It’s not SDE, I promise! J ) Go to the Role privileges tab.

Make sure SDE can create database objects. Optionally, make SDE a Superuser. Nothing to change on the rest of the tabs. Hit OK.

Next, we need to enable the ST_GEOMETRY storage type by copying ESRI’s shared library.

Find the ST_GEOMETRY.SO library in the DatabaseSupport directory under ArcGIS\Desktop10.1.

Copy that file to your Linux box using whatever tools you have available (scp, filezilla, etc). We need to figure out where to put the file…

As root, run the pg_config command, as follows:

# /opt/PostgreSQL/9.1/bin/pg_config

Look for the line that says “PKGLIBDIR = “. That is the directory where we need to copy the st_geometry.so shared library.

BINDIR = /opt/PostgreSQL/9.1/bin

DOCDIR = /opt/PostgreSQL/9.1/doc/postgresql

HTMLDIR = /opt/PostgreSQL/9.1/doc/postgresql

INCLUDEDIR = /opt/PostgreSQL/9.1/include

PKGINCLUDEDIR = /opt/PostgreSQL/9.1/include/postgresql

INCLUDEDIR-SERVER = /opt/PostgreSQL/9.1/include/postgresql/server

LIBDIR = /opt/PostgreSQL/9.1/lib

PKGLIBDIR = /opt/PostgreSQL/9.1/lib/postgresql

LOCALEDIR = /opt/PostgreSQL/9.1/share/locale

MANDIR = /opt/PostgreSQL/9.1/share/man

SHAREDIR = /opt/PostgreSQL/9.1/share/postgresql

SYSCONFDIR = /opt/PostgreSQL/9.1/etc/postgresql

PGXS = /opt/PostgreSQL/9.1/lib/postgresql/pgxs/src/makefiles/pgxs.mk

CONFIGURE = ‘–with-libs=/usr/local/openssl/lib:/usr/local/ldap-2.4.23/lib:/usr/local/lib’ ‘–with-includes=/usr/local/openssl/include:/usr/local/ldap-2.4.23/include:/usr/local/include’ ‘–prefix=/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64’ ‘–with-ldap’ ‘–with-openssl’ ‘–with-perl’ ‘–with-python’ ‘–with-tcl’ ‘–with-tclconfig=/opt/ActiveTcl-8.5/lib’ ‘–with-pam’ ‘–with-krb5’ ‘–enable-thread-safety’ ‘–with-libxml’ ‘–with-ossp-uuid’ ‘–docdir=/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64/doc/postgresql’ ‘–with-libxslt’ ‘–with-libedit-preferred’ ‘–with-gssapi’ ‘LD_LIBRARY_PATH=/usr/local/openssl/lib:/usr/local/ldap-2.4.23/lib:/usr/local/lib’

CC = gcc

CPPFLAGS = -D_GNU_SOURCE -I/usr/local/include/libxml2 -I/usr/local/openssl/include -I/usr/local/ldap-2.4.23/include -I/usr/local/include

CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv

CFLAGS_SL = -fpic

LDFLAGS = -L/usr/local/lib -L/usr/local/openssl/lib -L/usr/local/ldap-2.4.23/lib -L/usr/local/lib -Wl,–as-needed -Wl,-rpath,’/mnt/buildfarm/pginstaller.auto/server/staging/linux-x64/lib’,–enable-new-dtags

LDFLAGS_EX =

LDFLAGS_SL =

LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm

VERSION = PostgreSQL 9.1.6

[root@jtb-oracle ~]#

 

I have Cygwin installed, and just use SCP:

$ scp st_geometry.so root@192.168.0.20:/opt/PostgreSQL/9.1/lib/postgresql/

We need to make the library executable, so as root, run the following command:

chmod +rx /opt/PostgreSQL/9.1/lib/postgresql/st_geometry.so

Now, fire up trustly ol’ ArcCatalog. Create a new Spatial Database Connection.

When you select “OK”, it will show you as connected, but there is nothing in our database yet…

In ArcToolbox, go to Data Management Tools->Geodatabase Administration->Enable Enterprise Geodatabase. Start the tool by double clicking.

Select your Database Connection, and a valid ESRI Authorization File.

If everything is configured correctly, you should get a successful message:

 

To test, I created a new database user called DEMO, giving DEMO the same privs as SDE.

 

Create a new database connecting using the DEMO user:

 

You can import a new feature class. For my test, I will import some Interstates.

By default, the new geodatabase will use the ST_GEOMETRY spatial type for storage. If you want to use the POSTGIS storage, you can change your Environment setting to use the PG_GEOMETRY config keyword.

Running a quick query to test:

PostgreSQL with PostGIS is correctly configured and hosting a geodatabase…

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>