DevAdmin

I am so glad to announce pgBucket 2.0 beta version, which is evolved from the version 1.0. Below are this version feature highlights and hoping that everybody likes these features. Event jobs (Cascading jobs) Dedicated configuration file Extended table print Auto job disable Custom job failure Dedicated connection pooler Improved the daemon stability/coding standards Please find the below URL for the features review. https://bitbucket.org/ dineshopenscg/pgbucket/ overview --Dinesh

Sharing knowledge which I have gained from last 6 years. So glad to be part of PostgreSQL High Performance Cookbook, where I have discussed all the knowledge I have gained from PostgreSQL database. PostgreSQL High Performance Cookbook Working with PostgreSQL from last 6 years, I have gained so much of knowledge about database management systems. Being a DBA for several years, I explored so many tools which work great with PostgreSQL database. During this 6 years journey, I got a chance to meet many wonderful peoples who guided me very well. I would like to say thanks to everyone who taught me PostgreSQL database in soft/hard ways :-). Also, would like to say thanks to every PostgreSQL developer, and authors and bloggers, from where I have learned many more things. Finally thanks to OpenSCG team Thanks to my wife manoja  for her wonderful support, and my friend Baji Shaik for his help in writing the content.

pgBucket v1.0 pgBucket v1.0 (concurrent job scheduler for PostgreSQL) is released. This version is more stable and fixed the issues which was observed in the previous beta releases. Highlights of this tool are Schedule OS/DB level jobs Cron style syntax {Schedule up to seconds} On fly job modifications Instant daemon status by retrieving live job queue, job hash Enough cli options to deal with all the configured/scheduled job Here is the URL for the pgBucket build/usage instructions.  https://bitbucket.org/dineshopenscg/pgbucket I hope this tool will be helpful for the PostgreSQL users to get things done in the scheduled time. Note: This tool requires c++11{gcc version >= 4.9.3} to compile. --Dinesh

Hi Everyone, I would like to inform to you all that,  pgBucket  beta2[Simple concurrent job scheduler for postgresql] version is ready with more stability. Thank you all in advance for your inputs/comments/suggestions. --Dinesh

Hi Everyone, I would like to inform to you all that, pgBucket [Simple concurrent job scheduler for postgresql] beta version is ready with enhanced architecture and new features. It would be more great if you could share your inputs and suggestions on this, which will help me to make this tool as stable. Thank you all in advance. --Dinesh

Hi All, I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database. I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ], pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals. Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works. Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way. Features are: OS/DB jobs Cron style sytax Online job modi...

Hi, I am pretty sure that, there will be a right heading for this post. For now, i am going with this. If you could suggest me proper heading, i will update it :-) OK. let me explain the situation. Then will let you know what i am trying to do here, and how i did it. Situation here is, We have a table, which we need to run update on “R” no.of records. The update query is using some joins to get the desired result, and do update the table.  To process these “R” no.of records, it is taking “H” no.of hours. That too, it’s giving load on the production server. So, we planned to run this UPDATE as batch process.  Per a batch process, we took “N” no.or records. To process this batch UPDATE, it is taking “S” no.of seconds. With the above batch process, production server is pretty stable, and doing great. So, we planned to run these Batch updates parallel.  I mean, “K” sessions, running different record UPDATEs. Of-course, we can also increase the Batch size ...

Hi As a part of ORACLE to PostgreSQL Migration, I come across to implement a trigger on Oracle, which sync it's data to PostgreSQL. I have tried with a simple table as below, which is hopefully helpful to others. Find this link to configure the heterogeneous dblink to postgres. I believe, the below approach works effectively with the Primary Key tables of Oracle Database. If we don't have primary key in a table, then the UPDATE,DELETE statements going to fire multiple times in Postgres, which leads performance issues. ORACLE CREATE TABLE test(t INT PRIMARY KEY); CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; C number; N number; BEGIN c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@pglink; IF INSERTING THEN DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'INSERT INTO test VALUES('||:NEW.t||');'); n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c); ELSIF DELETING THEN DBMS_HS_PASSTHROU...

