Sharing Tech Knowledge - 2.0

Latex.- The powerdot class for presentations

2012-03-04T22:30:00.002-06:00

I started working with the powerdot document class. I find it extremely easy to use and powerful.
http://ctan.org/tex-archive/macros/latex/contrib/powerdot

This is my test presentation.

Wich I achieved with the following code

\documentclass{powerdot}
\begin{document}
\title{PostgreSQL replication concepts}
\author{Rene Romero Benavides}
\date{March 4,2012}
\begin{slide}{PostgreSQL}
\begin{itemize}
\item Here is the first line on my slide
\pause
\item And now here's the next
\end{itemize}
\end{slide}
\end{document}

References.
www.bakoma-tex.com/doc/latex/powerdot/powerdot.pdf
http://csgsa.cs.clemson.edu/seminar/tools06/docbook/html/ch02s04.html#id2396476
http://www.miwie.org/presentations/html/powerdot.html
doctorh.umwblogs.org/files/2010/12/courseC.pdf
http://www.dmi.me.uk/blog/2010/11/08/creating-a-presentation-with-latex-and-powerdot/

Mmmm... second guessing, I'll try beamer too.

PostgreSQL - Forcing index scans

2012-03-02T17:14:00.000-06:00

I'm following a presentation by Bruce Momjiam titled "Explainng the Postgres Query Optimizer", and I found an exercise where you can force the query optimizer to use index scans (RAF not long ago told me about this too). I don't quite understand the value of doing this as if a value is present in a table several times it could be more costly to carry on an index scan versus a sequential scan, but ... you never now

SET enable_seqscan = false;
SET enable_bitmapscan  = false;

WITH letter (letter, count) AS ( SELECT letter, count(*) from sample GROUP by 1) SELECT letter AS "1", count,(SELECT * FROM lookup_letter(letter) AS "12" limit 1) AS lookup_letter FROM letter ORDER BY 2 DESC

So now it uses index scan each time regardless.

1 | count |                              lookup_letter                               
---+-------+--------------------------------------------------------------------------
 p |   199 | Index Scan using i_sample on sample  (cost=0.00..39.05 rows=199 width=2)
 s |     9 | Index Scan using i_sample on sample  (cost=0.00..21.91 rows=9 width=2)
 c |     8 | Index Scan using i_sample on sample  (cost=0.00..19.64 rows=8 width=2)
 r |     7 | Index Scan using i_sample on sample  (cost=0.00..19.63 rows=7 width=2)
 t |     5 | Index Scan using i_sample on sample  (cost=0.00..15.09 rows=5 width=2)
 d |     4 | Index Scan using i_sample on sample  (cost=0.00..15.07 rows=4 width=2)
 v |     4 | Index Scan using i_sample on sample  (cost=0.00..15.07 rows=4 width=2)
 f |     4 | Index Scan using i_sample on sample  (cost=0.00..15.07 rows=4 width=2)
 _ |     3 | Index Scan using i_sample on sample  (cost=0.00..12.80 rows=3 width=2)
 a |     3 | Index Scan using i_sample on sample  (cost=0.00..12.80 rows=3 width=2)
 u |     3 | Index Scan using i_sample on sample  (cost=0.00..12.80 rows=3 width=2)
 e |     2 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)
 i |     1 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)
 k |     1 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)

Where in cases of index scanning a value that repeats to a high degree it's related cost is higher too, comparing it to:

RESET ALL;
WITH letter (letter, count) AS ( SELECT letter, count(*) from sample GROUP by 1) SELECT letter AS "1", count,(SELECT * FROM lookup_letter(letter) AS "12" limit 1) AS lookup_letter FROM letter ORDER BY 2 DESC;

 1 | count |                             lookup_letter                             
---+-------+-----------------------------------------------------------------------
 p |   199 | Seq Scan on sample  (cost=0.00..13.16 rows=199 width=2)
 s |     9 | Seq Scan on sample  (cost=0.00..13.16 rows=9 width=2)
 c |     8 | Seq Scan on sample  (cost=0.00..13.16 rows=8 width=2)
 r |     7 | Seq Scan on sample  (cost=0.00..13.16 rows=7 width=2)
 t |     5 | Bitmap Heap Scan on sample  (cost=4.29..12.76 rows=5 width=2)
 v |     4 | Bitmap Heap Scan on sample  (cost=4.28..12.74 rows=4 width=2)
 f |     4 | Bitmap Heap Scan on sample  (cost=4.28..12.74 rows=4 width=2)
 d |     4 | Bitmap Heap Scan on sample  (cost=4.28..12.74 rows=4 width=2)
 a |     3 | Bitmap Heap Scan on sample  (cost=4.27..11.38 rows=3 width=2)
 u |     3 | Bitmap Heap Scan on sample  (cost=4.27..11.38 rows=3 width=2)
 _ |     3 | Bitmap Heap Scan on sample  (cost=4.27..11.38 rows=3 width=2)
 e |     2 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)
 i |     1 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)
 k |     1 | Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=2)
(14 filas)

PostgreSQL - Common Table Expressions

2012-03-01T04:00:00.000-06:00

I just stumbled upon this presentation by Bruce Momjian:
http://momjian.us/main/writings/pgsql/cte.pdf
I had heard of these beauties before but never really used them ... they're astonishing!

I'm just going to replicate the given examples :

test=# with source as ( select 1) SELECT * FROM source;
 ?column? 
----------
        1
(1 row)

test=# WITH source AS ( SELECT 1 as col1) SELECT * FROM source;
 col1 
------
    1
(1 row)

test=# WITH source (col1) AS ( SELECT 1 ) SELECT * FROM source;
 col1 
------
    1
(1 row)

test=# WITH source (col2) AS ( SELECT 1 AS col1) SELECT col2 AS col3 FROM source;
 col3 
------
    1
(1 row)

test=# WITH source AS ( SELECT 1,2 ) SELECT * FROM source;
 ?column? | ?column? 
----------+----------
        1 |        2
(1 row)

-- here it starts to get interesting

test=# WITH source AS ( SELECT 1,2), source2 AS ( SELECT 3,4) select * from source UNION ALL SELECT * FROM source2;
 ?column? | ?column? 
----------+----------
        1 |        2
        3 |        4
(2 rows)

test=# WITH source AS ( select lanname, rolname FROM pg_language JOIN pg_roles ON lanowner = pg_roles.oid ORDER BY lanname) SELECT * FROM source UNION ALL SELECT MIN(lanname), NULL FROM source;
  lanname   | rolname  
------------+----------
 c          | postgres
 internal   | postgres
 plpgsql    | postgres
 plpython2u | postgres
 plpythonu  | postgres
 sql        | postgres
 c          | 
(7 rows)

test=# WITH class (oid, relname) AS ( SELECT oid, relname FROM pg_class WHERE relkind = 'r' ) SELECT c.relname, pa.attname FROM pg_attribute pa JOIN  class c ON c.oid = pa.attrelid ORDER BY 1,2 LIMIT 5;
  relname   | attname 
------------+---------
 child_test | cmax
 child_test | cmin
 child_test | col1
 child_test | col2
 child_test | ctid
(5 rows)


--and this is where I give up as I'm extreeeeeemly tired

By the way: cmin,cmax, and ctid are system columns:

cmin

The command identifier (starting at zero) within the inserting transaction.
cmax

The command identifier within the deleting transaction, or zero.
ctid

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

http://www.postgresql.org/docs/current/static/ddl-system-columns.html

Another set of statements that I find fascinating:

test=# set statement_timeout = '1s';
SET
test=# WITH RECURSIVE source AS ( select 1 UNION ALL SELECT 1 FROM source ) SELECT * FROM source;
ERROR:  canceling statement due to statement timeout

test=# RESET statement_timeout;
RESET

And this one is absolutely epic:

test=# WITH RECURSIVE source (counter) AS ( SELECT 1 UNION ALL SELECT counter + 1 FROM source WHERE counter < 10) SELECT * FROM source;
 counter 
---------
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
(10 rows)

test=# WITH RECURSIVE source (counter,product) AS ( SELECT 1,1 UNION ALL SELECT counter +1 , product * (counter +1) FROM source WHERE counter < 10) SELECT counter, product FROM source;
 counter | product 
---------+---------
       1 |       1
       2 |       2
       3 |       6
       4 |      24
       5 |     120
       6 |     720
       7 |    5040
       8 |   40320
       9 |  362880
      10 | 3628800
(10 rows)

test=# WITH RECURSIVE source (counter,product) AS ( SELECT 1,1 UNION ALL SELECT counter +1 , product * (counter +1) FROM source WHERE counter < 12) SELECT counter, product FROM source WHERE counter = 12;
 counter |  product  
---------+-----------
      12 | 479001600
(1 row)

Data Visualization . d3.js - Voronoi Tesselation

2012-02-29T00:45:00.002-06:00

Project home page:
http://mbostock.github.com/d3/
A beautiful framework to develop awesome data visualizations, but gosh, too many lines of code to do simple things, and I need to master python and django first. I'll give a try latter ... maybe.

Though it is good to have the reference.

Data Visualization . Colorbrewer. Vanity coloring your maps

2012-02-28T01:30:00.000-06:00

I love this app that helps you choose just the right color combinations for your layers in maps.

http://colorbrewer2.org/

This is how I used it on my first map:

Cool, isn't it? Yay!
P.S. By the way,darker means more violent...

Pentaho BI. Jpivot - java.util.EmptyStackException

2012-02-27T22:30:00.002-06:00

I found my first bug with Pentaho BI while following this tutorial, but the solution was very straightforward, to replace the jpivot jar file....(because the old one was using an incorrect method).

Java.util.EmptyStackException
at java.util.Stack.peek(Stack.java:85)
at mondrian.server.Locus.peek(Locus.java:63)
at mondrian.rolap.SqlMemberSource.getMemberChildren2(SqlMemberSource.java:902)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:830)
at mondrian.rolap.SqlMemberSource.getMemberChildren(SqlMemberSource.java:804)
at mondrian.rolap.SmartMemberReader.readMemberChildren(SmartMemberReader.java:247)
at mondrian.rolap.SmartMemberReader.getMemberChildren(SmartMemberReader.java:210)
at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.readMemberChildren(RolapCubeHierarchy.java:586)
at mondrian.rolap.RolapCubeHierarchy$CacheRolapCubeHierarchyMemberReader.getMemberChildren(RolapCubeHierarchy.java:682)
at mondrian.rolap.SmartMemberReader.getMemberChildren(SmartMemberReader.java:176)
at mondrian.rolap.RolapSchemaReader.internalGetMemberChildren(RolapSchemaReader.java:170)
at mondrian.rolap.RolapSchemaReader.getMemberChildren(RolapSchemaReader.java:152)
at mondrian.rolap.RolapSchemaReader.getMemberChildren(RolapSchemaReader.java:146)
at mondrian.olap.DelegatingSchemaReader.getMemberChildren(DelegatingSchemaReader.java:78)
at com.tonbeller.jpivot.mondrian.MondrianMemberTree.internalGetChildren(MondrianMemberTree.java:209)
at com.tonbeller.jpivot.mondrian.MondrianMemberTree.getChildren(MondrianMemberTree.java:193)
at com.tonbeller.jpivot.navigator.member.TreeModelAdapter.getChildren(TreeModelAdapter.java:95)

etc...

The solution was given by dulci :

dulci
    Hey,
    I have the solution! Do this!:

    1.- stop-pentaho
    2.- Rename the library biserver-ce/tomcat/webapp/pentaho/WEB-INF/lib/jpivot-1.8.0-100420.jar to jpivot-1.8.0-100420.jar.OLD
    3.- Paste the library that we downloaded from http://www.stratebi.es/todobi/Ene12/jpivot-1.8.0-100420.jar
    4 - start pentaho and that's it!

And bingo!!! This is the beauty of open source.

Reference:
http://forums.pentaho.com/showthread.php?89323-JPivot-error-want-to-check-the-solution

PostgreSQL - Example Database for Training (pagila improved . 540 MB once installed)

2012-02-24T17:25:00.002-06:00

I just migrated from MySQL to PostgreSQL the example database based on pagila (mysql's sakila) but improved, as depicted in this book (which I highly recommend) :

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html

And I'm releasing it just like the original authors did, under the LGPL license.

You have to create a user 'wcm' with the necessary privileges in order to load this db.

Compressed text dump 71 mb.
https://rapidshare.com/files/3990485799/wcm.sql.tar.gz

Power Architect entity-relationship diagram.
https://rapidshare.com/files/2635761979/er_diagram_architect.architect

wcm=# select version();                                     version                                                                                        
-------------------------------------------------------------------------------------                                           
 PostgreSQL 9.1.1 on i586-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.6.2, 32-bit                                                  
(1 row)

This is what the tables look like:

wcm=# set search_path = wcm;
SET
wcm=# \dt+
                            Listado de relaciones
 Esquema |        Nombre        | Tipo  |  Dueño   |   Tamaño   | Descripción 
---------+----------------------+-------+----------+------------+-------------
 wcm     | actor                | tabla | rene     | 6304 kB    | 
 wcm     | country              | tabla | rene     | 16 kB      | 
 wcm     | customer             | tabla | rene     | 32 MB      | 
 wcm     | customer_order       | tabla | rene     | 65 MB      | 
 wcm     | customer_order_line  | tabla | rene     | 107 MB     | 
 wcm     | director             | tabla | rene     | 840 kB     | 
 wcm     | distributor          | tabla | rene     | 624 kB     | 
 wcm     | dvd                  | tabla | rene     | 0 bytes    | 
 wcm     | dvd_release          | tabla | rene     | 23 MB      | 
 wcm     | dvd_release_actor    | tabla | rene     | 22 MB      | 
 wcm     | dvd_release_director | tabla | rene     | 0 bytes    | 
 wcm     | employee             | tabla | rene     | 0 bytes    | 
 wcm     | employee_job         | tabla | rene     | 8192 bytes | 
 wcm     | inventory            | tabla | rene     | 0 bytes    | 
 wcm     | job_description      | tabla | rene     | 8192 bytes | 
 wcm     | language             | tabla | rene     | 0 bytes    | 
 wcm     | lookup_type          | tabla | rene     | 8192 bytes | 
 wcm     | lookup_value         | tabla | rene     | 8192 bytes | 
 wcm     | promoted_dvd_release | tabla | rene     | 0 bytes    | 
 wcm     | promotion            | tabla | rene     | 8192 bytes | 
 wcm     | purchase_order       | tabla | rene     | 0 bytes    | 
 wcm     | purchase_order_line  | tabla | rene     | 0 bytes    | 
 wcm     | region               | tabla | rene     | 8192 bytes | 
 wcm     | warehouse            | tabla | rene     | 8192 bytes | 
 wcm     | website              | tabla | rene     | 8192 bytes | 
(25 filas)

Empty tables : purchase_order,promoted_dvd_release and purchase_order_line can be filled to your wish, the others
are not important and are easy to fill as well. The structure is kind of self explanatory but post a comment should you have any doubts.

PostgreSQL - Compressing text dumps on the fly

2012-02-24T13:11:00.003-06:00

This one was missing in my recipes ...


pg_dump -U myUser myDB | gzip > myDB.sql.gz

Then, to restore:

gzip -cd myDB.sql.gz | pg_restore ...

or

or zcat myDB.sql.gz |pg_restore


The "tar" utility is for bundling up a bunch of files and directories into a single file (the name is a contraction of "tape archive"). In that respect, a "tar" file is kind-of like a "zip" file, except that "zip" always implies compression while "tar" does not.

Note finally that "gzip" is not "zip." The "gzip" utility just compresses; it doesn't make archives.

Reference:

http://stackoverflow.com/questions/2379482/shell-scripting-use-a-pipe-as-an-input-for-tar

MySQL . mysqldump. exporting data with sql inserts to PostgreSQL

2012-02-23T17:01:00.002-06:00

Generating a working mysql data dump with inserts to load into PostgreSQL.

Key options for generating the dump.

mysqldump -u user  --extended-insert=FALSE --no-create-info --compact --compatible=postgresql database > file.sql

Converting incompatible characters

sed "s/\\\'/\'\'/g" file.sql > file1.sql

And importing into postgresql

psql database < file1.sql

That was quick, wasn't it?References:http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html http://chriswa.wordpress.com/2008/02/20/mysqldump-data-only/http://en.wikibooks.org/w/index.php?title=Programming:Converting_MySQL_to_PostgreSQL

MySQL . Error 1396 (HY000) <- nasty bug

2012-02-23T15:16:00.001-06:00

The error goes like this:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.16-log |
+------------+
1 row in set (0.00 sec)


CREATE USER youruser IDENTIFIED BY 'youruser';
ERROR 1396 (HY000) at line 4: Operation CREATE USER failed for 'youruser'@'%'

The bug is depicted here:

http://bugs.mysql.com/bug.php?id=28331

and the workaround that worked for me:

mysql> delete from mysql.db where user='youruser';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='youruser';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Another nuance that drives me off from using mysql (without needing to).

Pentaho . Chartbeans intro

2012-02-22T13:25:00.001-06:00

I'm studying the way to adapt my favorite charting library to pentaho bi. I made a simple map.

http://wiki.pentaho.com/display/ServerDoc2x/Introduction+to+Pentaho+ChartBeans#IntroductiontoPentahoChartBeans-ExamplefromaJavaDeveloperPerspective

The provided example is a bit outdated, I needed to reconfigure some things, but I'm on my way. Once I accomplish it I'll publish a walkthrough tutorial.

Pentaho. Pentaho Charts

2012-02-19T02:31:00.001-06:00

I'm evaluating Pentaho BI, and I'm getting to like it very much. I'm interested in leveraging my own charts through chartbeans (the pentaho project to provide a wrapper to foreign charting libraries) as the ones from jfreechart don't appeal to me, and flash is not supported by some devices ,but they look very impressive like this one:

Chartbeans project:
http://wiki.pentaho.com/display/COM/The+Pentaho+ChartBeans+Project

PostgreSQL - fuzzystrmatch . distance between strings

2012-02-18T04:41:00.003-06:00

I think this feature could be useful for data cleansing, or in general, for tasks related with string comparisons.

http://www.postgresql.org/docs/8.3/static/fuzzystrmatch.html

F.9.2. Levenshtein
This function calculates the Levenshtein distance between two strings:
   levenshtein(text source, text target) returns int
Both source and target can be any non-null string, with a maximum of 255 characters.

test=# Create extension fuzzystrmatch;
CREATE EXTENSION

test=# select levenshtein('john smith','john schmit');
 levenshtein 
-------------
           3
(1 row)

Calculating their degree of similarity between two words sounds easy... but I'll try that after a long nap. One thing that would be awesome, is to somehow implement an efficient auto-complete feature using postgresql...

Visualization - Google charts API

2012-02-18T03:19:00.001-06:00

Yet another charting API.

Charts are rendered using HTML5/SVG technology to provide cross-browser compatibility and cross platform portability to iPhones, iPads and Android. No plugins are needed.

http://code.google.com/intl/es-MX/apis/chart/interactive/docs/index.html

Drawback (sort of):
Can I use charts offline?
No; your computer must have live access to http://www.google.com/jsapi in order to use charts. This is because the visualization libraries that your page requires are loaded dynamically before you use them. The code for loading the appropriate library is part of the included jsapi script, and is called when you invoke the google.load() method. Our terms of service do not allow you to download the google.load or google.visualization code to use offline.

And...the motion chart is fantastic!!!

PostgreSQL. Thesaurus dictionary - Normalizing political state names

2012-02-14T00:02:00.001-06:00

I want to normalize the name of mexico states to some abbreviation in an automatic fashion as I'll be parsing a lot of documents containing statistical information about different variables in the whole country and they refer to states in heterogeneous ways.
For instance, I want the strings 'edo. de mexico', 'estado de mexico','mexico','edo de mexico' to yield 'mex' instead, which is the abbreviation I've chosen for that particular state (which in time will help me assign it's proper numeric identifier for its respective polygon in maps).

1.Creating the configuration file.

vi /usr/pgsql-9.1/share/tsearch_data/mexican_states.ths

? ? mexico : mex
edo ? mexico : mex
mexico : mex

:wq

2.Creating the text search dictionary

CREATE TEXT SEARCH DICTIONARY mexican_states_ths 
(
TEMPLATE = thesaurus,
DictFile = mexican_states,
Dictionary = pg_catalog.spanish_stem
);

3. Altering the mapping of the text search configuration for the respective language (spanish).

ALTER TEXT SEARCH CONFIGURATION spanish ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH mexican_states_ths, spanish_stem;

4. And testing the results.

postgres=# SELECT plainto_tsquery('spanish','edo de mexico');
 plainto_tsquery 
-----------------
 'mex'
(1 row)

postgres=# SELECT plainto_tsquery('spanish','mexico');
 plainto_tsquery 
-----------------
 'mex'
(1 row)

postgres=# SELECT plainto_tsquery('spanish','estado de mexico');
 plainto_tsquery 
-----------------
 'mex'
(1 row)

postgres=# SELECT plainto_tsquery('spanish','edo. de mexico');
 plainto_tsquery 
-----------------
 'mex'
(1 row)

Certainly some previous treatment is necessary, such as lowercasing and unquoting, but that's easier than editing files by hand, which I find kind of silly.

Reference:
http://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html
While learning how to achieve this, I blew away my spanish text search configuration. Here's how to get it back
http://forums.enterprisedb.com/posts/list/1575.page
i.e.

Go to your $SHAREDIR i.e. (pg_config --sharedir)
Check the contents of file: snowball_create.sql.

Virtualbox - could not find a valid medium format for the target disk - vmdk.gz

2012-02-12T04:19:00.001-06:00

I just ran into the following issue while importing a virtual appliance (.ova) to virtualbox.

Failed to import appliance /home/rene/Descargas/MusicBrainz NGS 2012-01-12.ova.
Could not find a valid medium format for the target disk '/home/rene/VirtualBox VMs/vm/musicbrainz-server-2012-01-12-disk1.vmdk.gz'.

