Installation of PostgreSQL / PostGIS / ArcSDE on Debian/Ubuntu

I found a lot of Installationscripts about some parts of the whole Installationprocess, but as I need the whole thing, I created this HowTo for setting up ArcSDE with PostGIS Support on Debian or Ubuntu.

Install Debian/Ubuntu with ext4 Filesystem because it has more tuning possibilities than ext3 and is basically faster than ext3.[1]

Install PostgreSQL 8.4.7 and PostGIS 1.5.1.[2]

sudo apt-get install postgresql postgis postgresql-8.4-postgis postgresql-contrib-8.4

The Postgresql Package automatically creates a new user ‘postgres’, changes its home directory to /var/lib/postgresql/ and stores the current default database (main) in the directory /var/lib/postgresql/8.4/main/. Because of the actual partitions sizes, the /home directory has the most space available, so change the home directory of Postgresql to /home/ and link to /var/lib/ (don’t forget to stop the Server to prevent failures):

/etc/init.d/postgres stop
sudo mv -f /var/lib/postgresql /data/
sudo chown -R postgres:postgres /data/postgresql
sudo ln -s /home/postgresql /var/lib/

Afterwards, edit the postgresql.conf file for the current cluster (f.e. /etc/postgresql/8.4/main/postgresql.conf) to make postgresql listen to all interfaces.

listen_address = '*'

Change the pg_hba.conf (same directory as postgresql.conf) to let clients connect from outside.[3]

host     all     all      10.101.0.0/16    md5

And start the PostgreSQL instance afterwards.

/etc/init.d/postgres start

The storage of the ArcSDE database can be changed by creating a new directory and setup a new tablespace (see later). Change the permissions to forbid manual edits.

mkdir /data/postgres/8.4/main/sde
chmod 0700 /data/postgres/8.4/main/sde

Now log into the PostgreSQL console

sudo su postgres
psql
ALTER USER postgres PASSWORD 'password';

At first, create the new role sde.

CREATE ROLE sde LOGIN PASSWORD 'password' SUPERUSER NOINHERIT CREATEDB;

If a new tablespace is necessary, create it and set the right path.

CREATE TABLESPACE sde OWNER sde LOCATION '/var/lib/postgresql/8.4/main/sde';

Now create the new database (Other encodings could result in encoding problems). Set the tablespace if needed.

CREATE DATABASE data OWNER sde ENCODING 'UTF8' TABLESPACE sde;

Grant all privileges to the role sde and allow everyone to connect to the database.

GRANT ALL ON DATABASE data TO sde;
GRANT CONNECT ON DATABASE data TO public;

Switch to the new database and install the language ‘plpgsql’, used by some Postgis functions.

c data
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;
q

Install the Postgis functions in the current database and log into the database again.

psql data -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql data -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql data

Prepare the database for the installation of ArcSDE.

CREATE SCHEMA sde AUTHORIZATION sde;
GRANT ALL ON SCHEMA sde TO sde;
GRANT USAGE ON SCHEMA sde TO public;

Because ArcSDE uses Postgis functions, it’s necessary to give the sde account access to the public tables of Postgis.

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.geometry_columns TO sde;
GRANT SELECT ON TABLE public.spatial_ref_sys TO sde;
q

To install ArcSDE, it’s preferable to create a new account to prevent permission escalation.

sudo adduser sde
sudo useradd sde admin

Create a new directory as installation directory and mount the ArcSDE ISO-File.

mount ISO
sudo mkdir /opt/arcsde
sudo chown sde:sde /opt/arcsde
sudo mkdir /mnt/iso/
sudo mount -o loop ArcSDE10_UNIX_121100.iso /mnt/iso

The ArcSDE Service search the Socket of the PostgreSQL Instance in the /temp/ directory per default. Because there is no option to change this behavior, it’s necessary to link the run-socket file of PostgreSQL to the temp directory.

ln -s /var/run/postgresql/.s.PSQL.5432 /tmp/

ArcSDE 10 needs some extra libraries where some of them are out of date. Install these libraries explicit by downloading and setting up the required packages.

For Debian x86:

sudo apt-get install libmotif4
sudo ln -s /usr/lib/libXm.so.4 /usr/lib/libXm.so.3
sudo wget http://ftp.us.debian.org/debian/pool/main/g/gcc-3.4/gcc3.4-base_3.4.6-9_i386.deb
sudo dpkg -i gcc-3.4-base_3.4.6-9_i386.deb
sudo wget http://ftp.us.debian.org/debian/pool/main/g/gcc-3.4/libg2c0_3.4.6-9_i386.deb
sudo dpkg -i libg2c0_3.4.6-9_i386.deb
sudo ln -s /usr/lib/libcrypto.so.0.9.8 /usr/lib/libcrypto.so.6
sudo ln -s /usr/lib/libldap-2.4.so.2 /usr/lib/libldap_r-2.3.so.0

