Friday, December 30, 2011

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

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.

Thursday, December 29, 2011

Postgresql - pgpool - a wary pgpool_failover script

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

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

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.

Wednesday, December 28, 2011

Postgresql - pg_archivecleanup

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

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

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?

Tuesday, December 27, 2011

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

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 ?

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()

Monday, December 26, 2011

Postgresql - streaming replication - new features in 9.1

 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

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/

Sunday, December 25, 2011

Postgresql -plpgsql. Generating primes as SETOF RECORD

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

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.

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

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

Saturday, December 24, 2011

javascript - Generating primes

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



 


Friday, December 23, 2011

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

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

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

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

Thursday, December 22, 2011

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

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

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

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

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

Wednesday, December 21, 2011

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

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

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

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

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

Tuesday, December 20, 2011

Linux - variables inside strings

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

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

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

  1. CHECKPOINT
  2. First stage of online recovery - custom script
  3. Wait until all clients have disconnected
  4. CHECKPOINT
  5. Second stage of online recovery- custom script
  6. Start up postmaster (perform pgpool_remote_start)
  7. 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.
  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.
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

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

Monday, December 19, 2011

Postgresql - pgpool + streaming replication

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

Friday, December 16, 2011

Postgresql - Logging (messages)

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

Thursday, December 15, 2011

Postgresql Troubleshooting - FATAL: valor no válido para el parámetro «lc_messages»: «en_US.UTF-8»

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

Wednesday, December 14, 2011

Postgresql - pg_config

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)

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.

Tuesday, December 13, 2011

linux - ssh-copy-id

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

Wednesday, December 7, 2011

Postgresql - timing

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?