The thing is that this appliance was generated by vmware with gzip compression enabled for the vmdk disk. I don't know whether there's another workaround, but it turns out that the .ova file is in fact a tar file so :

tar -xvf MusicBrainz\ NGS\ 2012-01-12.ova
gzip -d musicbrainz-server-2012-01-12-disk1.vmdk.gz

And you're all set.

Source:
http://itproctology.blogspot.com/2009/05/ovf-and-ova-dilemma.html
http://communities.vmware.com/thread/283650

Flotr2 - Found another free charting library

2012-02-11T21:32:00.000-06:00

To me, there's no excuse not to present our data in an attractive fashion.
http://www.humblesoftware.com/flotr2/documentation

IntroductionFlotr2 is a library for drawing HTML5 charts and graphs. It is a branch of flotr which removes the Prototype dependency and includes many improvements.Features: mobile support framework independent extensible plugin framework custom chart types FF, Chrome, IE6+, Android, iOS lines bars candles pies bubbles
Usage

To use Flotr2, include the flotr2.min.js script in your page and create a visible div
with positive width and height. A graph is drawn with the Flotr.draw(container, data, options) method.

PostgreSQL. Mail merging-Part 2.The plpython function

2012-02-10T21:18:00.002-06:00

Let's get this thing done.

Creating an auxiliary view,to get the name of the columns tables and views.

CREATE VIEW columns AS SELECT
  c.relname tab,
  a.attname as col
  FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.relname::regclass   
  WHERE
       n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
  AND c.relkind IN ('v','r')
  AND a.attnum > 0

Creating and populating the table where the "recipients" will be listed.

test=# CREATE TABLE deceased (recipient_name text,name_of_deceased text, his_her text, positive_attribute text,my_signature text);
CREATE TABLE
test=# INSERT INTO deceased values ('Jim Morrison','his',' a great artist'),('Kurt Kobain','his',' a tormented soul'),('John Lenon','his',' a charismatic and charming person'),('Janis Joplin','her',' authentic, a punch of energy ');
INSERT 0 3
UPDATE deceased SET my_signature = 'Rene', recipient_name = 'Mr.X';

And after some research and thought, I came up with this:

CREATE OR REPLACE FUNCTION mail_merge(source text,document text,path text) RETURNS void AS 
$$
#source -> the table or view that contains the list
#document -> the template with placeholders matching the field names of source prefixed with colons (:)
#path -> the directory where the generated documents will be created
import re
placeholders = []
doc_text = ""
columns = ""
rv = plpy.execute("SELECT col from columns WHERE tab = %s order by 1" % (plpy.quote_literal(source)))
for x in rv:
  placeholders = placeholders + [x['col']] 
rv = plpy.execute("SELECT document from documents WHERE doc_name =  %s " % (plpy.quote_literal(document)))
for x in rv:
  doc_text = doc_text + x['document']
for i in range(0,len(placeholders)):
  columns = columns + placeholders[i]+" "
  if i < len(placeholders) -1:
    columns = columns +","
rv = plpy.execute("SELECT "+columns+" FROM "+source )
for x in rv: 
  tmp = doc_text
  for i in range(0,len(placeholders)):
    tmp = re.sub(":"+str(placeholders[i]),str(x[placeholders[i]]),tmp)
  #here you create the file containing tmp, in path 
$$ LANGUAGE plpython2u;

With this function you can, based on a table or a view create n documents (where n equals the number of rows in the table or view), based on a template, setup as described in my previous entry. The field names of the table or view will determine the placeholders names.

The more I work with python, the more I like it =-).

PostgreSQL . Mail merging ....with PostgreSQL ? Part 1

2012-02-09T00:56:00.000-06:00

Yep , it's a weird concept, but the idea is to help a friend to automate a rather boring task. With mail merging, I'm talking about having a document template and you need to send it to a list of people. I know what you're thinking...gosh that's what office suites are for, but hell, I want to do it with PostgreSQL! hahahaha

This is my test document in Libre Office Writer:

Dear :recipient_name

I know :name_of_deceased has passed away. I was dismayed to hear of :his_her passing and am writing to extend my deepest sympathies to you and your family.

:name_of_deceased was :positive_attribute
If there is anything I can do to help you through this difficult time, please let me know and I'd be more than willing to offer my assistance.

With warmest regards,
:my_signature

Note that I'm using the following placeholders (variables if you wish):
:name_of_deceased
:his_her
:positive_attribute
:my_signature

I've saved it with a html format so I can manipulate flat text (it would be more elegant to use something like Latex I reckon).

Preprocessing and loading the document template:

Getting rid of the new line character:

-bash-4.2$ tr '\n' ' ' < letter.html | tee > letter_new.html

Let's create the table that will hold my documents:

CREATE TABLE documents (id serial primary key,doc_name text, document text);

Load the template into the table

letters=#\copy documents (document) FROM 'letter_new.html' WITH  DELIMITER AS '^';
letters=# UPDATE documents SET doc_name = 'condolences' WHERE id = 1;

Now my template looks like this in the database:

letters=# select * from documents;
id       | 1
doc_name | condolences
document | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD>    <META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=windows-1252">      <TITLE></TITLE>         <META NAME="GENERATOR" CONTENT="LibreOffice 3.4  (Win32)">      <META NAME="CREATED" CONTENT="20120208;22492965">       <META NAME="CHANGED" CONTENT="0;0">     <STYLE TYPE="text/css">         <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }     -->     </STYLE> </HEAD> <BODY LANG="es-MX" DIR="LTR"> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm">Dear :recipient_name</P> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm">I know :name_of_deceased  has passed away. I was dismayed to hear of  :his_her passing and am writing to extend my deepest sympathies to you and your family.</P> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm">:name_of_deceased was :positive_attribute</P> <P STYLE="margin-bottom: 0cm">If there is anything I can do to help you through this difficult time, please let me know and I'd be more than willing to offer my assistance.</P> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm"><BR> </P> <P STYLE="margin-bottom: 0cm">With warmest regards,</P> <P STYLE="margin-bottom: 0cm">:my_signature</P> </BODY> </HTML>

In the second part I'll construct a python function that outputs documents based on this template and on a table of deceased people =-). But that'll be after I try to get some sleep ...

GeoServer - My first map served

2012-02-08T13:41:00.000-06:00

I just served my first map with GeoServer
http://geoserver.org/display/GEOS/Welcome
and it was fairly easy. Their documentation is good enough for not being necessary to explain how did I do it, I just wanted to spread the word about this nice tool.

This is the contour of my hometown: México City.

I just found some references for tutorials though.
http://gis.stackexchange.com/questions/7987/tutorials-for-geoserver

Python. Why should you learn python?

2012-02-08T00:52:00.000-06:00

Today I was confronted with the question: why should anybody learn python?
And...gee, I couldn't give a strong argument besides: "because I like it, it's clear, fun to work with, and I you can do everything with it"

So in this entry I'll "curate" some facts and opinions of people from the web advocating for the Python programming language.

Because it's a de-facto standard in the industry. [1] [2][3]
Excellent relation between speed/memory consumption/language features [1]

Very easy to learn [1][2]
Elegant and clear syntax [1]:

class BankAccount(object):
    def __init__(self, initial_balance=0):
        self.balance = initial_balance
    def deposit(self, amount):
        self.balance += amount
    def withdraw(self, amount):
        self.balance -= amount
    def overdrawn(self):
        return self.balance < 0
my_account = BankAccount(15)
my_account.withdraw(5)
print my_account.balance

A huge variety of modules to accomplish anything you can think of. [1][2]
Straight forward web programming [1][2]

---------------------------------------------------------------------------------------------------------------

Other unsorted reasons from stack overflow:

As for what tasks can be done:
Python is extremely fun to develop in.
Everything can be done with Python.
If something can't be done, you can create an extension for it.
Everything can not only be done, but it can be done fast. For example a program that takes you weeks in C++ might take you a day in Python.
Great for prototyping, and even for usage in a commercial setting.
As for language features:
Scripting language
Multi-paradigms (object oriented, imperative, functional)
Dynamic type system
Automatic memory management
Links to other dynamic languages
Embedding into other languages
Nice py2exe program that converts python scripts to executables for windows. Other platforms include it by default.
Strongly typed without static typing
Large standard library
Includes lists, dictionaries, sets, and much more as basic data types
Some implementations of python compile directly to machine code
It is actively being worked on
It has an antigravity module as noted by Jeremy Cantrell
...
As for resources available:
Python is free to use
There are a ton of online resources and free tutorials
There are a ton of books on Python
There is a large community of developers available for help

Why should you learn Python?
Because it is a modern, elegant, highest level OO language
Because it is highly expressive, i.e., you will earn higher productivity
Because it comes with "batteries included" i.e. libraries for whatever you want
Because it is well documented and has a well-established community
Because it is simple to learn (and easy to read!) if you already know another OO language
Because it is relatively portable (that is, portable until you use system-specific features)
Because it is free and already installed on any modern Linux distribution (and also on Mac OS X and *BSD, I guess)
As for what can be done in Python: everything except extremely low-level things (i.e. kernels, drivers, programming a microcontroller). Also, you should not use Python directly for number crunching (it is far too slow for that), but you can wrap C or C++ number crunching libraries with Python modules (and it has already been done, the most notable examples being Numpy and Scipy).

------------------------------------------------------------------------------------------------------------------
I'll update this post as I find more convincing rationales advocating for python.

PostgreSQL - MVCC revisited

2012-02-06T22:01:00.002-06:00

I had previously read about this topic but I needed to grasp it a little bit more, so I made this mental map:

And also deviced a very simple exercise based on this document and this one :

To me, the most important piece of information is this:

Basic idea: tuple is visible if xmin is valid and xmax is not."Valid" means "either committed or the current transaction".

postgres=# CREATE TABLE test(id int, time timestamp);
CREATE TABLE
postgres=# BEGIN;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
        13756
(1 row)

postgres=# INSERT INTO test values(generate_series(1,100),current_timestamp);
INSERT 0 100
postgres=# SELECT *,xmin,xmax FROM test LIMIT 5;
 id |           time            | xmin  | xmax 
----+---------------------------+-------+------
  1 | 2012-02-06 21:45:00.11556 | 13756 |    0
  2 | 2012-02-06 21:45:00.11556 | 13756 |    0
  3 | 2012-02-06 21:45:00.11556 | 13756 |    0
  4 | 2012-02-06 21:45:00.11556 | 13756 |    0
  5 | 2012-02-06 21:45:00.11556 | 13756 |    0
(5 rows)

postgres=# COMMIT;
COMMIT

Now let's generate some dead tuples:

postgres=# BEGIN;
BEGIN
postgres=# SELECT txid_current();
 txid_current 
--------------
        13758
(1 row)

postgres=# UPDATE test SET time = current_timestamp WHERE id < 6;
UPDATE 100
postgres=# SELECT *,xmin,xmax FROM test LIMIT 5;
 id |            time            | xmin  | xmax 
----+----------------------------+-------+------
  1 | 2012-02-06 21:48:03.560034 | 13758 |    0
  2 | 2012-02-06 21:48:03.560034 | 13758 |    0
  3 | 2012-02-06 21:48:03.560034 | 13758 |    0
  4 | 2012-02-06 21:48:03.560034 | 13758 |    0
  5 | 2012-02-06 21:48:03.560034 | 13758 |    0
(5 rows)

postgres=# DELETE FROM test WHERE id < 6;
DELETE 5
postgres=# SELECT *,xmin,xmax FROM test LIMIT 5;
 id |            time            | xmin  | xmax 
----+----------------------------+-------+------
  6 | 2012-02-06 21:48:03.560034 | 13758 |    0
  7 | 2012-02-06 21:48:03.560034 | 13758 |    0
  8 | 2012-02-06 21:48:03.560034 | 13758 |    0
  9 | 2012-02-06 21:48:03.560034 | 13758 |    0
 10 | 2012-02-06 21:48:03.560034 | 13758 |    0
(5 rows)

postgres=# ROLLBACK;
ROLLBACK

postgres=# SELECT *,xmin,xmax FROM test LIMIT 5;
 id |           time            | xmin  | xmax  
----+---------------------------+-------+-------
  1 | 2012-02-06 21:45:00.11556 | 13756 | 13758
  2 | 2012-02-06 21:45:00.11556 | 13756 | 13758
  3 | 2012-02-06 21:45:00.11556 | 13756 | 13758
  4 | 2012-02-06 21:45:00.11556 | 13756 | 13758
  5 | 2012-02-06 21:45:00.11556 | 13756 | 13758
(5 rows)

How do we get a glance at how many dead tuples in a table?

postgres=# select * from pg_stat_all_tables where relname = 'test';
-[ RECORD 1 ]-----+------------------------------
relid             | 16969
schemaname        | public
relname           | test
seq_scan          | 7
seq_tup_read      | 320
idx_scan          | 
idx_tup_fetch     | 
n_tup_ins         | 100
n_tup_upd         | 105
n_tup_del         | 5
n_tup_hot_upd     | 105
n_live_tup        | 100
n_dead_tup        | 5
last_vacuum       | 
last_autovacuum   | 2012-02-06 21:51:33.926522-06
last_analyze      | 
last_autoanalyze  | 2012-02-06 21:46:33.783941-06
vacuum_count      | 0
autovacuum_count  | 1
analyze_count     | 0
autoanalyze_count | 1

n_dead_tup = number of dead tuplesAnd now, lets VACUUM the table.

postgres=# VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 5 removable, 100 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 100 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM

I still feel that I need to dive more into the subject. One thing that would be great is to peep into "deleted" row contents =-).

Python - SciPy. Concetrate on ideas not on implementations

2012-02-05T14:41:00.000-06:00

I just discovered Scipy:

SciPy (pronounced "Sigh Pie") is open-source software for mathematics, science, and engineering. It is also the name of a very popular conference on scientific programming with Python. The SciPy library depends on NumPy, which provides convenient and fast N-dimensional array manipulation. The SciPy library is built to work with NumPy arrays, and provides many user-friendly and efficient numerical routines such as routines for numerical integration and optimization. Together, they run on all popular operating systems, are quick to install, and are free of charge. NumPy and SciPy are easy to use, but powerful enough to be depended upon by some of the world's leading scientists and engineers. If you need to manipulate numbers on a computer and display or publish the results, give SciPy a try!

I wanted to find the inverse of a matrix using plpython in the old way (row operations) ,so I could practice the python syntax, but now I'm just going to use SciPy.

This is the guide to the linear algebra module:
http://docs.scipy.org/doc/scipy/reference/tutorial/linalg.html

postgres=# CREATE OR REPLACE FUNCTION inverse_matrix (m text)
RETURNS text
AS $$
from scipy import linalg
from numpy import matrix
return linalg.inv(matrix(m));
$$ LANGUAGE plpython2u;

postgres=# select inverse_matrix($$'[1 3 5; 2 5 1; 2 3 8]'$$);
-[ RECORD 1 ]--+----------------------
inverse_matrix | [[-1.48  0.36  0.88]
               |  [ 0.56  0.08 -0.36]
               |  [ 0.16 -0.12  0.04]]

As simple as that! =-)

Data Visualization - Word clouds

2012-02-03T03:06:00.000-06:00

I'm using this javascript library to create simple word clouds:
http://code.google.com/p/nebulos/wiki/UserGuide
I just love creative people and javascript!

This is another example using:
https://github.com/DukeLeNoir/jQCloud

Postgresql - Domains

2012-02-01T16:33:00.001-06:00

I'm studying good database design principles and discovered a beautiful concept : domains.

You have a SQL database and you want a column to be restrained to one of a few values.

If the list will never change, and ordering is preset or doesn't matter: use an ENUM
If the list might change but changes require programming, OR the list will not change but needs to be alpha sorted: use a DOMAIN
If the list will change, and changes do not require other programming: use a lookup table.

http://nigel.mcnie.name/blog/the-five-minute-guide-to-postgresql-domains-enums-on-steroids

For instance, I defined the following domains to enforce data validation to some extent.

CREATE DOMAIN general_text TEXT
 CONSTRAINT max_length CHECK (LENGTH( VALUE ) < 255 )
 ;

CREATE DOMAIN email_string TEXT
 CONSTRAINT valid_email_form CHECK ( VALUE ~ '_*@_*._*')
 CONSTRAINT valid_email_length CHECK ( LENGTH(VALUE) > 5)
 ;
 
CREATE DOMAIN number_string TEXT
 CONSTRAINT valid_number CHECK (VALUE ~ '[0-9]+')
 ;
 
CREATE DOMAIN telephone_string TEXT
 CONSTRAINT valid_telephone_form CHECK (VALUE ~ '[0-9\-]+')
 CONSTRAINT valid_telephone_length CHECK (LENGTH(VALUE) >= 8 )
 ;

CREATE DOMAIN counter int
 CONSTRAINT positive_number CHECK ( VALUE >= 0)
 ;

Defining a table that uses them:

CREATE TABLE editoriales(
  id serial primary key,
  nombre general_text not null,
  direccion general_text,
  estado_id int,
  pais_id int,
  codigo_postal number_string , 
  telefono telephone_string
);

The thing that got me into their use, is that they can be treated as if they were tables ,you can modify them / add new constraints and ... they are reusable!!!

Python - My first plpython function

2012-01-30T00:35:00.002-06:00

I needed a function that is able to remove white spaces inside a string in order to rename table and columns to a more standard form. There are probably faster / easier ways to do this, but for now, this will suffice.

postgres=# CREATE OR REPLACE FUNCTION remove_blanks (t text)
RETURNS text
AS $$
import string
result = ""
for char in t:
  if not(char in string.whitespace):
    result = result + char
return result
$$ LANGUAGE plpython2u;
CREATE FUNCTION

postgres=# \df
                                List of functions
 Schema |         Name          | Result data type | Argument data types |  Type  
--------+-----------------------+------------------+---------------------+--------
public | remove_blanks         | text             | t text              | normal


postgres=# select remove_blanks('This string contains spaces');
      remove_blanks       
--------------------------
 Thisstringcontainsspaces
(1 row)

Python - Generating primes

2012-01-29T12:03:00.000-06:00

I always make a generating primes program when I'm learning a new language, as if it were my "hello world". I'm using python 3.2

import math

def cribe(size):
    nums = [0]*size
    for i in range(2,int(math.sqrt(size))):
        if(nums[i] == 1):
            continue
        for j in range(2,size):
            if i * j > size - 1:
                break
            if nums[i*j] == 0:
                nums[i*j] = 1
    for i in range(2,size):
        if(nums[i] ==0 ):
            print(i)        
limit = input('Up to what number? ')
cribe(int(limit))

My IDE is by the moment aptana-studio, haven't found anything better.

http://aptana.com/

Flot - Attractive Javascript plotting for jQuery - MIT License

2012-01-29T01:47:00.001-06:00

I just found out about this open source chart plotting library. This is quite a static example, but a very wide array of functionalities are available, many kinds of interactions with the charts are possible. I intent to use it in my personal proyects of data visualization.

http://code.google.com/p/flot/

I tailored and customized this one, but here are some other examples:
http://people.iola.dk/olau/flot/examples/

PostgreSQL - Forbid access to a database temporarily.

2012-01-27T18:27:00.002-06:00

I found out about this feature, thanks to someone who asked how to do this in the postgresql-es-ayuda list.

Disable connections to a PostgreSQL database temporarily

Today I had to disable access to some PostgreSQL databases temporarily. This can be easily done using pgAdmin. Under database properties, set "Connection Limit " to 0 from the default of -1. The corresponding SQL statement is
 ALTER DATABASE "dbname" WITH CONNECTION LIMIT = 0;
When you are ready to allow connections, change this back to -1.

Reference:
http://vimalkumar.in/2011/01/27/disable-connections-to-a-postgresql-database-temporarily/

PostgreSQL - Renaming tables and columns with mixed case names

2012-01-27T13:01:00.001-06:00

I just imported a mysql database to postgresql, the thing is that tables and columns are written using both upper and lower case characters and we don't like that naming schema.

1. I have this query that lists almost all objects in a particular schema (I stored it as a view named "tables").

view "tables"

SELECT n.nspname as Schema,
  c.relname as Name,
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as Type,
  pg_catalog.obj_description(c.oid, 'pg_class') as Description
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Now, I generate the needed SQL to rename the tables:

SELECT 'ALTER TABLE ' || $$"$$ || name ||$$"$$||' RENAME TO '|| lower(name)||' ; ' FROM (SELECT name FROM tables WHERE type = 'table') AS t1;

So far so good. But then, how to you rename table columns?

Well, this is my base query (attributes) for that:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '33954' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

The part that needs to be parametrized is : WHERE a.attrelid = ?

I might also use this one:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '^(actividad)$'
            AND pg_catalog.pg_table_is_visible(c.oid)
    )
;

http://bytes.com/topic/postgresql/answers/692471-how-get-column-names-table

How to obtain the table's oid ?
http://www.postgresql.org/docs/8.0/static/datatype-oid.html

SELECT oid::text FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

For instance, obtaining the oid of the table "activity" in my database:

SELECT attrelid FROM pg_attribute WHERE attrelid = 'actividad'::regclass limit 1;
-[ RECORD 1 ]---
attrelid | 33954

Which is the number that my query "attributes" is embedding in the query. I'll finish this post when I get home.
-----------------------------------------------------------------------------------------------------------------------
Putting together all that info, I came to this query:

CREATE VIEW columns AS SELECT n.nspname as Schema,
  c.relname as Name,
  a.attname as Col 
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.relname::regclass    
WHERE 
       n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
  AND c.relkind = 'r'
  AND a.attnum > 0
ORDER BY 1,2
;

Now only remains switching to lower case those table columns:

select 'ALTER TABLE ' ||schema||'.'||name||' RENAME COLUMN '||$$"$$||col||$$"$$||' TO '||$$"$$||lower(col)||$$"$$||' ;' FROM columns;

I put the column's name double quoted, as another issue is having those names with blank spaces between words, for which I'll probably need a function, gosh!

Django and python - First steps

2012-01-27T02:48:00.003-06:00

