Big Data / Data Analytics Job

Data Architect   3-7+ years   

Salary : £70000 - £75000 per annum     location : Central London

Posted: 26/06/2018    Job Type : Permanent

Job Ref: 439101143    Skills & Apply ->

Data Scientist   3-7+ years   

Salary : £232 - £298 per day     location : London

Posted: 18/06/2018    Job Type : Temporary

Job Ref: 775109025    Skills & Apply ->

Analyze Data to Identify type of Readmissions using PostgreSQL

previous post - ERROR invalid input syntax for type date SQL state: 22007

What is patient readmission

Normally Readmission is - if a patient returns within 30 days of previous discharge.

reason for readmission

Multiple factors contribute to avoidable hospital readmission: they may result from poor quality care or from poor transitions between different providers and care settings.

The problem of readmission to the hospital is receiving increased attention as a potential way to address problems in quality of care, cost of care, and care transition

readmission table structure

After cleansed and identified the required attribute to get the readmission details, my entity model look like this

patient_id	disease	adm_date	dis_date
1069	Oncology	6/17/2013 6:51	6/21/2013 7:15
1078	Neuro	7/18/2013 12:10	7/20/2013 08:12
1082	Ortho	7/19/2013 12:10	7/22/2013 08:12
1085	Cardiothor	8/25/2013 12:10	8/27/2013 08:12
1085	Cardiothor	9/13/2013 12:10	9/16/2013 08:12

Now, we have to write a query to get the list of readmission details. Condition is, current admit date is between the last discharge date and 30 days from then.

readmission query in postgresql

Select count(*) "Readmission Count", disease
From readmission_view v
Where Exists
(
Select * From readmission_view
Where patient_id = v.patient_id and
v.adm_date between dis_date and dis_date + 30
)
group by disease
order by count(*)

readmission result

disease	Readmission Count
Ortho	878
Neuro	567
Oncology	155

Using the result optioned from the query to find the percentage of patient readmission. based on the result need extra care on the disease.

Related posts :
cross database triggers in PostgreSQL

how to backup & restore cross database PostgreSQL server

ERROR invalid input syntax for type date SQL state: 22007

previous post - Install TeamPostgreSQL on Ubuntu 14

SQL state: 22007

Type casting varchar to date in where clause

select * from patient_history where
where admissiondate::DATE > releasedate::DATE

Error

ERROR:invalid input syntax for type date:"" ********** Error **********

ERROR:invalid input syntax for type date:""
SQL state: 22007

Solution

In my case, type casting for admissiondate working fine but not for releasedate. Then i spent few time for the field releasedate then found few empty / null values in releasedate column.

select * from patient_history where
releasedate is null OR releasedate =''

I removed empty entry from the releasedate , then the same query for type casting working fine.

select * from patient_history where
v.admissiondate::DATE > releasedate::DATE

Related posts :
List all duplicate Foreign Key constraint in PostgreSQL

List the all the FOREIGN KEY constraints with create/drop statement.

Installing TeamPostgreSQL on 64 bit Ubuntu 14.x OS

previous post - Rserve - TCP/IP server which allows other programs to use facilities of R

TeamPostgreSQL - PostgreSQL Web Admin GUI Tools

Installing TeamPostgreSQL on 64 bit Ubuntu 14.x OS

boss@solai:~$ ./teampostgresql_ubuntu.sh

Error

Unpacking JRE ...
Preparing JRE ...
./teampostgresql_ubuntu.sh: 256: ./teampostgresql_ubuntu.sh: bin/unpack200: not found Error unpacking jar files. Aborting. You might need administrative priviledges for this operation.

Solution

boss@solai:~$ sudo dpkg --add-architecture i386

boss@solai:~$ sudo apt-get update

boss@solai:~$ sudo apt-get install libc6:i386 libncurses5:i386 libstdc++6:i386


now 32 bit lib installed in your system. try install..

Error

boss@solai:~$ ./teampostgresql_ubuntu.sh

