PostgreSQL export csv from shell

I needed to get stats per minute for a SMS TV vote from 50 tables – one table per vote:

solution 1:

#!/bin/sh
op=2
op=3
ip="10.10.10.157"

# NO work - version 8.0.4 ...
# op=1
# ip="10.10.10.5"

dbname="vote_2012_01"
for num in `seq 1 50`
do
	if (( ${num} < 10 ))
	then
		num="0${num}"
	fi
	
echo "copy (
select 
'Pesen ${num}' as pesen
,operator_id as op
, count(*) as cntr 
, date_trunc('minute',intime) as minutes
from  vote_201201_${num}
where true 
and operator_id = ${op} 
group by minutes, op 
order by minutes, op
)
to stdout  with csv header
force quote pesen,minutes
;
" | psql -U me -h ${ip} ${dbname} -F , --no-align  > data.op.${op}.pesen.${num}.csv

done

==============================================================
solution 2 – copy(SQL) did not work at psql -v 8.0.4 on me

#!/bin/sh
# done by dragkh
op=2
op=3
ip="10.10.10.157"

#work - version 8.0.4 ...
op=1
ip="10.10.10.5"

dbname="vote_2012_01"

for num in `seq 1 50`
do
	if (( ${num} < 10 ))
	then
		num="0${num}"
	fi
	
echo "select
'\"Pesen ${num}\"' as pesen
,operator_id as op
, count(*) as cntr 
, '\"' || date_trunc('minute',intime) || '\"' as minutes
from  vote_201201_${num}
where true 
and operator_id = ${op} 
group by minutes, op 
order by minutes, op
;
" | psql -U me -h ${ip} ${dbname} -F , --no-align  > data.op.${op}.pesen.${num}.csv

done

PostgreSQL propmt with user, hostname , port, dbname and current timestamp


bsms=# \set PROMPT1 '%[%033[1;33;40m%]%n@%M:%>::%/%R[%`date`]%[%033[0m%]%# '
bsms@[darkwater]:5432::bsms=[Wed Nov 30 01:14:48 EET 2011]# select now();
now
-------------------------------
2011-11-30 01:14:52.998601+02
(1 row)

bsms@[darkwater]:5432::bsms=[Wed Nov 30 01:14:53 EET 2011]#


Fedora 15 Gnome3 add compiled from git pgadmin3 to applications menu

compile pgadmin3 from git in /opt/installs/ – use the instruction at the git pgadmin repository
when you have it compiled :

cp /opt/installs/pgadmin3/pkg/pgadmin3.desktop /usr/share/applications
ln -s /usr/local/bin/pgadmin3 /usr/bin/
mkdir /usr/share/pgadmin3/
cp /opt/installs/pgadmin3/pgadmin/include/images/pgAdmin3.png /usr/share/pgadmin3/
root@darkstar:[Mon Jul 04 15:48:06]:[/usr/share/applications]$ cat pgadmin3.desktop 
[Desktop Entry]
Encoding=UTF-8
Name=pgAdmin III
Exec=/usr/bin/pgadmin3
Icon=/usr/share/pgadmin3/pgAdmin3.png
Type=Application
Categories=Application;Development;
MimeType=text/html
DocPath=/usr/share/pgadmin3/docs/en_US/index.html
Comment=PostgreSQL Tools

the trick of adding custom app at Gnome3 application menu is taken from here:
http://forums.fedoraforum.org/showthread.php?p=1480880

done

Fedora release 15 (Lovelock) 64 bit Picasa 3

yum install wine.x86_64 -y
yum localinstall picasa-2.7.3736-15.i386.rpm -y

Picasa wont run after that
ERRORS:

[Sun Jun 19 23:21:37]:[~]$ picasa
/usr/bin/picasa: line 139:  4304 Segmentation fault      (core dumped) "$PIC_BINDIR"/wrapper check_dir.exe.so
/usr/bin/picasa: line 175:  4414 Segmentation fault      (core dumped) "$PIC_BINDIR/wrapper" regedit /E $registry_export HKEY_USERS\\S-1-5-4\\Software\\Google\\Picasa\\Picasa2\\Preferences\\
istoykov@T400:[Sun Jun 19 23:22:52]:[~]$ picasa
/usr/bin/picasa: line 189:  5154 Segmentation fault      (core dumped) "$PIC_BINDIR"/wrapper check_dir.exe.so
/usr/bin/picasa: line 248:  5268 Segmentation fault      (core dumped) "$PIC_BINDIR"/wrapper set_lang.exe.so

fix :

cp /usr/bin/wine-preloader /opt/google/picasa/3.0/wine/bin/wine-preloader