I am sure that, most of you aware of this. But, for me it's the first time, I accomplished it. As one of my assigned tasks to build a 32-bit instance of postgresql on 64-bit machine, I have followed the below approach. I hope, it will be helpful to others as well, if you got any problems. As an initial step on this task, I have tried to build a sample "c" program using "gcc -m32". Once, I resolved this, I moved to compile the PostgreSQL 9.0. [root@localhost Desktop]# gcc -m32 -o test test.c In file included from /usr/include/features.h:385,                  from /usr/include/stdio.h:28,                  from test.c:1: /usr/include/gnu/stubs.h:7:27: error: gnu/stubs-32.h: No such file or directory To resolve the above issue, I have installed the 32-bit glibc-devel package through yum. yum -y install glibc-devel.i686 glibc-devel Again, I have tried to run the same command. [root@localhost...

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL. We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo. Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..? As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.} Please follow the below steps to set up the 1 - 1 multi master replication. 1. Follow the below steps to get all the pre-requisites for the Bucardo. yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here . tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For t...

Recently i faced a problem with some catalog views, which do not give you the complete information as a normal user. For example, take pg_stat_activity, pg_stat_replication, pg_settings, e.t.c. If we run the above catalog views as non super user, you don't get the result what we get as a superuser. This is really a good security between super user and normal user. What we need to do, if we want to collect these metrics as normal user. I think the possible solution is "Write a wrapper function with security definer as like below" and grant/revoke the required privileges to the user/public. CREATE OR REPLACE FUNCTION pg_stat_activity( RETURNS SETOF pg_catalog.pg_stat_activity AS $$ BEGIN RETURN QUERY(SELECT * FROM pg_catalog.pg_stat_activity); END $$ LANGUAGE PLPGSQL SECURITY DEFINER; REVOKE ALL ON FUNCTION pg_stat_activity() FROM public; CREATE VIEW pg_stat_activity AS SELECT * FROM pg_stat_activity(); REVOKE ALL ON pg_stat_activity FROM public; ...

