Archive

Archive for the ‘PostgreSQL’ Category

PostgreSQL 9.2.3 system queries

January 15, 2014 Leave a comment

1. check child tables which inherit a base partitioned table

	SELECT 
	pg_inherits.*
	, c.relname AS child
	, p.relname AS parent
	FROM
	pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid)
	JOIN pg_class as p ON (inhparent=p.oid) 
	where true 
	and p.relname = 'bsms_in'
	--    and c.relname = %(inherited_table)s
	;
 inhrelid | inhparent | inhseqno |      child       | parent  
----------+-----------+----------+------------------+---------
 51790061 |   8753326 |        1 | bsms_in_p2014w02 | bsms_in
 52461300 |   8753326 |        1 | bsms_in_p2014w03 | bsms_in
 53187892 |   8753326 |        1 | bsms_in_p2014w04 | bsms_in
(3 rows)

2. How to drop old PostgreSQL table which sequence is used by some tables that inherited its structure. Example of the issue:

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:08:56 EEST 2014]# drop table bsms_in_old_big;
ERROR:  cannot drop table bsms_in_old_big column id because other objects depend on it
DETAIL:  default for table bsms_in_p2014w01 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w02 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w03 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w04 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w05 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w06 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w07 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w08 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w09 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w10 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w11 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w12 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w13 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w14 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w15 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w16 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w17 column id depends on sequence bsms_in_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 9.258 ms

Get The list of the sequences and the corresponding tables that are their “OWNERS”

SELECT s.relname as sequence_name,  
       n.nspname as sequence_schema,  
       t.relname as related_table, 
       a.attname as related_column 
  FROM pg_class s, pg_depend d, pg_class t, pg_attribute a, pg_namespace n 
  WHERE s.relkind     = 'S' 
    AND n.oid         = s.relnamespace 
    AND d.objid       = s.oid 
    AND d.refobjid    = t.oid 
    AND (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) order by sequence_name;

Free the sequences : more info about “owned by” here : http://www.postgresql.org/docs/9.2/static/sql-altersequence.html Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:00 EEST 2014]# ALTER SEQUENCE bsms_in_id_seq OWNED BY NONE;
ALTER SEQUENCE
Time: 1.085 ms
dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:13 EEST 2014]# drop table bsms_in_old_big;
DROP TABLE
Time: 18.084 ms

Slackre4Life

Categories: PostgreSQL Tags:

Public DNS resolve for VM instances hosted at OpenStack nova compute grizzly edition

July 25, 2013 1 comment

Issue – the OpenStack instances hostnames are not resolved at the moment by their floating IPs.

Fast solution – use PowerDNS
Short story:
1. Boot an instance , assign it a floating IP, open tcp ports 22 8001 and udp port 53.
2. Install PowerDNS, the particular yum package is named pdns.
3. install the mysql backend for PowerDNS, its available as yum package as well.
4. Install MariaDB , set the proper configuration, the initialize it at the default location.
5. Set the pdns user, create the pdns database and create the tables needed.
6. Configure the pdns service to use the gmysql backend.
7. Create and user at the OpenStack MySQL able to connect from the floating IP subnet.
8. Grant that user select permissions on all nova.* tables.
9. At the PowerDNS instance, set simple python / php / bash script to query the nova database and to inject the hostnames and the floating IPs as DNS records for the particular OpenStack domains at the pdns db at the pdns OpenStack instance.
In fact, what I do is to clean up all the available OpenStack DNS records, and to inject them again in one transaction, in a view to clean up the deleted and changed hostnames / floating IPs.
10. Set up the external DNS server to forward all queries related to the OpenStack domains to the PowerDNS floating UP.

In general, its good to start a python daemon, in a view to keep the recent status of the OpenStack DNS as a simple hash,
and only when there are changes in OpenStack instances / assigned floating IPs, to push the updates to the RowerDNS db backend.
I hate when the database is used in a lazy way.

All that can be done by hacking the nova python code, as PowerDNS has restful API as well,
but I know there are incoming new OpenStack modules which will do that.

The long story with examples and links TBD.

Update : the source code is here : https://github.com/seikath/openstack-external-dns

One more update to be pushed to the git source:
In a view to push the VM hostname changes without recreating the VM, I decided to use short_description field instead of the hostname,
as the nova rename instance command changes only that field, and the hostname remains intact.