I've always wanted to learn python, and I've always wanted to learn to use a web framework. I'm giving Django a try.

https://www.djangoproject.com/

I'm developing my first app (tutorial 1), but they suggest that you get comfortable with the api before going through the second one.

I'd never thought that developing a database driven web application would be so easy. I'm starting to like it very much (despite those "expected indent" messages =-) ).

MySQL - mini-migration to PostgreSQL

2012-01-26T17:48:00.002-06:00

Today I had to perform a "nano-migration" from mysql (from a mysql dump) to postgresql, but gee I needed a refresher.

These links were very useful.

To install mysql (on opensuse).

http://en.opensuse.org/SDB:MySQL_installation

http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user

To import the sql dump to mysql.
http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/

Enterprisedb offers a migration wizard for this kind of boring and burdensome chores:

http://www.enterprisedb.com/docs/en/8.4/migrate/Table%20of%20Contents.htm#TopOfPage

Except that it didn't do an automatic conversion of the datetime mysql type, I'm really satisfied with the results.

There are also lots of scripts and other tools available.
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

Other interesting references:
http://www.raditha.com/postgres/

Linux - OpenSUSE startup script with systemd

2012-01-24T15:57:00.001-06:00

I've been struggling with my opensuse box to execute some scripts as root when the system starts, and I just found the answer to why it wasn't working.

I have found out that in openSUSE 12.1, some startup scripts do not operate due to the usage of systemd. systemd really speeds up system startup by running the many startup processes in parallel and avoids the usage of shell scripts altogether. That is is good for speed, but not good if you are in need of running something in openSUSE as root when the system is restarted.(1)

1. We need to create the text file after-local.service (shown below) in the folder /lib/systemd/system as root (Alt-F2):

Code:

kdesu kwrite /lib/systemd/system/after-local.service

This is what you put into this file and save it:

Code:

#  This file is part of systemd.
#
#  systemd is free software; you can redistribute it and/or modify it
#  under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.

[Unit]
Description=/etc/init.d/after.local Compatibility
ConditionFileIsExecutable=/etc/init.d/after.local

[Service]
Type=oneshot
ExecStart=/etc/init.d/after.local
TimeoutSec=0
StandardOutput=tty
RemainAfterExit=yes
SysVStartPriority=99

[Install]
WantedBy=multi-user.target

2. Next, we need to add the after-local.service to systemd. Open up a terminal session and run the following command:

Code:

sudo systemctl enable /lib/systemd/system/after-local.service

3. Finally, you need to edit the file /etc/init.d/after.local as root and add in the stuff you want to run as root when you startup openSUSE

Postgresql - Studying the MVCC model

2012-01-23T02:31:00.001-06:00

I intent to understand PostgreSQL throughly. I found a great presentation here:
PostgreSQL Through Pictures.
http://momjian.us/main/writings/pgsql/internalpics.pdf
It has a section dealing with this particular issue.

I also like this resource which depicts a practical example.
http://devcenter.heroku.com/articles/postgresql-concurrency

I find interesting following the examples illustrated with the explanations, I never learn more than when doing, but for now, I'm a bit tired and it's quite late.

Postgresql - Installing Postgresql 9.1 on Amazon Linux

2012-01-20T23:18:00.000-06:00

First of all, there's such thing as a Linux from Amazon

http://aws.amazon.com/amazon-linux-ami/

Amazon Linux AMI

The Amazon Linux AMI is a supported and maintained Linux image provided by Amazon Web Services for use on Amazon Elastic Compute Cloud (Amazon EC2). It is designed to provide a stable, secure, and high performance execution environment for applications running on Amazon EC2. It also includes packages that enable easy integration with AWS, including launch configuration tools and many popular AWS libraries and tools. Amazon Web Services provides ongoing security and maintenance updates to all instances running the Amazon Linux AMI. The Amazon Linux AMI is provided at no additional charge to Amazon EC2 users.

It comes bundled with postgresql 8.4 which is good enough, but gee, I like bleeding edge stuff and postgresql has improved much since =-)

I followed this tutorial made by some altruistic guy that also shares his discoveries (what would it be the internet without people like that?)

http://imperialwicket.com/aws-install-postgresql-90-on-amazon-linux

And everything went smoothly, so smoothly that I'm smiling =-).

I'm reproducing the important parts here as a backup.

Update the yum repositories

I want to install the latest stable postgresql from pgrpms.org. We could just download the rpm and manually install from the file, but that inevitably results in some dependency issues. I prefer to configure an alternate yum repository for a particular keyword. So we need to update the configuration for the Amazon repositories (be sure to update both "main" and "updates", and do not forget the asterisk).

sudo vim /etc/yum.repos.d/amzn-main.repo
[At the bottom of the "[amzn-main]" section, after "enabled=1", add "exclude=postgresql*"]
sudo vim /etc/yum.repos.d/amzn-updates.repo
[Add the same exclude to the bottom of the "[amzn-updates]" section]

Download the repository/key installation rpm from pgrpms.org

wget http://yum.pgrpms.org/reporpms/9.0/pgdg-redhat-9.0-2.noarch.rpm
sudo rpm -ivh pgdg-redhat-9.0-2.noarch.rpm

Since this rpm is generated for RHEL6, we need to make a minor change to the resulting /etc/yum.repos.d/pgdg-90-redhat.repo file. Update the URLs, replacing the $releaseserver value with '6'. The Amazon Linux $releaseserver value is a date, instead of the primary version number that this repository configuration is expecting.

sudo vim /etc/yum.repos.d/pgdg-90-redhat.repo

The updated base url values should look like this (update two of them):

## ORIGINAL
# baseurl=http://yum.pgrpms.org/9.0/redhat/rhel-$releasever-$basearch
## UPDATED
baseurl=http://yum.pgrpms.org/9.0/redhat/rhel-6-$basearch

All we have done is told yum that it should use the amzn repositories for everything except packages that meet the "postgresql*" criteria. After that, install a new repository configuration for the pgrpms.org repository.

webProgramming - http://api.themoviedb.org. Getting info about movies

2012-01-20T23:16:00.001-06:00

I have a friend that among other things sells movies. I decided to give him a hand to fetch the relevant info about them so he can publish his own movie catalog.
With the api from http://api.themoviedb.org, it couldn't be easier. You need to request an access key first (it's free) for personal use or to develop an open source application.

I'm using this implementation for php
http://sourceforge.net/projects/simpletmdb/
and an example by Dan Bettles

Fetching the info is as simple as this:

/**
 * SimpleTmdb Demo
 *
 * @author Dan Bettles 
 * modified a little bit by René Romero
 */

$title = basename(__FILE__, '.php') . ' Demo';

$oResults = false;

if (isset($_GET['sendRequest'])) {
    require_once dirname(dirname(__DIR__)) . '/include/boot.php';
    $oResults = simpletmdb\Tmdb::create('here you put your access key')
        ->Movie
        ->getInfo($_GET['id']);
}
?>


    
        
        <?php echo $title ?>
        
    
    
        

            ID

            
                        id ?> |
            name ?> |
            tagline ?> |
            imdb_id ?> |
            certification ?> |
            language ?> |
            released ?> |
            homepage ?> |
            url ?> |
            runtime ?> |
            trailer ?> |
                        images->xpath("image[(@type = 'poster') and (@size = 'cover')]")) { ?>
                        
                         |
                        overview ?>|
               categories && ($oGenres = $oResults->categories->xpath("category[@type = 'genre']"))) { ?>
                            
                            
                            
            
        
        studios && ($oStudios = $oResults->studios->xpath("studio"))){?>
                
                
                
                 |
               countries && ($oCountries = $oResults->countries->xpath("country"))){?>
                           
        
        
                |
                cast && ($oCast = $oResults->cast->xpath("person[@job = 'Director']"))){?>
                
                
                
                |
        cast && ($oCast = $oResults->cast->xpath("person[@job = 'Producer']"))){?>
                
                
                
                |
        cast && ($oCast = $oResults->cast->xpath("person[@job = 'Original Music Composer']"))){?>
                
                
                
                |
        cast && ($oCast = $oResults->cast->xpath("person[@job = 'Author']"))){?>
                
                
                
                |
        cast && ($oCast = $oResults->cast->xpath("person[@job = 'Actor']"))){?>
                
                
                
                |
            
                Movie not found

TODO: Batch processing, and remove trailing commas.

PostgreSQL. fsync and speed

2012-01-14T20:15:00.000-06:00

I was startled to read about data corruption in PostgreSQL in the pgsql-general list, I had never heard of such thing. In Oracle it struck me as well, the ones that get corrupted once in a while are the database blocks in the database buffer, and there's nothing to do about it, but nevermind.
It turns out that the person having this issue was running his server with the fsync option turned off.

What does fsync do?

The fsync() function can be used by an application to indicate that all data for the open file description named by fildes is to be transferred to the storage device associated with the file described by fildes in an implementation-dependent manner. The fsync() function does not return until the system has completed that action or until an error is detected.

http://pubs.opengroup.org/onlinepubs/7908799/xsh/fsync.html

So changes to a file are made over a memory buffer and then you close it or use fsync to "commit" the changes to disk. If you disable it, might gain some boost on your database speed, but under the risk of loosing data should a system crash or error occur.

As a side note, I stumbled upon this stack overflow question:
http://stackoverflow.com/questions/3736533/why-doesnt-mongodb-use-fsync
Where never_had_a_name asks why on earth doesn't MongoDB use fsync on each write, and the most important answer is:

This is going against ACID, more specifically against the D, which stands for durability:
Durability [guarantees] that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure, and that all integrity constraints have been satisfied, so the DBMS won't need to reverse the transaction.

ACID properties mostly apply to traditional RDBMS systems. NoSQL systems, which includes MongoDB, give up on one or more of the ACID properties in order to achieve better scalability. In MongoDB's case durability has been sacrificed for better performance when handling large amounts of updates.

Should the necessity arise, here is one good piece of advice from Craig Ringer in the pgsql-general discussion list:

Since you were running with fsync off, you must have had good backups or replication configured, because the documentation warns you that running with fsync=off will probably destroy your data and is only for expert users.

In case you don't have a current replica or backup: first, before you do ANYTHING ELSE follow the instructions here:

http://wiki.postgresql.org/wiki/Corruption

Now - **AFTER** following the instructions above - start the database in single user recovery mode. See:

http://www.postgresql.org/docs/current/static/app-postgres.html

where the "--single" command line argument for the "postgres" executable is documented.

Once you have a single user mode backend running you can REINDEX then shut the backend down, start back up in multi-user mode, and see if you have any luck.

IMHO I would never sacrifice data integrity over performance, but you never know when a client would come with a problem like this.

PostgreSQL - export to CSV or any other character delimited files

2012-01-14T18:22:00.001-06:00

Just a small tip.

Exporting output to CSV format.

pgbenchdb=# \copy (select * from pgbench_history limit 30) TO 'test.csv' CSV

pgbenchdb=# \! cat test.csv
98,3,256655,-2623,2012-01-11 17:28:15.200909,
43,2,94378,2717,2012-01-11 17:28:15.200909,
46,1,414961,1716,2012-01-11 17:28:15.201082,
45,8,246477,3341,2012-01-11 17:28:15.200927,
84,7,743181,-1348,2012-01-11 17:28:15.201073,
20,1,713970,4518,2012-01-11 17:28:15.20119,
76,8,778718,-2588,2012-01-11 17:28:15.200988,
9,2,683187,-1675,2012-01-11 17:28:15.201074,
71,3,793922,-748,2012-01-11 17:28:15.261927,
93,6,484349,1816,2012-01-11 17:28:15.261776,
2,4,47256,2383,2012-01-11 17:28:15.261615,
64,2,567480,-4884,2012-01-11 17:28:15.201201,
75,8,716626,-293,2012-01-11 17:28:15.2013,
82,9,74376,-4112,2012-01-11 17:28:15.284261,
74,5,13936,-2220,2012-01-11 17:28:15.284299,
70,3,365642,-4313,2012-01-11 17:28:15.284375,
81,4,82626,2974,2012-01-11 17:28:15.285328,
27,2,379620,3392,2012-01-11 17:28:15.261669,
87,8,561117,-153,2012-01-11 17:28:15.284997,
85,9,953844,-4951,2012-01-11 17:28:15.296038,
38,5,447869,-3591,2012-01-11 17:28:15.307329,
73,4,130721,4496,2012-01-11 17:28:15.307251,
1,7,200428,-4388,2012-01-11 17:28:15.307193,
72,2,907918,-4323,2012-01-11 17:28:15.216612,
30,1,76931,4226,2012-01-11 17:28:15.318388,
27,7,87543,3849,2012-01-11 17:28:15.307277,
5,9,963441,-2585,2012-01-11 17:28:15.307221,
19,4,584590,-2414,2012-01-11 17:28:15.331634,
78,5,543756,476,2012-01-11 17:28:15.333069,
99,10,867181,-2597,2012-01-11 17:28:15.332944,

About \copy:

\copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

Or using any other delimiter instead of comma, '|' for instance which I regard as being more reliable as commas are sometimes used inside text fields.

pgbenchdb=# COPY (select * from pgbench_history limit 30) TO '/var/lib/pgsql/test.csv' WITH DELIMITER '|';
COPY 30
-- with this variant of the copy command only absolute paths are allowed

the another variant:

pgbenchdb=# \copy (select * from pgbench_history limit 30) TO 'test.csv' WITH DELIMITER '|';
pgbenchdb=# \! cat test.csv
98|3|256655|-2623|2012-01-11 17:28:15.200909|\N
43|2|94378|2717|2012-01-11 17:28:15.200909|\N
46|1|414961|1716|2012-01-11 17:28:15.201082|\N
45|8|246477|3341|2012-01-11 17:28:15.200927|\N
84|7|743181|-1348|2012-01-11 17:28:15.201073|\N
20|1|713970|4518|2012-01-11 17:28:15.20119|\N
76|8|778718|-2588|2012-01-11 17:28:15.200988|\N
9|2|683187|-1675|2012-01-11 17:28:15.201074|\N
71|3|793922|-748|2012-01-11 17:28:15.261927|\N
93|6|484349|1816|2012-01-11 17:28:15.261776|\N
2|4|47256|2383|2012-01-11 17:28:15.261615|\N
64|2|567480|-4884|2012-01-11 17:28:15.201201|\N
75|8|716626|-293|2012-01-11 17:28:15.2013|\N
82|9|74376|-4112|2012-01-11 17:28:15.284261|\N
74|5|13936|-2220|2012-01-11 17:28:15.284299|\N
70|3|365642|-4313|2012-01-11 17:28:15.284375|\N
81|4|82626|2974|2012-01-11 17:28:15.285328|\N
27|2|379620|3392|2012-01-11 17:28:15.261669|\N
87|8|561117|-153|2012-01-11 17:28:15.284997|\N
85|9|953844|-4951|2012-01-11 17:28:15.296038|\N
38|5|447869|-3591|2012-01-11 17:28:15.307329|\N
73|4|130721|4496|2012-01-11 17:28:15.307251|\N
1|7|200428|-4388|2012-01-11 17:28:15.307193|\N
72|2|907918|-4323|2012-01-11 17:28:15.216612|\N
30|1|76931|4226|2012-01-11 17:28:15.318388|\N
27|7|87543|3849|2012-01-11 17:28:15.307277|\N
5|9|963441|-2585|2012-01-11 17:28:15.307221|\N
19|4|584590|-2414|2012-01-11 17:28:15.331634|\N
78|5|543756|476|2012-01-11 17:28:15.333069|\N
99|10|867181|-2597|2012-01-11 17:28:15.332944|\N

You could also fire something like this up:

In order to pass the output to the client, you could do something like this:

rene@linux-2561:~> psql -h 10.0.0.113 -U postgres -c "COPY (SELECT * FROM pgbench_history limit 10) TO STDOUT WITH CSV;" pgbenchdb > test.csv
rene@linux-2561:~> cat test.csv
98,3,256655,-2623,2012-01-11 17:28:15.200909,
43,2,94378,2717,2012-01-11 17:28:15.200909,
46,1,414961,1716,2012-01-11 17:28:15.201082,
45,8,246477,3341,2012-01-11 17:28:15.200927,
84,7,743181,-1348,2012-01-11 17:28:15.201073,
20,1,713970,4518,2012-01-11 17:28:15.20119,
76,8,778718,-2588,2012-01-11 17:28:15.200988,
9,2,683187,-1675,2012-01-11 17:28:15.201074,
71,3,793922,-748,2012-01-11 17:28:15.261927,
93,6,484349,1816,2012-01-11 17:28:15.261776,

Now I'm curious about my statement of some text having embedded commas:

rene@linux-2561:~> psql -h 10.0.0.113 -U postgres -c "COPY (SELECT  'this, is, a,nasty, field') TO STDOUT WITH CSV;" pgbenchdb > test.csv
rene@linux-2561:~> cat test.csv 
"this, is, a,nasty, field"

Nevermind, if it does detect embedded commas it double quotes the field. Nice!

The "\! COMMAND" psql's internal command is great, I just figured it out.

PosgreSQL - monitoring. User auditing "on steroids"

2012-01-13T19:15:00.001-06:00

I'm taking a deep look into the latest PostgreSQL added features (in 9.0 >), and I'm very happy with this one:

Instead of being able to set up configuration variables per database or per user, one can now set them up for a certain user in a certain database:

marc=# ALTER ROLE marc IN database marc set log_statement to 'all';
ALTER ROLE

To know who has which variables set-up in which user+database, there is a new psql command:

marc=# \drds
List of settings
role | database | settings
-----+----------+-----------------
marc | marc | log_statement=all
(1 row)

For instance, I've enabled log_statement = 'all' to my testing role 'rene' on a database called 'rene', even though, the general setting is established to 'none'.
And this is what the log (set as a foreign table) looks like when I execute something like a 'create table' statement.

-[ RECORD 1 ]----------+---------------------------------------------------------
log_time               | 2012-01-13 18:47:17.46-06
user_name              | rene
database_name          | rene
process_id             | 8584
connection_from        | [local]
session_id             | 4f10d072.2188
session_line_num       | 2
command_tag            | idle
session_start_time     | 2012-01-13 18:46:42-06
virtual_transaction_id | 2/89
transaction_id         | 0
error_severity         | LOG
sql_state_code         | 00000
message                | statement: create table test(a int primary key);
detail                 | 
hint                   | 
internal_query         | 
internal_query_pos     | 
context                | 
query                  | 
query_pos              | 
location               | 
application_name       | psql

To me, this level of customization of the auditing feature is just beautiful.

This is what the new \drds psql internal command does behind the scenes.

SELECT rolname AS role, datname AS database,pg_catalog.array_to_string(setconfig, E'\n') AS settings
                       FROM pg_db_role_setting AS s
                       LEFT JOIN pg_database ON pg_database.oid = setdatabase
                       LEFT JOIN pg_roles ON pg_roles.oid = setrole
                       WHERE pg_roles.rolname ~ '^(rene)$'
                       ORDER BY role, database;

Obviously I figured that out through the logs =-).

The novelty created in order to implement this feature is the pg_db_role_setting table, that internally has this structure:

postgres=# \d pg_catalog.pg_db_role_setting
Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Modifiers 
-------------+--------+-----------
 setdatabase | oid    | not null
 setrole     | oid    | not null
 setconfig   | text[] | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"

Reference:

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Per_database.2Brole_configuration

And something as simple as that, is what it takes to make someone like me happy =-D.

PostgreSQL - PITR . Getting back lost data, due to "user error".

2012-01-11T18:05:00.001-06:00

There are circumstances in which something bad happens in the database (like dropping a table or deleting important data), but if you've got a base backup and a series of consistent wal segments, there's nothing to worry about.

Case: Important data was deleted by a malicious or careless database user.

Assumptions:

A base backup created before the data was deleted.
wal archiving enabled and working
log reports set with log_statement(mod)

Let's say the "malicious" user performs the following operations:

postgres@linux-2561:~> psql -p 5433 pgbenchdb
psql (9.1.1)
Type "help" for help.

pgbenchdb=# select count(*) from pgbench_history;
 count 
-------
 10000
(1 row)

pgbenchdb=# delete from pgbench_history where extract(second from mtime)::int % 7 = 0;
DELETE 1350
pgbenchdb=# delete from pgbench_history where extract(second from mtime)::int % 11 = 0;
DELETE 611
pgbenchdb=# delete from pgbench_history where extract(second from mtime)::int % 13 = 0;
DELETE 289
pgbenchdb=# delete from pgbench_history where extract(second from mtime)::int % 17 = 0;
DELETE 779
pgbenchdb=# select count(*) from pgbench_history;
 count 
-------
  6971
(1 row)

After some time we get a call, telling us that today, some data was deleted from one very important table called pgbench_history.

Problem 1. When was this data deleted?
If we've got the reporting logs set to an appropriate level, this is a rather simple task.

postgres=# select log_time,message from pglog where log_time::date = current_date and message like '%delete%' and message like '%history%' order by log_time asc;
          log_time          |                                        message                                         
----------------------------+----------------------------------------------------------------------------------------
 2012-01-11 17:30:26.378-06 | statement: delete from pgbench_history where extract(second from mtime)::int % 7 = 0;
 2012-01-11 17:30:34.854-06 | statement: delete from pgbench_history where extract(second from mtime)::int % 11 = 0;
 2012-01-11 17:30:41.01-06  | statement: delete from pgbench_history where extract(second from mtime)::int % 13 = 0;
 2012-01-11 17:30:45.457-06 | statement: delete from pgbench_history where extract(second from mtime)::int % 17 = 0;
(4 rows)

So, we roll forward (starting from the time that the base backup was taken) up to the time, just before any of those deletions took place, that is, before 2012-01-11 17:30:26.378-06.

To make sure all transactions are present in the archiving directory.

pgbenchdb=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/2A1A0A80
(1 row)

The only thing we have to do is to start our base backup with a recovery.conf file that specifies the "recovery_target_time" as '2012-01-11 17:30:26' for example.

