Table Of Contents
What's new and upcoming in PostgreSQL
PostgreSQL 9.4beta1 and PostGIS 2.2.0 dev on Windows
PostgreSQL 9.4beta1 was released last week and windows binaries for both 32-bit and 64-bit are already available to try it out from http://www.postgresql.org/download/windows. Since this is a beta release, there are no installers yet, just the zip binary archive. To make the pot a little sweeter, we've setup the PostGIS windows build bot (Winnie) to automatically build for 9.4 - PostGIS 2.2.0 development branch and pgRouting 2 branches whenever there is a change in the code. We also have the pointcloud extension in the extras folder. If you are on 9.3, we've got 2.2 binaries for that as well. The PostGIS/pgRouting related stuff you can find at http://postgis.net/windows_downloads in the 9.4 folder.
For the rest of this article we'll discuss a couple of stumbling blocks you may run into.
Much of what we'll describe here is windows specific, but thanks to the beauty of extensions and GUCs, the extension install and GUC setting part for PostGIS is applicable to all operating systems.
Setting up PostgreSQL 9.4beta1 for experimentation
Playing around with PostgreSQL 9.4beta1 is generally fairly easy. Download the zip archive from http://www.postgresql.org/download/windows for 9.4 and write up a batch script to start the database server as we described in Starting PostgreSQL in windows without install.
If you want to use PostGIS raster support, add the following line to your batch script before the initdb line:
@SET GDAL_DATA=%~dp0\gdal-data
Make sure when you launch for first time you have the initdb line unremarked so it builds the cluster.
Here is where you may run into problems.
If you don't have Visual C++ 2013 runtime installed on your pc, you may get an error:
"The program can't start because MSVCR120.dll is missing from your computer. Try reinstalling the program to fix this problem."If you get that error, download the Visual C++ Redistributable Packages for Visual Studio 2013: http://www.microsoft.com/en-us/download/details.aspx?id=40784. The vcredist_x86.exe you will need if you are running the 32-bit version of PostgreSQL 9.4 and the vcredist_x64.exe is generally the one you want if you are running the 64-bit PostgreSQL 9.4beta1.
After you install the respective setup, you should be able to launch your batch script without error.
- Next shut down out of your batch script by click enter to stop this assumes you are using our sample script.
- Download the respective PostGIS and pgRouting binaries from: http://postgis.net/windows_downloads and copy into your PostgreSQL folder.
- Use the pgAdmin in bin folder of your new 9.4 setup, connect to the server
- Create a new database
Connect to the database and running the following commands at pgAdmin SQL editor or psql shell. This part is the same regardless what OS you are on and if you want the full shebang.
CREATE EXTENSION postgis; CREATE EXTENSION fuzzystrmatch; --needed for tiger geocoder CREATE EXTENSION address_standardizer; --commonly used with tiger geocoder CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology;
If you also unzipped in the pgRouting binaries:
CREATE EXTENSION pgrouting;
More instructions here: http://pgrouting.org
This PostGIS family of extensions is very fertile and more babies expected later.
If you want PointCloud - still experimental, then make sure to copy the pointcloud extension from the extras folder (PostgreSQL specific folder) http://postgis.net/windows_downloads and then run:
CREATE EXTENSION pointcloud; CREATE EXTENSION pointcloud_postgis;
If you are interested in learning more about LIDAR support with pointcloud, check out LIDAR in PostgreSQL with PointCloud video and related slides PointCloud slides.
Check things are installed
SELECT postgis_full_version();Should output something of the form:
POSTGIS="2.2.0dev r12575" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER
As of PostGIS 2.0.6, 2.1.3 and 2.2.0, raster drivers and outdb are disabled by default, these can be renabled either via environment variables or for PostGIS 2.2.0 the new GUC variables detailed in the What's new section: New in 2.2.
For a generic launch script that works for all versions, I just add the lines to my script before the pg_ctl start:
@SET POSTGIS_ENABLE_OUTDB_RASTERS=1 @SET POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL
However you can instead use the new ALTER SYSTEM feature in 9.4, and do this at an SQL Editor prompt:
ALTER SYSTEM SET postgis.gdaL_enabled_drivers TO 'ENABLE_ALL'; ALTER SYSTEM SET postgis.enable_outdb_rasters TO '1';
You can alternatively set these at the database or session level if you want each database/session to have different raster options.
For changes to take effect (if you did at system), you have to stop (enter) and relaunch the batch script.
To confirm you have all drivers, run the below and you should get 10 or more records
SELECT * FROM ST_GDALDrivers();Basics
An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum
In this exercise, we'll go thru installing PostgreSQL 9.3 on a CentOS 6 64-bit box. We'll cover upgrading in a later article. For the rest of this article, we'll go over configuring yum to use the PostgreSQL PGDG Yum repository found at http://yum.postgresql.org , which has the latest and greatest of 9.3. It's been a while since we wrote step by step instructions for installing with Yum.
Last we did was An almost idiot's guide to Install PostgreSQL 9.0 with Yum which is now very dated.
Installing PostgreSQL 9.3 from Yum repository
The list of latest PostgreSQL versions and repos for Fedora 18-20, Red Hat 5-6, CentOS 5-6, and Scientific Linux 5-6 are located at http://yum.postgresql.org/repopackages.php.
- Figure out which OS you are on. Note the PGDG repository 9.3 only has binaries for the aforementioned, though for lower versions you can still get 9.2, 9.1 etc..
First run
uname -a
Mine happens to return:Linux 30266-3-2358424 2.6.32-358.2.1.el6.x86_64 #1 SMP Wed Mar 13 00:26:49 UTC 2013 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 release 6.4 (Final) - 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:
sudo rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm sudo yum list | grep postgresqlshould give you entries that look something like below and the ones we want are marked with pgdg93.bacula-director-postgresql.x86_64 5.0.0-12.el6 base bacula-storage-postgresql.x86_64 5.0.0-12.el6 base freeradius-postgresql.x86_64 2.1.12-4.el6_3 base postgresql.i686 8.4.20-1.el6_5 updates postgresql.x86_64 8.4.20-1.el6_5 updates postgresql-contrib.x86_64 8.4.20-1.el6_5 updates postgresql-devel.i686 8.4.20-1.el6_5 updates postgresql-devel.x86_64 8.4.20-1.el6_5 updates postgresql-docs.x86_64 8.4.20-1.el6_5 updates postgresql-jdbc.noarch 8.4.701-8.el6 base postgresql-libs.i686 8.4.20-1.el6_5 updates postgresql-libs.x86_64 8.4.20-1.el6_5 updates postgresql-odbc.x86_64 08.04.0200-1.el6 base postgresql-plperl.x86_64 8.4.20-1.el6_5 updates postgresql-plpython.x86_64 8.4.20-1.el6_5 updates postgresql-pltcl.x86_64 8.4.20-1.el6_5 updates postgresql-server.x86_64 8.4.20-1.el6_5 updates postgresql-test.x86_64 8.4.20-1.el6_5 updates postgresql93.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-contrib.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-debuginfo.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-devel.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-docs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-jdbc.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-jdbc-debuginfo.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-libs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-odbc.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-odbc-debuginfo.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-plperl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-plpython.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-pltcl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-server.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-test.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql_autodoc.noarch 1.41-1.rhel6 pgdg93 qt-postgresql.i686 1:4.6.2-28.el6_5 updates qt-postgresql.x86_64 1:4.6.2-28.el6_5 updates
- To see all packages from pgdg93 do:
yum list | grep pgdg93. This lists the core PostgreSQL offering and packaged PostgreSQL extensions. I've snipped the list a bit to show just extensions we often install:pgrouting_93.x86_64 2.0.0-1.rhel6 pgdg93 pgrouting_93-debuginfo.x86_64 2.0.0-1.rhel6 pgdg93 pgxnclient.x86_64 1.2.1-1.rhel6 pgdg93 : : plv8_93.x86_64 1.4.1-1.rhel6 pgdg93 plv8_93-debuginfo.x86_64 1.4.1-1.rhel6 pgdg93 postgis2_93.x86_64 2.1.1-1.rhel6 pgdg93 postgis2_93-client.x86_64 2.1.1-1.rhel6 pgdg93 postgis2_93-debuginfo.x86_64 2.1.1-1.rhel6 pgdg93 postgis2_93-devel.x86_64 2.1.1-1.rhel6 pgdg93 postgis2_93-docs.x86_64 2.1.1-1.rhel6 pgdg93 postgis2_93-utils.x86_64 2.1.1-1.rhel6 pgdg93 postgresql93.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-contrib.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-debuginfo.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-devel.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-docs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-jdbc.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-jdbc-debuginfo.x86_64 9.3.1100-1PGDG.rhel6 pgdg93 postgresql93-libs.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-odbc.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-odbc-debuginfo.x86_64 09.02.0100-1PGDG.rhel6 pgdg93 postgresql93-plperl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-plpython.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-pltcl.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-server.x86_64 9.3.4-1PGDG.rhel6 pgdg93 postgresql93-test.x86_64 9.3.4-1PGDG.rhel6 pgdg93
-
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 postgresql93 postgresql93-server postgresql93-libs postgresql93-contrib postgresql93-devel - You should get a prompt something like:
Type: y
Dependencies Resolved ============================================================================================================================================================================================================================================================================================ Package Arch Version Repository Size ============================================================================================================================================================================================================================================================================================ Installing: postgresql93 x86_64 9.3.4-1PGDG.rhel6 pgdg93 1.0 M postgresql93-contrib x86_64 9.3.4-1PGDG.rhel6 pgdg93 483 k postgresql93-devel x86_64 9.3.4-1PGDG.rhel6 pgdg93 1.5 M postgresql93-libs x86_64 9.3.4-1PGDG.rhel6 pgdg93 190 k postgresql93-server x86_64 9.3.4-1PGDG.rhel6 pgdg93 4.1 M Installing for dependencies: libxslt x86_64 1.1.26-2.el6_3.1 base 452 k uuid x86_64 1.6.1-10.el6 base 54 k Transaction Summary ============================================================================================================================================================================================================================================================================================ Install 7 Package(s) Total download size: 7.7 M Installed size: 32 M Is this ok [y/N]:
It should then download the packages and install them with a Complete! exclamation when done.
Installing PostGIS binaries
PostGIS unfortunately has a lot of dependencies because of GDAL and JSON dependency introduced in 2.0 and while most of the time it's as easy as adding:
sudo yum install postgis2_93
It often is not. As with this virgin box we tried installing on we got these errors:
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libcfitsio.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libspatialite.so.2()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libnetcdf.so.6()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libfreexl.so.1()(64bit)
Error: Package: postgis2_93-client-2.1.1-1.rhel6.x86_64 (pgdg93)
Requires: libjson.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libarmadillo.so.3()(64bit)
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
Requires: hdf5
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
Requires: json-c
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libhdf5.so.6()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libdap.so.11()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libCharLS.so.1()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libdapserver.so.7()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libdapclient.so.3()(64bit)
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
Requires: libjson.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
Requires: libgta.so.0()(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.
For CentOS 6, RHEL 6 64-bit use:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmFor CentOS 6, RHEL 6 32-bit use:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpmFor CentOS 5, RHEL 5 64-bit use:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpmFor CentOS 5, RHEL 5 32-bit use:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpmSince we are on CentOS 6 64-bit we went with the first set.
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmYou should get a screen that looks something like this if successful
Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm warning: /var/tmp/rpm-tmp.C2K8LE: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY Preparing... ########################################### [100%] 1:epel-release ########################################### [100%]
Now we repeat the above command again:
sudo yum install postgis2_93After the above, you should see a screen that looks like this:
Dependencies Resolved ============================================================================================================================================================================================================================================================================================ Package Arch Version Repository Size ============================================================================================================================================================================================================================================================================================ Installing: gdal-libs x86_64 1.9.2-4.el6 pgdg93 3.8 M postgis2_93 x86_64 2.1.1-1.rhel6 pgdg93 5.0 M Installing for dependencies: CharLS x86_64 1.0-1.el6 epel 73 k armadillo x86_64 3.800.2-1.el6 epel 16 k atlas x86_64 3.8.4-2.el6 base 2.8 M blas x86_64 3.2.1-4.el6 base 321 k cfitsio x86_64 3.240-3.el6 epel 498 k freexl x86_64 1.0.0d-1.el6 epel 31 k geos x86_64 3.4.2-1.rhel6 pgdg93 529 k giflib x86_64 4.1.6-3.1.el6 base 37 k hdf5 x86_64 1.8.5.patch1-7.el6 epel 1.4 M json-c x86_64 0.10-2.el6 epel 24 k lcms-libs x86_64 1.19-1.el6 base 100 k libdap x86_64 3.11.0-1.el6 epel 445 k libgeotiff x86_64 1.4.0-1.el6 pgdg93 749 k libgta x86_64 1.0.2-2.el6 epel 31 k libspatialite x86_64 2.4.0-0.6.RC4.el6 epel 611 k libtool-ltdl x86_64 2.2.6-15.5.el6 base 44 k netcdf x86_64 4.1.1-3.el6.5 epel 4.8 M openjpeg-libs x86_64 1.3-10.el6_5 updates 60 k poppler x86_64 0.12.4-3.el6_0.1 base 557 k poppler-data noarch 0.4.0-1.el6 base 2.2 M postgis2_93-client x86_64 2.1.1-1.rhel6 pgdg93 122 k proj x86_64 4.8.0-2.rhel6 pgdg93 172 k unixODBC x86_64 2.2.14-12.el6_3 base 378 k xerces-c x86_64 3.0.1-20.el6 base 866 k Transaction Summary ============================================================================================================================================================================================================================================================================================ Install 26 Package(s) Total download size: 25 M Installed size: 113 M
Is this ok [y/N]:
Click y
After you are done you should have a screen like this:
Installed: gdal-libs.x86_64 0:1.9.2-4.el6 postgis2_93.x86_64 0:2.1.1-1.rhel6 Dependency Installed:
CharLS.x86_64 0:1.0-1.el6 armadillo.x86_64 0:3.800.2-1.el6 ..
Note that after all this, you still need to enable PostGIS in each database you will want to use it in. We'll cover that later in this article after we are done setting up the PostgreSQL service.
Installing pgRouting binaries
pgRouting is also now available via Yum. Devrim was kind enough to add CGAL to the repo to simplify the install a bit. So If you want to use pgRouting with PostGIS, it should be a simple:
yum install pgrouting_93
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.3 and devl 9.4, 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.3 with your preferred changes:
Note that # are remarks to you so don't type them in
su root vi /etc/sysconfig/pgsql/postgresql-9.3
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=5438 PGDATA=/pgdata93
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. Devrim has the details of these changes in What-is-new-in-PostgreSQL-9.0-RPMs.html.
I'll reiterate the key elements and consequences of these changes you need to watch out for.
Key Steps to setup
If you want the service to start up on reboot, then simply do this:
su root service postgresql-9.3 initdb service postgresql-9.3 start chkconfig --list #(to see list of services) chkconfig postgresql-9.3 on #(to have start on bootup)
/usr/pgsql-9.3/bin/postgresql93-setup initdb
#to enable service on start-up
systemctl enable postgresql-9.3The data cluster is created by default in /var/lib/pgsql/9.3/data and the extensions folder is in /usr/pgsql-9.3/share/extension/ and the binaries
are in /usr/pgsql-9.3/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.
To connect to your postgres server do this:
su postgres /usr/pgsql-9.3/bin/psql -p 5432
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 /usr/pgsql-9.3/bin/psql -p 5432 -c "CREATE EXTENSION adminpack;"
On startup do a:
service postgresql-9.3 status You should see a pid runnig ... message if all is good. If it says stopped, then check your /var/lib/pgsql/9.3/data/pg_log last log file which usually lists what went wrong. Often times it's usually caused by a typo you introduced in pg_hba.conf or postgresql.conf .
su postgres psql -p 5432 #or whatever port you chose CREATE role regina LOGIN PASSWORD 'whateveryouwant' SUPERUSER;
At the psql prompt: type the query below to verify the location of your cluster:
SELECT setting FROM pg_settings WHERE name = 'config_file';which should return an answer of where your configuration file is:
setting ----------------------------------------- /var/lib/pgsql/9.3/data/postgresql.confor The location of where you setup your cluster.
then do a \q to exit the psql console
After that:
cd /var/lib/pgsql/9.3/data (or the location of your data cluster) vi postgresql.conf
#make changes to memory, enable etc.
change
#listen_addresses='localhost'tolisten_addresses='*'if you want postgresql to run on all ips. You can set to a specific if you have multiple ips too.if you need to change ports and data cluster -- don't do it in postgresql.conf -- remember in /etc/sysconfig/pgsql/postgresql-9.3
- Setup access permissions
vi pg_hba.conf #make changes to access .e.g add a line below if you want to be able to access postgresql from external. host all all 0.0.0.0/0 md5
- When done with all of that do:
service postgresql-9.3 restart
Whenever you change the pg_hba.conf file, you need to do a restart or a:
service postgresql-9.3 reload
for the changes to take effect.
Creating a PostGIS/pgRouting enabled database
If you want to create a new database that is PostGIS enabled, do this:
su postgres /usr/pgsql-9.3/bin/psql -p 5432
In psql command prompt type following:
CREATE ROLE gisdb_user LOGIN PASSWORD 'whatever'; CREATE DATABASE gisdb WITH OWNER gisdb_user; \connect gisdb; CREATE EXTENSION postgis; SELECT postgis_full_version(); --if you want a side of pgRouting with PostGIS -- add this -- CREATE EXTENSION pgrouting; SELECT * FROM pgr_version(); \q \exit
The output of the postgis_full_version() command should be something like:
POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RASTERif postgis is properly installed.
The output of SELECT * FROM pgr_version();; should be something like:
version | tag | build | hash | branch | boost ---------+-----------------+-------+---------+--------+-------- 2.0.0 | pgrouting-2.0.0 | 0 | f26831f | master | 1.41.0
Turning off firewall or enabling PostgreSQL access
If after all those changes, you still can't access your PostgreSQL server from pgAdmin external, verify if you have firewall running. The Image I grabbed had iptables on.
su root service iptables status
Just for perspective, our rule table looked like this before. As you can see no rule to enable postgres access.
Table: filter Chain INPUT (policy ACCEPT) num target prot opt source destination 1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED 2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80 6 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443 7 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited Chain FORWARD (policy ACCEPT) num target prot opt source destination 1 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited Chain OUTPUT (policy ACCEPT) num target prot opt source destination
service iptables stop
Verify you can connect after turning off. Okay now turn back on. The below example will enable tcp ports 5432-5438 (which we will use for postgres services) and insert the rule in the chain INPUT at row 7. Note my 10th rule is to reject all, so want all my access rules to be before that.
service iptables start iptables -I INPUT 7 -p tcp --dport 5432:5438 -j ACCEPT service iptables save service iptables restart
After we were done, it looked like this:
Chain INPUT (policy ACCEPT) num target prot opt source destination 1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED 2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80 6 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443 7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpts:5432:5438 8 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited Chain FORWARD (policy ACCEPT) num target prot opt source destination 1 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited Chain OUTPUT (policy ACCEPT) num target prot opt source destination
PL Programming
Writing PostGIS raster Map Algebra Callback Functions in PLV8 Advanced
I've read from many that PL/V8 mathematic operations are generally faster than what you get with SQL functions and PL/pgsql functions. One area where I thought
this speed would be really useful was for writing Map Algebra call-back functions. A PostGIS 2.1+ map algebra callback function signature looks like:
func_name(double precision[][][] value, integer[][] pos, text[] VARIADIC userargs)
So for starters, I set out to rewrite my favorite call-back function ST_Range4ma as a PL/V8. I left out all the logic for handling pos and userargs, because frankly I never use those. In doing so, I discovered a couple of things. The handling of pos and userargs really weighs down the built-in implementation. PL/V8 is indeed faster even if I were to write out all the for loop logic that the built-in PostGIS plpgsql version has. For demonstration here are 2 implementations of range function.
One in PL/V8
CREATE FUNCTION plv8_range4ma(value float8[][][],
pos integer[][][], VARIADIC userargs text[] DEFAULT NULL::text[])
RETURNS double precision AS
$$
return ( Math.max.apply(null, value) - Math.min.apply(null, value) );
$$
LANGUAGE plv8 IMMUTABLE;
One in SQL
CREATE FUNCTION sql_range4ma(value float8[][][],
pos integer[][][], VARIADIC userargs text[] DEFAULT NULL::text[])
RETURNS double precision AS
$$
SELECT MAX(v) - MIN(v) FROM unnest($1) As v;
$$
LANGUAGE sql IMMUTABLE;
Now to compare the speeds on the first band of an 800x678 pixel image.
-- plv8 8,241 ms
SELECT ST_AsPNG(ST_MapAlgebra (
rast
, 1,
'plv8_range4ma(double precision[][][], integer[][], text[])'::regprocedure,
'8BUI', 'FIRST', NULL, 4, 4
) ) As plv8_rele
FROM pics;
-- sql 17,701 ms
SELECT
ST_AsPNG(ST_MapAlgebra (
rast
, 1,
'sql_range4ma(double precision[][][], integer[][], text[])'::regprocedure,
'8BUI', 'FIRST', NULL, 4, 4
) )
FROM pics;
-- built-in st_range4ma 51,922 ms
SELECT
ST_AsPNG(ST_MapAlgebra (
rast
, 1,
'st_range4ma(double precision[][][], integer[][], text[])'::regprocedure,
'8BUI', 'FIRST', NULL, 4, 4
) )
FROM pics;
Before | After |
There is however one big gotcha with the PL/V8 handling of arrays. It seems if you give it an n-dimensional array (a matrix) as is the case with MapAlgebra callbacks that get passed a neighborhood of pixel values, it collapses it down to a 1-dimensional array though all the original elements still appear to be in the collapsed array. This bummed me a bit when I ran into it, but then I realized for many use-cases such as Max, Min, Range the collapsing effect simplifies the work and has no effect on the result. For cases where you do need to keep track of the dimensions, I suspect you can create an SQL wrapper that includes the dimensions of the pixel value array and then in the plv8 logic need to break the array up based on the dimension. That will be my next venture.
Application Development
Using HStore for Archiving Intermediate
I'm not a big proponent of schemaless designs, but they have their place. One particular place where I think they are useful is for archiving of data where even though the underlying table structure of the data you need to archive is changing, you want the archived record to have the same fields as it did back then. This is a case where I think Hstore and the way PostgreSQL has it implemented works pretty nicely.
Side note: one of the new features of PostgreSQL 9.4 is improved GIN indexes (faster and smaller) which is very often used with hstore data (and the new jsonb type). We're really looking forward to the GIN improvements more so than the jsonb feature. We're hoping to test out this improved index functionality with OpenStreetMap data soon and compare with our existing PostgreSQL 9.3. OpenStreetMap pbf and osm extract loaders (osm2pgsql, imposm) provide option for loading tagged data into PostgreSQL hstore fields, in addition to PostGIS geometry and other attribute fields. So 9.4 enhancements should be a nice gift for OSM data users. More on that later.
Test table
Lets suppose we had a contacts table that we wanted to record whenever we delete or update:
CREATE TABLE contacts(id serial primary key, first_name varchar(40)
, last_name varchar(40), email varchar(75));
INSERT INTO contacts(first_name, last_name, email)
VALUES ('Joey', 'Bubba', 'joey@xyz.com')
, ('Forrest', 'Gump', 'fgump@bg1234.com');
Archive Bag
We create a catch-all archive table that can archive most anything.
-- archiving table --
CREATE EXTENSION hstore;
CREATE TABLE archive(table_name text, row_id text
, archive_type char(1), archive_dt timestamptz DEFAULT CURRENT_TIMESTAMP, data hstore);
For archiving especially if you are going to do crazy things like dump PostGIS geometries/geographies in your hstore (a storelocken), you should use GIST instead of GIN since GIN is lossless, will fail for large sizes. For this example we'll at most just have tiny points in this exercise.
CREATE INDEX idx_archive_data ON archive USING gin (data);
Archiving data
For Archiving we could use triggers or just build into our delete/update logic using a writeable CTE like
-- update data --
WITH cte AS (UPDATE contacts SET email = 'fgump2@bg12345.com' WHERE first_name = 'Forrest' RETURNING *)
INSERT INTO archive(table_name, row_id, archive_type, data)
SELECT 'contacts', cte.id::text, 'U', hstore(cte) As data
FROM cte;
Later on we might think its nice to store the locations of our contacts with a geography point field
-- change structure
CREATE EXTENSION postgis;
ALTER TABLE contacts ADD COLUMN geog geography(Point,4326);
CREATE INDEX idx_contacts_geog ON contacts USING gist(geog);
Then we update a record
-- update again --
WITH cte AS (UPDATE contacts SET geog = ST_Point(-71.060, 42.358)::geography
WHERE first_name = 'Forrest' RETURNING *)
INSERT INTO archive(table_name, row_id, archive_type, data)
SELECT 'contacts', cte.id::text, 'U', hstore(cte) As data
FROM cte;
Then delete a record
WITH cte AS (DELETE FROM contacts WHERE first_name = 'Joey' RETURNING *)
INSERT INTO archive(table_name, row_id, archive_type, data)
SELECT 'contacts', cte.id::text, 'D', hstore(cte) As data
FROM cte;
Inspecting our Archive
-- query data -
SELECT table_name, row_id, data ? 'geog' As has_geog
, data->'first_name' As first_name, archive_dt
FROM archive;
table_name | row_id | has_geog | first_name | archive_dt -----------+--------+----------+------------+------------------------------- contacts | 2 | f | Forrest | 2014-04-24 23:26:45.407163-04 contacts | 2 | t | Forrest | 2014-04-24 23:31:57.034065-04 contacts | 1 | t | Joey | 2014-04-24 23:32:13.314088-04
Note the output tells us that the first time we updated Forrest, we did not have a geography column and second time we updated Forrest and when we deleted Joey, we had a geography column. Of course you can do more interesting queries like each() etc to get values of every single field as needed.