Unpacking JRE ...
Preparing JRE ...
Starting Installer ...
Could not display the GUI. This application needs access to an X Server.
If you have access there is probably an X library missing.
******************************************************************* You can also run this application in console mode without access to an X server by passing the argument -c *******************************************************************
An error occurred:
java.lang.UnsatisfiedLinkError:
/opt/sw/teampostgresql_ubuntu.sh.16586.dir/jre/lib/i386/xawt/libmawt.so: libXext.so.6: cannot open shared object file: No such file or directory
Error log: /tmp/install4jError8210847315454692406.log

Solution

Install again by adding -c option

boss@solai:~$ ./teampostgresql_ubuntu.sh -c

Related posts :
LIST OUT ALL (FORIEN KEY,PRIMARY KEY, UNIQUE KEY) CONSTRAINTS WITH REFERENCED TABLE & FIELD

CROSS TABLE DATA TRANSFER

RPostgreSQL Data analytics on PostgreSQL data using R

previous post - XGBoost in R. Error in xgb.DMatrix can not open file

Data analytics on PostgreSQL data using R. Working with R and PostgreSQL for large scale data analytics

Installing RPostgreSQL

install.packages('RPostgreSQL')

compilation terminated. /usr/lib/R/etc/Makeconf:128: recipe for target 'RS-PQescape.o' failed make: *** [RS-PQescape.o] Error 1 ERROR: compilation failed for package 'RPostgreSQL' * removing '/home/boss/R/x86_64-pc-linux-gnu-library/3.1/RPostgreSQL'
RS-PostgreSQL.h:23:26: fatal error: libpq-fe.h: No such file or directory

Solution

RPostgreSQL require libpq-dev.

libpq-dev is a set of library functions that allow client programs (for our case its 'R') to pass queries to the PostgreSQL backend server and to receive the results of these queries.

So we have to install libpg-dev on OS not from R terminal

solai@server# sudo apt-get install libghc-postgresql-libpq-dev

then install RPostgreSQL inside R session
> install.packages('RPostgreSQL')

Related posts :
RPostgresql : how to pass dynamic parameter to dbGetQuery

Working With RHadoop

RPostgresql : how to pass dynamic parameter to dbGetQuery statement

previous post - Working with Association in R

Working with RPostgreSQL package

How to pass dynamic / runtime parameter to dbGetQuery in RPostgrSQL ?

#use stri_paste to form a query and pass it into dbGetQuery icd = 'A09'
require(stringi)

qry <- stri_paste("SELECT * FROM visualisation.ipd_disease_datamart WHERE icd ='", icd, "'",collapse="")

rs1 <- dbGetQuery(con, qry)

Error

 
 Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (cannot allocate a new connection -- 
maximum of 16 connections already opened)

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")

con <- dbConnect(drv, dbname="DBName", host="127.0.0.1",port=5432,user="yes",password="yes")

Solution
close the connection. max 16 connection can able to establish from R to PostgreSQL, if exceeds this limit, will throw error.

##list all the connections
dbListConnections(drv)
## Closes the connection
dbDisconnect(con)
## Frees all the resources on the driver
dbUnloadDriver(drv) #OR on.exit(dbUnloadDriver(drv), add = TRUE)

How to close/drop all the connection Postgresql session.?

We can terminate the PostgreSQL connection using "pg_terminate_backend" SQL command.
In my case I was open up 16 connection using RPostgreSQL unfortunately forget to release them.
So I ended up with Max. connection exceed limit.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE client_addr = '10.184.36.131' and pid > 20613 AND pid <> pg_backend_pid();

In above query, pg_stat_activity will return list of all the active connection.
I have terminating only the connection from R session which made from the (client_addr) IP 10.184.36.181

Next : Apache Spark : start Worker across cluster

Error while starting PostgreSQL with Windows OS : pg_ctl: invalid data in PID file "postmaster.pid"

while start PostgreSQL on windows OS, 

