Postgres OnLine Journal: January 2016 / February 2016
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

Basics
Using PostgreSQL Contribs

Basics

 

An almost idiot's guide to install PostgreSQL 9.5, PostGIS 2.2 and pgRouting 2.1.0 with Yum



If you already have a working PostgreSQL 9.5 install, and just want to skip to relevant sections, follow this list:

As a general note, these instructions are what I did for CentOS 7. For lower versions ther are some differences in packages you'll get. For example currently if you are installing on CentOS 6 (and I presume by extension other 6 family), you won't get SFCGAL and might have pgRouting 2.0 (instead of 2.1)

ADVERTISEMENT: Our upcoming book pgRouting: A PRACTICAL GUIDE focusing on pgRouting 2.1+ just went on preview sale. More details about the launch at BostonGIS: pgRouting: A PRACTICAL GUIDE now on sale

Installing PostgreSQL 9.5 from Yum repository

The list of latest PostgreSQL versions and repos for Fedora 20-22, Red Hat 5-7, CentOS 6-7, Oracle EL Linux 6-7, and Scientific Linux 5-7, Amazon Linux AMI are located at http://yum.postgresql.org/repopackages.php.

  1. Figure out which OS you are on. Note the PGDG repository 9.5 only has binaries for the aforementioned, though for lower versions you can still get 9.4, 9.3 etc..

    First run

    uname -a
    Mine happens to return: Linux centos7 3.10.0-327.4.4.el7.x86_64 #1 SMP Tue Jan 5 16:07:00 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux so I know its a 64-bit because of the X86_64 at the end.
    cat /etc/redhat-release
    Mine returns:
    CentOS Linux release 7.2.1511 (Core)
  2. Install the right rpms from http://yum.postgresql.org/repopackages.php for your platform (note the hyperlink url) since you'll need it for rpm addition. In our case:

    On my CentOS 7 ran:

    sudo rpm -ivh http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
  3. To see all packages from pgdg95 do: sudo yum list | grep pgdg95 should give you entries that look something like below and the ones we want are marked with pgdg95.
    CGAL.x86_64                                4.7-1.rhel7                 pgdg95
    CGAL-debuginfo.x86_64                      4.7-1.rhel7                 pgdg95
    CGAL-demos-source.x86_64                   4.7-1.rhel7                 pgdg95
    CGAL-devel.x86_64                          4.7-1.rhel7                 pgdg95
    SFCGAL.x86_64                              1.2.2-1.rhel7               pgdg95
    SFCGAL-debuginfo.x86_64                    1.2.2-1.rhel7               pgdg95
    SFCGAL-devel.x86_64                        1.2.2-1.rhel7               pgdg95
    SFCGAL-libs.x86_64                         1.2.2-1.rhel7               pgdg95
    barman.noarch                              1.5.1-1.rhel7               pgdg95
    boxinfo.noarch                             1.4.0-1.rhel7               pgdg95
    check_postgres.noarch                      2.22.0-1.rhel7              pgdg95
    cstore_fdw_95.x86_64                       1.4-1.rhel7                 pgdg95
    cstore_fdw_95-debuginfo.x86_64             1.4-1.rhel7                 pgdg95
    cyanaudit95.noarch                         0.9.5-1.rhel7               pgdg95
    dbi-link.noarch                            2.0.0-3.rhel7               pgdg95
    emaj.x86_64                                1.2.0-1.rhel7               pgdg95
    emaj-debuginfo.x86_64                      1.1.0-1.rhel7               pgdg95
    gdal.x86_64                                1.11.0-4.rhel7              pgdg95
    gdal-debuginfo.x86_64                      1.11.0-4.rhel7              pgdg95
    gdal-devel.x86_64                          1.11.0-4.rhel7              pgdg95
    gdal-doc.noarch                            1.11.0-4.rhel7              pgdg95
    gdal-java.x86_64                           1.11.0-4.rhel7              pgdg95
    gdal-javadoc.noarch                        1.11.0-4.rhel7              pgdg95
    gdal-libs.x86_64                           1.11.0-4.rhel7              pgdg95
    gdal-perl.x86_64                           1.11.0-4.rhel7              pgdg95
    gdal-python.x86_64                         1.11.0-4.rhel7              pgdg95
    geoip95.noarch                             0.2.4-1.rhel7               pgdg95
    geos.x86_64                                3.5.0-1.rhel7               pgdg95
    geos-debuginfo.x86_64                      3.5.0-1.rhel7               pgdg95
    geos-devel.x86_64                          3.5.0-1.rhel7               pgdg95
    geos-python.x86_64                         3.5.0-1.rhel7               pgdg95
    ip4r95.x86_64                              2.0.2-2.rhel7               pgdg95
    ip4r95-debuginfo.x86_64                    2.0.2-2.rhel7               pgdg95
    libevent.x86_64                            2.0.22-1.rhel7              pgdg95
    libevent-debuginfo.x86_64                  2.0.22-1.rhel7              pgdg95
    libevent-devel.x86_64                      2.0.22-1.rhel7              pgdg95
    libevent-doc.noarch                        2.0.22-1.rhel7              pgdg95
    libpqxx.x86_64                             1:4.0.1-0.1.rhel7           pgdg95
    libpqxx-debuginfo.x86_64                   1:4.0.1-0.1.rhel7           pgdg95
    libpqxx-devel.x86_64                       1:4.0.1-0.1.rhel7           pgdg95
    mailchimp_fdw95.x86_64                     0.3.1-1.rhel7               pgdg95
    multicorn95.x86_64                         1.3.1-1.rhel7               pgdg95
    multicorn95-debuginfo.x86_64               1.3.1-1.rhel7               pgdg95
    nagios-plugins-pgactivity.noarch           1.25beta1-1                 pgdg95
    ogr_fdw95.x86_64                           1.0.1-1.rhel7               pgdg95
    ogr_fdw95-debuginfo.x86_64                 1.0.1-1.rhel7               pgdg95
    ora2pg.noarch                              16.2-1.rhel7                pgdg95
    orafce95.x86_64                            3.1.2-1.rhel7               pgdg95
    orafce95-debuginfo.x86_64                  3.1.2-1.rhel7               pgdg95
    pagila95.noarch                            0.10.1-1.rhel7              pgdg95
    pg_activity.noarch                         1.2.0-1.rhel7               pgdg95
    pg_catcheck95.x86_64                       1.0.0-1.rhel7               pgdg95
    pg_catcheck95-debuginfo.x86_64             1.0.0-1.rhel7               pgdg95
    pg_partman95.x86_64                        2.2.2-1.rhel7               pgdg95
    pg_partman95-debuginfo.x86_64              2.2.2-1.rhel7               pgdg95
    pg_qualstats95.x86_64                      0.0.7-1.rhel7               pgdg95
    pg_qualstats95-debuginfo.x86_64            0.0.7-1.rhel7               pgdg95
    pg_repack95.x86_64                         1.3.2-1.rhel7               pgdg95
    pg_repack95-debuginfo.x86_64               1.3.2-1.rhel7               pgdg95
    pg_stat_kcache95.x86_64                    2.0.2-1.rhel6               pgdg95
    pg_stat_kcache95-debuginfo.x86_64          2.0.2-1.rhel6               pgdg95
    pg_top95.x86_64                            3.7.0-3.rhel7               pgdg95
    pg_top95-debuginfo.x86_64                  3.7.0-3.rhel7               pgdg95
    pg_track_settings95.x86_64                 1.0.0-2.rhel7               pgdg95
    pgadmin3_95.x86_64                         1.22.0-1.rhel7              pgdg95
    pgadmin3_95-debuginfo.x86_64               1.22.0-1.rhel7              pgdg95
    pgadmin3_95-docs.x86_64                    1.22.0-1.rhel7              pgdg95
    pgagent_95.x86_64                          3.4.0-2.rhel7               pgdg95
    pgagent_95-debuginfo.x86_64                3.4.0-2.rhel7               pgdg95
    pgbackman.noarch                           1.1.0-1.rhel7               pgdg95
    pgbadger.noarch                            7.3-1.rhel7                 pgdg95
    pgbouncer.x86_64                           1.7-1.rhel7                 pgdg95
    pgbouncer-debuginfo.x86_64                 1.7-1.rhel7                 pgdg95
    pgbson95.x86_64                            1.0.1-1.rhel7               pgdg95
    pgbson95-debuginfo.x86_64                  1.0.1-1.rhel7               pgdg95
    pgcenter.x86_64                            0.2.0-1.rhel7               pgdg95
    pgcenter-debuginfo.x86_64                  0.2.0-1.rhel7               pgdg95
    pgcluu.noarch                              2.4-1.rhel7                 pgdg95
    pgdg-oraclelinux95.noarch                  9.5-2                       pgdg95
    pgdg-redhat95.noarch                       9.5-2                       pgdg95
    pgdg-sl95.noarch                           9.5-2                       pgdg95
    pgfincore95.x86_64                         1.1.2-2.rhel7               pgdg95
    pgfincore95-debuginfo.x86_64               1.1.2-2.rhel7               pgdg95
    pgmemcache-95.x86_64                       2.1.2-1.rhel7               pgdg95
    pgmemcache-95-debuginfo.x86_64             2.1.2-1.rhel7               pgdg95
    pgmp95.x86_64                              1.0.2-1.rhel7               pgdg95
    pgmp95-debuginfo.x86_64                    1.0.2-1.rhel7               pgdg95
    pgpool-II-95.x86_64                        3.4.3-3.rhel7               pgdg95
    pgpool-II-95-debuginfo.x86_64              3.4.3-3.rhel7               pgdg95
    pgpool-II-95-devel.x86_64                  3.4.3-3.rhel7               pgdg95
    pgpool-II-95-extensions.x86_64             3.4.3-3.rhel7               pgdg95
    pgpoolAdmin.noarch                         3.4.1-1.rhel7               pgdg95
    pgreplay.x86_64                            1.2.0-1.rhel7               pgdg95
    pgreplay-debuginfo.x86_64                  1.2.0-1.rhel7               pgdg95
    pgrouting_95.x86_64                        2.1.0-1.rhel7               pgdg95
    pgrouting_95-debuginfo.x86_64              2.1.0-1.rhel7               pgdg95
    pgsi.noarch                                1.2.0-1.rhel7               pgdg95
    pgsphere95.x86_64                          1.1.1-4.rhel7               pgdg95
    pgsphere95-debuginfo.x86_64                1.1.1-4.rhel7               pgdg95
    pgtap95.noarch                             0.95.0-1.rhel7              pgdg95
    pguri95.x86_64                             1.20150415-1.rhel7          pgdg95
    pguri95-debuginfo.x86_64                   1.20150415-1.rhel7          pgdg95
    pgxnclient.x86_64                          1.2.1-1.rhel7               pgdg95
    pgxnclient-debuginfo.x86_64                1.2.1-1.rhel7               pgdg95
    phpPgAdmin.noarch                          5.1-2.rhel7                 pgdg95
    plpgsql_check_95.x86_64                    1.0.2-1.rhel7               pgdg95
    plpgsql_check_95-debuginfo.x86_64          1.0.2-1.rhel7               pgdg95
    plproxy95.x86_64                           2.6-1.rhel7                 pgdg95
    plproxy95-debuginfo.x86_64                 2.6-1.rhel7                 pgdg95
    plr95.x86_64                               8.3.0.16-1.rhel7            pgdg95
    plr95-debuginfo.x86_64                     8.3.0.16-1.rhel7            pgdg95
    plsh95.x86_64                              1.20130823-1.rhel7          pgdg95
    plsh95-debuginfo.x86_64                    1.20130823-1.rhel7          pgdg95
    plv8_95.x86_64                             1.4.4-1.rhel7               pgdg95
    plv8_95-debuginfo.x86_64                   1.4.4-1.rhel7               pgdg95
    postcode_95.x86_64                         1.3.0-1.rhel7               pgdg95
    postcode_95-debuginfo.x86_64               1.3.0-1.rhel7               pgdg95
    postgis2_95.x86_64                         2.2.1-3.rhel7               pgdg95
    postgis2_95-client.x86_64                  2.2.1-3.rhel7               pgdg95
    postgis2_95-debuginfo.x86_64               2.2.1-3.rhel7               pgdg95
    postgis2_95-devel.x86_64                   2.2.1-3.rhel7               pgdg95
    postgis2_95-docs.x86_64                    2.2.1-3.rhel7               pgdg95
    postgis2_95-utils.x86_64                   2.2.1-3.rhel7               pgdg95
    postgresql94-jdbc.noarch                   9.4.1207-1.rhel7            pgdg95
    postgresql94-jdbc-javadoc.noarch           9.4.1207-1.rhel7            pgdg95
    postgresql95.x86_64                        9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-contrib.x86_64                9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-debuginfo.x86_64              9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-devel.x86_64                  9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-docs.x86_64                   9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-libs.x86_64                   9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-odbc.x86_64                   09.05.0100-1PGDG.rhel7      pgdg95
    postgresql95-odbc-debuginfo.x86_64         09.03.0400-1PGDG.rhel7      pgdg95
    postgresql95-plperl.x86_64                 9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-plpython.x86_64               9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-pltcl.x86_64                  9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-python.x86_64                 4.1.1-2PGDG.rhel7           pgdg95
    postgresql95-python-debuginfo.x86_64       4.1.1-2PGDG.rhel7           pgdg95
    postgresql95-server.x86_64                 9.5.0-2PGDG.rhel7           pgdg95
    postgresql95-tcl.x86_64                    2.0.0-1.rhel7               pgdg95
    postgresql95-tcl-debuginfo.x86_64          2.0.0-1.rhel7               pgdg95
    postgresql95-test.x86_64                   9.5.0-2PGDG.rhel7           pgdg95
    proj.x86_64                                4.8.0-2.rhel7               pgdg95
    proj-debuginfo.x86_64                      4.8.0-2.rhel7               pgdg95
    proj-devel.x86_64                          4.8.0-2.rhel7               pgdg95
    proj-epsg.x86_64                           4.8.0-2.rhel7               pgdg95
    proj-nad.x86_64                            4.8.0-2.rhel7               pgdg95
    python-argcomplete.noarch                  0.3.7-1.rhel7               pgdg95
    python-argh.noarch                         0.23.0-1.rhel7              pgdg95
    python-psycopg2.x86_64                     2.6.1-1.rhel7               pgdg95
    python-psycopg2-debuginfo.x86_64           2.6.1-1.rhel7               pgdg95
    python-psycopg2-doc.x86_64                 2.6.1-1.rhel7               pgdg95
    repmgr95.x86_64                            3.0.3-1.rhel7               pgdg95
    repmgr95-debuginfo.x86_64                  3.0.3-1.rhel7               pgdg95
    slony1-95.x86_64                           2.2.4-4.rhel7               pgdg95
    slony1-95-debuginfo.x86_64                 2.2.4-4.rhel7               pgdg95
    split_postgres_dump.noarch                 1.3.3-1.rhel7               pgdg95
    tail_n_mail.noarch                         1.27.0-1.rhel7              pgdg95
    tds_fdw95.x86_64                           1.0.7-1.rhel7               pgdg95
    tds_fdw95-debuginfo.x86_64                 1.0.7-1.rhel7               pgdg95
    usda-r18.noarch                            1.0-2.rhel7                 pgdg95
  4. We usually install the below. The devel is optional but needed if you want to compile some other add-on later like non-packaged extensions.

    yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel
  5. You should get a prompt something like:
    Dependencies Resolved
    
    ================================================================================
     Package                   Arch        Version                Repository   Size
    ================================================================================
    Installing:
     postgresql95              x86_64      9.5.0-2PGDG.rhel7      pgdg95      1.3 M
     postgresql95-contrib      x86_64      9.5.0-2PGDG.rhel7      pgdg95      526 k
     postgresql95-devel        x86_64      9.5.0-2PGDG.rhel7      pgdg95      1.7 M
     postgresql95-libs         x86_64      9.5.0-2PGDG.rhel7      pgdg95      213 k
     postgresql95-server       x86_64      9.5.0-2PGDG.rhel7      pgdg95      4.1 M
    Installing for dependencies:
     libxslt                   x86_64      1.1.28-5.el7           base        242 k
    
    Transaction Summary
    ================================================================================
    Install  5 Packages (+1 Dependent package)
    
    Total download size: 8.1 M
    Installed size: 34 M
    Is this ok [y/d/N]:
    Type: y

    It should then download the packages and install them with a Complete! exclamation when done.