original source of the fix : http://www.fedoraforum.org/forum/showthread.php?t=222601&page=2

Note :
if you have the issue with the following error :
HttpOpenRequest failed (12157) -
https://www.google.com/accounts/ClientAuth [13]

use this work around :

cp /usr/lib/wine/wininet.dll.so /opt/google/picasa/3.0/wine/lib/wine/wininet.dll.so

original source of the fix : http://www.google.com/support/forum/p/Picasa/thread?tid=6eb9c7d06d5aa10c&hl=en

Fedora release 15 (Lovelock) 64 bit PgAdmin3 latest from git

yum localinstall postgresql-upgrade-9.0.4-2.fc15.x86_64.rpm
yum install postgresql-devel
yum install libxslt-devel
yum install wxGTK-devel
yum install perl-Wx
yum install qt3.i686
yum install automake
echo "/usr/lib/qt-3.3/lib" >> /etc/ld.so.conf.d/qt-x86.conf 
ldconfig
service postgresql initdb
service postgresql start
git clone git://git.postgresql.org/git/pgadmin3.git
cd pgadmin3
bash bootstrap
./configure
make -j3 install 

Fedora 15 Lovelock 64 bit Quanta Plus install

yum install kdewebdev kdewebdev-libs kdewebdev-devel -y

Linux bash: get the Apache PID with high CPU consumtion and extract the URL

make sure you have the extended status on and the http://localhost/server-status is accessible

#!/bin/sh
# by dragkh
# Wed Apr 06 12:33:56

# Config
CPU_LIMIT=10
TOP_PROCESSES=10
LOG_FILE=/var/log/high.CPU.apache.URLs.log

top -b -U apache  -n 1 | \
grep -v grep | \
grep apache  | \
sort -rn -k9 | \
head  -${TOP_PROCESSES} | \
awk -v CPU_LIMIT=${CPU_LIMIT} '($9 > CPU_LIMIT){print $0}' | \
while read PID USER      PR  NI  VIRT  RES  SHR S CPU MEM    TIME  COMMAND
do 
  echo -n "$(date) :[$HOSTNAME]: PID[${PID}]:CPU[${CPU}] : Apache => " >> ${LOG_FILE}
  links -dump http://localhost/server-status | grep -v grep | grep "${PID}" >> ${LOG_FILE}
done

Linux bash: check exicting IP in the active vhost configs

ifconfig  | grep "inet addr:" | grep -v grep | awk '{print $2}' | sed 's/^addr://' | sort | uniq | while read IP; do echo -n "checking $IP "; echo $(fgrep $IP /etc/httpd/conf.d/sites/*conf | tr '\n' ' ') ;  done

Linux : Slackware : PostgreSQL 8.3.7 find the duplicates in a table

“PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2″

ext_id should be unique, but its not, so to get all the records with duplicate ext_id :

select 
ext_id 
from dd_out dd 
where true 
and ext_id in 
(
  select ext_id  as ddd from (
    select count(s.ext_id) as cnt, s.ext_id from dd_out s where true group by s.ext_id 
  ) as cra  
  where cnt > 1 
)
;

Linux Slackware 13.1 : install VBoxGuestAdditions_4.0.4

cd /opt/downloads/
wget http://dlc.sun.com.edgesuite.net/virtualbox/4.0.4/VBoxGuestAdditions_4.0.4.iso
mkdir /mnt/VBoxGuestAdditions_4.0.4
mount -t iso9660 -o loop /opt/downloads/VBoxGuestAdditions_4.0.4.iso  /mnt/VBoxGuestAdditions_4.0.4/
cd /mnt/VBoxGuestAdditions_4.0.4/
root@7932:[Sun Mar 06 13:45:40]:[/mnt/VBoxGuestAdditions_4.0.4]$ ./VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.0.4 Guest Additions for Linux.........
VirtualBox Guest Additions installer
Removing existing VirtualBox DKMS kernel modules ...done.
Removing existing VirtualBox non-DKMS kernel modules ...done.
Building the VirtualBox Guest Additions kernel modules
Building the main Guest Additions module ...done.
Building the shared folder support module ...done.
Building the OpenGL support module ...done.
Doing non-kernel setup of the Guest Additions ...done.
Starting the VirtualBox Guest Additions ...fail!
(modprobe vboxguest failed)
Installing the Window System drivers
Installing X.Org Server 1.7 modules ...done.
Setting up the Window System to use the Guest Additions ...done.
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services components ...done.
root@7932:[Sun Mar 06 13:46:38]:[/mnt/VBoxGuestAdditions_4.0.4]$ /etc/rc.d/rc.hald  restart
Starting HAL daemon:  /usr/sbin/hald --daemon=yes