C:>"C:/Program Files/PostgreSQL/8.3/bin/pg_ctl.exe" -D "C:/ProgramFiles/PostgreSQL/8.3/data" start

Error

pg_ctl: invalid data in PID file "C:/ProgramFiles/PostgreSQL/8.3/data/postmaster.pid"

pg_ctl: invalid data in PID file "/postgres/directory/postmaster.pid"

SOLUTION

Just manually remove  the postmaster.pid file at /postgres/directory/postmaster.pid and start it again, now PostgreSQL server will start successfully.  

Why this cause - Explanation 

postmaster.pid was the process ID file for PostgreSQL, which indicate PostgreSQL process. If the file exists, it would mean that PostgreSQL is alive and running. If it doesn't, it would mean PostgreSQL have been terminated, either on purpose or abnormally.

In our case, the file exist but the PostgreSQL instance is not running. i'm just looking up the file postmaster.pid, it was empty but showing size as 1KB, no indication of process ID describe the process. this end of with the conclusion of PostgreSQL have been terminated abnormally, leaving an empty postmaster.pid.

Permission denied Error, Export CSV file using COPY TO command in PostgreSQL

In PostgreSQL, COPY command used for exporting or importing data in CSV format.

simple COPY commands for  export the Table data in CSV format.

postgres@boss:~$ psql 
postgres=# COPY Table_Name TO '/root/Desktop/table_data_export.csv';

ERROR:  could not open file "/root/Desktop/ table_data_export.csv" for writing: Permission denied


even i've done all the permission  (chmod)  and changed the owner (chown) to postgres. Then I tried to export the same on location postgres/data, now problem get solved.

postgres=# COPY Table_Name TO '/home/postgres/data/table_data_export.csv';

Postgresql - Triggers on DDL Statements

PostgreSQL 9.3

Awaiting DDL Triggers i.e EVENT TRIGGERS  features  finally  come live with PostgreSQL 9.3

Yes!!.. now we can have a Trigger on DDL Statements like Oracle does.

Syntax                                                                                                                               

CREATE - EVENT TRIGGER 

CREATE EVENT TRIGGER trigger_name
  ON event
  [ WHEN condition IN (values)  ]
  EXECUTE PROCEDURE function_name()

event : ddl_command_start, ddl_command_end and sql_drop 

ddl_command_start

 Trigger function ( i.e function_name() ) would be called before execution of CREATE/ALTER/DROP command.


ddl_command_end

Trigger function ( i.e function_name() ) would be called after execution  of CREATE/ALTER/DROP command.


sql_drop 

Trigger function ( i.e function_name() ) would be called Just before "ddl_command_end" event triggered.
Only for the DROP commands
To view the list of objects deleted by pg_event_trigger_dropped_objects()

condition : tg_tag. ( It will return calling trigger command ) 
values : CREATE TABLE, CREATE FUNCTION complete list of values 
function_name : A normal pl/pgsql function with return type as event_trigger & it not needed to return any value.

Ex:
CREATE TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION fn_ddl_trigger()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'Current running DDL command is : % ', tg_tag;
END;
$$;

CREATE EVENT TRIGGER 1)  Simple  

CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start
   EXECUTE PROCEDURE fn_ddl_trigger();

OUTPUT 1)

root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table1 (empname varchar(200));
NOTICE:  Current running DDL command is: CREATE TABLE
CREATE TABLE

CREATE EVENT TRIGGER 2) With 'WHEN' condition
      

CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start WHEN tg_tag in (ALTER_TABLE, DROP_TABLE)
EXECUTE PROCEDURE fn_ddl_trigger();

OUTPUT 2)  trigger function will get called only ALTER and DROP, not for CREATE TABLE, so no messgae to display.

root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table2 (empname varchar(200));

CREATE TABLE

ALTER - EVENT TRIGGER

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO new_owner
ALTER EVENT TRIGGER name RENAME TO new_name

DROP - EVENT TRIGGER

DROP EVENT TRIGGER [ IF EXISTS ] name