If you need to configure things the way you want to configure things

Skip this section if you just want to run with defaults and will only have one instance of PostgreSQL

If you plan to run multiple versions of PostgreSQL e.g. stable 9.5 and devl 9.6, on the same server (so they use different ports), or you just need to be in control of all your options, you'll want to tweak your services scripts a bit. The best way to do that is to edit/create a file in /etc/sysconfig/pgsql/postgresql-9.5 with your preferred changes:

Note that # are remarks to you so don't type them in

su root
vi /etc/sysconfig/pgsql/postgresql-9.5

if the file doesn't exist -- it will be blank which is okay: Click i to insert a line and start typing. I generally like to run on non-standard ports just to avoid postgresql bots blindly pinging on 5432. You also need to run on different ports if you have multiple instances running on same server. Note: PGDATA you don't need to add unless you are unhappy with the default location.

My file when I am done will look something like this

PGPORT=5440
PGDATA=/pgdata95

Click Escape key followed by :w :q (:w saves the file and :q exits)

Creating PostgreSQL data cluster and starting the service

With the Yum setup each version of postgresql goes in its own separate folder, so you can easily have parallel installs of different versions of PostgreSQL without worring about overriding the default data folder.

I'll reiterate the key elements and consequences of these changes you need to watch out for.

Changing password of system postgres account