The production code is changed and tested, just not pushed to the public repo.

Compile svn kannel sqlbox and opensmmp with PostgreSQL and gsoap support at CentOS 6.2 x86_64 GNU/Linux

November 29, 2012 2 comments

1, install PostgreSQL 9.2 from the official postgresql.org repository

yum -y install wget bash-completion
wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm
yum localinstall --nogpgcheck pgdg-centos92-9.2-6.noarch.rpm
yum install postgresql92-server postgresql92-plperl postgresql92-pltcl postgresql92-devel postgresql92-docs postgresql92-libs postgresql92-plpython postgresql92-contrib -y 
yum install skytools-92 skytools-92-modules pgtune pgbouncer pgagent_92 pg_top92 bucardo

2. install kannel with PostgreSQL and soap interface

yum install automake  pcre* mlocate -y
yum groupinstall 'Development Tools'
yum install libxml2-devel -y
yum install openssl-devel -y 
mkdir /opt/installs/kannel.svn && cd /opt/installs/kannel.svn
svn co https://svn.kannel.org/gateway/trunk
cd /opt/installs/
svn co https://gsoap2.svn.sourceforge.net/svnroot/gsoap2 gsoap2
cd gsoap2
./configure

The build will fail with the following error :

make -j3
cd . && /bin/sh /opt/installs/gsoap2/missing --run autoconf
 cd . && /bin/sh /opt/installs/gsoap2/missing --run automake-1.10 --foreign 
/opt/installs/gsoap2/missing: line 46: automake-1.10: command not found
WARNING: `automake-1.10' is needed, and you do not seem to have it handy on your
         system.  You might have modified some files without having the
         proper tools for further handling them.  Check the `README' file,
         it often tells you about the needed prerequirements for installing
         this package.  You may also peek at any GNU archive site, in case
         some other package would contain this missing `automake-1.10' program.
make: *** [Makefile.in] Error 1
make: *** Waiting for unfinished jobs....
aclocal.m4:14: error: this file was generated for autoconf 2.61.
You have another version of autoconf.  If you want to use that,
you should regenerate the build system entirely.
aclocal.m4:14: the top level
autom4te: /usr/bin/m4 failed with exit status: 63
WARNING: `autoconf' is missing on your system.  You should only need it if
         you modified `configure.in'.  You might want to install the
         `Autoconf' and `GNU m4' packages.  Grab them from any GNU
         archive site.

After some digging into the source and googling in deep, the fix is simple, but very efficient …:

cd /opt/installs/gsoap2/
touch config.h.in
touch Makefile.in
touch configure
# NOW you can build it 🙂 
make -j3
.....
**  The gSOAP code generator for C and C++, soapcpp2 release 2.8.11
**  Copyright (C) 2000-2012, Robert van Engelen, Genivia Inc.
**  All Rights Reserved. This product is provided "as is", without any warranty.
**  The soapcpp2 tool is released under one of the following two licenses:
**  GPL or the commercial license by Genivia Inc.

Saving wsdlStub.h annotated copy of the input declarations
Saving xmime.nsmap namespace mapping table
Saving wsdlH.h interface declarations
Saving wsdlC.cpp XML serializers

Compilation successful
....
# install it : 
make -j3  install 
....
+--------------------------------------------------------+
| You now have successfully built and installed gsoap.   |
|                                                        |
| You can link your programs with -lgsoap++ for          |
| C++ projects created with soapcpp2 and you can link    |
| with -lgsoap for C projects generated with soapcpp2 -c |
|                                                        |
| There are also corresponding libraries for SSL and     |
| zlib compression support (-lgsoapssl and lgsoapssl++)  |
| which require linking -lssl -lcrypto -lz               |
|                                                        |
| Thanks for using gsoap.                                |
|                                                        |
|               http://sourceforge.net/projects/gsoap2   |
+--------------------------------------------------------+

Now go for the kannel install