Installing PostgreSQL on windows : The "Secondary Logon" service is not running.

Error 

The "Secondary Logon" service is not running. This service is required for the installer to initialize the database. Please start this service and try again.

Solution 
    As error mentioned clearly PostgreSQL server Installation needed  the Secondary logon service to be up and run.  to start the service by

home-> Control panel -> administrative tools -> services 

then find Secondary Logon service. Start this service by right click.

continue with your Installation.
     

Data transfer from one table in a PostgreSQL database to the another table in a different database.

Transfer Data between databases with PostgreSQL

      

      pg_dump sourceDB  -t fromTbl -c -s | psql -h 192.16.3.2 targetDB;

 Problem with pg_dump
    pg_dump: server version: 9.x.x ; pg_dump version: 9.y.y
    pg_dump: aborting because of server version mismatch

above command well suited if both server running on same version of PostgreSQL.
 If Source & Target server  are running in different version, have to use COPY command. 
 copy data from one table in a PostgreSQL database to the corresponding table in a different database running on different server.

i.e cross database copy/transfer data in PostgreSQL,

general syntax  for cross database copy command:

psql -c "copy (select list of column  from table_name ) to stdin " dbanme | psql -c "table_name(specify the column ) from stdout " targetDB

• Target table must be exist
• Table/Relation may differ in name
• if source and target databases are following different table schema  
• Want to transfer only few column from source database table to  target database 
• copy/transfer few column  between databases 
• Server may run different version of PostgreSQL database.
• cross database data transfer  

example :
in sourceDB table employee(eid,ename,esalary, edesignation )
in targetDB table staff(sid,sname,spay)
 now we would like to transfer data between this two databases 

psql -c " copy ( select eid, ename, esalary from employee) to stdin " sourceDB | psql -c " copy staff(sid,sname,spay) from stdout " targetDB

data from employee table in source database  copy/transferred to  staff table in targetDB. 

 note :

• single command will do both backup from one database and restore  to another database
• we just moved only 3 column from employee table not all  
• data type of the two table must be sameif source and target  databases in different server use -h option in psql command
• ex :
• psql -h 127.0.0.1 -c "copy (select eid,ename from emp ) to stdin " sourceDB | psql -h 192.168.37.2 -c "copy staff(sid,sname) from stdout " targetDB

solved error - zabbix cinfigured with PostgreSQL

decided & installing zabbix tool for monitoring rdbms / no-sql database.

I got few error while installing zabbix with  PostgreSQL database. here i'm listing few with solution.

I created database manually in psq.

CREATE DATABASE zabbix;

ERROR 1)

....
....

pg_free_result() expects parameter 1 to be resource, boolean given [include/db.inc.php:566]

....
 

SOLUTION
I checked database "zabbix" which i was created by manually, no relation were found.
run the following command to create relation schema 

postgres@boss:[~]$ psql -hlocalhost zabbix
psql (9.0.6, server 9.0.0)
Type "help" for help.

zabbix=# \i /usr/share/zabbix-server-pgsql/schema.sql
zabbix=# \i /usr/share/zabbix-server-pgsql/images.sql
zabbix=# \i /usr/share/zabbix-server-pgsql/data.sql

ERROR 2)

....
....

zabbix server is not running the information displayed may not be current

....
 

SOLUTION

step 1) go through the log 

root@boss[~]#vi /var/log/zabbix/zabbix_server.log psql

19447:20130704:162637.902 Database is down. Reconnecting in 10 seconds.
 19447:20130704:162647.903 [Z3001] connection to database 'zabbix' failed: [0] FATAL:  role "zabbix" does not exist 

step 2) log clearly tells that no role in name of zabbix  exist in database

postgres@boss:[~]$ psql -hlocalhost zabbix
psql (9.0.6, server 9.0.0)
Type "help" for help.

zabbix=#CREATE ROLE zabbix WITH LOGIN; 

ERROR 3)

