Postgresql: import export of data

Export

copy (select * from shared_db.dt_process where process_name = 'AM_PARTITION') to '/tmp/shared_db__dt_process.csv' delimiter ',' csv header;

Import

copy shared_db.dt_process from '/tmp/shared_db__dt_process.csv';

CentOS: install Oracle JDK

• Download JDK from download.oracle.com

• cd /usr/local/share
  tar xvfz /media/host/jdk-8u60-linux-x64.tar.gz
  ln -s jdk1.8.0_60 jdk

• alternatives --install /usr/bin/java java /usr/local/share/jdk/bin/java 1
  alternatives --install /usr/bin/javac javac /usr/local/share/jdk/bin/javac 1
  alternatives --install /usr/bin/jar jar /usr/local/share/jdk/bin/jar 1

OSX: pipe

shift + Right Alt + l

CentOS 6 VM: french Macbook keyboard under X11

setxkbmap -layout fr -model macintosh

QNAP: add dnsomatic cronjob

Source: http://forum.qnap.com/viewtopic.php?t=12970

vi /share/CACHEDEV1_DATA/homes/dnsomatic/dnsomatic.sh

#!/bin/sh
HOSTNAME="all.dnsomatic.com"
USER="my_dnsomatic_user"
PASS="my_dnsomatic_password"
TMPFILE="/tmp/dnsomatic.oldip"
DATENOW=$(date)
 