Now, when you're not sure about up to what time to replay the wal files, I think there's no other means than by trial and error. In such cases you better make a copy of your base backup before replaying the wal files.

vi recovery.conf

restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
recovery_target_time = '2012-01-11 17:30:26'

Once the recovery finishes, querying the pgbench_history table:

postgres@linux-2561:~/pitr2/pg_log> psql -p 5436 pgbenchdb
psql (9.1.1)
Type "help" for help.

pgbenchdb=# select count(*) from pgbench_history;
 count 
-------
 10000
(1 row)

One thing that would be wonderful is to go back again in time. The thing here is that WAL replaying means that the changes to the database are reproduced and applied to the data files, then no going back is possible. But nothing could stop you from going further ahead ... I think.

Well you can actually go back by restoring your copy of the base backup and setting an earlier "recovery_target_time" =-) . On this terms it's obvious to infer that you cannot go further back from the time at which the base backup was created.

Postgresql - monitoring. log_statement (mod)

2012-01-11T13:15:00.003-06:00

That option enables logging (like the auditing feature in Oracle) in this way:
mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.

This is what there is to know about this setting from the pg_settings view:

name       | log_statement
setting    | mod
unit       | 
category   | Reporting and Logging / What to Log
short_desc | Sets the type of statements logged.
extra_desc | 
context    | superuser
vartype    | enum
source     | configuration file
min_val    | 
max_val    | 
enumvals   | {none,ddl,mod,all}
boot_val   | none
reset_val  | mod
sourcefile | /var/lib/pgsql/pitr1/postgresql.conf
sourceline | 400

side note about the superuser context:
superuser
These settings can be set from postgresql.conf, or within a session via the SET command; but only superusers can change them via SET. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.

http://www.postgresql.org/docs/current/static/view-pg-settings.html

I'm experimenting with PITR, and I'll need to know up to what point to recover. Ideally just before the deletion of some important data.

Then I execute , some ddl operations to test that they're effectively being logged:

postgres@linux-2561:~/pitr1/pg_log> pgbench -c 4 -j 2 -s 10 -t 10 -p 5433 pgbenchdb
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 4
number of threads: 2
number of transactions per client: 10
number of transactions actually processed: 40/40
tps = 30.567626 (including connections establishing)
tps = 30.718934 (excluding connections establishing)

And see that effectively they where logged:

postgres@linux-2561:~/pitr1/pg_log> tail -f postgresql-2012-01-11_125210.csv
2012-01-11 13:08:14.660 CST,"postgres","pgbenchdb",10577,"[local]",4f0dde1d.2951,38,"idle in transaction",2012-01-11 13:08:13 CST,2/59,2516,LOG,00000,"statement: UPDATE pgbench_tellers SET tbalance = tbalance + -3973 WHERE tid = 15;",,,,,,,,,""
2012-01-11 13:08:14.660 CST,"postgres","pgbenchdb",10577,"[local]",4f0dde1d.2951,39,"idle in transaction",2012-01-11 13:08:13 CST,2/59,2516,LOG,00000,"statement: UPDATE pgbench_branches SET bbalance = bbalance + -3973 WHERE bid = 3;",,,,,,,,,""
2012-01-11 13:08:14.661 CST,"postgres","pgbenchdb",10577,"[local]",4f0dde1d.2951,40,"idle in transaction",2012-01-11 13:08:13 CST,2/59,2516,LOG,00000,"statement: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (15, 3, 286453, -3973, CURRENT_TIMESTAMP);",,,,,,,,,""
2012-01-11 13:08:14.683 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,34,"idle in transaction",2012-01-11 13:08:13 CST,4/10,2517,LOG,00000,"statement: UPDATE pgbench_tellers SET tbalance = tbalance + 1104 WHERE tid = 94;",,,,,,,,,""
2012-01-11 13:08:14.683 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,35,"idle in transaction",2012-01-11 13:08:13 CST,4/10,2517,LOG,00000,"statement: UPDATE pgbench_branches SET bbalance = bbalance + 1104 WHERE bid = 10;",,,,,,,,,""
2012-01-11 13:08:14.683 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,36,"idle in transaction",2012-01-11 13:08:13 CST,4/10,2517,LOG,00000,"statement: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (94, 10, 413230, 1104, CURRENT_TIMESTAMP);",,,,,,,,,""
2012-01-11 13:08:14.708 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,37,"idle in transaction",2012-01-11 13:08:13 CST,4/11,0,LOG,00000,"statement: UPDATE pgbench_accounts SET abalance = abalance + 2487 WHERE aid = 112736;",,,,,,,,,""
2012-01-11 13:08:14.724 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,38,"idle in transaction",2012-01-11 13:08:13 CST,4/11,2518,LOG,00000,"statement: UPDATE pgbench_tellers SET tbalance = tbalance + 2487 WHERE tid = 85;",,,,,,,,,""
2012-01-11 13:08:14.725 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,39,"idle in transaction",2012-01-11 13:08:13 CST,4/11,2518,LOG,00000,"statement: UPDATE pgbench_branches SET bbalance = bbalance + 2487 WHERE bid = 2;",,,,,,,,,""
2012-01-11 13:08:14.725 CST,"postgres","pgbenchdb",10579,"[local]",4f0dde1d.2953,40,"idle in transaction",2012-01-11 13:08:13 CST,4/11,2518,LOG,00000,"statement: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (85, 2, 112736, 2487, CURRENT_TIMESTAMP);",,,,,,,,,""

In a more structured fashion (setting up the log as a foreign table):

postgres=# select * from pglog where message like '%UPDATE%';

log_time               | 2012-01-11 13:38:34.642-06
user_name              | postgres
database_name          | pgbenchdb
process_id             | 12719
connection_from        | [local]
session_id             | 4f0de53a.31af
session_line_num       | 1
command_tag            | idle in transaction
session_start_time     | 2012-01-11 13:38:34-06
virtual_transaction_id | 5/1216
transaction_id         | 0
error_severity         | LOG
sql_state_code         | 00000
message                | statement: UPDATE pgbench_accounts SET abalance = abalance + 2131 WHERE aid = 423974;
detail                 | 
hint                   | 
internal_query         | 
internal_query_pos     | 
context                | 
query                  | 
query_pos              | 
location               | 
application_name       |

Now, there's no place for doubts about who did what.

Source:
http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html

Other references:
http://sharingtechknowledge.blogspot.com/2012/01/postgresql-foreign-tables-filefdw.html

Now, in a busy system having such granular level of logging will generate huge logs, so specifying a log rotation setting is a must.

Postgresql - the pg_xlog directory

2012-01-10T20:30:00.001-06:00

I'll talk about this topic on the light of my previous entry:
http://sharingtechknowledge.blogspot.com/2012/01/postgresql-recovery-through-archiving.html
The sub-directory pg_xlog inside the database cluster ($PGDATA) keeps transaction logs (or Write Ahead Log - WAL).

I noticed that when archiving is enabled a couple of WAL segments are kept here and it's only after a pg_switch_pgxlog(), that they're transfered to the target archiving directory.

In the docs they mention that it's wise to keep this directory on a separate device for performance reasons, by making a symbolic link to a directory outside the data cluster.

If you don't specify a restore command or if it fails consistently , pg_xlog will grow indefinitely, that's one good thing to have in mind.

Postgresql - Recovery through archiving (PITR). Simple example

2012-01-10T15:40:00.001-06:00

Ok, I have a brand new PostgreSQL 9.1 installation and I'll set up a simple recovery scenario.
My (brand new)database cluster path:
/var/lib/pgsql/pitr1

Step1. Before starting the server, enable archiving.
a. Define where the WAL logs will be archived.
For these purposes I'll use the directory /var/lib/pgsql/wal_archive

b.Configure the connection and archiving options (I changed these from the default values):

vi postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;
port = 5433                     # I have the 5432 busy with another experiment
wal_level = archive             # minimal, archive, or hot_standby
                                # (change requires restart)
archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f'
max_wal_senders = 5             # max number of walsender processes
                                # (change requires restart)

The part worth explaining is this:
archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f'
with this command we instruct postgresql to archive each wal segment (equivalent to a redo log in Oracle) it generates, represented with the variable %p (this variable will be replaced with its full path) to the directory /var/lib/pgsql/wal_archive. %f stands for the name of the wal segment and it will be replaced with the actual wal segment name behind the scenes too. PostgreSQL will handle for us those %p and %f variables.
The default value for the parameter max_wal_senders must be set to a positive integer value greater than cero for this to work.