cd /opt/installs/kannel.svn/trunk/
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
# it will fail due to missing libpq-fe.h
# fix it by creating the proper link 
ln -s /usr/pgsql-9.2/ /usr/pgsql
# now try again
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
# it will fails AGAIN 🙂 with the following error : 
Configuring for gSOAP support ...
checking whether to compile with SOAP support... not found
configure: error: Unable to find gSOAP import at /usr/share/gsoap/import or plugin at /usr/share/gsoap/plugin
root@aegir.voicecom.bg:[Thu Nov 29 23:11:29]:[/opt/installs/kannel.svn/trunk]$ /usr/local/bin/soapcpp2 -d `dirname soap/service/parlayx/soapH.h` -I /usr/share/gsoap/import -I soap/service/parlayx -cLxw soap/service/parlayx/parlayx_sms_send_service_2_1_wrapper.h

**  The gSOAP code generator for C and C++, soapcpp2 release 2.8.11
**  Copyright (C) 2000-2012, Robert van Engelen, Genivia Inc.
**  All Rights Reserved. This product is provided "as is", without any warranty.
**  The soapcpp2 tool is released under one of the following two licenses:
**  GPL or the commercial license by Genivia Inc.

Critical error: #import: Cannot open file "wsse.h" for reading.
Hint: use option -I<path> (for example -Igsoap/import:gsoap/custom:.)

# Fix that with creating the proper link :
ln -s /usr/local/share/gsoap /usr/share/

# now try again
./configure  --with-pgsql --enable-ssl --with-gsoap --enable-start-stop-daemon --enable-pcre
......
License information ...
+--------------------------------------------------------------------+
| License:                                                           |
| This software is subject to the Kannel Software License, available |
| in this distribution in the file LICENSE. By continuing this       |
| installation process, you are bound by the terms of this license   |
| agreement. If you do not agree with the terms of this license, you |
| must abort the installation process at this point.                 |
|                                                                    |
|                      The Kannel Group <http://www.kannel.org/>     |
+--------------------------------------------------------------------+

Thank you for using Kannel.

# now compile and install 
make -j3 install 
.....
Using project directory path: soap/service/parlayx/
Saving soap/service/parlayx/soapStub.h annotated copy of the input declarations
Using px1 service name: SendSmsBinding
Using px1 service style: document
Using px1 service encoding: literal
Using px1 service location: http://localhost:9080/ParlayXSms/services/SendSms
Using px1 schema namespace: http://www.csapi.org/wsdl/parlayx/sms/send/v2_1/service
Saving soap/service/parlayx/SendSmsBinding.nsmap namespace mapping table
Using px2 service name: SmsNotificationBinding
Using px2 service style: document
Using px2 service encoding: literal
Using px2 service location: http://localhost:9080/SmsNotificationService/services/SmsNotification
Using px2 schema namespace: http://www.csapi.org/wsdl/parlayx/sms/notification/v2_1/service
Saving soap/service/parlayx/SmsNotificationBinding.nsmap namespace mapping table
Using px3 service name: ReceiveSmsBinding
Using px3 service style: document
Using px3 service encoding: literal
Using px3 service location: http://localhost:9080/ReceiveSmsService/services/ReceiveSms
Using px3 schema namespace: 
Saving soap/service/parlayx/ReceiveSmsBinding.nsmap namespace mapping table
Saving soap/service/parlayx/soapClient.c client calling stubs
Saving soap/service/parlayx/soapServer.c server request dispatcher
Saving soap/service/parlayx/soapH.h interface declarations
Saving soap/service/parlayx/soapC.c XML serializers

Compilation successful
.....

Complile and install opensmppbox :

cd /opt/installs/kannel.svn/trunk/addons/opensmppbox
./bootstrap
./configure
make -j3 install 

Complile and install sqlbox :

cd /opt/installs/kannel.svn/trunk/addons/sqlbox
./bootstrap
./configure
make -j3 install 

Check the install :

cd /usr/local/sbin
root@darkstart:[Thu Nov 29 23:24:31]:[/usr/local/sbin]$ ls
bearerbox  opensmppbox  run_kannel_box  smsbox  sqlbox  start-stop-daemon  wapbox

aaand that is it ,,, Slackware4Life .. 😀

Drupal install of commerce_kickstart-7.x-1.8-core at AWS on two PostgreSQL 9.1.4 replicated nodes with Nginx and php-fpm

July 22, 2012 Leave a comment

I decided to try Drupal on two PostgreSQL nodes replicated as master and readonly slave.
The usual choice for webserver is nginx