I hope this gives you a bit FUN with pg SQL. select * from (select array_to_string(array_agg(CASE WHEN (power((xx.x-25),2)/130+power((yy.y-25),2)/130)=1 THEN '$' WHEN (sqrt(power(xx.x-20,2)+power(yy.y-20,2)))<2 THEN '#' WHEN (sqrt(power(xx.x-20,2)+power(yy.y-30,2)))<2 THEN '#' WHEN (sqrt(power(xx.x-29,2)+power(yy.y-25,2)))<4 THEN '#' WHEN (power((xx.x-10),2)/40+power((yy.y-10),2)/40)=1 THEN '$' WHEN (power((xx.x-10),2)/40+power((yy.y-40),2)/40=1) THEN '$' ELSE ' ' END),' ') as cartoon from (select generate_series(1,40) as x) as xx,(select generate_series(1,50) as y) as yy group by xx.x order by xx.x) as co_ord; Oracle Mode CREATE OR REPLACE TYPE series AS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(n INT, m INT) RETURN series PIPELINED IS BEGIN FOR i IN n..m LOOP PIPE ROW (i); END LOOP; RETURN; END; SELECT REPLACE(WM_CONCAT( CASE WHEN (power((xx.COLUMN_VALUE-25),2)/...

Hi, Today, i have faced an interesting problem like below. I want to get MIN(UNNEST(ARRAY[0, 1, 2, ....]))  as non-zero small element. In this case, it's 1. Below is my problem description. postgres=# SELECT SUM(val), MAX(val), MIN(val) FROM ( SELECT UNNEST(ARRAY[1, 2, 3]) val UNION ALL --Appending some dummy rows, for getting what i would like to expect. SELECT UNNEST(ARRAY[0, 0, 0]) val ) AS FOO;  sum | max | min -----+-----+-----    6 |  3  | 0 (1 row) As you see, i can able to identify the sum, max without any problem. But when it comes to "min", i am getting the value as 0. But, I want the minimum as 1 as per my requirement. I can able to get the min, max, sum from the first array it self. But, my implementation doesn't allow this. :( I have tried it in so many ways, and finally found the following solution. I believe, there will be some better ways also, but just wanted to keep a note on this. postgres=# SELECT SUM(val), MAX(...

Hi Isn't the nice way to represent the Oracle Architecture. Soon i will be posting the PostgreSQL architecture as well. Dinesh Kumar

I would like to share the pgpool configuration, and it's failback mechanism in this post. Hope it will be helpful to you in creating pgpool and it's failback setup. Pgpool Installation & Configuration 1. Download the pgpool from below link(Latest version is 3.2.1).     http://www.pgpool.net/mediawiki/index.php/Downloads 
2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory. 3. Install the pgpool by executing the below commands:   ./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location. /opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createla...

It took sometime to me to understand the powerful concept, i.e, REGEX. Below is the REGEX expression to parse the CSV log files, which have been generated by PostgreSQL. Regex is :- -=-=-=-=-= "^((([^, \"\n\r ]*)|( \" ([^ \" ]|( \"\" ))* \" )),){22}( \" ([^ \" ]|( \"\" ))* \" [ \r\n ]+)" Use any regex tools like RegexBuddy, which will give you detailed information about this regular expression. Dinesh Kumar

I developed this game a bit long ago, and would like to share with the world. Yes, ofcourse, we can optimize the code of c here, but i have concentrated only on desired functionality for this. Once, i got the desired result, i haven't looked into any of the line in this code. {Very bad habbit, i need to over come this.} I hope you enjoy it, and correct if any problems occurs. This game is for only 2 players, which will give you the realtime game feel with your opponent. First find the code, and then instructions. C Program -=-=-=-=-=- #include "stdio.h" #include "ncurses.h" #include "/opt/PostgreSQL/9.0/include/libpq-fe.h" #include "stdlib.h" #include "string.h" char symbol[3]; PGconn * PGconnect(char ch) { PGconn *conn; PGresult *res; FILE *fp; int cnt,i=0; char conn_string[500],hostaddr[32],port[7],dbname[50],user[50],password[50],name[10]; const char *paramValues[2]; fp=fopen("/tmp/.cred","...

Do you want an input from end user, while running PostgreSQL script. No Problem, here is the one of the solution for you. In the following example, i am taking the confirmation from an end user, before dropping an existing database called "sample". -- When any exception raised from this script, the complete script execution is going to fail. -- We will be raising a custom exception, when the Drop DB != 'y|Y'; -- \set ON_ERROR_STOP on -- Take input from the user. -- \prompt 'Are you sure to drop ' Do_You_Want_To_Drop_Db -- Get the user input. -- \set Do_Drop_Db '\'' :Do_You_Want_To_Drop_Db '\'' -- Creating a temp table to store the value of the user confirmation. -- This will be dropped when the session got closed. -- CREATE TEMP TABLE Drop_Db AS SELECT :Do_Drop_Db::text AS confirm; DO $$ BEGIN IF EXISTS(SELECT * FROM Drop_Db WHERE confirm NOT IN('y', 'Y')) THEN RAISE EXCEPTION 'Database won''t dr...

Hi Below is the sample ECPG test case what i have prepared. Sample ECPG Scripts ================ -bash-4.1$ more foo2.h typedef struct { foo123 a[8]; } bar; -bash-4.1$ more foo1.h typedef struct { int x; }foo123; -bash-4.1$ more foo.pgc EXEC SQL INCLUDE "foo1.h"; EXEC SQL INCLUDE "foo2.h"; int main() {     EXEC SQL BEGIN DECLARE SECTION;         char* dbname = "edb";         char* db     = "edb@localhost:5444";         char* user   = "enterprisedb";         char* passwd = "adminedb";     EXEC SQL END DECLARE SECTION;     bar records;     EXEC SQL WHENEVER SQLERROR      GOTO sql_error;     EXEC SQL CONNECT :user IDENTIFIED BY :passwd AT :dbname USING :db;         EXEC SQL AT :dbname DECLARE cur_TBL CURSOR FOR                 SELECT CO...

Hi , Libcurl is a utility tool which we can also send e-mails using SMTP library. Below is a sample program which helps you to do the same from Linux-C Language. Below is the program ================ #include <stdio.h> #include <string.h> #include <curl/curl.h> int main(void) {   CURL *curl;   CURLcode res;   FILE *FP;   struct curl_slist *recipients = NULL;   static const char *from = "********@gmail.com";   static const char *to = "********@gmail.com";   FP=fopen("/tmp/Email","r");   curl = curl_easy_init();   if(curl) {     curl_easy_setopt(curl, CURLOPT_URL, "smtp://smtp.gmail.com:587");     curl_easy_setopt(curl, CURLOPT_USE_SSL, CURLUSESSL_ALL);     curl_easy_setopt(curl, CURLOPT_USERNAME, "From_Email_User@gmail.com");     curl_easy_setopt(curl, CURLOPT_PASSWORD, "*********");     curl_easy_setopt(curl, CURLOPT_MAIL_FROM, from...