c. Configure the connection settings. (I'll make the base backup through the pg_basebackup function, so authentication for replication is required.
vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     postgres                                trust
host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

Step2. Start up the server, create a test database, make some ddl/dml operations and confirm that wal segments are being sent to the location specified.

Starting the server up:

postgres@linux-2561:~/pitr1> pg_ctl start -D .
postgres@linux-2561:~/pitr1> cd pg_log/
postgres@linux-2561:~/pitr1/pg_log> ls
postgresql-2012-01-10_114123.log
postgres@linux-2561:~/pitr1/pg_log> tail -f postgresql-2012-01-10_114123.log 
2012-01-10 11:41:23 CST   LOG:  database system was shut down at 2012-01-10 11:10:12 CST
2012-01-10 11:41:23 CST   LOG:  database system is ready to accept connections
2012-01-10 11:41:23 CST   LOG:  autovacuum launcher started
#so it started without error

Creating the database and filling it with dummy data:

postgres@linux-2561:~/pitr1/pg_log> createdb -p 5433 pgbenchdb
postgres@linux-2561:~/pitr1/pg_log> pgbench -i -s 10 -p 5433 pgbenchdb
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
1000000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

verifying that wal segments are being archived.

postgres@linux-2561:~/pitr1/pg_log> ls ../../wal_archive/
000000010000000000000001  000000010000000000000004  000000010000000000000007  00000001000000000000000A
000000010000000000000002  000000010000000000000005  000000010000000000000008
000000010000000000000003  000000010000000000000006  000000010000000000000009

make some more transactions that among other thing affect the pgbench_history table that keeps track of the time an insertion was made.

postgres@linux-2561:~/pitr1/pg_log> pgbench -s 10 -c 6 -j 2 -t 100 -p 5433 pgbenchdb 
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 6
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 600/600
tps = 187.111393 (including connections establishing)
tps = 187.944077 (excluding connections establishing)

Step3. Make the base backup .
In this case I'll perform the recovery on the same machine.

postgres@linux-2561:~/pitr1> cd 
postgres@linux-2561:~> ls
data_sr  pgbench_transaction.sql  pgpool_scripts_backup  pitr1  repmgr  unclassified  wal_archive
postgres@linux-2561:~> mkdir pitr2
postgres@linux-2561:~> pg_basebackup -p 5433 -D /var/lib/pgsql/pitr2/
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

A file with backup extension was generated with the following contents:

START WAL LOCATION: 0/E000020 (file 00000001000000000000000E) <- the name of the starting wal file
STOP WAL LOCATION: 0/E000094 (file 00000001000000000000000E)
CHECKPOINT LOCATION: 0/E000020
BACKUP METHOD: streamed
START TIME: 2012-01-10 13:10:00 CST <- the time at which pg_start_backup was run
LABEL: pg_basebackup base backup <- label given in pg_start_backup (executed automatically by pg_basebackup)
STOP TIME: 2012-01-10 13:10:02 CST

a. Change this backup server port so I does not conflict with the original one.

postgres@linux-2561:~/pitr2> ls
backup_label  global   pg_hba.conf    pg_log        pg_notify  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.conf
base          pg_clog  pg_ident.conf  pg_multixact  pg_serial  pg_subtrans  pg_twophase  pg_xlog     postmaster.opts
postgres@linux-2561:~/pitr2> vi postgresql.conf 
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5434

Step4. Create the recovery.conf file on the backup clusterThe backup server will read this file on start up and it will "know" that it has to perform recovery (read wal segments from the specified location and apply them to data files) starting from the first wal segment generated during backup.

vi /var/lib/pgsql/pitr2/recovery.conf
recovery_command = 'cp /var/lib/pgsql/wal_archive/%f %p' #this is the only mandatory setting.

Before starting up the server, I'd like to generate more WAL segments on the master.

postgres@linux-2561:~/pitr2> pgbench -s 10 -c 6 -j 2 -t 100 -p 5433 pgbenchdb 
Scale option ignored, using pgbench_branches table count = 10
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 6
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 600/600
tps = 191.475816 (including connections establishing)
tps = 192.120436 (excluding connections establishing)

Take note of the last transaction timestamp.

pgbenchdb=# SELECT max(mtime) from pgbench_history;
            max             
----------------------------
 2012-01-10 13:54:47.366648
(1 row)

Now, there's the possibility that the last transactions on the principal server aren't still stored in the last wal segment, so we force a log switch (that executes with a previous checkpoint).Also, you can force a segment switch manually with pg_switch_xlog, if you want to ensure that a just-finished transaction is archived as soon as possiblehttp://www.postgresql.org/docs/8.2/static/functions-admin.html

postgres@linux-2561:~/pitr2> psql -p 5433 -c "select pg_switch_xlog();"
 pg_switch_xlog 
----------------
 0/F879168
(1 row)

One thing I need to review is this info in the manual.

you should at least save the contents of the cluster’s pg_xlog subdirectory, as it might contain logs which were not archived before the system went down.

Step5. Having everything set up. Let's start up the backup server.Expected result: to have an exact copy of the master server being the last transaction timestamp: 2012-01-10 13:54:47.366648

postgres@linux-2561:~/pitr2> pg_ctl start -D .
server starting
postgres@linux-2561:~/pitr2> 2012-01-10 21:21:04 GMT   FATAL:  data directory "/var/lib/pgsql/pitr2" has group or world access
2012-01-10 21:21:04 GMT   DETAIL:  Permissions should be u=rwx (0700).

Yikes!!!!! Ok then.

postgres@linux-2561:~> chmod 700 -R pitr2

postgres@linux-2561:~> pg_ctl start -D pitr2/
server starting
postgres@linux-2561:~> tail -f pitr2/pg_log/postgresql-2012-01-10_1
postgresql-2012-01-10_114123.log  postgresql-2012-01-10_130842.log  postgresql-2012-01-10_152456.log
postgres@linux-2561:~> tail -f pitr2/pg_log/postgresql-2012-01-10_152456.log 
2012-01-10 15:24:56 CST   LOG:  could not open file "pg_xlog/000000010000000000000010" (log file 0, segment 16): No such file or directory
2012-01-10 15:24:56 CST   LOG:  redo done at 0/F87914C
2012-01-10 15:24:56 CST   LOG:  last completed transaction was at log time 2012-01-10 13:55:13.965712-06
2012-01-10 15:24:56 CST   LOG:  restored log file "00000001000000000000000F" from archive
cp: cannot stat `/var/lib/pgsql/wal_archive/00000002.history': No such file or directory
2012-01-10 15:24:56 CST   LOG:  selected new timeline ID: 2
cp: cannot stat `/var/lib/pgsql/wal_archive/00000001.history': No such file or directory
2012-01-10 15:24:56 CST   LOG:  archive recovery complete
2012-01-10 15:24:59 CST   LOG:  autovacuum launcher started
2012-01-10 15:24:59 CST   LOG:  database system is ready to accept connections

Regarding to the .history files not found, the docs say: You may also see an error message at the start of recovery for a ﬁle named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations.

Now, to make some generic tests for consistency.Data in the primary server:

postgres@linux-2561:~> psql -p 5433 pgbenchdb
psql (9.1.1)
Type "help" for help.

pgbenchdb=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size  | Description 
--------+------------------+-------+----------+--------+-------------
 public | pgbench_accounts | table | postgres | 124 MB | 
 public | pgbench_branches | table | postgres | 40 kB  | 
 public | pgbench_history  | table | postgres | 88 kB  | 
 public | pgbench_tellers  | table | postgres | 40 kB  | 
(4 rows)

pgbenchdb=# select max(mtime) from pgbench_history;
            max             
----------------------------
 2012-01-10 13:54:47.366648
(1 row)

Data in the backup server:

postgres@linux-2561:~> psql -p 5434 pgbenchdb
psql (9.1.1)
Type "help" for help.

pgbenchdb=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size  | Description 
--------+------------------+-------+----------+--------+-------------
 public | pgbench_accounts | table | postgres | 124 MB | 
 public | pgbench_branches | table | postgres | 40 kB  | 
 public | pgbench_history  | table | postgres | 88 kB  | 
 public | pgbench_tellers  | table | postgres | 40 kB  | 
(4 rows)

pgbenchdb=# select max(mtime) from pgbench_history;
            max             
----------------------------
 2012-01-10 13:54:47.366648
(1 row)

Now my wal_archive directory looks like this:

postgres@linux-2561:~/wal_archive> ls
000000010000000000000001  000000010000000000000006  00000001000000000000000B  00000001000000000000000F
000000010000000000000002  000000010000000000000007  00000001000000000000000C  00000002000000000000000F
000000010000000000000003  000000010000000000000008  00000001000000000000000D  00000002.history
000000010000000000000004  000000010000000000000009  00000001000000000000000E
000000010000000000000005  00000001000000000000000A  00000001000000000000000E.00000020.backup

This recovery produced an 00000002.history file that contains this timeline:

1       00000001000000000000000F        no recovery target specified

So this is the last wal segment that was applied to data files in this recovery.After that wal segment a new series was created, namely:

00000002000000000000000F

So, it only incremented the left most hexadecimal number to create this new wal segment series.

PostgreSQL - Full text search. Installing a new german dictionary

2012-01-10T00:03:00.001-06:00

It really startled me PostgreSQL not being able to tell that 'Freundin' belongs to the same word family as 'Freund'. I looked it up and turned out that no appropriate german dictionary was installed on my system.

Step1. Install the dictionary.
Main website:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
File:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz

Extract it and place it into this directory or equivalent:
/usr/pgsql-9.1/share/tsearch_data

Step2. We'll need to do some encoding conversion:

[root@localhost german_compound]# 
iconv -f ISO8859-1 -t utf8 german.dic > ../german.dict
iconv -f ISO8859-1 -t utf8 german.aff > ../german.affix
iconv -f ISO8859-1 -t utf8 german.stop > ../german.stop

http://archives.postgresql.org/pgsql-general/2009-02/msg00121.php

Step3. Follow this configuration track:
http://domas.monkus.lt/full-text-search-postgresql
I named my configuration 'de'

Step4. Test the results.

postgres=# SELECT to_tsvector('de','Freundin') @@ to_tsquery('de','Freund');
 ?column? 
----------
 t
(1 row)

postgres=# SELECT to_tsvector('de','Studentin') @@ to_tsquery('de','Student');
 ?column? 
----------
 t
(1 row)
postgres=# SELECT to_tsvector('de','Freunde') @@ to_tsquery('de','Freund');
 ?column? 
----------
 t
(1 row)
postgres=# SELECT to_tsvector('de','freundlich') @@ to_tsquery('de','Freund');
 ?column? 
----------
 t
(1 row)

Now I'll add a good spanish one. So it seems I'll be able to sleep tonight, at least as soon as I play around with PITR a little bit.

Tools - Flowcharting software . yEd

2012-01-09T18:29:00.000-06:00

I'm using this app to make some flowcharts and I'm liking it very much. It runs on Windows, Linux and Mac.

http://www.yworks.com/en/products_yed_about.html

And guess what? It's free!.

Other alternatives are discussed here.
http://stackoverflow.com/questions/358304/good-freeware-for-flowcharts-on-windows

Postgresql - Full text search. First encounter

2012-01-09T11:55:00.000-06:00

I'm amazed about the text searching capabilities embedded into PostgreSQL. It has the 'ability' to tell apart word variants and classify them as belonging to the same family (through their normalization).

My default configuration

pgbenchdb=# show default_text_search_config;
 default_text_search_config 
----------------------------
 pg_catalog.english
(1 row)

Though ,I better make it explicit in each case.

For instance:

Base word: friend
variants: friends , friendly

--in english
pgbenchdb=# SELECT to_tsvector('english','friends') @@ to_tsquery('english','friend');                                     
 ?column?                                                                                                                     
----------                                                                                                                    
 t                                                                                                                            
(1 row)    
--it matches                                                                                                                   
                                                                                                                              
pgbenchdb=# SELECT to_tsvector('english','friendly') @@ to_tsquery('english','friend');                                       
 ?column?                                                                                                                     
----------                                                                                                                    
 t                                                                                                                            
(1 row)


--in spanish

pgbenchdb=# SELECT to_tsvector('spanish','amigos') @@ to_tsquery('spanish','amigo');
 ?column? 
----------
 t
(1 row)

pgbenchdb=# SELECT to_tsvector('spanish','amigable') @@ to_tsquery('spanish','amigo');
 ?column? 
----------
 t
(1 row)

--in german

pgbenchdb=# SELECT to_tsvector('german','Freunde') @@ to_tsquery('german','Freund');
 ?column? 
----------
 t
(1 row)

pgbenchdb=# SELECT to_tsvector('german','Freundin') @@ to_tsquery('german','Freund');
 ?column? 
----------
 f
(1 row)
--it doesn't match!!!???

pgbenchdb=# SELECT to_tsvector('german','freundlich') @@ to_tsquery('german','Freund');
 ?column? 
----------
 f
(1 row)
--it doesn't match!!!???

Well, the german configuration needs further development, it ain't an easy language, that's a fact.

Here is a very good insight about different text search options, including this one.
http://www.slideshare.net/billkarwin/full-text-search-in-postgresql

I'll look up the Postgresql extension network, I might find something useful.

Postgresql - the pg_settings view

2012-01-09T00:47:00.003-06:00

This view is roughly the equivalent to the v$spparameter/v$parameter views in Oracle. Each setting related with the running instance is detailed and documented here . From now on I'll try to relate everything I study about PostgreSQL with its counterpart on Oracle, so I reinforce the topics I learned in the OCA certification.

pg_settings

The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

http://www.postgresql.org/docs/9.1/static/view-pg-settings.html

How many such settings are there?

postgres=# select count(*) from pg_settings;
 count 
-------
   219
(1 row)

One thing I regard worth looking up, is how to tell what settings were changed but are not yet applied as you need to restart the server for them to take effect (those with the postmaster context).

Let's play around a little bit. What are those settings?

postgres=# select name,setting from pg_settings where context = 'postmaster';
              name              |               setting                
--------------------------------+--------------------------------------
 allow_system_table_mods        | off
 archive_mode                   | off
 autovacuum_freeze_max_age      | 200000000
 autovacuum_max_workers         | 3
 bonjour                        | off
 bonjour_name                   | 
 config_file                    | /var/lib/pgsql/data1/postgresql.conf
 data_directory                 | /var/lib/pgsql/data1
 external_pid_file              | 
 hba_file                       | /var/lib/pgsql/data1/pg_hba.conf
 hot_standby                    | off
 ident_file                     | /var/lib/pgsql/data1/pg_ident.conf
 listen_addresses               | localhost
 logging_collector              | on
 max_connections                | 100
 max_files_per_process          | 1000
 max_locks_per_transaction      | 64
 max_pred_locks_per_transaction | 64
 max_prepared_transactions      | 0
 max_wal_senders                | 0
 port                           | 5432
 shared_buffers                 | 3072
 shared_preload_libraries       | 
 silent_mode                    | off
 ssl                            | off
 ssl_ciphers                    | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH
 superuser_reserved_connections | 3
 track_activity_query_size      | 1024
 unix_socket_directory          | 
 unix_socket_group              | 
 unix_socket_permissions        | 0777
 wal_buffers                    | 96
 wal_level                      | minimal
(33 rows)

Let's say I want to restrict the max_connections setting to half its default value.

postgres=# update pg_catalog.pg_settings set setting = '50' where name = 'max_connections';
ERROR:  parameter "max_connections" cannot be changed without restarting the server

Aha! So, it's much more simple than that, it won't let you do it through this view! Well,on these grounds, my question does not apply to PostgreSQL.

So I guess, this implementation is analogous to starting the Oracle instance with a pfile, so no need to have two views as oracle does, one for current settings and one for those written in the spfile that might not be effective just yet.

Postgresql - Foreign Tables. twitter_fdw. advanced twitter searches

2012-01-08T11:31:00.000-06:00

Case scenario:
Postgresql 9.1 on Fedora 15

Objective: find the latest 100 tweets, that talk about postgresql and contain the word 'query' .

My latest experience with this topic:
http://sharingtechknowledge.blogspot.com/2012/01/postgresql-foreign-tables-filefdw.html

Prerequisite: Postgresql 9.1 with contrib modules and postgresql9.1-server-devel (or whatever is called in your distro).
Step1. Install the twitter_fdw wrapper:
http://pgxn.org/dist/twitter_fdw/

Step2. Create the extension in the database you'll use for this purpose.

postgres=# CREATE EXTENSION twitter_fdw;

Step3. Fire the query away. (The CREATE EXTENSION statement creates not only FDW handlers but also Data Wrapper, Foreign Server, User Mapping and twitter table) <- isn't that great?



postgres=# SELECT from_user AS user , text::varchar(144) AS message FROM twitter WHERE q = '#postgresql' and text::varchar(144) ilike '%query%';

The result:

user     |                                                                  message                       
                                           
--------------+------------------------------------------------------------------------------------------------ -------------------------------------------
 postgresqlbr | Parallel query processing may finally be coming to #PostgreSQL http://t.co/cFgNsQHx - Parallel query processing m... http://t.co/hbfAdytF
 chenghlee    | Parallel query processing may finally be coming to #PostgreSQL http://t.co/TZZ4gM7a
(2 rows)

I didn't like a column named 'text', so I renamed it to 'message' but the thing stopped working (I should have known better --> the mapping! duh), so I switched the change back. I'll define a custom foreign server, user mapping , and foreign table latter just for practice.

Postgresql - Foreign tables. file_fdw wrapper. querying the log file

2012-01-07T20:35:00.000-06:00

The foreign table functionality has been around for quite some time in the SQL Standard. A foreign table derives from the concept of External Data Management (SQL/MED) .

SQL/MED is Management of External Data, a part of the SQL standard that deals with how a database management system can integrate data stored outside the database. There are two components in SQL/MED:

Foreign Table
a transparent access method for external data

http://wiki.postgresql.org/wiki/SQL/MED

Another reference:

In 2003, a new extension called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects in SQL databases. In 2011, PostgreSQL 9.1 was released with a great support of this standard.

In a nutshell, you can now use various Foreign Data Wrappers (FDW) to connect a PostgreSQL Server to remote data stores.

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

In this post I'll be using the file_fdw extension:

What does it do?

The file_fdw module provides the foreign-data wrapper file_fdw, which can be used to access data files in the server's file system. Data files must be in a format that can be read by COPY FROM; see COPY for details.

Case scenario. Setup a foreign table to read the server log directly within the database manager.
Postgresql version 9.1.1 on Debian squeeze
Step 0. Install if you haven't done it, the contrib module

root@debian1:~# apt-get install postgresql-contrib-9.1

Step 1. Reconfigure your postgresql.conf file so that logs are generated in a csv format and appropriate rotation of the log is chosen, and reload the configuration.

I though of these parameters (note that a static name for the generated log file is required for obvious reasons).

# - Where to Log -

log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql_log' # log file name pattern,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 5d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.

Step 2. Read through and follow along the example depicted in this link (the only change needed is the path to your logfile):
http://www.postgresql.org/docs/devel/static/file-fdw.html

Step 3. Query the log in whatever fashion you like.
For example:

postgres@debian1:~/data_sr$ psql -p 5432 pgbenchdb
psql (9.1.1)
Type "help" for help.
pgbenchdb=# select log_time, error_severity, message from pglog order by log_time desc;

Output

log_time          | error_severity |                                                         message                                                         
----------------------------+----------------+-------------------------------------------------------------------------------------------------------------------------
 2012-01-07 20:00:43.223-06 | ERROR          | column "logtime" does not exist
 2012-01-07 19:58:57.02-06  | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures
 2012-01-07 19:58:57.02-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:58:56.009-06 | LOG            | archive command failed with exit code 1
 2012-01-07 19:58:54.998-06 | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:54.93-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:54.93-06  | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures
 2012-01-07 19:57:53.92-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:57:52.91-06  | LOG            | archive command failed with exit code 1
 2012-01-07 19:56:52.837-06 | WARNING        | transaction log file "000000020000000000000041" could not be archived: too many failures

On that warning:the transaction log file couldn't be archived, as the target machine where I was sending it to, was down. =-)
Publishing from my fortress of solitude, they gave me back my keys. Yay!!!!

Other references:
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html
http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/
http://pgxn.org/
http://vibhorkumar.wordpress.com/2011/12/06/postgresql-9-1-new-in-object-manipulation/

Postgresql - Streaming replication: synchronous replication

2012-01-06T17:35:00.001-06:00

One cool new feature in postgresql 9.1 is the ability to set up synchronous streaming replication. What does that mean? That one transaction will be committed only when it has been written to wal records (being sent) and applied to the standby servers that are replicating data in a synchronous manner. This sounds like an extra overhead to dml operations, so I guess it's advisable for you to have an excellent communication pathway between the master and the synchronous standby servers. The main advantage of this set up is that you can guarantee to a great deal the reliability of data in case of failure, if one transaction was given the status of committed it will become available instantly in all the synchronous standby's as well, so you'll have virtually no data loss in case of contingencies.

Here are the steps to configure this kind of streaming replication:
http://vibhorkumar.wordpress.com/2011/10/20/asynchronoussynchronous-streaming-replication-in-postgresql-9-1/

The important part being:

To switch Asynchronous replication to Synchronous Replication,use following steps:

1. Change following parameter in postgresql.conf on Primary Server:
synchronous_standby_names = 'sync_replication'

2. Reload the above changes on primary using following command:
pg_ctl -D [primary data directory] reload

3. Change following parameter in recovery.conf file on Standby:
primary_conninfo = 'host=localhost port=5432 application_name=sync_replication'
4. Restart the standby using following command:
pg_ctl -D [standby directory] restart -m fast

Then ,the difference is that :

you specify the synchronous_standby_names in the master's configuration, for the standby servers that will replicate in a synchronous fashion.
reload the master's configuration
add the application_name option to the primary_conninfo string in the recovery.conf file in each synchronous standby.
restart the standby server.

Testing case:
+ Start a big transaction on the master server and kill one synchronous slave in the middle of it.
Expected result:
+ The transaction will be rolled back. No more transactions will be allowed 'till the synchronous standby server becomes available and re-synchronizes.

Results:
+ The transaction is stuck!!!
This is what the master says about it:

in the log:

2012-01-06 17:09:58 CST [unknown] postgres LOG:  could not send data to client: Connection reset by peer

current activity in pg_stat_activity:

-[ RECORD 2 ]----+--------------------------------
datid            | 24576
datname          | pgbenchdb
procpid          | 13296
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2012-01-06 17:09:50.726341-06
xact_start       | 2012-01-06 17:09:50.728063-06
query_start      | 2012-01-06 17:10:01.36678-06
waiting          | f
current_query    | commit;

the replication state in pg_stat_replication:

pgbenchdb=# select * from pg_stat_replication;
(No rows)

I was kind of expecting that.

There's no remedy for me other than to kill the transaction manually (there must me some sort of replication timeout parameter somewhere!).
But before that, am I able to execute dml operations through another session?
Yes!!!! Oops! (but this happens after I cancel it)

If I cancel manually the hung up transaction I get the following message:

pgbenchdb=# insert into pgbench_history (tid) values ( generate_series(1,100));
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 100

postgres@debian1:~$ psql -p 5432 pgbenchdb < test_sync_replication.sql
BEGIN
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
COMMIT

Well, canceling a request is not something that applications usually are programmed to. But the issue that worries me is that it won't return an error message, it will freeze waiting for the synchronous standby to reply!Need to dive in the docs to figure out what the hell is going on.Despite the above results, I think it's important to pinpoint that the status of committed was not granted in neither the transactions.

Postgresql - psql passing variables to a sql script

2012-01-06T14:02:00.000-06:00

It could arise the necessity to parametrize the scripts that we pass to psql, maybe just for testing purposes. For instance, this is the script that pgbench uses for each transaction that simulated clients execute:

BEGIN;

    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

    END;

I called this script : pgbench_transaction.sql
As you can see, each variable has a colon as prefix. You can execute manually by invoking psql like this:

postgres@linux-2561:~> psql -p 9999 -v tid=1 -v bid=2 -v aid=3 -v delta=4  pgbenchdb < pgbench_transaction.sql

so you prefix each variable=value pair with a -v flagAnd the output:

BEGIN
UPDATE 1
 abalance 
----------
        8
(1 row)

UPDATE 1
UPDATE 1
INSERT 0 1
COMMIT

an alternative way to do this "parameter passing" is by including set statements inside the script, obviously this approach is less flexible.

BEGIN;
        \set tid 1
        \set bid 2
        \set aid 3
        \set delta 4

    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    END;

but useful nonetheless.There's an issue with using quotes, they must be escaped.For instance:

\set myvariable '\'somestring\'' SELECT * FROM FOO WHERE BAR = :myvariable

http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql

Postgresql - pgpool : num_init_children

2012-01-06T11:51:00.000-06:00

Yesterday I was hitting my head against a wall trying to figure out why pgbench got stuck while doing a simple test involving 50 concurrent clients. It turns out that I was overlooking the "num_init_children" parameter in the pgpool configuration.

pgpool-II accepts up to num_init_children concurrent sessions. If the number of concurrent sessions reach num_init_children, new session will be queued
pgbench creates concurrent connections before starting actual transactions. So if the number of concurrent transactions specified by "-c" exceeds num_init_children, pgbench will stuck because it will wait for pgpool accepting connections forever.
On the other hand PostgreSQL does not accept concurrent sessions more than max_connections.
If you want to test pgpool-II's connection queuing, you can use psql instead of pgbench.

Reference. Question number 10.
http://pgpool.projects.postgresql.org/FAQ.html

I guess I need to follow what's usually told in this cases : RTFM!
Then the default value for that parameter is 32, setting it up to 51:

transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 50
number of threads: 2
number of transactions per client: 10
number of transactions actually processed: 500/500
tps = 33.339290 (including connections establishing)
tps = 33.685822 (excluding connections establishing)

Not that bad, but it can improve, that's for sure.

Postgresql - Spotting differences between schemas

2012-01-06T02:01:00.000-06:00

How do you tell the difference between database schemas in an automatic fashion?

One approach is to make a sql dump from both schemas and apply the diff unix command.

http://linux.101hacks.com/linux-commands/diff-command-examples/

I'll use a toy database called switch_books

First make the first backup (the "before" backup)

-bash-4.2$ pg_dump switch_books > switch_books_before.sql
-bash-4.2$ psql switch_books

Then I create a dummy table:

switch_books=# create table fake_table (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fake_table_pkey" for table "fake_table"
CREATE TABLE
switch_books=# \q

And take a new shot (the after backup)

-bash-4.2$ pg_dump switch_books > switch_books_after.sql

# generate the diff file
-bash-4.2$ diff switch_books_before.sql switch_books_after.sql > switch_books_diff.sql

-bash-4.2$ cat switch_books_diff.sql | less

And reviewing the results:

164a165,175
> -- Name: fake_table; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
> --
>
> CREATE TABLE fake_table (
>     id integer NOT NULL
> );
>
>
> ALTER TABLE public.fake_table OWNER TO postgres;
>
> --
358a370,377
> -- Data for Name: fake_table; Type: TABLE DATA; Schema: public; Owner: postgres
> --
>
> COPY fake_table (id) FROM stdin;
> \.
>
>
> --
583a603,610
> -- Name: fake_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
> --
>
> ALTER TABLE ONLY fake_table
>     ADD CONSTRAINT fake_table_pkey PRIMARY KEY (id);
>
>

RAF mentioned today this utility in a conversation.

There are other tools that go one step beyond by generating a sql script that will make the differences go away.
http://apgdiff.startnet.biz/

Postgresql - Maintenaince. Cleaning up the pg_attribute table

2012-01-05T18:12:00.000-06:00

What is the pg_attribute table? http://www.postgresql.org/docs/9.1/static/catalog-pg-attribute.html

The catalog pg_attribute stores information about table columns. There will be exactly one pg_attribute row for every column in every table in the database. (There will also be attribute entries for indexes, and indeed all objects that have pg_class entries.)The term attribute is equivalent to column and is used for historical reasons.

Sometimes it could become "bloated", that is, it will have unnecessary information (due to invalid database objects) and some cleaning up would be in order for performance reasons.I found this reference about the issue:http://www.chasingnuts.com/2010/cleaning_pg_attribute_bloat/comment-page-1/#comment-54458As simple as this


VACUUM FULL  pg_catalog.pg_class;
REINDEX TABLE pg_catalog.pg_class;

Damn, life would be so fucking beautiful if we all shared what we know.

Life . Small time Oracle Certification

2012-01-03T02:42:00.000-06:00

I really hate conformism and self deception. I invested about two months on an Oracle certification course, and I don't think it was really worth it. The thing I loath the most is that they made us memorize what is called "a brain dump" to have the right to present the certification tests. So each one of us had a very easy time approving them. I kind of like Oracle and study on my own when I can't sleep (like just now), but it doesn't feel right ... they took all the joy, all the sense of achievement away from me. From the business standpoint I realize that in that way, they generate returning happy (and conformists) customers, and hence more profits, but it's just not right!
So,I say, screw you small timers! Shame on you!

Postgresql - Security : Enabling server-side SSL support

2012-01-03T00:37:00.003-06:00

In the last post, I made a client connect through a SSH tunnel in order to avoid sending plain text messages to the backend. Now I'm testing the enabling of SSL support on the postgresql server.

I'm following this walktrough:
http://vibhorkumar.wordpress.com/2011/07/17/how-to-enable-ssl-in-postgresqlppas/

And I've already executed successfully the first part.
Tip:
+ SSL support seems to be available in the binary packages of some distros (so you don't have to compile postgresql from source to have this feature, just enable ssl through the postgresql.conf file ssl = on in the respective step)

This is how it looks a successful connection to the server, using SSL (in spanish =-( ):

Now, how does this communication channel look to a sniffer ? ( wireshark for instance)

Well, following the TCP stream, you only get a bunch of non-readable rubish.

Wich is great! It would be awesome to break this encryption somehow ... nah, just kidding.

Linux - Which process is bound to a particular port

2012-01-02T19:20:00.004-06:00

I found this neat script, for you to use whenever in doubt about what process is bound to a particular port.

#!/bin/bash
# See Linux Server Hacks #54
# $Id: whosOnPort.sh,v 1.5 2007/03/28 06:50:09 oracle Exp $
#

port=$1
# get the process info (pid/name). restricted to tcp only
procinfo=$( netstat --numeric-ports -nlp 2> /dev/null | \
            grep ^tcp | grep -w ${port} | tail -n 1 | awk '{print $7}' )

case "${procinfo}" in
"")
  echo "No process listening on port ${port}"
  ;;

"-")
  echo "Process is running on ${port}, but current user does not have rights to see process information."
  ;;

*)
  echo "${procinfo} is running on port ${port}"
  ps uwep ${procinfo%/*}
  ;;

esac

Source

https://github.com/tardate/sources/blob/master/whosOnPort.sh

It parses netstat info.

Example:

wwwrun@linux-2561:~> ./whosOnPort.sh  9999
9591/pgpool is running on port 9999
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
wwwrun    9591  0.0  0.1  10000  4128 ?        S    18:55   0:00 /usr/local/bin/pgpool -f /usr/local/etc/pgpool.conf -F /usr/local/e

Mmm, alternatively you can grep the /etc/services file (I own that one to RAF). For instance:

postgres@debian1:~$ cat /etc/services | grep 5432
postgresql      5432/tcp        postgres        # PostgreSQL Database
postgresql      5432/udp        postgres

But I like the script mentioned above better as it gives you more useful info.

Microsoft - Licenciamiento. Multiplexing y Client Access License - Afterthoughts

2012-01-02T12:49:00.002-06:00

Estoy buscando una forma de darle la vuelta al asunto del multiplexing. Estas son mis ideas de momento :

Lamentablemente no hay demasiada información o feedback de usuarios que se refieran a experiencias y criterios (o workarounds) adoptados por empresas respecto a este asunto.
Creo que hasta aquí llega mi papel como abogado del diablo =-) . Supongo que es tiempo de consultar a un auditor de tecnología.

Postgresql - Security : SSH Tunneling your PostgreSQL communications

2012-01-02T01:48:00.003-06:00

In my previous entry, I highlighted the importance of having encrypted communications between the clients and the backend server. The first approach I'll attack, is to use the ssh tunneling facility as depicted on the official docs, I regard it as being the most straightforward option (or at least the fastest to implement).

http://www.postgresql.org/docs/9.1/static/ssh-tunnels.html

Testing scenario
+ one postgresql client running on a windows machine (IP 192.168.56.66)
+ one postgresql backend server running on fedora linux (IP 192.168.56.101)

Prerequisites:
+ A ssh server running where the postgresql backend server lives.
+ A linux account with which you can connect to the postgresql server host (and it's properly configured in the pg_hba.conf file (if necessary) .
+ Putty for windows
+ Having understood what the on-line postgresql documentation says on this matter.

This is the tutorial I'm using for the putty configuration:
http://oldsite.precedence.co.uk/nc/putty.html

- Note that I'm connecting through the port 22 (default port for ssh)

- 63333 is the port that will listen for connections on the client host, and it will be the port the I'll specify in the psql connection.

- Make the ssh connection to the host where the postgresql server is running (this is the tunnel).
http://img848.imageshack.us/img848/2262/82292019.th.png

- Using the ssh tunnel : Connect to the server using psql -h localhost -p 63333

Drawbacks:
+ Encryption is not enforced for the clients to be able to connect to the server.
+ I think that relying on the clients to set the tunnel might not be a good idea

Postgresql - Security : Sniffing PostgreSQL communications

2012-01-01T10:08:00.009-06:00

This is the kind of info that wireshark can give you about any particular unencrypted network message using the pgsql protocol:

Field name	Type	Description	Versions
pgsql.authtype	Signed 32-bit integer	Authentication type	1.0.0 to 1.6.4
pgsql.code	String	Code	1.0.0 to 1.6.4
pgsql.col.index	Unsigned 32-bit integer	Column index	1.0.0 to 1.6.4
pgsql.col.name	String	Column name	1.0.0 to 1.6.4
pgsql.col.typemod	Signed 32-bit integer	Type modifier	1.0.0 to 1.6.4
pgsql.condition	String	Condition	1.0.0 to 1.6.4
pgsql.copydata	Byte array	Copy data	1.0.0 to 1.6.4
pgsql.detail	String	Detail	1.0.0 to 1.6.4
pgsql.error	String	Error	1.0.0 to 1.6.4
pgsql.file	String	File	1.0.0 to 1.6.4
pgsql.format	Unsigned 16-bit integer	Format	1.0.0 to 1.6.4
pgsql.frontend	Boolean	Frontend	1.0.0 to 1.6.4
pgsql.hint	String	Hint	1.0.0 to 1.6.4
pgsql.key	Unsigned 32-bit integer	Key	1.0.0 to 1.6.4
pgsql.length	Unsigned 32-bit integer	Length	1.0.0 to 1.6.4
pgsql.line	String	Line	1.0.0 to 1.6.4
pgsql.message	String	Message	1.0.0 to 1.6.4
pgsql.oid	Unsigned 32-bit integer	OID	1.0.0 to 1.6.4
pgsql.oid.table	Unsigned 32-bit integer	Table OID	1.0.0 to 1.6.4
pgsql.oid.type	Unsigned 32-bit integer	Type OID	1.0.0 to 1.6.4
pgsql.parameter_name	String	Parameter name	1.0.0 to 1.6.4
pgsql.parameter_value	String	Parameter value	1.0.0 to 1.6.4
pgsql.password	String	Password	1.0.0 to 1.6.4
pgsql.pid	Unsigned 32-bit integer	PID	1.0.0 to 1.6.4
pgsql.portal	String	Portal	1.0.0 to 1.6.4
pgsql.position	String	Position	1.0.0 to 1.6.4
pgsql.query	String	Query	1.0.0 to 1.6.4
pgsql.routine	String	Routine	1.0.0 to 1.6.4
pgsql.salt	Byte array	Salt value	1.0.0 to 1.6.4
pgsql.severity	String	Severity	1.0.0 to 1.6.4
pgsql.statement	String	Statement	1.0.0 to 1.6.4
pgsql.status	Unsigned 8-bit integer	Status	1.0.0 to 1.6.4
pgsql.tag	String	Tag	1.0.0 to 1.6.4
pgsql.text	String	Text	1.0.0 to 1.6.4
pgsql.type	String	Type	1.0.0 to 1.6.4
pgsql.val.data	Byte array	Data	1.0.0 to 1.6.4
pgsql.val.length	Signed 32-bit integer	Column length	1.0.0 to 1.6.4
pgsql.where	String	Context	1.0.0 to 1.6.4

http://www.wireshark.org/docs/dfref/p/pgsql.html

This is for example one very innocent looking capture result of executing psql internal command \l (list databases):

Extended descriptions:

+ Well, not everything regarding sniffing communications is bad, I guess you can troubleshoot a variety of problems with its help.

But I'm a lazy kind of guy in the sense that I'd rather capture automatically any communication related to PostgreSQL and have it saved in a txt file. How to accomplish such thing?

One option is using tshark:
http://blog.timstoop.nl/2008/11/03/sniffing-postgresql-queries-with-tsharkwireshark/

Conclusions:
+ Added another tool for troubleshooting postgresql issues.
+ Of course it must be documented somewhere in the web, but using this technique you get a glance at what psql internal commands do.
+ Only very lame postgresql administrators don't encrypt postgresql messages for sensitive communications.

Postgresql - psql : list of available datatypes

2012-01-01T00:23:00.002-06:00

This post is just to honor the wide array of data types available in PostgreSQL. This list is available using psql's internal command \dT [pattern]

For instance:

postgres=# \dT+ *
                                                               Listado de tipos de dato
  Esquema   |           Nombre            |  Nombre interno  | Tamaño | Elementos |                            Descripción                            
------------+-----------------------------+------------------+--------+-----------+-------------------------------------------------------------------
 pg_catalog | "any"                       | any              | 4      |           | 
 pg_catalog | "char"                      | char             | 1      |           | single character
 pg_catalog | abstime                     | abstime          | 4      |           | absolute, limited-range date and time (Unix system time)
 pg_catalog | aclitem                     | aclitem          | 12     |           | access control list
 pg_catalog | anyarray                    | anyarray         | var    |           | 
 pg_catalog | anyelement                  | anyelement       | 4      |           | 
 pg_catalog | anyenum                     | anyenum          | 4      |           | 
 pg_catalog | anynonarray                 | anynonarray      | 4      |           | 
 pg_catalog | bigint                      | int8             | 8      |           | ~18 digit integer, 8-byte storage
 pg_catalog | bit                         | bit              | var    |           | fixed-length bit string
 pg_catalog | bit varying                 | varbit           | var    |           | variable-length bit string
 pg_catalog | boolean                     | bool             | 1      |           | boolean, 'true'/'false'
 pg_catalog | box                         | box              | 32     |           | geometric box '(lower left,upper right)'
 pg_catalog | bytea                       | bytea            | var    |           | variable-length string, binary values escaped
 pg_catalog | character                   | bpchar           | var    |           | char(length), blank-padded string, fixed storage length
 pg_catalog | character varying           | varchar          | var    |           | varchar(length), non-blank-padded string, variable storage length
 pg_catalog | cid                         | cid              | 4      |           | command identifier type, sequence in transaction id
 pg_catalog | cidr                        | cidr             | var    |           | network IP address/netmask, network address
 pg_catalog | circle                      | circle           | 24     |           | geometric circle '(center,radius)'
 pg_catalog | cstring                     | cstring          | var    |           | 
 pg_catalog | date                        | date             | 4      |           | date
 pg_catalog | double precision            | float8           | 8      |           | double-precision floating point number, 8-byte storage
 pg_catalog | fdw_handler                 | fdw_handler      | 4      |           | 
 pg_catalog | gtsvector                   | gtsvector        | var    |           | GiST index internal text representation for text search
 pg_catalog | inet                        | inet             | var    |           | IP address/netmask, host address, netmask optional
 pg_catalog | int2vector                  | int2vector       | var    |           | array of int2, used in system tables
 pg_catalog | integer                     | int4             | 4      |           | -2 billion to 2 billion integer, 4-byte storage
 pg_catalog | internal                    | internal         | 4      |           | 
 pg_catalog | interval                    | interval         | 16     |           | @  , time interval
 pg_catalog | language_handler            | language_handler | 4      |           | 
 pg_catalog | line                        | line             | 32     |           | geometric line (not implemented)
 pg_catalog | lseg                        | lseg             | 32     |           | geometric line segment '(pt1,pt2)'
 pg_catalog | macaddr                     | macaddr          | 6      |           | XX:XX:XX:XX:XX:XX, MAC address
 pg_catalog | money                       | money            | 8      |           | monetary amounts, $d,ddd.cc
 pg_catalog | name                        | name             | 64     |           | 63-character type for storing system identifiers
 pg_catalog | numeric                     | numeric          | var    |           | numeric(precision, decimal), arbitrary precision number
 pg_catalog | oid                         | oid              | 4      |           | object identifier(oid), maximum 4 billion
 pg_catalog | oidvector                   | oidvector        | var    |           | array of oids, used in system tables
 pg_catalog | opaque                      | opaque           | 4      |           | 
 pg_catalog | path                        | path             | var    |           | geometric path '(pt1,...)'
 pg_catalog | pg_node_tree                | pg_node_tree     | var    |           | string representing an internal node tree
 pg_catalog | point                       | point            | 16     |           | geometric point '(x, y)'
 pg_catalog | polygon                     | polygon          | var    |           | geometric polygon '(pt1,...)'
 pg_catalog | real                        | float4           | 4      |           | single-precision floating point number, 4-byte storage
 pg_catalog | record                      | record           | var    |           | 
 pg_catalog | refcursor                   | refcursor        | var    |           | reference to cursor (portal name)
 pg_catalog | regclass                    | regclass         | 4      |           | registered class
 pg_catalog | regconfig                   | regconfig        | 4      |           | registered text search configuration
 pg_catalog | regdictionary               | regdictionary    | 4      |           | registered text search dictionary
 pg_catalog | regoper                     | regoper          | 4      |           | registered operator
 pg_catalog | regoperator                 | regoperator      | 4      |           | registered operator (with args)
 pg_catalog | regproc                     | regproc          | 4      |           | registered procedure
 pg_catalog | regprocedure                | regprocedure     | 4      |           | registered procedure (with args)
 pg_catalog | regtype                     | regtype          | 4      |           | registered type
 pg_catalog | reltime                     | reltime          | 4      |           | relative, limited-range time interval (Unix delta time)
 pg_catalog | smallint                    | int2             | 2      |           | -32 thousand to 32 thousand, 2-byte storage
 pg_catalog | smgr                        | smgr             | 2      |           | storage manager
 pg_catalog | text                        | text             | var    |           | variable-length string, no limit specified
 pg_catalog | tid                         | tid              | 6      |           | (block, offset), physical location of tuple
 pg_catalog | time with time zone         | timetz           | 12     |           | time of day with time zone
 pg_catalog | time without time zone      | time             | 8      |           | time of day
 pg_catalog | timestamp with time zone    | timestamptz      | 8      |           | date and time with time zone
 pg_catalog | timestamp without time zone | timestamp        | 8      |           | date and time
 pg_catalog | tinterval                   | tinterval        | 12     |           | (abstime,abstime), time interval
 pg_catalog | trigger                     | trigger          | 4      |           | 
 pg_catalog | tsquery                     | tsquery          | var    |           | query representation for text search
 pg_catalog | tsvector                    | tsvector         | var    |           | text representation for text search
 pg_catalog | txid_snapshot               | txid_snapshot    | var    |           | txid snapshot
 pg_catalog | unknown                     | unknown          | var    |           | 
 pg_catalog | uuid                        | uuid             | 16     |           | UUID datatype
 pg_catalog | void                        | void             | 4      |           | 
 pg_catalog | xid                         | xid              | 4      |           | transaction id
 pg_catalog | xml                         | xml              | var    |           | XML content
(73 filas)

And if they're not enough, you can always define your very own data types.

Microsoft - Licenciamiento. Multiplexing y Client Access License - Introducción

2011-12-30T14:49:00.001-06:00

Estoy haciendo una pequeña investigación sobre el esquema de licenciamiento de Microsoft para sus productos server con especial énfasis a SQL server.

Uploaded with ImageShack.us

La verdad es que sale extremadamente caro incurrir en alguna infracción por incumplimiento de los términos de licenciamiento para los clientes que lo utilizan. Y pensar que hay alternativas gratuitas como Postgresql que igualan o superan las prestaciones de SQL server.

Postgresql - pgpool - a wary pgpool_failover script

2011-12-29T19:31:00.001-06:00

I came up with this script to issue automatic failover of the master node in the cluster.

#!/bin/bash
# Failover command for streaming replication
# If standby goes down, does nothing. 
# If primary goes down, create a trigger file so that one standby
# takes over the primary node role

failed_node=$1
old_master=$2
trigger_file=$3

standby1=10.0.0.201
standby2=10.0.0.202
standby3=10.0.0.113

if [ $failed_node = $old_master ]; then
#create the trigger file in the first available standby
#test for the next available standby 
CMD=`ssh postgres@$standby1 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby1 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
CMD=`ssh postgres@$standby2 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby2 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
CMD=`ssh postgres@$standby3 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby3 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
exit 1;
fi

I say it's wary as it tests whether the first standby node is up and running, if not tests if the second is,then the thrid... else returns a failure code (non cero integer).
Note: when a standby node (in constant recovery state) identifies the existence of the trigger file defined in the recovery.conf file it stops replicating from the allegedly dead master and becomes an independent server and enables write capabilities.

I used this piece of advice:
http://www.panix.com/~elflord/unix/bash-tute.html

Using Quotes to enclose your variables

Sometimes, it is a good idea to protect variable names in double quotes. This is usually the most important if your variables value either (a) contains spaces or (b) is the empty string. An example is as follows:

#!/bin/bash
X=""
if [ -n $X ]; then # -n tests to see if the argument is non empty
echo "the variable X is not the empty string"
fi

This script will give the following output:

the variable X is not the empty string

Why ? because the shell expands $X to the empty string. The expression [ -n ] returns true (since it is not provided with an argument). A better script would have been:

#!/bin/bash
X=""
if [ -n "$X" ]; then # -n tests to see if the argument is non empty
echo "the variable X is not the empty string"
fi

In this example, the expression expands to [ -n "" ] which returns false, since the string enclosed in inverted commas is clearly empty.

Sometimes scripts are a reflexion of the writer's personality =-)

Linux - change a network interface name

2011-12-29T16:42:00.000-06:00

http://ergodicthoughts.blogspot.com/2009/03/rename-interface-eth-in-linux.html

I need to have homogenous names for my network interfaces in the whole cluster. Some were named eth0 others eth1 and so on. Now all of them are called eth0, this unlikely requirement is due to the fact that getting the ip from the master server depends on one network interface called eth0.

Instructions:

Edit the file -
vi /etc/udev/rules.d/70-persistent-net.rules

as root or a power user and change entries with eth0 to whatever you desire.
udev support is available for most kernels in the 2.6 generation.

This is how I'm getting the ip info:

MASTERNODE=`/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'`

Postgresql - pgpool - getting it done without repmgr

2011-12-29T13:53:00.000-06:00

Formerly I had used repmgr to aid my recovery and failover scripts in pgpool, but recently changed my mind and now I'm removing that extra layer.

The recovery script should look like this now:

#!/bin/bash
 # first stage recovery
 # $1 datadir
 # $2 desthost
 # $3 destdir
 MASTERNODE=`/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'`
 CMD=`ssh postgres@$2 "pg_basebackup --pgdata=$3 --host=$MASTERNODE --xlog --port=5432"`
 CMD1=`ssh postgres@$2 "echo standby_mode = \'on\'  > $3/recovery.conf"`
 CMD2=`ssh postgres@$2 "echo primary_conninfo = \'host=$MASTERNODE port=5432 user=postgres password=pgsql\' >> $3/recovery.conf"`
 echo $CMD
 echo $CMD1
 echo $CMD2
exit 0

+ One thing that had me scratching my head a little was the single quote inside double quotes that are inside back ticks -> but then my old friend the back slash came to rescue. =-)

+ The issue that emerges now, is: how does pg_basebackup behave when the cluster directory is not empty?

+ I had previously modified the /etc/ssh/ssh_config parameter file so that user environment variables are permited. So I aggregated them in the ~/.ssh/environment file, this in order to enter only the binary name instead of the whole path. Well,second guessing I think that it's better to leave the ssh environment variables alone.

Postgresql - pg_archivecleanup

2011-12-28T14:03:00.000-06:00

I left open one question raised when configuring log shipping. How to prevent the wal directory from consuming all the available space?

http://www.postgresql.org/docs/devel/static/pgarchivecleanup.html

F.25. pg_archivecleanup

pg_archivecleanup is designed to be used as an archive_cleanup_command to clean up WAL file archives when running as a standby server (see Section 25.2). pg_archivecleanup can also be used as a standalone program to clean WAL file archives.

F.25.1. Usage

To configure a standby server to use pg_archivecleanup, put this into its recovery.conf configuration file:

archive_cleanup_command = 'pg_archivecleanup archivelocation %r'

where archivelocation is the directory from which WAL segment files should be removed.

It looks like you have to specify up to what file you intent to remove from the wal history.

Postgresql - System Administration Functions

2011-12-28T13:52:00.000-06:00

http://www.postgresql.org/docs/9.1/static/functions-admin.html

With postgresql you've got functions for :

Configuration Settings
Server Signalling
Backup Control
Recovery Information
Recovery Control
Database Object Size
Database Object Location
Generic File Access
Advisory Lock

The ones that I like the most:
pg_cancel_backend(pid int) Cancel a backend's current query
pg_reload_conf() Cause server processes to reload their configuration files
pg_is_in_recovery() True if recovery is still in progress.
pg_database_size(name) Disk space used by the database with the specified name
pg_indexes_size(regclass) Total disk space used by indexes attached to the specified table
pg_size_pretty(bigint) Converts a size in bytes into a human-readable format with size units
pg_table_size(regclass) Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)
pg_tablespace_size(oid) Disk space used by the tablespace with the specified OID
pg_tablespace_size(name) Disk space used by the tablespace with the specified name
pg_total_relation_size(regclass) Total disk space used by the specified table, including all indexes and TOAST data

One concept that caught my eye is "Advisory Lock"

Advisory locks are 'long-term, cooperative' locks. They are long-term in the sense that they do not follow mvcc rules, and are in fact completely orthogonal to transactional locks. This is a slightly long-winded way of saying that advisory locks are not released upon transaction commit/rollback and can be held outside of a transaction. This is noteworthy because advisory locks are one of the very few things that can be used to maintain session state without using persistent storage (prepared statements, temporary tables, and listen/notify also qualify here), and especially noteworthy because they are the only mechanism in PostgreSQL that I know that can affect the behavior of another session without consideration of either session's transactional state. Advisory locks are cooperative in that all users of them are on the honor system -- this is quite different from standard mvcc locks which follow very strict rules. Anything can be locked at any time. Advisory locks can be thought of as a low level interface to the internal locking engine and will generally need some scaffolding to make them useful.

I'll study them and I'll make some tests using them later.
http://merlinmoncure.blogspot.com/2006/12/postgresql-8.html

Postgresql - Sending wal segments to the standby servers

2011-12-28T13:10:00.000-06:00

I'm reviewing this function in the context of this error that it's being generated in one hot standby server with streaming replication:

postgres@debian1:~/data_sr/pg_log$ tail -f postgresql-2011-12-27_172357.log 
2011-12-27 17:54:45 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:54:55 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:54:55 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:54:55 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:55:05 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:55:05 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:55:05 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:55:15 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:55:15 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:55:15 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()

http://www.postgresql.org/docs/9.1/static/functions-admin.html

pg_last_xlog_receive_location()
text
Get last transaction log location received and synced to disk by streaming replication.
While streaming replication is in progress this will increase monotonically. If recovery has completed this will remain static at the value of the last WAL record received and synced to disk during recovery. If streaming replication is disabled, or if it has not yet started, the function returns NULL.

Apparently the recovery remains in progress eternally, as the recovery.conf file is still in the cluster (when the recovery process ends it should be renamed to recovery.done).
how to stop it?

I think, there's the issue:

The one thing that you absolutely must specify in recovery.conf is the restore_command, which tells PostgreSQL how to get back archived WAL file segments.

Example:
The simplest useful command is something like:
restore_command = 'cp /mnt/server/archivedir/%f %p'
which will copy previously archived WAL segments from the directory /mnt/server/archivedir.

http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

The thing with this parameter setting is that is optional for streaming replication.

26.1. Archive Recovery Settings

restore_command (string)

The shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery, but optional for streaming replication.

http://www.postgresql.org/docs/9.1/static/archive-recovery-settings.html#RESTORE-COMMAND

This document gave me very good insights about how streaming replication works "under the hood".
http://wiki.postgresql.org/images/9/90/FOSDEM2010-Streaming_Replication.pdf
And this one too:
http://www.korokithakis.net/posts/how-postgresqls-sr-streaming-replication-works/

Solution:
Send wal segments from master to slaves and include the restore_command to the recovery.conf file in the slaves. Sending wal segments to the standby servers assures that if they fall too far behind from the master they will be able to still resync using the archived wall segments.
The solution implies very small modifications:

On the master server, in the postgresql.conf change this:

archive_command = 'cd .'

to

archive_command ='scp %p postgres@10.0.0.201:/tmp/%f' # command to use to archive a logfile segment

and on the standby, add this to the recovery.conf

restore_command ='cp /tmp/%f "%p"'

and after that:

postgres@debian1:~/data_sr/pg_log$ tail -f postgresql-2011-12-28_130051.log 
2011-12-28 13:00:51 CST   LOG:  database system was interrupted; last known up at 2011-12-28 12:55:12 CST
2011-12-28 13:00:51 CST   LOG:  creating missing WAL directory "pg_xlog/archive_status"
2011-12-28 13:00:51 CST   LOG:  entering standby mode
2011-12-28 13:00:51 CST   LOG:  restored log file "000000010000000000000020" from archive
2011-12-28 13:00:52 CST   LOG:  redo starts at 0/20000020
2011-12-28 13:00:52 CST   LOG:  consistent recovery state reached at 0/21000000
2011-12-28 13:00:52 CST   LOG:  database system is ready to accept read only connections
cp: cannot stat `/tmp/000000010000000000000021': No such file or directory
2011-12-28 13:00:52 CST   LOG:  streaming replication successfully connected to primary

No more output, which usually means that everything is just fine. But the recovery.conf file is still there ... well, no errors, no problem, good enough to me.

It remains in recovery state:

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

Now, how to prevent the wal directory in the standby from becoming bloated, waste and potentially consume all the space?

Cellprogramming - Multicore Programming Primer: Learn and Compete in Programming the PLAYSTATION®3 Cell Processor.

2011-12-27T19:10:00.000-06:00

I found about this site just now. I had been procrastinating the cell processor for a while due to some compromises such as the stupid Oracle OCA certification, but now I'm going to commit myself to this proyect.

http://groups.csail.mit.edu/cag/ps3/index.shtml

Parallel programming has been an issue I've been interested in since I took my computer architecture course at UAM Azcapotzalco, and I intent to get very good at it =-).

I'm re-posting a small mental map I did before deleting my previous blog.

Here another interesting resources:
http://users.ece.gatech.edu/lanterma/mpg08/
http://users.ece.gatech.edu/lanterma/mpg08/CBE_Programming_Tutorial_v3.0.pdf
http://www.ibm.com/developerworks/power/library/l-powarch/index.html

SDK's:
http://www.ief.u-psud.fr/~lacas/Download/Download.html

And more:
http://www.ps3devwiki.com/index.php?title=Cell_Programming_IBM

Postgresql - pg_basebackup - forget about rsync ?

2011-12-27T17:56:00.000-06:00

What's new in PostgreSQL 9.1
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1

pg_basebackup
http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html

this is amazing stuff!

pg_basebackup is used to take base backups of a running PostgreSQL database cluster. These are taken without affecting other clients to the database, and can be used both for point-in-time recovery (see Section 24.3) and as the starting point for a log shipping or streaming replication standby servers (see Section 25.2).
pg_basebackup makes a binary copy of the database cluster files, while making sure the system is automatically put in and out of backup mode automatically.
The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a user having REPLICATION permissions (see Section 20.2), and the user must be granted explicit permissions in pg_hba.conf. The server must also be configured with max_wal_senders set high enough to leave at least one session available for the backup.

Then let's make some dummy example:
+Let's have a server configured with the following options:

listen_addresses='*'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'       # we can also use exit 0, anything that
                           # just does nothing
max_wal_senders = 10
wal_keep_segments = 5000     # 80 GB required on pg_xlog
hot_standby = on

Basic replication options taken from
https://github.com/greg2ndQuadrant/repmgr

1. Creating a database with data:

postgres@linux-2561:~> createdb pgbenchdb

postgres@linux-2561:~> pgbench -i -F 10 -s 10 -h 10.0.0.113 -p 5432 pgbenchdb
pgbenchdb=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size   | Description 
--------+------------------+-------+----------+---------+-------------
 public | pgbench_accounts | table | postgres | 1302 MB | 
 public | pgbench_branches | table | postgres | 40 kB   | 
 public | pgbench_history  | table | postgres | 0 bytes | 
 public | pgbench_tellers  | table | postgres | 80 kB   | 
(4 rows)


pgbenchdb=# SELECT pg_size_pretty(pg_database_size('pgbenchdb'));
 pg_size_pretty 
----------------
 1326 MB
(1 row)

2. Make a base backup on the standby node:

postgres@debian1:~$ time pg_basebackup --pgdata=/var/lib/postgresql/data_sr --xlog --progress --verbose --host=10.0.113 --port=5432 
xlog start point: 0/1B000020
1392758/1392758 kB (100%), 1/1 tablespace                                    
xlog end point: 0/1B0000C4
pg_basebackup: base backup completed

real    0m36.909s
user    0m0.740s
sys     0m11.565s

Not bad for a 1.3GB database !

3. Set the backup server to behave as a hot standby streaming replication server, by creating a recovery.conf file in $PGDATA.

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.113 port=5432 user=postgres password=pgsql'

4. Startup the standby server

pg_ctl start

5. Query on the master the replication status:

pgbenchdb=# \x 
Expanded display is on.
pgbenchdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
procpid          | 7776
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.0.0.201
client_hostname  | 
client_port      | 56744
backend_start    | 2011-12-27 17:23:57.15636-06
state            | streaming
sent_location    | 0/1E000120
write_location   | 0/1E000120
flush_location   | 0/1E000120
replay_location  | 0/1E000120
sync_priority    | 0
sync_state       | async

6. Make some changes on the master

postgres@linux-2561:~> pgbench -c 50 -d -j 2 -t 100 -h 10.0.0.113 pgbenchdb
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 50
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 89.336188 (including connections establishing)
tps = 89.461080 (excluding connections establishing)

pgbenchdb=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size   | Description 
--------+------------------+-------+----------+---------+-------------
 public | pgbench_accounts | table | postgres | 1302 MB | 
 public | pgbench_branches | table | postgres | 168 kB  | 
 public | pgbench_history  | table | postgres | 792 kB  | 
 public | pgbench_tellers  | table | postgres | 248 kB  | 
(4 rows)

6. Log in to the standby and see if changes are there (in a very lazy fashion).

postgres=# \c pgbenchdb
You are now connected to database "pgbenchdb" as user "postgres".
pgbenchdb=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size   | Description 
--------+------------------+-------+----------+---------+-------------
 public | pgbench_accounts | table | postgres | 1302 MB | 
 public | pgbench_branches | table | postgres | 168 kB  | 
 public | pgbench_history  | table | postgres | 792 kB  | 
 public | pgbench_tellers  | table | postgres | 248 kB  | 
(4 rows)

pgbenchdb=# insert into pgbench_history (tid) values (generate_series(1,1000));
ERROR:  cannot execute INSERT in a read-only transaction

Other references:

http://postgresql.ru.net/manual/pgbench.html
http://www.postgresql.org/docs/9.1/static/warm-standby.html
http://www.postgresql.org/docs/8.1/interactive/backup-online.html
http://wiki.postgresql.org/wiki/Streaming_Replication

Then, what's rsync? =-)

TODO:
What the hell are these errors about?

postgres@debian1:~/data_sr/pg_log$ tail -f postgresql-2011-12-27_172357.log 
2011-12-27 17:54:45 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:54:55 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:54:55 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:54:55 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:55:05 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:55:05 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:55:05 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()
2011-12-27 17:55:15 CST postgres postgres ERROR:  recovery is in progress
2011-12-27 17:55:15 CST postgres postgres HINT:  WAL control functions cannot be executed during recovery.
2011-12-27 17:55:15 CST postgres postgres STATEMENT:  SELECT pg_current_xlog_location()

Postgresql - streaming replication - new features in 9.1

2011-12-26T19:59:00.000-06:00

I just found this info:

What's new in 9.1
pg_stat_replication makes non-wizard monitoring possible
max_standby_*_delay can be big
hot_standby_feedback makes MVCC style snapshot export easy
Base backups possible using the database connection
Synchronous replication - Improvements in b-tree delete handling

+This makes the functions of repmgr kind of redundant (most of them, and those that excel the aforementioned are provided by pgpool) , my rationale behind it's usage was its rsync optimization, but now you're able to manage base backups through the database connection, I'll give that a try.

+To leverage repmgr was really easy but removing additional ( and maybe unnecessary) layers is the best approach (in my opinion).
+Also, synchronous replication is very good news, with it you can really assure a great deal of reliability.

http://www.2ndquadrant.com/static/2quad/media/pdfs/talks/char11/FastRepmgrScaling.pdf

Postgresql - System Information Functions - querying for an IP

2011-12-26T13:37:00.000-06:00

I need to ask the PostgreSQL server for its IP. One answer I found is using one of the "System Information Functions" called inet_server_addr().

The big gotcha regarding this function is that it returns NULL should the connection be made via a unix-domain socket.

inet_client_addr returns the IP address of the current client, and inet_client_port returns the port number. inet_server_addr returns the IP address on which the server accepted the current connection, and inet_server_port returns the port number. All these functions return NULL if the current connection is via a Unix-domain socket.

As a side note, the array of functions to get information about the system is pretty cool.

http://www.postgresql.org/docs/9.1/static/functions-info.html

Among many other things you can issue queries about the session, access privileges, comments, schema visibility, the catalog, transaction id's and snapshots.

I guess I'll need to do it the linux way or figure out how to use something else than unix-domain sockets for my connections.

This sort of does the trick:

/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'

But it relies on two assumptions: that there's an eth0 interface and that the node is connected through that interface to the cluster's network.

http://www.cyberciti.biz/faq/bash-shell-command-to-find-get-ip-address/

Postgresql -plpgsql. Generating primes as SETOF RECORD

2011-12-25T23:00:00.000-06:00

In my previous attempt I returned the result by just printing the prime numbers as messages, but obviously you couldn't use them for further processing. One solution is to return the result as a SETOF RECORD.

CREATE OR REPLACE FUNCTION primes( upLim INTEGER ) 
RETURNS SETOF RECORD  AS
$$
DECLARE
nums boolean [];
BEGIN
 
FOR i IN 2..upLim LOOP
nums[i] = true;
END LOOP;
FOR i IN 2..TRUNC(|/upLim) LOOP
 IF nums[i] = true
 THEN
  FOR j IN 2..upLim LOOP
  nums[i*j] = false;
  EXIT WHEN i * j > upLim;
  END LOOP;
 END IF;
END LOOP;
 
FOR i IN 2..upLim LOOP
 IF nums[i] = true THEN
 RETURN QUERY SELECT i;
 END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;


SELECT * from primes(1000) as foo(prime int);

I'm getting the impression of plpgsql being a very dense topic ... but I'm getting to love it too!

Linux - the DISPLAY environment variable

2011-12-25T13:00:00.000-06:00

One interesting option regarding the DBCA (Database Configuration Assistant in Oracle ) , and I guess from many other programs, is to specify them through the DISPLAY environment variable where to send the windows they open .

This is standard X Window System—you set an environment variable DISPLAY to tell the program where to send the windows it opens. For example,export DISPLAY=10.10.10.65:0.0 will redirect all X windows to the machine identified by IP address 10.10.10.65, no matter which machine you are actually running the DBCA on.

This is great stuff! and it's kind of weird me just discovering it.

Linux - Simulating global variables - the /etc/profile file.

2011-12-25T12:29:00.000-06:00

From some book I'm reading:

To simulate setting what might be thought of as “global” variables applying to all sessions by all users, set them in the /etc/profile file, which is executed at each logon.

A seemingly good reference:

http://www.hot.ee/airm/linux/BLFS6.3/postlfs/profile.html

After the stupid oca certification I'll get one of Linux, but a real one, not bullshit.

Postgresql - plpgsql - Generating primes

2011-12-25T02:09:00.000-06:00

I deemed it appropriate to start with plpgsql once and for all. So this is the plpgsql version of the algorithm (Eratosthenes' sieve) I implemented with javascript. This is my first plpgsql function ever, so I guess many improvements/corrections are in order.

CREATE OR REPLACE FUNCTION primes( upLim INTEGER ) RETURNS VOID AS
$$
DECLARE
nums boolean [];
BEGIN

FOR i IN 2..upLim LOOP
nums[i] = true;
END LOOP;
FOR i IN 2..TRUNC(|/upLim) LOOP
 IF nums[i] = true 
 THEN
  FOR j IN 2..upLim LOOP
  nums[i*j] = false;
  EXIT WHEN i * j > upLim;
  END LOOP;
 END IF;
END LOOP;

FOR i IN 2..upLim LOOP
 IF nums[i] = true THEN
 RAISE NOTICE '% ',i;
 END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT primes(100000) ;

Total query runtime: 242672 ms.
1 row retrieved.

Kinda slow, I must admit...mmmm

javascript - Generating primes

2011-12-24T10:57:00.000-06:00

I'm interested in learning and mastering javascript, so I'll be writing some toy programs once in a while. Of course, the greater the upper limit the longer it will take to calculate them.



 
 
 


Generador de numeros primos 

Proporciona el limite superior

Generador de numeros primos

Proporciona el limite superior

Postgresql - pgpool - analysis of the recovery, failover and failback operations

2011-12-23T19:02:00.003-06:00

I realized I had misunderstood the concepts related to the administration of a pgpool cluster. I have summarize those concepts here.

Recovery ->
Commands (1st stage and 2nd stage) run by the master to incorporate a new slave node.

These scripts are run by the postgresql server (naturally, by the same user that started the instance...right? )

Thus the recovery procedure requires that the primary server is up and running.
The cluster variables such as %m %M are not available for this operation. As the parameters (three) are passed by the pgpool_recovery function.

% pgpool_recovery datadir remote_host remote_datadir
datadir:        Database cluster path of a master node.
remote_host:    Hostname of a recovery target node.
remote_datadir: Database cluster path of a recovery target node.

What do the provided examples do?
recovery_1st_stage_command = 'basebackup.sh' -> Performs a base backup of the primary and sends it to the new (or in recovery) node
recovery_2nd_stage_command = '' -> does nothing

This is what happens behind the curtain:
For your reference, here are the steps taken in the recovery procedure.

Pgpool-II connects to primary server's template1 database as user = recovery_user, password = recovery_password.
Primary server executes pgpool_recovery function.
pgpool_recovery function executes recovery_1st_stage_command. Note that PostgreSQL executes functions with database cluster as the current directory. Thus recovery_1st_stage_command is executed in the database cluster directory.
Primary server executes pgpool_remote_start function. This function executes a script named "pgpool_remote_start" in the database cluster directory, and it executes pg_ctl command on the standby server to be recovered via ssh. pg_ctl will start postmaster in background. So we need to make sure that postmaster on the standby actually starts.
pgpool-II tries to connect to the standby PostgreSQL as user = recovery_user and password = recovery_password. The database to be connected is "postgres" if possible. Otherwise "template1" is used. pgpool-II retries for recovery_timeout seconds. If success, go to next step.
If failback_command is not empty, pgpool-II parent process executes the script.
After failback_command finishes, pgpool-II restart all child processes.

+ The unusual thing for me at this point is that this functions are executed by the postgresql server. I need to analyze how does it do it.

This is the signature for the pgpool functions:
template1=# \df
List of functions
Schema |         Name         | Result data type | Argument data types |  Type  
--------+----------------------+------------------+---------------------+--------
public | pgpool_recovery      | boolean          | text, text, text    | normal
public | pgpool_regclass      | oid              | cstring             | normal
public | pgpool_remote_start  | boolean          | text, text          | normal
public | pgpool_switch_xlog   | text             | text                | normal
public | pgpool_walrecrunning | boolean          |                     | normal
(5 rows)
+ pgpool_remote_start has two arguments:
DEST=$1 -> the slave server
DESTDIR=$2 -> the cluster (datadir) on the slave server

Then, this is why you can't specify parameters for these commands in the pgpool.conf file, as they're passed implicitly.

Now, where the hell is this script located? It must be present in the database cluster (datadir) where the master is running).

Corollary. my recovery_1st_stage_command would look like this:
#!/bin/bash
# first stage recovery  
# $1 datadir
# $2 desthost
# $3 destdir

#as I'm using repmgr it's not necessary for me to know datadir(master) $1
RECOVERY_NODE=$2
CLUSTER_PATH=$3
#repmgr needs to know the master's ip
MASTERNODE=`/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'`

cmd1=`ssh postgres@$RECOVERY_NODE "repmgr -D $CLUSTER_PATH --force standby clone $MASTERNODE"`
echo $cmd1  
#-------------------------------------------------------------------------------------------------
The location of the failback and failover scripts is not relative to the database cluster, the absolute path is needed, these are executed by the apache (or wwwrun ) user.

Failback ->
failback_command. command to run when a node is attached. If not empty, pgpool-II parent process executes the script.
After it finishes,child processes are restarted.

Failover ->
failover_command.
command to run when a node is detached
--an alert maybe ?
side effects.
1. pgpool kills all its child processes, which will in turn terminate all active sessions to pgpool
2. pgpool invokes the failover_command and waits for its completion
3. after this, pgpool starts new child processes and is ready again to accept connection from clients

follow_master_command.
command to run only after a master failover.Typically the command should be used to recover the slave from the new master by call the pcp_recovery_node command for example
1. If follow_master_command is not empty, when a master failover is completed:
a) pgpool degenerates all nodes except the new master
b) starts new child processes to be ready to accept connections from clients
c) after this, pgpool run the command set into the 'follow_master_command' for each degenerated nodes ( typically the command should be used to recover the slave from the new master by call the pcp_recovery_node command for example ).

Other concepts
What do we understand for node degeneration? disconnection from the cluster.

For the failover_command, failback_command and follow_master_command you can use these parameters in your scripts:

Special character Description
%d Backend ID of an attached node.
%h Hostname of an attached node.
%p Port number of an attached node.
%D Database cluster path of an attached node.
%M Old master node
%m New master node
%H Hostname of the new master node.
%P Old primary node ID.
%% '%' character

They're not available for the recovery commands.

In summary:
Recovery. applies when attaching a new slave node, or reattaching a previously detached slave node, the master is required to be up and running.
Failback. command that runs when a node is attached (or after a recovery)
Failover. when a node is detached (master or slave)

Possible failover strategy.
1. If the master is detached, promote the first slave node that is available.
2. Degenerate all the slave nodes except the new master.
3. Tell all the other nodes to now follow the new master.

Postgresql - pgpool, repmgr - the failback process

2011-12-23T16:08:00.000-06:00

The process of failback refers to attaching a formerly detached (due to failover) back to the cluster.

Using repmgr the process of failback, comes to this:

repmgr -D /var/lib/pgsql/9.0 --force standby clone node1

The rationale behind this:

And if a previously failed node becomes available again, such as the lost node1 above, you can get it to resynchronize by only copying over changes made while it was down . That happens with this what's called a forced clone, which overwrites existing data rather than assuming it starts with an empty database directory tree:

This can be much faster than creating a brand new node that must copy over every file in the database.

+ Of course, in the detached node, the database server is in shutdown state. Then you do the force clone and then start the database up.

Behind the scenes I guess repmgr writes the appropriate recovery.conf file so as to tell the newly incorporated node to follow the cluster's master.

Linux - whereis / type

2011-12-23T16:04:00.000-06:00

It's incredible that up to this day I lived without knowing about these commands.

You can use "type" or "whereis" command to find out which command shell executes and to print binary (command) file location for specified command.

http://www.cyberciti.biz/tips/find-linux-unix-command-location.html

For instance:

postgres@linux-2561:~> whereis repmgr
repmgr: /usr/bin/repmgr /usr/bin/X11/repmgr
postgres@linux-2561:~> type -a repmgr
repmgr is /usr/bin/repmgr
repmgr is /usr/bin/X11/repmgr

Update: the 'which' utility seems to accomplish the same.
http://linux.about.com/library/cmd/blcmdl1_which.htm

Postgresql - repmgr: Promoting standby repmgr: restarting server using pg_ctl sh: pg_ctl: not found . Can't restart PostgreSQL server

2011-12-22T20:00:00.000-06:00

I was issuing this command through a remote ssh session. repmgr needs pg_ctl and pg_config to be in the PATH environment variable.
Issue: through ssh you only can use (by default) some env. variables that are not necessarily all the variables you need:

for instance, in my linux box with the postgres user:

postgres@linux-2561:~> ssh localhost env
LESSKEY=/etc/lesskey.bin
NNTPSERVER=news
MANPATH=/usr/local/man:/usr/local/share/man:/usr/share/man
XDG_SESSION_ID=38
HOSTNAME=linux-2561.site
XKEYSYMDB=/usr/X11R6/lib/X11/XKeysymDB
HOST=linux-2561.site
SHELL=/bin/bash
PROFILEREAD=true
HISTSIZE=1000
SSH_CLIENT=::1 53239 22
MORE=-sl
USER=postgres
XNLSPATH=/usr/share/X11/nls
HOSTTYPE=i386
CONFIG_SITE=/usr/share/site/i686-pc-linux-gnu
FROM_HEADER=
PAGER=less
CSHEDIT=emacs
XDG_CONFIG_DIRS=/etc/xdg
MINICOM=-c on
MAIL=/var/mail/postgres
PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/opt/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin
CPU=i686
SSH_SENDS_LOCALE=yes
INPUTRC=/etc/inputrc
PWD=/var/lib/pgsql
LANG=en_US.UTF-8
PYTHONSTARTUP=/etc/pythonstart
GPG_TTY=not a tty
QT_SYSTEM_DIR=/usr/share/desktop-data
SHLVL=1
HOME=/var/lib/pgsql
SDL_AUDIODRIVER=pulse
ALSA_CONFIG_PATH=/etc/alsa-pulse.conf
LESS_ADVANCED_PREPROCESSOR=no
OSTYPE=linux
XCURSOR_THEME=DMZ
WINDOWMANAGER=/usr/bin/startkde
G_FILENAME_ENCODING=@locale,UTF-8,ISO-8859-15,CP1252
LESS=-M -I -R
MACHTYPE=i686-suse-linux
LOGNAME=postgres
CVS_RSH=ssh
XDG_DATA_DIRS=/usr/local/share:/usr/share
SSH_CONNECTION=::1 53239 ::1 22
LESSOPEN=lessopen.sh %s
XDG_RUNTIME_DIR=/run/user/postgres
LESSCLOSE=lessclose.sh %s %s
G_BROKEN_FILENAMES=1
COLORTERM=1
_=/usr/bin/env

Solution:

This can be achieved in 2 steps:
- Add ‘PermitUserEnvironment yes’ to your sshd_config file and restart the ssh service.
Rem: Default value is set to ‘No’.
Update: on opensuse this parameter is set into the /etc/ssh/sshd_config file
- Define your environment variables in .ssh/environment on the target host.
eg LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib

Reference:
http://www.netexpertise.eu/en/ssh/environment-variables-and-ssh.html

Once followed the aforementioned steps, everything goes just fine =-).
repmgr: Promoting standby
repmgr: restarting server using pg_ctl
repmgr: STANDBY PROMOTE successful. You should REINDEX any hash indexes you have.

webProgramming - BookInfo.0.5

2011-12-22T19:46:00.000-06:00

I'm just making a back up of the last version, some improvements are in order but I achieved the functionality I was expecting, I'm leaving the key, should someone be interested in using it.

Don't forget to download jquery-1.7.1.min.js and jquery.jsonp-2.2.0.min.js in the same folder where the html file lives.

<html>
<head>
<style type="text/css">
    .inputtext { width: 754px; height: 154px; }
</style>
    <script type="text/javascript" src="jquery-1.7.1.min.js"></script>
    <script type="text/javascript" src="jquery.jsonp-2.2.0.min.js"></script>
    
    <script type="text/javascript">
        function nvl(node){
            return node == undefined ? " " : node;
        }
    </script>
    
<script type="text/javascript" >
        function getBook(isbn) {
            $.jsonp({
                "url": "https://www.googleapis.com/books/v1/volumes?key=amp;callback=?&q=isbn:"+ isbn,
                "success": function(response){
                    console.log("sucess ,isbn: "); 
                    console.log(isbn);
                    if (response.totalItems > 0){
                    for (var i = 0; i < response.items.length; i++) {
                    var item = response.items[i];
                    // in production code, item.text should have the HTML entities escaped.
                    document.getElementById("content").innerHTML += " <br> " +isbn+ " | "
                    + nvl(item.volumeInfo.title) +" | "
                    + nvl(item.volumeInfo.authors) +" | "
                    + nvl(item.volumeInfo.publisher) +" | "
                    + nvl(item.volumeInfo.publishedDate) +" | "
                    + nvl(item.volumeInfo.description) + " | "
                    + nvl(item.volumeInfo.pageCount) +" | "
                    + nvl(item.volumeInfo.printType) +" | "
                    + nvl(item.volumeInfo.categories) +" | "
                    + nvl(item.volumeInfo.averageRating) +" | "
                    + nvl(item.volumeInfo.ratingsCount) + " | "
                    + nvl(item.volumeInfo.language) + " | "
                    + nvl(item.volumeInfo.previewLink)  + " | "
                    + nvl(item.volumeInfo.infoLink) + " | ";
                    
                    if (item.volumeInfo.imageLinks != undefined){
                    document.getElementById("content").innerHTML += 
                    nvl(item.volumeInfo.imageLinks.smallThumbnail) + " | " 
                    + nvl(item.volumeInfo.imageLinks.thumbnail) + " | " ;
                    }
                    else document.getElementById("content").innerHTML +=  " | | ";
                    
                    if (item.volumeInfo.industryIdentifiers != undefined){
                    document.getElementById("content").innerHTML += 
                    nvl(item.volumeInfo.industryIdentifiers[0].type) + " | "
                    + nvl(item.volumeInfo.industryIdentifiers[0].identifier) + " | "
                    + nvl(item.volumeInfo.industryIdentifiers[1].type) + " | "
                    + nvl(item.volumeInfo.industryIdentifiers[1].identifier) + " | "
                    }
                    else document.getElementById("content").innerHTML +=  " | | | |";
                    
                    
                    if (item.saleInfo != undefined){
                    document.getElementById("content").innerHTML += 
                    nvl(item.saleInfo.country) + " | "
                    + nvl(item.saleInfo.saleability) + " | "
                    + nvl(item.saleInfo.isEbook) + " | ";
                    
                    }
                    else document.getElementById("content").innerHTML +=  " | | |";
                    
                    
                    document.getElementById("content").innerHTML += "<br> <br>";
                   
                    }
                }
                else
                {
                document.getElementById("content").innerHTML += isbn 
                + "||||||||||||||||||||||| <br> <br>";
                }    
                },
                "error": function(d,msg) {
                    alert("Could not find book ");
                }
            });
        }
    </script>   
    <script type="text/javascript">
    function parseInput(){
        var input = document.getElementById("isbnList").value.split('\n');
        for (i=0; i< input.length ; i++){
        console.log(input[i]);
        getBook(input[i].trim());
    }
    }
    </script>
    
</head>
<title>Books API Example</title>
Introduce el ISBN
 <br>

<textarea id='isbnList' class='inputtext'> </textarea>
 <br>

<button type="button" onclick='parseInput()'>Click</button>
<div id="content"></div>

<html>

Linux - making wwwrun a real user

2011-12-22T16:54:00.000-06:00

I needed to give the apache server process (wwwrun in opensuse, apache in other distros) , treatment of a regular user , as it's the one who runs commands behind pgpooladmin. I need to generate it's public key in order for him to be able to run remote commands on all the nodes it has access to.

It boils down to:

If you REALLY want to do all this, change the 'wwwrun' user line in /etc/passwd so that /bin/false is set to /bin/bash, and then set the password for that user (`passwd wwwrun`) and off you go.

A word of advise here is that there are many security concerns when carrying on with such unusual proceedings, but I'm on the testing stage and security is not by the moment an issue.

http://forums.opensuse.org/english/get-technical-help-here/applications/397685-logging-user-wwwrun.html

webProgramming - The Google Book Search API v1 - Part 2

2011-12-22T07:22:00.000-06:00

This is my first working code for the small application I'm working on. It would have taken me so much more time if weren't for HJRT.

Note the use of jquery-1.7.1.min.js and jquery.jsonp-2.2.0.min.js

<html>
<head>
<style type="text/css">
    .inputtext { width: 754px; height: 154px; }
</style>
    <script type="text/javascript" src="jquery-1.7.1.min.js"></script> 
    <script type="text/javascript" src="jquery.jsonp-2.2.0.min.js"></script> 
<script type="text/javascript" > 
        function getBook(isbn) {
            $.jsonp({
                "url": "https://www.googleapis.com/books/v1/volumes?callback=?&q=isbn:"+ isbn,
                "success": function(response){
                    console.log("sucess ,isbn: ");
                    console.log(isbn);
                    for (var i = 0; i < response.items.length; i++) {
                    var item = response.items[i];
                    // in production code, item.text should have the HTML entities escaped.
                    document.getElementById("content").innerHTML += " " +isbn+ " | "
                    + item.volumeInfo.industryIdentifiers[0].type + " | "
                    + item.volumeInfo.industryIdentifiers[0].identifier + " | "
                    + item.volumeInfo.industryIdentifiers[1].type + " | "
                    + item.volumeInfo.industryIdentifiers[1].identifier + " | "
                    + item.volumeInfo.title +" | " 
                    + item.volumeInfo.authors +" | "
                    + item.volumeInfo.publisher +" | "
                    + item.volumeInfo.publishedDate +" | "
                    + item.volumeInfo.description + " | "
                    + item.volumeInfo.pageCount +" | "
                    + item.volumeInfo.printType +" | "
                    + item.volumeInfo.categories +" | "
                    + item.volumeInfo.averageRating +" | "
                    + item.volumeInfo.ratingsCount + " | "
                    + item.volumeInfo.imageLinks.smallThumbnail +" | "
                    + item.volumeInfo.imageLinks.thumbnail + " | "
                    + item.volumeInfo.language + " | "
                    + item.volumeInfo.previewLink  + " | "
                    + item.volumeInfo.infoLink + " | "
                    + item.saleInfo.country + " | "
                    + item.saleInfo.saleability + " | "
                    + item.saleInfo.isEbook + " ~ ";
                    }
                    
                },
                "error": function(d,msg) {
                    console.log("Error");
                    alert("Could not find book ");
                }
            });
        }
    </script>    
    <script type="text/javascript">
    function parseInput(){
        var input = document.getElementById("isbnList").value.trim();
        console.log(input);
        getBook(input);
    }
    </script>
</head>
<title>Books API Example</title>
Introduce el ISBN 
<br>
<textarea id='isbnList' class='inputtext'> </textarea>
<br>
<button type="button" onclick='parseInput()'>Click</button>
<div id="content"></div>

<html>

Firebug was of invaluable help as well
https://addons.mozilla.org/es-es/firefox/addon/firebug/

One point to have in mind is that you'll need an access key from google if you are to use the API on a regular basis.

TODO:
+ to genuinely receive several ISBN as currently is only accepting one
+ trim any new line character that might be inside of any of the fields
http://www.webtoolkit.info/javascript-trim.html

Postgresql - streaming replication, trigger file vs. repmgr 'promote' command

2011-12-21T11:52:00.000-06:00

The trigger file is a file that get's created ( it's up to us when) when you want a failover to happen (i.e. to make the streaming replication stopped), hence, the slave that 'discovers' the trigger file becomes a master or independent node.

Such file location is specified in the recovery.conf configuration file.

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/path_to/trigger'

I suppose the server continuously monitors for the presence of such file.

In the example I'm following both master and slave run on the same machine on different ports, so

the failover script creates the trigger file locally, so as I'm running the master on one machine and the slave on another, I'll need to create the trigger file remotely to one of the slaves.

One question remains: ¿how to tell repmgr where to look for the trigger file?

Well, it turns out to not being necessary as a "promote" command is available in repmr."Such command is designed to be more efficient"

http://groups.google.com/group/repmgr/browse_thread/thread/cdb463bc399e1842?pli=1

    On Fri, Aug 12, 2011 at 11:35 PM, Joshua Sierles ...@gmail.com> wrote: > Is there an advantage to using repmgr's 'promote' command on the slave since it requires a restart? What's the difference from using the 'trigger file' method?
    repmgr follow is designed to be fast. That requires you to use repmgr
     promote to make that work correctly.
     The trigger file works, but will cause problems when running multiple standbys.


    --
     Simon Riggs                   http://www.2ndQuadrant.com/
     PostgreSQL Development, 24x7 Support, Training & Services

Other references:

http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/
http://wiki.postgresql.org/wiki/Streaming_Replication

The gotcha in my implementation is that I'm using repmgr to abstract the streaming replication configuration , in my opinion this reduces administration complexity but the tradeoff is a more elaborated initial deployment.

So, the failover script reduces itself to:

#!/bin/sh
# Execute command by failover.
# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8

standby_node=10.0.0.201

cmd=`ssh $standby_node "/var/lib/postgresql/repmgr -f /var/lib/postgresql/repmgr/repmgr.conf standby promote"`
if [ $failed_node_id = $old_primary_node_id ];then      # master failed
# the standby server is promoted 
echo $cmd
fi

Now it's time to kill the master.

Postgresql - pgpool, bind(/tmp/.s.PGSQL.9898) failed. reason: Address already in use

2011-12-21T10:48:00.000-06:00

When starting pgpool from pgadmin I got this message in the log file, then tried to remove the file in question, linux said: "hey it ain't such file", tried again, fired pgpool up , and there you go.

I'm adopting as temporary solution to delete that file at startup (nevertheless linux say's it doesn't exist).

rene@linux-2561:~> cat /var/log/pgpool/pgpool.log
2011-12-21 10:35:37 LOG:   pid 3531: Backend status file /var/log/pgpool//pgpool_status discarded
2011-12-21 10:35:37 LOG:   pid 3531: pgpool-II successfully started. version 3.1.1 (hatsuiboshi)
2011-12-21 10:35:37 ERROR: pid 3531: bind(/tmp/.s.PGSQL.9898) failed. reason: Address already in use
rene@linux-2561:~> sudo rm /tmp/.s.PGSQL.9898
rm: cannot remove `/tmp/.s.PGSQL.9898': No such file or directory
rene@linux-2561:~> cat /var/log/pgpool/pgpool.log
2011-12-21 10:37:15 LOG:   pid 3651: Backend status file /var/log/pgpool//pgpool_status discarded
2011-12-21 10:37:15 LOG:   pid 3651: pgpool-II successfully started. version 3.1.1 (hatsuiboshi)
2011-12-21 10:37:15 LOG:   pid 3651: find_primary_node: primary node id is 0

I'm sending this one to the mailing list.

General - The Google Book Search API v1

2011-12-21T02:53:00.000-06:00

Searching book info with the The Google Book Search API v1 is as easy as it gets!
For example, I was asked to find the available info of a book from a given ISBN number.

Books API Example

The cute part being:
<script src="https://www.googleapis.com/books/v1/volumes?q=isbn:9789681848828&callback=handleResponse"></script>

Output from the above query:

Introducción a la lógica | Autor: Irving M. Copi,Carl Cohen | fecha de publicacion: 1999 | editorial : Limusa Noriega Editores

References
http://code.google.com/intl/es-MX/apis/books/
http://code.google.com/intl/es-MX/apis/books/docs/v1/using.html#query-params
http://code.google.com/intl/es-MX/apis/books/docs/v1/using.html#RetrievingVolume

There are another quirks into handling this information, but I deemed the callback mechanism to be a very straightforward approach, nevertheless is not that smooth to store the results in this way.

Now, I'll enter this info into postgresql, I'll have tons of fun =-)
You can test this snippet by entering the ISBN you're interested in, save it as a html file and open it with firefox (is there another web browser?).

General - Beautifying code snippets in your blogs

2011-12-21T01:41:00.000-06:00

Syntax Highlighter is a javascript library that helps you format your code snippets within a blog or some other stuff sharing site.

You'll need only to add this block at the end of your tag <head> in your html template:

The trick is to embed your code within something like this:
<pre class="brush:html">
Your 'HTML' code goes here
</pre>

Sources:
http://alexgorbatchev.com/SyntaxHighlighter/integration.html
http://www.cyberack.com/2007/07/adding-syntax-highlighter-to-blogger.html

Linux - variables inside strings

2011-12-20T20:17:00.000-06:00

http://systemsboy.com/2006/11/using-ssh-to-send-variables-in-scripts.html

I needed to execute a remote command through ssh, and I used the proposed solution (something like this)

cmd=`ssh $1 "/usr/lib/postgresql/9.1/bin/pg_ctl start -D $2 2>/dev/null 1>/dev/null < /dev/null &"`
echo $cmd

Notice the back quote or back tick (or grave)

On this respect:

The back quote is not used for quoting characters. That character is used for command substitution, where the characters between them are executed by the shell and the results is inserted on that line.

Example:

% echo the date is `date`

More about quotes on scripts:

http://www.grymoire.com/Unix/Quote.html

Postgresql - pgpool + failover

2011-12-20T18:57:00.000-06:00

This is the provided script for failover in the example I'm following.
http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/failover.sh

#----------------------------------------------------------------------------------------------------------
#!/bin/sh
# Execute command by failover.
# special values: %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8
trigger=/var/log/pgpool/trigger/trigger_file1

if [ $failed_node_id = $old_primary_node_id ];then    # master failed
    touch $trigger    # let standby take over
fi

#---------------------------------------------------------------------------------------------------------

The reasoning behind the trigger file is that when the standby server finds the file it will decide to promote to primary. It's the health check process the one that will determine whether the primary is down.

Doubts:
1.Where is specified the order in which the parameters are given, where is it documented? Well, I guess that I'll take his word for it, as he's a guy from the development team.
2. In case I have another slave server, which one will be promoted?

Postgresql - pgpool online recovery

2011-12-20T16:10:00.000-06:00

What's online recovery ?
+ It's available in replication mode.
+ It means attaching a node while still servicing clients.

Prerrequisites
+ A recovery target node must be in detached state before doing online recovery
+ If the target PostgreSQL server has already started, it's necessary to shut it down.

Stages

CHECKPOINT
First stage of online recovery - custom script
Wait until all clients have disconnected
CHECKPOINT
Second stage of online recovery- custom script
Start up postmaster (perform pgpool_remote_start)
Node attach

In my opinion, the script for the first stage of online recovery can be left alone, as I'm using repmgr to make things easier to handle.

The only instruction I'll need for attaching the new node will be something like this:

repmgr -D /var/lib/pgsql/9.0 standby clone masterNode

There's also the possibility to specify this instruction in the first stage and in the second one

something like:

repmgr -D /var/lib/pgsql/9.0 --force standby clone masterClone

The first stage will create the equivalent to a base backup, and the second stage will catch

up with the latest changes that clients made during the first stage of recovery.

As with many things, the only way to know for sure the effects (positives and negatives) of

this approach will be doing some tests.

You can specify a script executed during the first stage. pgpool-II passes three arguments to the script.
The database cluster path of a master node.
The hostname of a recovery target node.
The database cluster path of a recovery target node.

Taking into account the parameters passed by pgpool both scripts could be like the following:

If you wish to add a PostgreSQL server dynamically, add 'backend_hostname' and
its associated parameters and reload pgpool.conf. pgpool-II registers this new
node as a detached node.

#!/bin/bash
# first stage recovery
# $1 The database cluster path of a master node.
# $2 The hostname of a recovery target node.
# $3 The database cluster path of a recovery target node.
MASTERNODE=10.0.0.113
cmd1=`ssh $2 "rm -r $3"`
cmd2=`ssh $2 "/usr/lib/postgresql/9.1/bin/repmgr -D $3 standby clone $MASTERNODE"`
echo $cmd1
echo $cmd2

#-------------------------------------------------------------------------------------------------

#!/bin/bash
# second stage recovery  
 # $1 The database cluster path of a master node.
 # $2 The hostname of a recovery target node.
 # $3 The database cluster path of a recovery target node. 
  MASTERNODE=10.0.0.113
  cmd1=`ssh $2 "/usr/lib/postgresql/9.1/bin/repmgr -D $3  --force standby clone $MASTERNODE"`
echo $cmd1

#-----------------------------------------------------------------------------------------------

#!/bin/bash
#
# Start PostgreSQL on the recovery target node
#
if [ $# -ne 2 ]
then
    echo "pgpool_remote_start remote_host remote_datadir"
    exit 1
fi

cmd=`ssh $1 "/usr/lib/postgresql/9.1/bin/pg_ctl start -D $2 2>/dev/null 1>/dev/null < /dev/null &"`
echo $cmd

Linux - startup script

2011-12-20T13:03:00.000-06:00

I never had the necessity of creating a start up script, but I need one for creating a folder within the /var/run path as it gets delete it on each reboot.

open a konsole and do the following:
1.cd /etc/init.d/
2.touch makedir
3.vim makedir
4. enter mkdir /var/run/pgpool | chown wwwrun -R /var/run/pgpool
5.save the file
6.chmod +x makedir
7.chkconfig -a makedir

The above commands does the following
1. changes directory to a directory which contains the startup scripts
2. creates a file named makedir
3. edit the file makedir (vim is a text editor....use man vim if you are not familiar with it)
4. write your command in this file
5. save the file (again....use man vim to learn how to save the file)
6. change the file to executable making it a script
7. add makedir script to startup so it starts even after reboot

http://forums.opensuse.org/archives/sf-archives/archives-install-boot/340570-how-run-custom-script-startup.html

Postgresql - pgpool + streaming replication

2011-12-19T19:53:00.000-06:00

I'm following this guide to set up a simple pgpool deployment with streaming replication:
http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/

+ the apache2 user process is called wwwrun in opensuse 12.1 ( don't know whether this is also true for other distributions)
+ the apache directory is located in /svr/www/htdocs

+ to enable the apache service you'll need to issue a systemctl
systemctl enable apache2.service
systemctl start apache2.service

+ in order for pgpooladmin to run, it's necessary to grant the wwwrun process user with the appropriate permissions over the underlying pgpool configuration files.

+ gotcha: specifying the health check user is necessary for pgpool to detect which server is master and which slave (whilst in streaming replication).

+ parallel query applies when you partition your tables over several nodes .

Questions:
Apparently pgpool determines which sql statements are dml (which are directed to the master ) and which are only dql (data query language ) which may be balanced. Obviously database permission subsists. pgpool acts as if were another client.

related links.
http://www.howtoforge.com/how-to-set-up-a-postgresql-9.0-hot-standby-streaming-replication-server-with-repmgr-on-opensuse-11.4

http://pgpool.projects.postgresql.org/pgpoolAdmin/doc/en/install.html

http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html

Postgresql - Logging (messages)

2011-12-16T11:56:00.000-06:00

Apparently log messages are stored by default in the pg_log directory inside the directory cluster. Supposedly you must configure the server to redirect all error messages to a file , for example in csv format.

http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html

Postgresql Troubleshooting - FATAL: valor no válido para el parámetro «lc_messages»: «en_US.UTF-8»

2011-12-15T13:31:00.004-06:00

Source machine: opensuse 12.1 en_US.UTF8
Target machine: debian squezze es_MX.utf8

This issue arose when trying to start a database cluster (inside a machine configured in spanish) based on a copy of a cluster in another machine that is configured with the english language.

The problem was that the only "locales" installed in the target machine were:

postgres@debian1:~$ locale -a
C
es_MX.utf8
POSIX

In Debian, there are several ways to configure a new locale:
http://people.debian.org/~schultmc/locales.html

I followed successfully the easy way:

The Easy Way

Install debconf (i.e. run apt-get update then apt-get install debconf, as root)
Run dpkg-reconfigure locales as root

And now,I list the locales again:

postgres@debian1:~$ locale -a
C
en_US.utf8
es_MX.utf8
POSIX

Postgresql - pg_config

2011-12-14T13:35:00.002-06:00

pg_config is a small application that provides information of the current postgresql installation.Many of the utilities that you install to support your postgresql server, rely on this utility to get the necessary parameters for postgresql binaries and libraries.

It's contained in the postgresql-contrib package and in my case, it's installed in this path:
/usr/pgsql-9.0/bin/pg_config
i.e. in the same path that the other postgresql binaries.

Of course, in order for the program that requires such information to find pg_config it must be included in the PATH environment variable.

http://www.postgresql.org/docs/8.1/static/app-pgconfig.html

Postgresql - repmgr (Replication Manager for PostgreSQL clusters)

2011-12-14T12:18:00.003-06:00

This tool is related with replication capabilities, it's very young (but the streaming replication and hot standby features are also very recent)
and claims to allow:
+ monitor the replication process.
+ allow DBAs to issue high availability operations such as switch-overs and fail-overs.

http://repmgr.org/

I've read about a lot of other replication monitoring solutions and they all are not that easy to install/configure, let's give this one a try.

I got to know about this through:
http://www.howtoforge.com/how-to-set-up-a-postgresql-9.0-hot-standby-streaming-replication-server-with-repmgr-on-opensuse-11.4-p2

Here's the official documentation for setting everything up.
https://github.com/greg2ndQuadrant/repmgr

Arguments for using repmgr
1. Less steps -> less errors
2. Easy to set up and easy to use
3. Monitoring facilities.
4. Company is known
5. You can set the replication in a database basis.

Shortcomings:
1. It must be installed additionally to the normal postgresql installation.
2. The limitations of streaming replication are also limitations of repmgr.
3. Development is in early stages (but this is expected as the streaming replication technology is also recent)
4. Prerequisites that must be met to be able to install repmgr.
5. The inner configuration issues to stream replication are hidden.

Independently of adopting this tool or not as the entry point for streaming replication, it's important to master the concepts lying behind.

linux - ssh-copy-id

2011-12-13T16:38:00.001-06:00

I used this tool today to copy one machine public key into the known hosts of another in order to set "trusted" communication. The other way to do this is to copy the they to the other machine and then copying it directly in the knownhosts file, which is obviously more complicated.

I need this kind of set up to enable streaming replication in postgresql with hot stand by capabilities.

http://linux.die.net/man/1/ssh-copy-id
http://www.howtoforge.com/how-to-set-up-a-postgresql-9.0-hot-standby-streaming-replication-server-with-repmgr-on-opensuse-11.4

The command to copy public keys
ssh-copy-id -i ~/.ssh/id_rsa.pub pgslave

Postgresql - timing

2011-12-07T02:48:00.001-06:00

One simple yet powerful tip is to activate the timing feature:

dellstore2=# \timing
Timing is on.
dellstore2=# SELECT count(*) FROM customers;
count
-------
20000
Time: 7.994 ms

The material I read this from, titles this particular section "Timing overhead". Is the timing feature supposed to cause some sort of overhead upon our queries?

Postgresql - Handling 'NaN'::float -> crosstab function

2011-11-25T13:51:00.001-06:00

Today AVR asked me: how do I compare against a NaN ? And I was like you mean to an AND negated ? LOL.
It turns out that 'NaN' is a simbolic value meaning "Not a number".

In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning "not-a-number". Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.

Note: In most implementations of the "not-a-number" concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

In addition to ordinary numeric values, the floating-point types have several special values:
Infinity
-Infinity
NaN

http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html

If you want to compare against the 'NaN' ("not a number") float value, then do an explicit cast to float: float('NaN').

http://stackoverflow.com/questions/5401455/is-there-a-database-independent-way-with-sqlalchemy-to-query-filtered-by-none

Here are some very good insights regarding this topic.

http://www.ivoa.net/internal/IVOA/InterOpOct2011Applications/null_nan-3.pdf

This issue arose due to the apparent fact that the function crosstab returns this symbolic constant.

In the end she did this:

CASE WHEN CHALCO = 'NaN'::float OR CHALCO IS NULL THEN 0 ELSE CHALCO END,

and seemed to work.