Installing postgres creates a user account called postgres. If you want to change the password to something you know do this:

sudo passwd postgres

You'll be prompted for a new password.

Key Steps to setup

If you want the service to start up on reboot, then simply do this:

su root
service postgresql-9.5 initdb 

If you get an error:

The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.

Do

/usr/pgsql-9.5/bin/postgresql95-setup initdb
service postgresql-9.5 start
chkconfig --list #(to see list of services)
chkconfig postgresql-9.5 on #(to have start on bootup)

The data cluster is created by default in /var/lib/pgsql/9.5/data and the extensions folder is in /usr/pgsql-9.5/share/extension/ and the binaries are in /usr/pgsql-9.5/bin. To use psql, you can generally get away with just typing psql without the full path, but if you have multiple installations of PostgreSQL, you're better off being explicit which one you want.

For the rest of these exercises, we are going to assume you are just using the default ports etc.

Installing adminpack

To install the adminpack useful for viewing logs and editing postgresql.conf from the comfort of PgAdmin, don't forget to install the admin pack

su postgres
cd ~/
/usr/pgsql-9.5/bin/psql -p 5432 -c "CREATE EXTENSION adminpack;"

Installing PostGIS binaries

PostGIS unfortunately has a lot of dependencies because of GDAL and JSON dependency introduced in 2.0 and while some of the time it's as easy as adding:

If still under postgres switch back to root with exit.

sudo yum install postgis2_95

It often is not. As with this virgin box we tried installing on we got these errors:

:
--> Finished Dependency Resolution
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libdapclient.so.3()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libdap.so.11()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libspatialite.so.2()(64bit)
Error: Package: postgis2_95-2.2.0-1.rhel6.x86_64 (pgdg95)
           Requires: hdf5
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libarmadillo.so.4()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libCharLS.so.1()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libgta.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libgeotiff.so.1.2()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libfreexl.so.1()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libdapserver.so.7()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libcfitsio.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libnetcdf.so.6()(64bit)
Error: Package: gdal-libs-1.9.2-7.rhel6.x86_64 (pgdg95)
           Requires: libhdf5.so.6()(64bit)

The issue is the dependency packages are not distributed with yum postgresql repo. Most of these are in the RHEL Extra Packages for Enterprise Linux (EPEL) repo. So if you get these errors, do the following:

  • The master mirror are located at http://dl.fedoraproject.org/pub/epel.

    First try:

    yum -y install epel-release

    If that doesn't work, you might have to find the explicit epel repo

    For CentOS 6, RHEL 6 64-bit/32-bit (you may need to find the version with

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

    For CentOS 5, RHEL 5 64-bit use:

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-5.noarch.rpm
  • Since we are on CentOS 6 64-bit we went with the second set.

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

    You should get a screen that looks something like this if successful

    ..
    ..
    Preparing...                ########################################### [100%]
       1:epel-release           ########################################### [100%]
    
  • Now we repeat the above command again:

    sudo yum install postgis2_95

  • After the above, you should see a screen that looks like this:

    Dependencies Resolved
    
    ================================================================================
     Package                    Arch      Version                  Repository  Size
    ================================================================================
    Installing:
     postgis2_95                x86_64    2.2.1-3.rhel7            pgdg95     3.8 M
    Installing for dependencies:
     CGAL                       x86_64    4.7-1.rhel7              pgdg95     253 k
     CharLS                     x86_64    1.0-5.el7                epel        63 k
     SFCGAL                     x86_64    1.2.2-1.rhel7            pgdg95      14 k
     SFCGAL-libs                x86_64    1.2.2-1.rhel7            pgdg95     1.7 M
     armadillo                  x86_64    4.320.0-1.el7            epel        21 k
     arpack                     x86_64    3.1.3-2.el7              epel       101 k
     atlas                      x86_64    3.10.1-10.el7            base       4.5 M
     blas                       x86_64    3.4.2-5.el7              base       399 k
     boost-date-time            x86_64    1.53.0-25.el7            base        51 k
     boost-serialization        x86_64    1.53.0-25.el7            base       170 k
     boost-system               x86_64    1.53.0-25.el7            base        39 k
     boost-thread               x86_64    1.53.0-25.el7            base        57 k
     cfitsio                    x86_64    3.370-1.el7              epel       526 k
     fontconfig                 x86_64    2.10.95-7.el7            base       228 k
     fontpackages-filesystem    noarch    1.44-8.el7               base       9.9 k
     freexl                     x86_64    1.0.0f-1.el7             epel        31 k
     gdal-libs                  x86_64    1.11.2-2.el7             epel       4.4 M
     geos                       x86_64    3.5.0-1.rhel7            pgdg95     540 k
     giflib                     x86_64    4.1.6-9.el7              base        40 k
     hdf5                       x86_64    1.8.12-7.el7             epel       1.6 M
     jasper-libs                x86_64    1.900.1-29.el7           base       149 k
     jbigkit-libs               x86_64    2.0-11.el7               base        46 k
     lapack                     x86_64    3.4.2-5.el7              base       5.4 M
     lcms2                      x86_64    2.6-2.el7                base       150 k
     libICE                     x86_64    1.0.9-2.el7              base        65 k
     libSM                      x86_64    1.2.2-2.el7              base        39 k
     libX11                     x86_64    1.6.3-2.el7              base       605 k
     libX11-common              noarch    1.6.3-2.el7              base       162 k
     libXau                     x86_64    1.0.8-2.1.el7            base        29 k
     libXdamage                 x86_64    1.1.4-4.1.el7            base        20 k
     libXext                    x86_64    1.3.3-3.el7              base        39 k
     libXfixes                  x86_64    5.0.1-2.1.el7            base        18 k
     libXxf86vm                 x86_64    1.1.3-2.1.el7            base        17 k
     libdap                     x86_64    3.13.1-2.el7             epel       423 k
     libgeotiff                 x86_64    1.2.5-14.el7             epel       545 k
     libgfortran                x86_64    4.8.5-4.el7              base       293 k
     libgta                     x86_64    1.0.4-1.el7              epel        32 k
     libjpeg-turbo              x86_64    1.2.90-5.el7             base       134 k
     libpng                     x86_64    2:1.5.13-7.el7_2         updates    213 k
     libquadmath                x86_64    4.8.5-4.el7              base       182 k
     libtiff                    x86_64    4.0.3-14.el7             base       167 k
     libtool-ltdl               x86_64    2.4.2-20.el7             base        49 k
     libwebp                    x86_64    0.3.0-3.el7              base       170 k
     libxcb                     x86_64    1.11-4.el7               base       189 k
     libxshmfence               x86_64    1.2-1.el7                base       7.2 k
     mesa-libGL                 x86_64    10.6.5-3.20150824.el7    base       184 k
     mesa-libGLU                x86_64    9.0.0-4.el7              base       196 k
     mesa-libglapi              x86_64    10.6.5-3.20150824.el7    base        39 k
     mpfr                       x86_64    3.1.1-4.el7              base       203 k
     netcdf                     x86_64    4.3.3.1-5.el7            epel       693 k
     ogdi                       x86_64    3.2.0-0.19.beta2.el7     epel       248 k
     openjpeg-libs              x86_64    1.5.1-10.el7             base        85 k
     poppler                    x86_64    0.26.5-5.el7             base       782 k
     poppler-data               noarch    0.4.6-3.el7              base       2.2 M
     proj                       x86_64    4.8.0-4.el7              epel       181 k
     unixODBC                   x86_64    2.3.1-11.el7             base       413 k
     xerces-c                   x86_64    3.1.1-7.el7_1            base       878 k
    
    Transaction Summary
    ================================================================================
    Install  1 Package (+57 Dependent packages)
    
    Total download size: 33 M
    Installed size: 169 M
    Is this ok [y/d/N]:
    

    Click y

  • After you might get an additional prompt to install more dependencies
    Is this ok [y/d/N]:
    Type: y
  • After you are done you should have a screen like this:

    Installed:
      postgis2_95.x86_64...
    Complete!

Note that after all this, you still need to enable PostGIS in each database you will want to use it in. Refer to Enabling Extensions for more details. The extension enabling is also pretty generic for all OS, and is described in PostGIS Install http://postgis.net/install/

Installing ogrfdw binaries

ogr_fdw the spatial vector foreign data wrapper, is also available on yum. To install:

yum install ogr_fdw95
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.5ninesolutions.com
 * epel: mirror.us.leaseweb.net
 * extras: mirror.atlanticmetro.net
 * updates: centos.mirror.constant.com
Resolving Dependencies
--> Running transaction check
---> Package ogr_fdw95.x86_64 0:1.0.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package           Arch           Version                  Repository      Size
================================================================================
Installing:
 ogr_fdw95         x86_64         1.0.1-1.rhel7            pgdg95          23 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 23 k
Installed size: 54 k
Is this ok [y/d/N]:

Type y

If successful, you should see

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.5ninesolutions.com
 * epel: mirror.us.leaseweb.net
 * extras: mirror.atlanticmetro.net
 * updates: centos.mirror.constant.com
Resolving Dependencies
--> Running transaction check
---> Package ogr_fdw95.x86_64 0:1.0.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package           Arch           Version                  Repository      Size
================================================================================
Installing:
 ogr_fdw95         x86_64         1.0.1-1.rhel7            pgdg95          23 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 23 k
Installed size: 54 k
Is this ok [y/d/N]: y
Downloading packages:
ogr_fdw95-1.0.1-1.rhel7.x86_64.rpm                         |  23 kB   00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : ogr_fdw95-1.0.1-1.rhel7.x86_64                               1/1
  Verifying  : ogr_fdw95-1.0.1-1.rhel7.x86_64                               1/1

Installed:
  ogr_fdw95.x86_64 0:1.0.1-1.rhel7

Complete!

Installing pgRouting binaries

pgRouting is a PostgreSQL extension that extends PostGIS for building Location Based Services (LBS) specifically for trip navigation and resource dispatch. It is the subject of our upcoming book pgRouting: A practical guide due out later this year but will be ready for early purchase and Early Preview hopefully within the next week.

Note that since pgRouting relies on PostGIS for much of its functionality, you should have install PostGIS first before attempting to install pgRouting.

To install:

yum install pgrouting_95

You should see a screen like:

Dependencies Resolved

================================================================================
 Package              Arch           Version               Repository      Size
================================================================================
Installing:
 pgrouting_95         x86_64         2.1.0-1.rhel7         pgdg95         243 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 243 k
Installed size: 1.1 M
Is this ok [y/d/N]:

Type: y to the prompt.

You should see a screen like this

Downloading packages:
pgrouting_95-2.1.0-1.rhel7.x86_64.rpm                      | 243 kB   00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgrouting_95-2.1.0-1.rhel7.x86_64                            1/1
  Verifying  : pgrouting_95-2.1.0-1.rhel7.x86_64                            1/1

Installed:
  pgrouting_95.x86_64 0:2.1.0-1.rhel7

Complete!

Now refer to the section Enabling Extensions for more details and also check out the pgRouting manual for pgRouting 2.1 at http://docs.pgrouting.org/2.1/doc/index.html.

Enabling Extensions in a database

To install PostGIS 2.2 and the extended family packaged with PostGIS 2.2 you'd create a database and install the extensions.

su postgres
/usr/pgsql-9.5/bin/psql -p 5432
CREATE DATABASE gistest;
\connect gistest;

Should give you message: You are now connected to database "gistest" as user "postgres".

Next install the extensions

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION ogr_fdw;

To verify the install do:

SELECT postgis_full_version();

You should get something like:

                                                                            postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

If the above output returns 2.2.0 instead of 2.2.1+, you should upgrade, by doing the following:

Exit out psql

\q
exit
and run yum with:

yum update postgis2_95

Which should give you:

Resolving Dependencies
--> Running transaction check
---> Package postgis2_95.x86_64 .. will be updated
---> Package postgis2_95.x86_64 .. will be an update
--> Finished Dependency Resolution
:

Now connect back as postgres and upgrade your database by doing the following

su postgres
/usr/pgsql-9.5/bin/psql -p 5432 -d gistest 

#once in psql console do the following
ALTER EXTENSION postgis UPDATE;
-- repeat for each extension so for example
-- if you installed postgis_topology
ALTER EXTENSION postgis_topology UPDATE;

-- then run to check version
SELECT postgis_full_version();

You should now see 2.2.1 listed

To install pgRouting and verify install

CREATE EXTENSION pgrouting;
SELECT * FROM pgr_version();

Should output

 version |       tag       | build |  hash   | branch | boost
---------+-----------------+-------+---------+--------+--------
 2.1.0   | pgrouting-2.1.0 | 1     | b38118a | master | 1.53.0
(1 row)

Using PostgreSQL Contribs

 

Import Foreign Schema hack with OGR_FDW and reading LibreOffice calc workbooks



PostgreSQL 9.4 and below doesn't support importing whole set of tables from a FOREIGN server, but PostgreSQL 9.5 does with the upcoming Import Foreign Schema. To use will require FDW wrapper designers to be aware of this feature and use the plumbing in their wrappers. IMPORT FOREIGN SCHEMA for ogr_fdw come PostgreSQL 9.5 release is on the features ticket list.

UPDATE: If you are using PostgreSQL 9.5+, you can use the IMPORT FOREIGN SCHEMA feature which is available in ogr_fdw 1.0.1+. We demonstrate this in: ogr fdw IMPORT FOREIGN SCHEMA.

The ogr_fdw comes with this to die for commandline utility called ogr_fdw_info that does generate the table structures for you and will also list all the tables in the Foreign data source if you don't give it a specific table name. So with this utility I wrote a little hack involving using PostgreSQL COPY PROGRAM feature to call out to the ogr_fdw_info commandline tool to figure out the table names and some DO magic to create the tables.

Though ogr_fdw is designed to be a spatial foreign data wrapper, it's turning out to be a pretty nice non-spatial FDW as well especially for reading spreadsheets which we seem to get a lot of. This hack I am about to demonstrate I am demonstrating with LibreOffice/OpenOffice workbook, but works equally well with Excel workbooks and most any data source that OGR supports.

This hack came in really handy for processing workbooks (with several spreadsheets e.g. an Excel or LibreOffice workbook) and SQL Server tables. Sadly I tried it on my MS Access data source, and while it could read the list of tables, PostgreSQL returned blank text when trying to grab the table structure. This was really weird considering from the commandline I can see the table structure. Haven't figured out what caused that. I blame it on some funky character MS Access is spitting out that PostgreSQL is reading as a black hole character or some weird permission issue with reading MS access catalogs from within PostgreSQL. The commandline worked fine using directly even for MS Access and reading the table once the foreign table was created worked fine as well.

The ogr_fdw_info wrapper function

The wrapper function just spits out the SQL to do the work. The reason I have it not do the work is because depending on my needs, I may say want to prefix the table names with something or change the data types it emits. For example in SQL Server, unless the timestamp field is the last field in the table, ogr_fdw doesn't handle it right. I still have to investigate this one. So where it says timestamp, for SQL Server I swap it out for varchar.

Note that I also hard-coded the executable path in there which is very specific to where it will end up if you are running PostgreSQL 9.4 on windows.

Some things to be cognizant of

  • The COPY FROM is a server SQL command (not the psql variant). As such the executable must be on the server and accessible by the postgres service account
  • Your data source must also be accessible from the server postgres process, which usually means it must reside on the server somewhere unless its a webservice like CartoDB or WFS.
  • Your paths will obviously be different if on Linux/Unix/Mac and probably much shorter. I purposely used the windows path because windows users have it harder with windows love of spaces and all that.
2016-01-03 - ogr_fdw_table function updated to prevent COPY FROM from mangling layer names with special characters in it. Done by forcing to text with csv and using | (as delimiter which wouldn't exist in most datasources so prevents , from being used which could be found in name of data source.
CREATE OR REPLACE FUNCTION ogr_fdw_sql_table(
    data_source text,
    layer text DEFAULT ''::text)
  RETURNS text AS
$$
BEGIN
        DROP TABLE IF EXISTS ogr_fdw_out;
        CREATE TEMP TABLE ogr_fdw_out(out text);
        IF layer > '' THEN 
        EXECUTE 'COPY ogr_fdw_out FROM PROGRAM ''"C:/Program Files/PostgreSQL/9.4/bin/ogr_fdw_info" -s "' 
            || data_source || '" -l "' 
            || layer || '"'' WITH (format ''csv'', delimiter ''|'')';
        ELSE
                EXECUTE 'COPY ogr_fdw_out FROM PROGRAM ''"C:/Program Files/PostgreSQL/9.4/bin/ogr_fdw_info" -s "' 
                || data_source 
                || '"''  WITH (format ''csv'', delimiter ''|'')';
        END IF;
        RETURN (SELECT string_agg(out,E'\n') from ogr_fdw_out);
END;

$$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

For demonstrating this little puppy in action, I need a workbook with several spreadsheets to do it justice. So I downloaded a LibreOffice spreadsheet template from Monthly Home Budget Workbook and created a new workbook called Budget2015.ods from that. Note that LibreOffice, OpenOffice, and the Microsoft Excel 2007 and above (the .xlsx) format all are some variant of compressed XML. I will reiterate that what data sources you can read with ogr_fdw is dictated by your libgdal library and what support it is compiled with. In the case of reading LibreOffice/OpenOffice workbooks and the newer Microsoft XLSX files, your GDAL library needs to be compiled with Expat. In order to read older Excel files (xls format), you need to have your libgdal built with FreeXL support. The windows builds we created and detailed in PostgreSQL 9.4 bag-o-fdws are built with Expat and FreeXL (and as you can see, offer xlsx and ods support) and xls.

Reading LibreOffice Calc workbooks (ODS) direclty from PostgreSQL

So to test out, I throw my new fangled budget workbook in my FDW folder and ran this little statement.

SELECT ogr_fdw_sql_table('C:/fdw_data/Budget2015.ods');

And the output is this beautiful thing. Now as a non-spatial person, you have to get over the hump that Layer means Table as far as you are concerned

Layers:
  Dashboard
  Form 2 - Equity
  Form 3 - Income
  Form 4 - Lump Sum Planning
  Form 5 - Monthly Cash Flow
  Form 7 - Allocation
  Form 8 - Spending Details
  Form 9 - Work Expenses
  Debt Analysis
  Categories and Items

So we've got a couple of spreadsheets (tables, layers) in this workbook. I'm going to demonstrate how to see the structure with 2 different widely different spreadsheets just to demonstrate a particular behavior of the OGR ODS driver (the XLSX and XLS drivers seem to behave the same as far as I can tell) when faced with something that is not quite tabular looking (or lacking headers) vs. is tabular.

SELECT ogr_fdw_sql_table('C:/fdw_data/Budget2015.ods', 'Dashboard');

The output of the Dashboard layer structure is:

CREATE SERVER myserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'C:/fdw_data/Budget2015.ods',
    format 'ODS' );

CREATE FOREIGN TABLE dashboard (
  fid integer,
  field1 varchar,
  field2 varchar,
  field3 real,
  field4 varchar,
  field5 varchar,
  field6 varchar,
  field7 varchar )
  SERVER myserver
  OPTIONS ( layer 'Dashboard' );

Now if you open up the workbook in LibreOffice Calc and look at the dashboard sheet, you'll see it's got charts and rows of data with no header. It has no header for the table. The only thing that is sure is that column 3 has got numbers. So since OGR needs to ascibe a column name and can't infer one from the top row, it just calls them field1.. fieldn. OGR always adds an additional column, called fid at the beginning which is really a row number identifier. You'll also sometimes see a geom column as the second column if it thinks it's a spatial datasource.

If you were to do the same exercise using:

SELECT ogr_fdw_sql_table('C:/fdw_data/Budget2015.ods','Form 4 - Lump Sum Planning');

You'd get the same CREATE SERVER command since they come from the same workbook, but your CREATE FOREIGN TABLE would look like this:

CREATE FOREIGN TABLE form_4___lump_sum_planning (
  fid integer,
  item varchar,
  annual_amount varchar,
  monthly_amount integer )
  SERVER myserver
  OPTIONS ( layer 'Form 4 - Lump Sum Planning' );

Note how the table name and the field names have been changed so they are legal field and column names for PostgreSQL (not requiring you to quote the columns or table name). For example the spaces in the header of spreadsheet cells got converted to underscore and lower case: e.g. Monthly Amount became monthly_amount. At first glance, it seems a little strange it considered annual_amount varchar and monthly_amount integer. OGR ODS driver by default infers the datatype from the data it sees (there are override environment variables for this, but can't pass them into the OGR_FDW yet I don't think). The Monthly Amount column had just 0s in it and the Annual Amount column had no values. If you go back and fill in the Annual Amount column with numbers (so that monthly_amount recomputes to numbers with decimals), and then you rerun this command, you'll find that the annual_amount and monthly_amount both then show as real data type.

To prepare the data for querying directly from PostgreSQL, we're going to:

  1. Create a schema to house foreign tables: CREATE SCHEMA budget2015;
  2. Create a foreign server pointing to this workbook. For this take the CREATE SERVER statement and replace myserver with: svr_budget2015_workbook.

    CREATE SERVER svr_budget2015_workbook
      FOREIGN DATA WRAPPER ogr_fdw
      OPTIONS (
        datasource 'C:/fdw_data/Budget2015.ods',
        format 'ODS' );
  3. Apply some DO magic to create the foreign tables: This do magic drops the foreign tables if they exist and recreates them. And also replaces references to myserver with the new server name. Finally it prefixes the tables with buget2015. so they get dumped in the budget2015 schema. You could do the same with set search_path before you run the DO.

    DO language plpgsql $$
    DECLARE var_sql text;
    BEGIN 
        var_sql := (WITH conn As (SELECT 'C:/fdw_data/Budget2015.ods'::text As conn),
        cte_tb_names AS 
            (SELECT unnest(
                string_to_array(ogr_fdw_sql_table(conn), E'\n') ) As table_name
                FROM conn)
            , tb AS (SELECT trim(table_name) As table_name
                FROM cte_tb_names WHERE table_name NOT LIKE 'Layers:%' and trim(table_name) > '')
        SELECT 
               string_agg( replace(regexp_replace(ogr_fdw_sql_table(conn, tb.table_name), 
                        'CREATE SERVER (.*);(.*)CREATE FOREIGN TABLE ([a-z0-9\_]+)', 
                  E'DROP FOREIGN TABLE IF EXISTS budget2015.\\3;CREATE FOREIGN TABLE budget2015.\\3'), 
            'myserver','svr_budget2015_workbook'), E'\n') As sql FROM tb, conn);
        
        EXECUTE var_sql;
    END ;$$;
    
    
  4. Finally to verify I got the worksheets as linked foreign tables, I run this query:

    SELECT table_name
        FROM information_schema.tables
    WHERE table_schema='budget2015'
    ORDER BY table_name;
    

    Which outputs this

             table_name
    ----------------------------
     categories_and_items
     dashboard
     debt_analysis
     form_2___equity
     form_3___income
     form_4___lump_sum_planning
     form_5___monthly_cash_flow
     form_7___allocation
     form_8___spending_details
     form_9___work_expenses

Now I can query some tables:

SELECT creditor, amount, interest
    FROM budget2015.debt_analysis 
WHERE interest < 0.08
ORDER BY creditor;
 creditor  | amount | interest
-----------+--------+----------
 Credit 1  |  20000 |   0.0775
 Credit 10 |        |     0.03
 Credit 11 |        |     0.04
 Credit 7  |        |  0.04875
 Credit 8  |        |     0.01
 Credit 9  |        |     0.02

Using PostgreSQL Contribs

 

PLV8 binaries for PostgreSQL 9.5 windows both 32-bit and 64-bit



I've built pl/v8 for PostgreSQL 9.5 both 32-bit and 64-bit.

I built basically using these instructions on my gist gist page.

I've listed below PL/V8 binaries I built for PostgreSQL 9.5 for windows 64-bit and 32-bit. These I tested with EDB PostgreSQL 9.5 windows installs and seem to work fine and also passed all regression tests. These were built with PLV8 v1.4.4.

PL/V8 PostgreSQL 9.5 binaries

We hope windows users find these useful.


Using PostgreSQL Contribs

 

Foreign Data Wrappers for PostgreSQL 9.5 windows



FDW binaries for PostgreSQL 9.5 windows 64-bit/32-bit

As you may have noticed we've been building our favorite extensions which we'll use in our PostgreSQL 9.5 installs. Next on our list are the FDWS

Below are the zip files that contain the binaries and dependency files for our favorite FDWs (that aren't normally available for windows)

These packages contain www_fdw and file_textarray_fdw

Both are www_fdw and file_textarray_fdw are covered in our PostgreSQL: Up and Running 2nd Edition book.

For this packaging, we left out our most favorite -- ogr_fdw, since ogr_fdw is now packaged as part of the PostGIS 2.2 Windows bundle. If you are not using PostGIS and would still like to use the ogr_fdw multi-purpose foreign data wrapper, you can download it as a standalone package from the PostGIS winnie experimental builds - http://winnie.postgis.net/download/windows/pg95/buildbot/extras/. Though ogr_fdw is best served with PostGIS, it has no direct dependency on PostGIS.

The curl library for www_fdw is built with SSL support and utilizes the ssleasy.dll packaged with the EDB installs. It's the same curl used by ogr_fdw.

FDWs in the 9.5 downloads