Table Of Contents
PLV8 binaries for PostgreSQL 9.5 windows both 32-bit and 64-bit
Foreign Data Wrappers for PostgreSQL 9.5 and 9.6 windows
From the Editors
Paris OSGeo Code Sprint 2016 Highlights
Leo and I attended the Paris OSGeo Code Sprint at Mozilla Foundation put together by Oslandia and funded by several companies. It was a great event. There was quite a bit of PostGIS related hacking that happened by many new faces. We have detailed at BostonGIS: OSGeo Code Sprint 2016 highlights some of the more specific PostGIS hacking highlights. Giuseppe Broccolo of 2nd Quadrant already mentioned BRIN for PostGIS: my story at the Code Sprint 2016 in Paris.
ODBC enhancements to GDAL and OGR FDW
One of the things high on our laundry list, was improving performance of the ODBC driver in ogr_fdw, which by extension means improving performance of ODBC driver in GDAL. We've been using ogr_fdw quite a bit, mostly for non-spatial uses.
While I've always had a fondness for the GDAL, which grew a bit with PostGIS raster dependency on GDAL, the onset of ogr_fdw foreign data wrapper means I really love GDAL now because I can bring this swiss army data tool right into my database to serve non-spatial workloads as well. An ETL driven by the power of PostgreSQL. What could be better? Anyway I think my interest got a bit infectious because one guy named Jack came on PostGIS mailing list talking about ODBC in OGR FDW. It was slow for him and I rightfully concluded he had a ton of tables in his SQL Server. I had discovered this myself a while back when trying to pull data from a very large SQL Server database, to which I had to overcome the issue by itemizing the tables I wanted. The fundamental issue is that the GDAL ODBC driver does a load of all table meta data on connection unless you explicitly state in the connection string which you want. Since the connection is called for every query in OGR_FDW, this can be like 5 minutes added to your query time even if you just wanted to query 1 row of a small table in a large database. This was the main thing I at least wanted to know how to start tackling. I pointed at the section in ogr_fdw where the query goes south and traced thru the ODBC gdal driver call. Paul Ramsey (author of ogr_fdw) had some ideas of how the ogr_fdw code could be changed to hold on to the ogr connection instead of opening it up on each query call.
Even Roualt who is the chief developer now of GDAL was at the sprint too, his writeup is here. He was very busy as you can see, so I didn't want to take up too much of his time. He was nice enough though to come over and point at the sections of the PostgreSQL driver that are useful to copy for improving the ODBC driver. I sadly haven't gotten too deep into that yet. The first thing that was nagging me that I needed to fix before jumping into the ODBC driver, was to fix the GDAL configure script, so I could build Native Windows ODBC support under mingw64 without having to hack the configure script each time. This is something I had promised to do like 7 months ago, but had run into a snaffu with my mingw64-w32 env still not liking my revised configure that seemed to work fine under mingw64-w64. I'm happy to say, I've overcome that issue and finally done it and it was my first non-trivial GDAL patch and was accepted for both GDAL 2.0 and GDAL trunk (2.1). So now on to the harder work of improving the ODBC driver.
Two on my todo for the ODBC driver, are to
- Implement as needed loading like what the GDAL PostgreSQL driver has
- Returning as many columns as what an ODBC datasource supports. For GDAL 2.0, I submitted a trivial patch (with Even's help) to bump up the hard-coded limit of 500 columns to 1024 columns. For GDAL 2.1, I plan to have a better patch to have limit only be based on the actual ODBC source.
Raster data and materialized views restore and foreign table gotchas
Ronan Dunklau of Dalibo came up to me to discuss the sorry state of restoring PostGIS raster data and reminded me how I was going to do something about this and how it also affected restoring some materialized views and some cases of PostGIS geometry (like spatial indexes that use ST_Transform). The fundamental issue is that PostGIS, like many other extensions doesn't enforce a schema in which PostGIS should be installed in, and we have many functions that call other functions. Since pg_restore always sets the search_path = pg_catalog, schema_table_view - your data does not come back if PostGIS is not installed in the same schema as your data and restoration of your data requires calling PostGIS functions that call other PostGIS functions during load. Several raster table check constraint functions fall into this bucket, but they are not the only ones. I suspect we are the only extension suffering from this problem because we have more functions than any other extension and might be the only extension that has functions calling other functions.
I had a plan, but my plan would not handle ALTER EXTENSION .. SET search_path. He proposed the interesting idea of using event triggers to handle that situation. With that encouragement, I started hacking away at the problem with a proof of concept that contained hand-coded ALTER FUNCTION and Paul's query to figure out schema PostGIS is installed in. When I got back home I replaced
with a perl script, encouraged by Dan Baston who said, "Why don't you just do it for all functions". Dan was also suffering database restore problems because of this issue and he doesn't even use raster. So I've done my first non-trivial perl script.
For PostGIS 2.2, the perl script, generates an SQL file that sets search path of all functions except for those used in relationships that require SQL inlining behavior. I discovered the unpleasant truth that doing - ALTER FUNCTION set search_path=... destroys
SQL inlining behavior, which we need to pull off having things like our ST_Intersects function utilize a spatial index. So sadly raster data restore issue is fixed, but materialized views and foreign table calls that utilize ST_Intersects that use ST_Intersects, ST_covers etc are not fixed.
For PostGIS 2.3, I've folded this script into the extension script, so that the search path setting thing happens automagically. I plan to experiment with Ronan's idea of using EVENT TRIGGERS to see if it works well or creates more problems than it solves.
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)
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.
- 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/Linuxso 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) - 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 - To see all packages from pgdg95 do:
sudo yum list | grep pgdg95should 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
-
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 - You should get a prompt something like:
Type: y
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]:
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
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. So to change you can: 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)
New versions you can once connected to system
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:
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)
For newer CentOS like 7+, use
systemctl list-unit-files #(to see list of services)
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 postgis2_95-client
The postgis2_95-client contains the PostGIS commandline tools shp2gpsql, pgsql2shp, raster2pgsql that are useful for loading or exporting spatial data.
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-releaseIf 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.rpmFor CentOS 5, RHEL 5 64-bit use:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-5.noarch.rpmSince 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.rpmYou 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_95After 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 exitand 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 Extensions
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.
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.
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.
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:
- Create a schema to house foreign tables:
CREATE SCHEMA budget2015; Create a foreign server pointing to this workbook. For this take the CREATE SERVER statement and replace
myserverwith:svr_budget2015_workbook.CREATE SERVER svr_budget2015_workbook FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'C:/fdw_data/Budget2015.ods', format 'ODS' );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 ;$$;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 Extensions
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 Extensions
Foreign Data Wrappers for PostgreSQL 9.5 and 9.6 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 and 3rd editions 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.
- PostgreSQL 9.6 w32 FDWs (zip) 7z version
- PostgreSQL 9.6 w64 FDWs (zip) 7z version
- PostgreSQL 9.5 w32 FDWs (zip) 7z version
- PostgreSQL 9.5 w64 FDWs (zip) 7z version
FDWs in the 9.5 and 9.6 downloads
- file_textarray_fdw - handles delimited files (even large ones very fast) and can handle delimited data with varying columns in each row.
- https://github.com/cyga/www_fdw - for querying web services