Follow the http://drupal.org/documentation/install and
http://drupal.org/project/commerce_kickstart
http://wiki.nginx.org/Drupal

# get the source and install it at the proper location like /usr/share/nginx/drupal
wget  http://ftp.drupal.org/files/projects/disable_messages-7.x-1.0.tar.gz

#set the proper nginx config 
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:36:27]:[/var/lib/pgsql]$ cat /etc/nginx/conf.d/drupal.conf
server {
        server_name darkstar.gotdns.org;
#        root /var/lib/pgsql9/xfs.data/drupal; 
        root /usr/share/nginx/drupal; ## <-- Your only path reference.
 
        location = /favicon.ico {
                log_not_found off;
                access_log off;
        }
 
        location = /robots.txt {
                allow all;
                log_not_found off;
                access_log off;
        }
 
        # This matters if you use drush
        location = /backup {
                deny all;
        }
 
        # Very rarely should these ever be accessed outside of your lan
        location ~* \.(txt|log)$ {
                allow 10.210.213.55/32;
                deny all;
        }
 
        location ~ \..*/.*\.php$ {
                return 403;
        }
 
        location / {
                # This is cool because no php is touched for static content
                try_files $uri @rewrite;
        }
 
        location @rewrite {
                # Some modules enforce no slash (/) at the end of the URL
                # Else this rewrite block wouldn't be needed (GlobalRedirect)
                rewrite ^/(.*)$ /index.php?q=$1;
        }
 
        location ~ \.php$ {
	        fastcgi_pass   127.0.0.1:9000;
                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                #NOTE: You should have "cgi.fix_pathinfo = 0;" in php.ini
                include fastcgi_params;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
                fastcgi_intercept_errors on;
		#fastcgi_pass   unix:/var/run/php-fpm.sock;
        	fastcgi_index  index.php;
		fastcgi_param  SCRIPT_NAME        $fastcgi_script_name;

        }
 
        # Fighting with ImageCache? This little gem is amazing.
        location ~ ^/sites/.*/files/imagecache/ {
                try_files $uri @rewrite;
        }
        # Catch image styles for D7 too.
        location ~ ^/sites/.*/files/styles/ {
                try_files $uri @rewrite;
        }
 
        location ~* \.(js|css|png|jpg|jpeg|gif|ico)$ {
                expires max;
                log_not_found off;
        }
}

#check the nginx config :
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:42:02]:[/var/lib/pgsql]$ nginx -t -c /etc/nginx/nginx.conf
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

# and reload it
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:44:05]:[/var/lib/pgsql]$ service  nginx reload
Reloading nginx:  

# create pgsql user and db and set the pemirssions
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 11:55:33]:[~]$ createuser -U postgres -h 127.0.0.1 --pwprompt --encrypted --no-createrole --no-createdb drupal 
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 12:06:25]:[~]$ createdb -U postgres -h 127.0.0.1 --encoding=UTF8 --owner=drupal drupal

# set the psql user permissions at the slave with same password you set to the drupal user at the master:
root@ip-10-28-82-97-node-2:[Sun Jul 22 16:01:59]:[/var/lib/pgsql/data]$ cat  /var/lib/pgsql/data/pg_hba.conf | grep -i drupal
hostnossl       drupal          drupal             masterIP/32	 password

# ativate the user permissions 
root@ip-10-28-82-97-node-2:[Sun Jul 22 14:23:31]:[/var/lib/pgsql/data]$ service postgresql reload

#check the connection to slave:
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 16:03:33]:[/usr/share/nginx]$ echo "select version();" | psql -h slaveIP -U drupal drupal
Password for user drupal: 
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
(1 row)


#start the install process hitting the index.php
#in case you get <strong>Error not found</strong>  change the permissions of the drupal root directory to 0755:   
chmod -R 0755  /usr/share/nginx/drupal

# install some of the php modules requested by the install as gd, dom and mbstring:
yum install php-soap php-mbstring php-xml  -y
#then restart the php-fpm 
root@domU-12-31-39-09-D2-CD-node-1:[Sun Jul 22 15:09:19]:[/var/lib/pgsql]$ service  php-fpm restart
Stopping php-fpm:                                          [  OK  ]
Starting php-fpm:                                          [  OK  ]