....
....
 19447:20130704:163227.949 Database is down. Reconnecting in 10 seconds.
 19447:20130704:163237.950 [Z3001] connection to database 'zabbix' failed: [0] FATAL:  role "zabbix" is not permitted to log in

may get above error if you don't have log-in rights,
SOLUTION

zabbix=#ALTER ROLE zabbix LOGIN; 

ERROR 4)

You are not able to choose some of the languages, because locales for them are not installed on the web server.

SOLUTION
  its waring message only, to  diaplsy installed locales

root@boss:[~]$locale -a 

to select & install missing locales by

root@boss:[~]$dpkg-reconfigure locales

ERROR 5)

....
....
25046:20130705:142910.383 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation config
 [select alert_history,event_history,refresh_unsupported,discovery_groupid,snmptrap_logging,severity_name_0,severity_name_1,severity_name_2,severity_name_3,severity_name_4,severity_name_5 from config where 1=1 and configid between 0 and 99999999999999]
 25046:20130705:142910.383 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation items
 [select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.flags,i.interfaceid,i.lastclock from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 0 and 99999999999999]
 25046:20130705:142910.384 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation hosts
 [select distinct t.triggerid,t.description,t.expression,t.error,t.priority,t.type,t.value,t.value_flags from hosts h,items i,functions f,triggers t where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.triggerid and h.status in (0) and i.status in (0,3) and t.status in (0) and t.flags not in (2) and h.hostid between 0 and 99999999999999]
 25046:20130705:142910.385 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation trigger_depends
 [select d.triggerid_down,d.triggerid_up from trigger_depends d where 1=1 and d.triggerid_down between 0 and 99999999999999 order by d.triggerid_down]
 25046:20130705:142910.388 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation hosts

 25063:20130705:143048.010 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest
 [select min(t.nextcheck) from httptest t,applications a,hosts h where t.applicationid=a.applicationid and a.hostid=h.hostid and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0) and t.httptestid between 0 and 99999999999999]
 25069:20130705:143049.903 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation escalations
 [select escalationid,actionid,triggerid,eventid,r_eventid,esc_step,status,nextcheck from escalations where 1=1 and escalationid between 0 and 99999999999999 order by actionid,triggerid,escalationid]
 25069:20130705:143052.904 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation escalations
 [select escalationid,actionid,triggerid,eventid,r_eventid,esc_step,status,nextcheck from escalations where 1=1 and escalationid between 0 and 99999999999999 order by actionid,triggerid,escalationid]
 25063:20130705:143053.011 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest
 [select t.httptestid,t.name,t.macros,t.agent,t.authentication,t.http_user,t.http_password from httptest t,applications a,hosts h where t.applicationid=a.applicationid and a.hostid=h.hostid and t.nextcheck<=1373014853 and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0) and t.httptestid between 0 and 99999999999999]
 25063:20130705:143053.012 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest
 [select min(t.nextcheck) from httptest t,applications a,hosts h where t.applicationid=a.applicationid and a.hostid=h.hostid and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0) and t.httptestid between 0 and 99999999999999]
 25069:20130705:143055.904 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation escalations
 [select escalationid,actionid,triggerid,eventid,r_eventid,esc_step,status,nextcheck from escalations where 1=1 and escalationid between 0 and 99999999999999 order by actionid,triggerid,escalationid]
 25063:20130705:143058.013 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest

[0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation escalations
 [select escalationid,actionid,triggerid,eventid,r_eventid,esc_step,status,nextcheck from escalations where 1=1 and escalationid between 0 and 99999999999999 order by actionid,triggerid,escalationid]
 25064:20130705:143105.801 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation drules
 [select distinct r.druleid,r.iprange,r.name,c.dcheckid from drules r left join dchecks c on c.druleid=r.druleid and uniq=1 where r.proxy_hostid is null and r.status=0 and (r.nextcheck<=1373014865 or r.nextcheck>1373014865+r.delay) and mod(r.druleid,1)=0 and r.druleid between 0 and 99999999999999]
 25064:20130705:143105.801 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation drules
 [select count(*),min(nextcheck) from drules where proxy_hostid is null and status=0 and mod(druleid,1)=0 and druleid between 0 and 99999999999999]
 25060:20130705:143105.927 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation alerts
 [select a.alertid,a.mediatypeid,a.sendto,a.subject,a.message,a.status,mt.mediatypeid,mt.type,mt.description,mt.smtp_server,mt.smtp_helo,mt.smtp_email,mt.exec_path,mt.gsm_modem,mt.username,mt.passwd,a.retries from alerts a,media_type mt where a.mediatypeid=mt.mediatypeid and a.status=0 and a.alerttype=0 and mt.mediatypeid between 0 and 99999999999999 order by a.alertid]
 25069:20130705:143107.907 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation escalations
 [select escalationid,actionid,triggerid,eventid,r_eventid,esc_step,status,nextcheck from escalations where 1=1 and escalationid between 0 and 99999999999999 order by actionid,triggerid,escalationid]
 25063:20130705:143108.016 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest
 [select t.httptestid,t.name,t.macros,t.agent,t.authentication,t.http_user,t.http_password from httptest t,applications a,hosts h where t.applicationid=a.applicationid and a.hostid=h.hostid and t.nextcheck<=1373014868 and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0) and t.httptestid between 0 and 99999999999999]
 25063:20130705:143108.017 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  permission denied for relation httptest

SOLUTION
to check the owner of the relation under database zabbix, it must be zabbix. in my case it is public then I alter into zabbix

step 1) check the owner of the table in PostgreSQL by

postgres@boss:[~]$ psql -hlocalhost zabbix
psql (9.0.6, server 9.0.0)
Type "help" for help.

zabbix=#\dt             
 List of relations
 Schema |         Name          | Type  | Owner  
--------+-----------------------+-------+--------
 public | acknowledges          | table | public
 public | actions               | table | public
 public | alerts                | table | public
 public | applications          | table | public
 public | auditlog              | table | public
 public | auditlog_details      | table | public
 public | autoreg_host          | table | public 
 ....
....

here Owner of the relation is public, alter into zabbix by

its tedious to alter one by one through single alter statement by

postgres@boss:[~]$ psql -hlocalhost zabbix
psql (9.0.6, server 9.0.0)
Type "help" for help. 
 alter table  owner to zabbix;

instead of,  alter owner of the all relation in zabbix database in PostgreSQL by

postgres@boss:[~]$ for tbl in `psql -qAt -h localhost -c "select tablename from pg_tables where schemaname = 'public';" zabbix` ;
 do  psql -hlocalhost -c "alter table $tbl owner to zabbix" zabbix ; done

ERROR 6)

item [Zabbix server:zabbix[process,ipmi poller,avg,busy]] became not supported: No "ipmi poller" processes started
 25066:20130705:145544.791 item [Zabbix server:zabbix[process,java poller,avg,busy]]  became not supported: No "java poller" processes started
 25066:20130705:145544.791 item [Zabbix server:zabbix[process,node watcher,avg,busy]] became not supported: No "node watcher" processes started
 25066:20130705:145549.798 item [Zabbix server:zabbix[process,snmp trapper,avg,busy]] became not supported: No "snmp trapper" processes started abbix-server-pgsql/data.sql

SOLUTION
open zabbix_server.conf file and add below 2 line

root@boss:[~]$ vi /etc/zabbix/zabbix_server.conf

StartSNMPTrapper=1
SNMPTrapperFile=[TRAP FILE]

ERROR 7)

PHP Fatal error:  Call to undefined function mysql_connect() in /etc/zabbix/mysql.php on line 62ERROR

SOLUTION

 open and add  extensions=mysql.so

root@boss:[~]$ vi /etc/php5/apache2/php.ini 
 extensions=mongo.so

and 

root@boss:[~]$ vi /etc/php5/cli/php.ini 
extensions=mongo.so