IP=$(/sbin/curl -s http://myip.dnsomatic.com)
if [ -r "/tmp/dnsomatic.oldip" ]; then
 OLDIP=`cat /tmp/dnsomatic.oldip`
 if [ "$OLDIP" = "$IP" ]; then
  echo "IPs match; no update"
  exit 0
 else
  RETURN=$(/sbin/curl -s -m 60 -k -u ${USER}:${PASS} https://updates.dnsomatic.com/nic/update?hostname=${HOSTNAME}&myip=$IP&wildcard=NOCHG&mx=NOCHG&backmx=NOCHG)
  echo "Return message of DNS-O-Matic : " $RETURN
  echo $RETURN | grep "good" > /dev/null 2>&1
  if [ "$?" -eq "0" ]; then
   echo $IP > /tmp/dnsomatic.oldip
  else
   echo "" > /tmp/dnsomatic.oldip
  fi
 fi
else
 touch /tmp/dnsomatic.oldip
fi

Then update crontab to run it once an hour:

 vi /etc/config/crontab

0 */1 * * * /share/CACHEDEV1_DATA/homes/dnsomatic/dnsomatic.sh >/tmp/dnsomatic.log 2>&1

crontab /etc/config/crontab
/etc/init.d/crond.sh restart

PostgreSQL: copy table

select * into archive.radius_cdr_pdp_p2015_07_09 from ods_db.radiucdr_pdp_p2015_07_09;

PostgreSQL: get function source code

select pg_get_functiondef(pg_proc.oid) from pg_proc, pg_namespace  where proname = 'add_cdr_req' and pg_namespace.oid = pg_proc.pronamespace and pg_namespace.nspname = 'crm_db';

Displaying current path in the window title with byobu on centos 6

The problem with CentOS 6, is that the tmux version that comes from the standard repo. is v1.6, and we need at least 1.8 to achieve the result.
So install tmux from the latest sources like explained here:

# download latest libevent2 and tmux sources, and extract them somewhere
# (thx bluejedi for tip on latest tmux URL)
#
# at the time of writing:
# https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
# http://sourceforge.net/projects/tmux/files/latest/download?source=files
# 
 
# install deps
yum install gcc kernel-devel make ncurses-devel
 
# cd to libevent2 src
./configure --prefix=/usr/local
make && make install
 
# cd to tmux src
LDFLAGS="-L/usr/local/lib -Wl,-rpath=/usr/local/lib" ./configure --prefix=/usr/local
make && make install
 
# you're good to go, for a sample ~/.tmux.conf check out
# https://github.com/sturadnidge/misc/blob/master/.tmux.conf

NB: Kill old tmux sessions before running byobu again.
And then use the following setting in your $HOME/.byobu/.tmux.conf

set -g status-left '#{pane_current_path} '

Linux: Running processes with associated open port

netstat -tulpn

byobu: Show pwd in prompt

Enable prompt

byobu-enable-prompt

Disable prompt

byobu-disable-prompt

mod_perl: How to send a custom content type along with custom_response

By default, mod_perl Apache2::Response::custom_response will use the following content-type: text/html; charset=iso-8859-1.
But, what if we want to use e.g. application/x-www-form-urlencoded; charset=utf-8 instead?

Main handler:

sub handler {
    my ($i_request_rec) = @_;

    @{$i_request_rec->pnotes}{'err_txt', 'err_content_type'} = ('appid=45465&status=-42', 'application/x-www-form-urlencoded; charset=utf-8');
    $i_request_rec->custom_response(Apache2::Const::SERVER_ERROR, '/error_vas/');
    return Apache2::Const::SERVER_ERROR;
}

Apache conf.:

<Location /error_vas/>
        SetHandler perl-script
        PerlResponseHandler Toto::ErrorVas
</Location>

ErrorVas.pm:

package Toto::ErrorVas;

use strict;
use warnings;
use Apache2::Const -compile => qw(:common :log);

sub handler {
    my ($i_request_rec) = @_;

    return Apache2::Const::NOT_FOUND unless $i_request_rec->prev;

    $i_request_rec->content_type($i_request_rec->prev->pnotes->{err_content_type});
    $i_request_rec->print($i_request_rec->prev->pnotes->{err_txt});

    return Apache2::Const::OK;
}

source: http://foertsch.name/ModPerl-Tricks/custom-content_type-with-custom_response.shtml

perl: setting multiple hash values at once

perl -MData::Dumper -e '@{$h}{qw/toto tata/} = (34, 56); print Dumper $h;'              
$VAR1 = {
          'tata' => 56,
          'toto' => 34
        };

perl: posting to multihomed hostname

What if you want to post to a hostname resolving to multiple IP addresses?

    local @LWP::Protocol::http::EXTRA_SOCK_OPTS = (
                                                   PeerAddr   => $my_hostname,
                                                   MultiHomed => 1,
                                                  );
    my $ua = LWP::UserAgent->new;
    my $response = $ua->post("https://$my_hostname/action",
                             Content_Type => 'application/x-www-form-urlencoded; charset=utf-8',
                             Content      => {
                                              param1 => 'value1',
                                              param2 => 'value2',
                                             });

php/oci-extension: missing leading zero

Reading numbers from Oracle in PHP results in missing leading 0s if -1 < value < 1.
The reason for this behavior is because Oracle OCI omits 0s in results and PHP converts all results from OCI to strings without performing any casting depending on column datatype.

A simple workaroud consists in converting to float:

$a = '.63';
echo (float) $a;

source: http://stackoverflow.com/questions/4284571/php-oci-oracle-and-default-number-format

Oracle alternative:

REGEXP_REPLACE(TO_CHAR(x), '^\.', '0.')

or if we handle negative numbers

REGEXP_REPLACE(TO_CHAR(x), '(-?)^\.', '\10.')

perl: print module version

perl -MLWP::UserAgent -e 'print $LWP::UserAgent::VERSION'

HTML: POST array not showing unchecked checkbox

<td>
  <input type="checkbox" name="cb[]"/>
  <input type="checkbox" name="cb[]"/>
</td>

• Solution 1) Explicit index + hidden trick

<td>
  <input type="hidden" name="cb[0]" value=""/>
  <input type="checkbox" name="cb[0]"/>
  <input type="hidden" name="cb[1]" value=""/>
  <input type="checkbox" name="cb[1]"/>
</td>

• Solution 2) Explicit index + fill in the blanks (e.g. with PHP)

<td>
  <input type="checkbox" name="cb[0]"/>
  <input type="checkbox" name="cb[1]"/>
</td>

$post_array = $_POST['cb'];
$defaults = array_fill(0, 2, '0');
$post_array = $post_array + $defaults;

byobu: terminate tmux session

tmux kill-session

Byobu keybindings

https://github.com/dustinkirkland/byobu/blob/master/usr/share/doc/byobu/help.tmux.txt

PostgreSQL: perl like join

db=# select array_to_string(array['a', 'b', 'c'], '-');                                                                     
 array_to_string 
-----------------
 a-b-c
(1 row)

PostgreSQL: date to epoch

select extract(epoch from localtimestamp(0));