if you get an Warning message like this one:
Compilation failed: unknown option bit(s) set at offset 0 in user_validate_name() (line 645 of /usr/share/nginx/drupal/modules/user/user.module).

this is due to php PECL pcre extension update
there will be some more similar errors indicated at
+1606 /usr/share/nginx/drupal/includes/bootstrap.inc
+3726 /usr/share/nginx/drupal/includes/common.inc
+645 /usr/share/nginx/drupal/modules/user/user.module

the fastest way to fix it at AWS is to do yum update of pcre.x86_64

in general with AWS is better to do just yum update

then, everything works as expected,

do not forget to set the permissions of ./sites/default/settings.php to 0755 after the install

how to set the drupal to use the slave :
thank to padraig o’sullivan who posted
The settings.php file for the Drupal site needs to be updated to know about this slave database.

end of the story, now we have possible web cluster as the second node could be used for second web server, just some db connection settings have to be adjusted. and NFS with CacheFS for the web writable part of the drupal.

The drupal admin is owned by my daughter now … she is about to create the online store.

Compile and install PostgreSQL 9.2 beta 1 at AWS micro instance (EC2)

May 21, 2012 Leave a comment

first things first – take some time and read http://www.postgresql.org/about/news/1395/

Compilation:
get the sources at the install directory and compile it :


cd /opt/installs/
wget http://ftp.postgresql.org/pub/source/v9.2.0beta1/postgresql-9.2beta1.tar.bz2
tar xvf postgresql-9.2beta1.tar.bz2
# prepare the configure script:
vi postgresql-9.2beta1.seikath.conf
cat  /opt/installs/postgresql-9.2beta1.seikath.conf
#!/bin/sh
here=`pwd`

cd /opt/installs/postgresql-9.2beta1
./configure  \
--with-ossp-uuid \
--with-libxml \
--with-openssl \
--with-pam \
--with-krb5 \
--with-python \
--with-perl  \
--with-tcl  \
--with-pgport=6543 \

if you get this error

checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
# you are missing some perl libraries
# install them:
yum install perl-YAML* -y
yum install perl-ExtUtils* -y

if you get this error

checking for library containing readline... no
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
# you are missing readline libraries
# install them:
yum install readline* -y

if you get this error

checking for pam_start in -lpam... no
configure: error: library 'pam' is required for PAM
# you are missing pam libraries
# install them:
yum install pam-devel -y

if you get this error

checking for uuid_export in -lossp-uuid... no
checking for uuid_export in -luuid... no
configure: error: library 'ossp-uuid' or 'uuid' is required for OSSP-UUID
# you are missing uuid libraries
# install them:
yum install uuid-devel -y

if you get this error

checking for tclConfig.sh... no
configure: error: file 'tclConfig.sh' is required for Tcl
# you are missing tclConfig.sh
# install it:
yum install tcl-devel -y

if you get this error

configure: error: header file <Python.h> is required for Python
# you are missing Python libraries
# install them:
yum install python-devel -y

use gmake to compile as recommended

echo "gmake -j3 | tee /tmp/postgresql-9.2beta1.install.log" | at now 
tail -F /tmp/postgresql-9.2beta1.install.log
...

All of PostgreSQL successfully made. Ready to install.
echo "gmake -j3 install | tee -a /tmp/postgresql-9.2beta1.install.log" | at now
tail -F /tmp/postgresql-9.2beta1.install.log
...
PostgreSQL installation complete.

# DONE 🙂  
root@domU-12-31-39-07-39-AE-node-1:[Tue May 22 03:38:50]:[/opt/installs/postgresql-9.2beta1]$ /usr/local/pgsql/bin/psql -V
psql (PostgreSQL) 9.2beta1
contains support for command-line editing

Post install check of plproxy-2.4 on replicated PostgreSQL 9.1.3 at Amazon Web Services (EC2 , S3)

May 18, 2012 Leave a comment

I got an error on the post install check of plproxy:


root@domU-12-31-39-07-39-AE-node-1:[Fri May 18 18:37:24]:[/opt/installs/plproxy-2.4]$ make installcheck postgres
/usr/lib64/pgsql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir='/usr/bin'   --dbname=regression --inputdir=test --dbname=regression plproxy_init plproxy_test plproxy_select plproxy_many plproxy_errors plproxy_clustermap plproxy_dynamic_record plproxy_encoding plproxy_split plproxy_target plproxy_sqlmed
(using postmaster on Unix socket, default port)
============== dropping database "regression"         ==============
DROP DATABASE
============== creating database "regression"         ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test plproxy_init             ... ok
test plproxy_test             ... FAILED
test plproxy_select           ... ok
test plproxy_many             ... ok
test plproxy_errors           ... ok
test plproxy_clustermap       ... ok
test plproxy_dynamic_record   ... ok
test plproxy_encoding         ... FAILED (test process exited with exit code 2)
test plproxy_split            ... ok
test plproxy_target           ... ok
test plproxy_sqlmed           ... ok

=======================
 2 of 11 tests failed. 
=======================

The differences that caused some tests to fail can be viewed in the
file "/opt/installs/plproxy-2.4/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/opt/installs/plproxy-2.4/regression.out".

make: *** [installcheck] Error 1

how to fix plproxy_test:
Edit the plproxy Make file and add to REGRESS_OPTS proper values in a view to use the correct user and host


root@domU-12-31-39-07-39-AE-node-1:[Fri May 18 19:10:12]:[/opt/installs/plproxy-2.4]$ fgrep REGRESS_OPTS  Makefile
REGRESS_OPTS = --dbname=regression --inputdir=test --user=postgres --host=127.0.0.1
# now we have :

root@domU-12-31-39-07-39-AE-node-1:[Fri May 18 19:05:51]:[/opt/installs/plproxy-2.4]$ make installcheck 
/usr/lib64/pgsql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir='/usr/bin'   --dbname=regression --inputdir=test --user=postgres --host=127.0.0.1 --no-locale --dbname=regression plproxy_init plproxy_test plproxy_select plproxy_many plproxy_errors plproxy_clustermap plproxy_dynamic_record plproxy_encoding plproxy_split plproxy_target plproxy_sqlmed
(using postmaster on 127.0.0.1, default port)
============== dropping database "regression"         ==============
DROP DATABASE
============== creating database "regression"         ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test plproxy_init             ... ok
test plproxy_test             ... ok
test plproxy_select           ... ok
test plproxy_many             ... ok
test plproxy_errors           ... ok
test plproxy_clustermap       ... ok
test plproxy_dynamic_record   ... ok
test plproxy_encoding         ... FAILED (test process exited with exit code 2)
test plproxy_split            ... ok
test plproxy_target           ... ok
test plproxy_sqlmed           ... ok

=======================
 1 of 11 tests failed. 
=======================


Compile and install PostGIS 2.0.0 on replicated PostgreSQL 9.1.3 at Amazon Web Services (EC2 , S3)

May 18, 2012 6 comments

How to install that :

1. get the source needed


cd /opt/installs
wget http://postgis.refractions.net/download/postgis-2.0.0.tar.gz
wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
wget http://download.osgeo.org/geos/geos-3.3.3.tar.bz2
git clone https://github.com/json-c/json-c.git
wget http://download.osgeo.org/gdal/gdal-1.9.1RC2.tar.gz
wget http://sourceforge.net/projects/cunit/files/latest/download
wget http://prdownloads.sourceforge.net/dblatex/dblatex-0.3.2.tar.bz2?download

# NOTE, you may skip the CUnit and dblatext install, but you will get these warnings at the configure of the postgs:
# ==========================================================================================
# checking for dblatex... no
# configure: WARNING: dblatex is not installed so PDF documentation cannot be built
# checking CUnit/CUnit.h usability... no
# checking CUnit/CUnit.h presence... no
# checking for CUnit/CUnit.h... no
# configure: WARNING: could not locate CUnit required for liblwgeom unit tests
# ==========================================================================================

yum install tetex-tex4ht -y
yum install libxml2-devel -y
yum install ImageMagick* -y

2. Compile and install proj-4


tar xvfz proj-4.8.0.tar.gz
cd proj-4.8.0
./configure ; make -j3 install; ldconfig
# if you run 64bit distro, the postgis build will fail so link the proj.pc at the 64bit pkgconfig directory :  
ln -s /usr/local/lib/pkgconfig/proj.pc /usr/lib64/pkgconfig/


