Jun 30 2014

SGeoS Add PostgreSQL 9.2.8 Enterprise Database – Module 2 of 9

Published by at 1228h under SL In General

PostgreSQL 9.2.8 Enterprise Database Server

Build steps for configuration Module-stage-2

  1. Start from completed system Module-stage-1
    In discussion with Josh Berkus (with Jeff Frost on the line) during PG update meeting of 2014.04.21, our target version is latest in PG 9.2 series, which was 9.2.8 as of that date, with PostGIS 2.0 extension.Per existing EAS data server configurations, data area goes in /data and logs in /pg_xlog
    System prep adapted from instructions in  PostgreSQL Wiki and this posting as well.PostGIS installation with myriad dependencies seemed best documented on this blog post.
  2. Configure YUM repository
    On the CentOS 6.5 system this is /etc/yum.repos.d/CentOS-Base.repo and it’s necessary to add the following lines to avoid having the default RHEL 6.5 version of postgresql installed:
    in [base]


    in [updates]

  3. Install PostgreSQL Global Development Group (PGDG) RPM packages for server
    Add these RPMs to replace with packages more current than the CentOS 6.5 default version.Start with installs of libraries upon which the PGDG package depends:

    yum localinstall http://yum.postgresql.org\

    This solves a dependency that will otherwise cause the subsequent line to be unhappy.

    then go after the PGDG package itself.

    yum localinstall http://yum.postgresql.org\


    and then the server package

    yum localinstall http://yum.postgresql.org\


    and last, the devel package, required by PostGIS

    yum localinstall  http://yum.postgresql.org\


  4. Consider adding PGDG contributed package This is a consideration for the development server; probably not needed for production.
    yum localinstall \


    /* If things just don’t work out right, and a better way forward is found that requires change at an earlier step in the PostgreSQL installation process, it’s OK.  Document what’s going to change next time, and then:

    yum erase postgresql-9.2*


  5. Ensure path to installed PG resources is included
    This was necessary for the postgresql user, and in testing for root.
  6. su - postgres

    There, edit .bash_profile to append these lines:

    And source the edits to make them active

    source .bash_profile
    which pg_ctl
  7. Configure major PG locations
    Before initializing PostgreSQL, configure the file system for desired location of data and logs.
    Since the SGeoS machine will sometimes be primarily a database server, choosing root-level locations for data and logs seems merited.Make sure  /etc/sysconfig/pgsql/postgresql.conf exists,  and edit thusly:
    pg_07If these directories will be the locations, then they’d better exist, be owned by PG, grouped with PG, and one should attempt to label an appropriate SElinux context with semanage.

    cd /
    mkdir /data
    mkdir /pg_xlog
    chown postgres /data /pg_xlog
    chgrp postgres /data /pg_xlog
    semanage fcontext -a -t postgresql_db_t “/data(/.*)?”
    semanage fcontext -a -t postgresql_db_t “/pg_xlog(/.*)?”
    su - postgres
    mkdir /data/9.2
    mkdir /data/9.2/data
    mkdir /pg_xlog/9.2
  8. Initialze PostgreSQL (one time only)
    Carefully verify that your data area is prepared and writeable by postgres user, then initialize. If mistakes are made, consider a cd into /data, then $ rm -Rf 9.2 to try once again.

    initdb -D /data/9.2/data


    Continue to tune the data area.  These locations reflect the SFGIS EAS data server style.
    In the interest of SElinux harmony, do the cp, and do not use mv.

    cd /data/9.2/data
    cp  postgresql.conf  postgresql.conf.orig
    cp  pg_hba.conf  pg_hba.conf.orig
    cp -R  pg_xlog  /pg_xlog/9.2

    Verify the size of the copy of pg_xlog

    du -s pg_xlog


    du -s /pg_xlog/9.2/pg_xlog


    Remove the original pg_xlog, and replace with a symbolic link to the copy

    rm -R pg_xlog
    ln -s /pg_xlog/9.2/pg_xlog pg_xlog

    This should leave the directory looking like this:



  9. Start PostgreSQL and verify it’s running; create test user
    As the postgres user, start the service, from root su – postgres to set environment.(without enviro. variables set)

    pg_ctl start -l /pg_xlog/9.2/pg_xlog/syslog -D /data/9.2/data

    (with enviro. variables set)

    pg_ctl start

    That should work, so next use psql create a test user and schema to validate connections.
    But first set your postgres db user (db super user) password
    Now is the time to record this assignment in the run book

    postgres=# ALTER USER Postgres WITH PASSWORD ‘<newpassword>’;


  10. Open server (firewall) port to PostgreSQL service
    Exit to root, edit /etc/sysconfig/iptables to open postgresql port with a line similar to this:
    pg_13Then restart iptables to read the new configuration

    service iptables restart
  11. Configure PostgreSQL service to accept connections
    As user postgres, edit some postgresql configuration files in /data/9.2/data/

    su - postgres
    cd $PGDATA

    (or try the aliased  ‘gopg’)

    Edit postgresql.conf so that listen_address and port are uncommented and set properly for testing purposes.  For production this can be locked down to  later.

    Edit pg_hba.conf so that it’s simplified to something like this, where the db users are configured to connect locally through loopback (, which can work through an ssh connection, and for testing also the addresses of Windows workstations from which a GUI administration tool could be run (here 10.x.xx.0/24)
    (using md5 requires that the postgres db user password has been set)

    restart postgresql to get these changes applied

    pg_ctl restart


  12. Verify Connections function from Windows workstation
    On the workstation, it’s possible to use a Windows GUI like pgAdmin III to confirm the configuration is working for remote access.  This example describes pgAdmin.
    Launch pgAdmin, and use File > Add Server… to open the New Server Registration dialog.
    Input a reference name for the server in Name, the server’s IP address in Host, and consider testing connection to Maintenance DB postgres with user postgres if you’ve configured things as described above.
    pg_17This should add a line to the Servers object in Server Groups of pgAdmin’s Object Browser.
    pg_18Double-clicking the server object should expand it to show components of the PG instance.
  13. Secure db TCP/IP  Connections  with SSL
    As user postgres, consider testing with a new self-signed cert for use only by PostgreSQL.  The keys can be in an area separated from data.  One approach to do this is to create a directory above the active $PGDATA but still within the installation tree, like /data/9.2/pki

    cd /data/9.2
    mkdir pki
    cd pki

    Once there, generate a private key for postgresql

    openssl genrsa -out pgca.key 4096

    Generate a Certificate Signing Request

    openssl req -new -key pgca.key -text -out pgca.csr

    Generate a Self-Signed Key

    openssl x509 -req -days 365 -in pgca.csr -signkey pgca.key -out pgca.crt

    Copy these  files to the following locations (DO NOT move them; copy them–then delete)

    mkdir certs
    mkdir private
    cp pgca.crt /data/9.2/pki/certs
    cp pgca.key /data/9.2/pki/private
    cp pgca.csr /data/9.2/pki/private
    chmod 600 /data/9.2/pki/certs/pgca.crt /data/9.2/pki/private/pgca.*
    rm pgca.*

    Once the connections have been verified as working, save a copy of postgresql.conf and proceed to edit the section near Security and Authentication, turning

    ssl = on
    ssl_ciphers = ‘ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH’
    ssl_cert_file = ‘certs/pgca.crt’
    ssl_key_file = ‘private/pgca.key’

    Save edits, then restart PostgreSQL (as postgres user)

    pg_ctl restart
  14. On Windows, add PostgreSQL Client libraries to ArcGIS for Desktop
    An SFGIS installer has been prepared for ArcGIS 10.2.2 for Desktop and PostgreSQL 9.2 useConfusingly, even on Windows 7 Pro x86_64 systems, it is essential to load the 32-bit drivers for ArcGIS 10.2 for Desktop—go figure.  Download from http://customers.esri.com the section DBMS Support Files (Client Libraries and Databases).  Avoid any temptation of downloading the Esri PostgreSQL 9.2.2 distribution if you wish to follow the hybrid build.  Instead, expand the PostgreSQL Client Libraries and download PostgreSQL 9.2.2 Client Libraries (Windows) for your workstations.Unpacking those and drilling down will reveal “32bit” and “64bit” folders.  Ignore the 64bit because it is only intended for Windows Server installs of ArcGIS for Server accessing PostgreSQL 9.2.  Instead, use the 32bit folder that is for all versions of ArcGIS for Desktop, even those on 64-bit Windows 7.  There should be six files (you’re in the 64bit folder if there’s only five!)
    Close all running ArcGIS apps, then
    copy all six into    “C:\Program Files (x86)\ArcGIS\Desktop10.2\bin“

  15. Configure PostgreSQL to be enabled at boot if desired
    For administrative convenience it may be desirable to have PostgreSQL always start up at boot.  Here’s how to set that; if the opposite result is desired, substitute “off” for “on”

    chkconfig postgresql-9.2 on
  16. Create an SDE database if desired
    For Esri Desktop user convenience it may be desirable to store data in Esri ST_GEOMETRY format as well as PostGIS  PG_GEOMETRY format.  While the PG_ is native to PostGIS, it is necessary to edit the single ArcGIS-enabled database’s SDE schema, sde_dbtune table, GEOMETRY_STORAGE row to have the value PG_GEOMETRY rather than the Esri-default ST_GEOMETRY.
    Of course, PostGIS must be installed before trying to load data with this geometry storage method!

No responses yet

Trackback URI | Comments RSS

Leave a Reply

You must be logged in to post a comment.