List the active connection/user in PostgreSQl,MySQL & mongoDB

As a DBA have to ensure number of active connection in database & which query consuming longer time to execute.

In PostgreSQL we can query from "pg_stat_activity" to know the status of the each connection.

when I was working in mongoDB(no-SQL) , wanted  to monitor the client  who currently accessing my server & the list active of operation.

here simple comparison,  how we to get  number of  active client (remote / local i.e different user ) connected to databases ( PostgreSQL,MySQL and mongoDB ) server.


PostgreSQL
----------------

select* from pg_stat_activity

postgres=# select datname, usename, application_name, client_addr,client_port,query_start,current_query from pg_stat_activity
 +------+---------+------------------------------+-------+---------+------+-------+------------------+
|datname  |usename   | application_name         | client_addr  | client_port | query_start 
+------+---------+------------------------------+-------+---------+------+-------+------------------+
template1 | postgres | pgAdmin III - Browser    | 127.0.0.1    |       56019 | 2013-06-14 19:52:52.775344+05:30
 SAAS     | postgres | psql                     | 192.168.1.10 |       58776 | 2013-06-14 19:58:33.084661+05:30
 koha     | postgres | pgAdmin III - Browser    | 192.168.0.2  |       56020 | 2013-06-14 19:52:52.729838+05:30
 pis      | postgres | pgAdmin III - Query Tool | 127.0.0.1    |       56022 | 2013-06-14 19:56:21.729744+05:30-

   - It display the list of active user/client ip address and query details.

Mysql
----------

show processlist; 

mysql> show processlist;
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| Id   | User    | Host                         | db    | Command | Time | State | Info             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| 3832 | root    | localhost                    | mysql | Query   |    0 | NULL  | show processlist |
| 3834 | erbnext | localhost                    | NULL  | Sleep   | 2524 |       | NULL             |
| 3837 | root    | solaimurugan.chennai.in:36125| koha  | Sleep   |    3 |       | NULL             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
3 rows in set (0.00 sec) 

it states that 3 active connections, 2 from localhost & 1 from client solaimurugan.chennai.in & it connects database mysql & koha.


mongoDB
------------

db.currentOp(true)

root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.currentOp(true).inprog.forEach(function(d){if(d.client && d.client!="0.0.0.0:0")printjson(d.client)})
----------------------------------------------------------------------------------------------------------------------
"solaimurugan.chennai.in:56231"
"127.0.0.1:49563" 
"192.168.31.101:50132" 
-------------------------------------------------------------------------------------------------------------------------------------

 mongoDB also has command to view total number of current connection & available connection to be established by client



db.serverStatus()

root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.serverStatus().connections
-----------------------------------------------------------------------------------------------------------------------------
{ "current" : 3, "available" : 816 }
------------------------------------------------------------------------------------------------------------------------------





Relation does not exist, but can access if i'm using within double quote

SQL is not case sensitive as long as you omit the quotes. The following names are all identical: 

           EMPLOYEE, EmPLoyee, emploYEE

The following tables are different: 

           "EMPLOYEE", "EmPLoyee", "emploYEE"

while using this 

\d EMPLOYEE (or)  \d employee

   it throw error : relation EMPLOYEE does not  exist.


\d "EMPLOYEE"

   it display the EMPLOYEE relation description 

if you get struck some where in PostgreSQL like this, do rename the table to either all upper/lower case.

1) \o /tmp/rename_tbl_lower

2) select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';

come out of psql then run

3) psql -U username database < /tmp/rename_tbl_lower


enter into psql 

\d employee 

 it display the EMPLOYEE relation description  


I've faced this issue while migrating data from Oracle to PostgreSQL  using Navigator tool. normally navigator tool ignore the constraint. it focus only the data migration part.  for this reason i just used ispirer-sqlways for convertion of  schema.

relation name created by  navigator and ispirer-sqlways are totally Ir-relevant, so I renamed all relation by upper/lower as said above.