Jun 30 2014
Build steps for configuration Module-stage-2
- 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.
- 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:
- 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\ /9.2/redhat/rhel-6-x86_64/postgresql92-libs-9.2.8-1PGDG.rhel6.x86_64.rpm
then go after the PGDG package itself.
yum localinstall http://yum.postgresql.org\ /9.2/redhat/rhel-6-x86_64/postgresql92-9.2.8-1PGDG.rhel6.x86_64.rpm
and then the server package
yum localinstall http://yum.postgresql.org\ /9.2/redhat/rhel-6-x86_64/postgresql92-server-9.2.8-1PGDG.rhel6.x86_64.rpm
and last, the devel package, required by PostGIS
yum localinstall http://yum.postgresql.org\ /9.2/redhat/rhel-6-x86_64/postgresql92-devel-9.2.8-1PGDG.rhel6.x86_64.rpm
- Consider adding PGDG contributed package This is a consideration for the development server; probably not needed for production.
yum localinstall \ http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/postgresql92-contrib-9.2.8-1PGDG.rhel6.x86_64.rpm
/* 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*
- Ensure path to installed PG resources is included
This was necessary for the postgresql user, and in testing for root.
- 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:
If 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
- 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:
- 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)
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
psql postgres=# ALTER USER Postgres WITH PASSWORD ‘<newpassword>’;
- Open server (firewall) port to PostgreSQL service
Exit to root, edit /etc/sysconfig/iptables to open postgresql port with a line similar to this:
Then restart iptables to read the new configuration
service iptables restart
- 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 pg_hba.conf so that it’s simplified to something like this, where the db users are configured to connect locally through loopback (127.0.0.1), 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
- 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.
This should add a line to the Servers object in Server Groups of pgAdmin’s Object Browser.
Double-clicking the server object should expand it to show components of the PG instance.
- 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)
- 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“
libeay32.dll libiconv.dll libiconv-2.dll libintl.dll libpq.dll ssleay32.dll
- 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
- 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!
su - postgres pwd /var/lib/pgsql
And source the edits to make them active
source .bash_profile which pg_ctl /usr/pgsql-9.2/bin/pg_ctl
Leave a Reply
You must be logged in to post a comment.