For Ubuntu x64:

sudo apt-get install ia32-libs, libmotif3, gcc, libcrypto++8
sudo dpkg -i libg2c0_3.4.6-6ubuntu5_amd64.deb
sudo ln -s /usr/lib/libcrypto.so.0.9.8 /usr/lib/libcrypto.so.6
sudo ln -s /usr/lib/libldap-2.4.so.2 /usr/lib/libldap_r-2.3.so.0

To bind the ArcSDE service to the port 5151 per default, edit the /etc/services file and add:

esri_sde         5151/tcp        # ArcSDE service on pinetree
esri_sde         5151/udp        # ArcSDE service on pinetree

Login as user sde and add environment variables in .profile in the users home directory.[4]

export SDEHOME=/opt/arcsde/sdeexe100
export PATH=$PATH:$SDEHOME/bin
export LD_LIBRARY_PATH=$SDEHOME/lib:/usr/lib/postgresql/8.4/lib

Now it’s time to install ArcSDE by executing (as sde user):

sudo sde
/mnt/iso/linux/pg/arcsde/install -load

Install ArcSDE to /opt/arcsde/.

At first, set the default service name of arcsde by changing the /opt/arcsde/sdeexe100/etc/services.sde file.

esri_sde         5151/tcp        # ArcSDE service on pinetree

To include the required Libraries of ArcSDE in PostgreSQL simply copy the st_geometry.so and the libst_raster_pg.so (for raster data support) to the library directory of PostgreSQL.

sudo cp /opt/arcsde/sdeexe100/pg841_st_lib/st_geometry.so /usr/lib/postgresql/8.4/lib/
sudo cp /opt/arcsde/sdeexe100/pg841_st_lib/libst_raster_pg.so /usr/lib/postgresql/8.4/lib

Change some parameters in the /opt/arcsde/sdeexe100/etc/dbinit.sde file.[5]

set SDE_DATABASE=data
set SDEDBECHO=TRUE
set SDEHOME=/opt/arcsde/sdeexe100
set SDEINSTANCE=esri_sde
set SDEVERBOSE=TRUE

Setup a new geodatabase to the PostgreSQL database. Don’t forget to create a license and include it in the setup process. Change the permission of the authorization file to prevent others reading the file.

sdesetup -o install -d POSTGRESQL -D data -u sde -l ~/authorization.ecp

If the storage of raster data is necessary, setup the required table entries.[6]

sdesetup -o install_st_raster -d POSTGRESQL -D data -u sde

To start/stop/list the ArcSDE Status, use the binary sdemon.[7]

sdemon -o start

To alter settings of the ArcSDE Instance, use the binary sdedbtune. Change the geometry storage type to postgis geometry and the raster storage type to the raster type of arcsde (default is binary).

sdedbtune -o alter -k DEFAULTS -P RASTER_STORAGE -v ST_RASTER -D data -u sde
sdedbtune -o alter -k DEFAULTS -P GEOMETRY_STORAGE -v PG_GEOMETRY -D data -u sde

Based on the guide at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server there are some tuning options.

In postgresql.conf

max_connections = 20 # less connections, more work_mem for each connection
shared_buffers = 1GB # http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS
temp_buffers = 64MB # http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-TEMP-BUFFERS
effective_cache_size = 2GB
work_mem = 100MB # (default = 1MB, too large values can be problematic)
fsync = off
wal_buffers = 16MB # http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm
checkpoint_segments = 32 # checkpoint every 512 MB (or every 5 minutes per default)
checkpoint_completion_target = 0.7

On Linux setting shared_buffers to large values requires to adjust some kernel parameters.[8] Add the parameters to /etc/sysctl.conf to make it permanent.

sudo sysctl -w kernel.shmmax=2147483648 # (=2GB)

Restarting postgresql is required to make changes affect. Use sudo sysctl -a | grep shmmax to determine the systems current shmmax value.

Depending on the SHMMAX value, it might also be needed to adjust SMALL. SHMALL is given in pages and should at least be SHMMAX / PAGE_SIZE. The page-size on a linux system can be determined with the command getconf PAGESIZE. However on a standard debian i386 System SHMALL is set to 2097152 (= 8GB), so no need to modify it.

For further instructions in working with ArcSDE, the ArcSDE Admin Command reference can be found at http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/admincmdref.htm.