3. Compile and install geos:
in case you dont have installed c++, install it


yum install gcc-c++ -y
tar xvf geos-3.3.3.tar.bz2 
cd geos-3.3.3
./configure --disable-libtool-lock
make -j3  install; ldconfig

4. Compile and install json-c lib:


cd /opt/install
cd cd json-c/
./autogen.sh
./configure
make -j3 install; ldconfig
# if you run 64bit distro, link the jspn.pc at the 64bit pkgconfig directory :  
ln -s /usr/local/lib/pkgconfig/json.pc /usr/lib64/pkgconfig/

4. Compile and install gdal-1.9.1 lib:
gdal compilation takes some time, so its better to detach the make process:


cd /opt/installs
tar xvf gdal-1.9.1RC2.tar.gz
cd gdal-1.9.1
./autogen.sh 
./configure
echo "make -j3 install; ldconfig | tee /tmp/gdal-1.9.1RC2.install.log" | at now 
tail -f /tmp/gdal-1.9.1RC2.install.log

6. Compile and install CUnit-2.1-2:

cd /opt/installs
tar xf CUnit-2.1-2-src.tar.bz2
cd CUnit-2.1-2
./configure  ; make -j3 install; ldconfig
# if you run 64bit distro, link the cunit.pc at the 64bit pkgconfig directory :  
ln -s /usr/local/lib/pkgconfig/cunit.pc /usr/lib64/pkgconfig/

6. Compile and install postgis-2.0.0


cd /opt/installs
tar xvfz postgis-2.0.0.tar.gz
cd postgis-2.0.0
./configure
# at the end you will see something like:
#
#  PostGIS is now configured for x86_64-unknown-linux-gnu
#
# -------------- Compiler Info ------------- 
#  C compiler:           gcc -g -O2
#  C++ compiler:         g++ -g -O2
#
# -------------- Dependencies -------------- 
#  GEOS config:          /usr/local/bin/geos-config
#  GEOS version:         3.3.3
#  GDAL config:          /usr/local/bin/gdal-config
#  GDAL version:         1.9.1
#  PostgreSQL config:    /usr/bin/pg_config
#  PostgreSQL version:   PostgreSQL 9.1.3
#  PROJ4 version:        48
#  Libxml2 config:       /usr/bin/xml2-config
#  Libxml2 version:      2.7.6
#  JSON-C support:       yes
#  PostGIS debug level:  0
#  Perl:                 /usr/bin/perl
#
# --------------- Extensions --------------- 
#  PostGIS Raster:       enabled
#  PostGIS Topology:     enabled
#
# -------- Documentation Generation -------- 
#  xsltproc:             /usr/bin/xsltproc
#  xsl style sheets:     /usr/share/sgml/docbook/xsl-stylesheets
#  dblatex:              /usr/bin/dblatex
#  convert:              /usr/bin/convert
#  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd
#
make -j3

There is huge possibility the build process to fail with the following error:


gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/usr/local/include   -I/usr/include/libxml2 -I../liblwgeom -I../libpgcommon -I. -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o lwgeom_in_geojson.o lwgeom_in_geojson.c
In file included from lwgeom_in_geojson.c:31:
/usr/local/include/json/json.h:27:34: error: json_object_iterator.h: No such file or directory
lwgeom_in_geojson.c:467: warning: no previous prototype for ‘postgis_libjson_version’
make[1]: *** [lwgeom_in_geojson.o] Error 1
make[1]: Leaving directory `/opt/installs/postgis-2.0.0/postgis'
make: *** [all] Error 1
root@ip-10-28-187-67-node-2:[Fri May 18 13:01:52]:[/opt/installs/postgis-2.0.0]$

The issue is the following :

line 27 of /usr/local/include/json/json.h defined json_object_iterator.h to be included, but that file is missing in the include directory:


root@ip-10-28-187-67-node-2:[Fri May 18 15:57:29]:[/var/log]$ sed '27,27!d' /usr/local/include/json/json.h 
#include "json_object_iterator.h"
root@ip-10-28-187-67-node-2:[Fri May 18 13:03:14]:[/opt/installs/postgis-2.0.0]$ ls -lrht /usr/local/include/json/
total 72K
-rw-r--r-- 1 root root 6.0K May 18 12:54 json_tokener.h
-rw-r--r-- 1 root root 1.1K May 18 12:54 json_object_private.h
-rw-r--r-- 1 root root  17K May 18 12:54 json_object.h
-rw-r--r-- 1 root root  579 May 18 12:54 json_inttypes.h
-rw-r--r-- 1 root root  671 May 18 12:54 json.h
-rw-r--r-- 1 root root  163 May 18 12:54 json_config.h
-rw-r--r-- 1 root root 1.7K May 18 12:54 debug.h
-rw-r--r-- 1 root root  693 May 18 12:54 bits.h
-rw-r--r-- 1 root root 1.1K May 18 12:54 arraylist.h
-rw-r--r-- 1 root root 2.2K May 18 12:54 printbuf.h
-rw-r--r-- 1 root root 6.6K May 18 12:54 linkhash.h
-rw-r--r-- 1 root root  971 May 18 12:54 json_util.h

root@ip-10-28-187-67-node-2:[Fri May 18 13:03:11]:[/opt/installs/postgis-2.0.0]$ locate json_object_iterator.h
/opt/installs/json-c/json_object_iterator.h

#how to fix the issue :
cp -vvv /opt/installs/json-c/json_object_iterator.h /usr/local/include/json/

so, clean the build and do it again:


cd /opt/installs/postgis-2.0.0
make clean 
./configure
echo "make -j3 install | tee -a /tmp/postgis-2.0.0.install.log" | at now 
tail -f /tmp/postgis-2.0.0.install.log

that is all for the install

now, we have to test it , before to claim it will work:
NOTE: do not test on a slave node in case the replication is active, the test will fail :

make test 
.....
Creating spatial db postgis_reg 
createdb: database creation failed: ERROR:  cannot execute CREATE DATABASE in a read-only transaction
createlang: could not connect to database postgis_reg: FATAL:  database "postgis_reg" does not exist
psql: FATAL:  database "postgis_reg" does not exist
dropdb: database removal failed: ERROR:  cannot execute DROP DATABASE in a read-only transaction
Preparing spatial db postgis_reg 

 Something went wrong during db initialization (core module).
 For details, check /tmp/pgis_reg/regress_log

dropdb: database removal failed: ERROR:  cannot execute DROP DATABASE in a read-only transaction
make[1]: *** [check] Error 1
make[1]: Leaving directory `/opt/installs/postgis-2.0.0/regress'
make: *** [check] Error 1

test on the master:

# Note at the test time the slave will be catching up 
# Start the test detached as it will take some time, at AWS micro instance it takes a lot 🙂  
echo "make -j3 test | tee -a /tmp/postgis-2.0.0.make.test.log" | at now 
tail -f  /tmp/postgis-2.0.0.make.test.log
....
.....
Creating spatial db postgis_reg 
Preparing spatial db postgis_reg 
TMPDIR is /tmp/pgis_reg
PATH is /usr/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/aws/bin:/root/bin

 PostgreSQL 9.1.3 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
 Postgis 2.0.0 - r9605 - 2012-05-18 17:38:33
   GEOS: 3.3.3-CAPI-1.7.4
   PROJ: Rel. 4.8.0, 6 March 2012

Running tests

 loader/Point ....................... ok 
 loader/PointM ....................... ok 
 loader/PointZ .....
....

postgres@kida:5432::postgres=[Fri May 18 12:32:41 EDT 2012]# \l
                                    List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------+----------+----------+-------------+-------------+-----------------------
 kannel        | mtel     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgis_reg   | root     | UTF8     | C           | en_US.UTF-8 | 
 postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 regression    | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 


postgres@juan:5432::postgres=[Fri May 18 12:37:36 EDT 2012]# select pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 0/C7AD518
(1 row)

Time: 500.731 ms

postgres@kida:5432::postgres=[Fri May 18 12:37:38 EDT 2012]# select pg_last_xlog_replay_location()
;
 pg_last_xlog_replay_location 
------------------------------
 0/C7AD518
(1 row)

Time: 162.957 ms

And that is all, you have now a Master – slave cluster with the Latest PostGIS ready for the devs ..:)

cheers

aaand …
Slackware4LIFE 🙂