DevAdmin

pgBucket 2.0 Beta Is Ready

2017-05-03T12:18:00.001+05:30

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

PostgreSQL High Performance Cookbook

2017-04-14T15:54:00.004+05:30

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 is ready

2016-10-06T14:35:00.001+05:30

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

pgBucket beta2 is ready

2016-08-03T08:19:00.001+05:30

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

pgBucket beta version is ready

2016-07-04T01:50:00.003+05:30

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

pgBucket - A new concurrent job scheduler

2016-06-03T12:11:00.002+05:30

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 modifications
Required cli options

Dependencies:

C++11

Here is the link for the source/build instructions, which hopefully helpful for you.

Let me know your inputs/suggestions/comments, which will help me to improve this tool.

Thanks as always.

--Dinesh Kumar

Parallel Operations With pl/pgSQL

2015-07-09T18:21:00.001+05:30


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 here.


 But we want to use much cpus to complete all these UPDATES as soon as possible.

Problem here is,

So, as i said, we need to run multiple UPDATEs on multiple records in parallel. But, how can one session is going to communicate with other sessions on this batch records.

I mean, If one session is running updates on 1 to 1000, how could the second session knows that the other session was processing from 1 to 1000.
If the second session knows this information, this will start from 1001 to 2000 in parallel. This is the problem i am trying to solve here.

I am not sure whether this is the optimal solution, but as per my requirement it’s working. :-)  Let me know if you see any problems in it.

Object Definitions

                      Table "public.test"
 Column |  Type   |                     Modifiers
--------+---------+----------------------------------------------------
 t      | text    |
 i      | boolean |
 seq    | bigint  | not null default nextval('test_seq_seq'::regclass)

postgres=# INSERT INTO test VALUES(generate_series(1, 9000), false, generate_series(1, 9000));
INSERT 0 9000

postgres=# \ds testing
           List of relations
 Schema |  Name   |   Type   |  Owner
--------+---------+----------+----------
 public | testing | sequence | postgres
(1 row)


CREATE OR REPLACE FUNCTION public.update_test_parallel(batch integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
VAR BIGINT;
DUMMY TEXT;
BEGIN

-- Adding this for Demo
--

SELECT pg_sleep(10) INTO DUMMY;

SELECT pg_advisory_lock(-1234) INTO DUMMY;

        SELECT nextval('testing') INTO VAR;
        EXECUTE E'SELECT nextval(\'testing\') FROM generate_series('||VAR||','||VAR+BATCH||')';

        -- We need to decrease the sequence value by one, since we executed nextval expression once
        -- Otherwise, it will affect the other session''s  execution.
        --
        SELECT setval('testing', currval('testing')-1) INTO DUMMY;

SELECT pg_advisory_unlock(-1234) INTO DUMMY;

        -- I want to update the test table of the column "I" with value "true".
        --
UPDATE test SET I=true WHERE SEQ BETWEEN VAR AND (VAR+BATCH);


RAISE NOTICE 'VAR IS %, VAR+BATCH IS %', VAR, (VAR+BATCH);
RAISE NOTICE 'CURRENT SEQ VALUE IS %', currval('testing');

EXCEPTION WHEN OTHERS THEN

        -- If there is an exception, we need to reset the sequence to it''s start position again.
        -- So that, the other sessions, will try with the same sequence numbers.
        --
        SELECT setval('testing', VAR-1) INTO DUMMY;
        SELECT pg_advisory_unlock(-1234) INTO DUMMY;
        RAISE EXCEPTION '%', SQLERRM;
END;
$function$;

Session 1

 
postgres=# SELECT public.update_test_parallel(3000);
NOTICE:  VAR IS 1, VAR+BATCH IS 3001
NOTICE:  CURRENT SEQ VALUE IS 3000
update_test_parallel
----------------------

(1 row)

Session 2

 
postgres=# SELECT public.update_test_parallel(3000);
NOTICE:  VAR IS 3001, VAR+BATCH IS 6001
NOTICE:  CURRENT SEQ VALUE IS 6000
update_test_parallel
----------------------

(1 row)

Session 3

 
postgres=# SELECT public.update_test_parallel(3000);
NOTICE:  VAR IS 6001, VAR+BATCH IS 9001
NOTICE:  CURRENT SEQ VALUE IS 9000
update_test_parallel
----------------------

(1 row)

Desired result

 
postgres=# SELECT COUNT(*) FROM test WHERE i is true;
count
-------
  9000
(1 row)


In the above implementation, i took "sequence" for the session's parallel execution with the help of advisory locks. Hope this helps to others as well.

Thanks as always for reading it, and welcome your inputs.

--Dinesh Kumar

Heterogeneous Database Sync

2014-12-30T20:30:00.001+05:30

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_PASSTHROUGH.PARSE@pglink(c, 'DELETE FROM test WHERE t='||:OLD.t);
  n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c);
 ELSIF UPDATING THEN
  DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'UPDATE test SET t='||:NEW.T||' WHERE t='||:OLD.T);
  n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c);
 END IF;
 DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@pglink(c);
COMMIT;
END;
/
SQL> INSERT INTO test VALUES(1);
SQL> INSERT INTO test VALUES(2);
SQL> INSERT INTO test VALUES(3);


SQL> DELETE FROM test WHERE t=2;

SQL> UPDATE test SET t=10 WHERE t=1;
SQL> COMMIT;
Commit complete.

PostgreSQL

CREATE TABLE test(t INT PRIMARY KEY);
postgres=# SELECT * FROM test;
t
----
3
10
(2 rows)

NOTE
Oracle's DG4ODBC don't support the 2 phase commit with PostgreSQL. In the above case, if we issue ROLLBACK in oracle, the data in postgresql will remain, which will lead to inconsistency. To fix this, i believe we have to use Asynchronous data copy by scheduling a job in Oracle as below.

CREATE SEQUENCE test_seq ORDER;

CREATE TABLE test_backup(SEQ INT, EVT VARCHAR(2), t INT, PRIMARY KEY(SEQ, EVT, t));

-- Trigger, which records the test table's information into test_backup.

CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
  INSERT INTO test_backup VALUES(test_seq.nextval, 'I', :NEW.t);
ELSIF DELETING THEN
  INSERT INTO test_backup VALUES(test_seq.nextval, 'D', :OLD.t);
ELSIF UPDATING THEN
    INSERT INTO test_backup VALUES(test_seq.nextval, 'UD', :OLD.t);
  INSERT INTO test_backup VALUES(test_seq.nextval, 'UI', :NEW.t);
END IF;
END;
/

-- Procedure, which sync the test_backup table to postgres test table.

create or replace PROCEDURE SYNCORATOPG
IS
REC TEST_BACKUP%ROWTYPE;
C NUMBER;
N NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
TYPE SYNCID IS TABLE OF INTEGER;
SYNCNUM SYNCID;
CNT INT:=0;
BEGIN
  
    BEGIN
        SYNCNUM:=SYNCID();
        FOR REC IN (SELECT * FROM test_backup ORDER BY SEQ ASC)
        LOOP
        SYNCNUM.EXTEND;
        CNT:=CNT+1;
        SYNCNUM(CNT):=REC.SEQ;
        c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@TOPG;
        IF REC.EVT = 'I' OR REC.EVT='UI' THEN
           DBMS_HS_PASSTHROUGH.PARSE@TOPG(c, 'INSERT INTO test VALUES('||REC.t||');');
        ELSIF REC.EVT = 'D' OR REC.EVT='UD' THEN
           DBMS_HS_PASSTHROUGH.PARSE@TOPG(c, 'DELETE FROM test WHERE t='||REC.T);
        END IF;
      
         n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@TOPG(c);
         DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@TOPG(c);
        END LOOP;
        
        COMMIT;
    END;
    
    BEGIN
      FOR i IN 1..SYNCNUM.COUNT
      LOOP
        DELETE FROM test_backup WHERE SEQ=SYNCNUM(i);
        SYNCNUM.DELETE(i);
      END LOOP;
       COMMIT;
    END;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'Fatal error.'||SQLERRM);
END;

--Scheduling a JOB in Oracle, which call the above SYNC procedure for every 1 minute.

BEGIN
DBMS_JOB.isubmit (
job => 113,
what => 'BEGIN SYNCORATOPG; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/(24*60) /* 1 Minute */');

COMMIT;
END;
/

-- Inserting Sample Data In Oracle

INSERT INTO test VALUES(-1);
INSERT INTO test VALUES(-2);
INSERT INTO test VALUES(-3);

UPDATE test SET t=-20 WHERE t=-2;
DELETE FROM test WHERE t=-1;
INSERT INTO test VALUES(-2);
COMMIT;

-- PostgreSQL

postgres=# SELECT * FROM test;
 t
----
  -3
 -20
  -2
(3 rows)

As usual, welcome your inputs.

--Dinesh Kumar

32-bit PostgreSQL Compilation On 64-bit CentOS 6.x

2014-06-03T13:16:00.002+05:30

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 Desktop]# gcc -m32 -o test test.c
/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-redhat-linux/4.4.6/libgcc_s.so when searching for -lgcc_s
/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-redhat-linux/4.4.6/libgcc_s.so when searching for -lgcc_s
/usr/bin/ld: cannot find -lgcc_s
collect2: ld returned 1 exit status

Now, I got a different error message, and tried to install the 32-bit libgcc.

[root@localhost Desktop]# yum install libgcc-*.i686
--> Running transaction check
---> Package libgcc.i686 0:4.4.7-4.el6 will be installed
--> Finished Dependency Resolution
Error: Protected multilib versions: libgcc-4.4.7-4.el6.i686 != libgcc-4.4.6-3.el6.x86_64

As it's complaining, we have the old version of x86_64, when compared the new one. Hence, I have tried to update the existing x86_64.

[root@localhost Desktop]# yum update libgcc-4.4.6-3.el6.x86_64
---> Package libgcc.x86_64 0:4.4.6-3.el6 will be updated
---> Package libgcc.x86_64 0:4.4.7-4.el6 will be an update
--> Finished Dependency Resolution

Once, it's updated the given library, I have again tried to install the libgcc-*.i686.

[root@localhost Desktop]# yum install libgcc-*.i686
Resolving Dependencies
--> Running transaction check
---> Package libgcc.i686 0:4.4.7-4.el6 will be installed
--> Finished Dependency Resolution

Now, I am trying to run the same "gcc -m32" command to check for any further issues.

[root@localhost Desktop]# gcc -m32 -o test test.c
[root@localhost Desktop]# ./test
Hello World

It looks, the sample "c program" is working fine, as a 32-bit application. Now, i am moving to PostgreSQL 9.0. As per my observation I have updated, installed the below components for the PostgreSQL.

yum update readline-6.0-3.el6.x86_64
yum install *readline*i686
yum update zlib-1.2.3-27.el6.x86_64
yum install *zlib*i686

Once, I got the all required 32-bit libraries, I have tried to compile the postgresql as below.

[root@localhost build]# CFLAGS=-m32 LDFLAGS=-m32 CXXFLAGS=-m32 ./configure --prefix=/opt/PostgreSQL/build
[root@localhost build]# make -j 4; make install;
....
make[1]: Leaving directory `/root/Downloads/postgresql-9.0.17/config'
PostgreSQL installation complete.

It seems, postgresql has built successfully on 64-bit machine as a 32-bit application.

Checking for the confirmation from OS, PG.

postgres=# SELECT version();
                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0.17 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit
(1 row)

[root@localhost build]# file /sbin/init
/sbin/init: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped

And, at the end, I have the postgresql as 32-bit application in 64-bit machine.

Thank you for reading, and please comment on this, if you have any questions.

--Dinesh Kumar

N-Node Mutlimaster Replication With Bucardo...!

2014-04-24T13:42:00.000+05:30

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 this, download the required active perl to set up or simply do as below.

apt-get install postgresql-plperl-9.3
or
yum install postgresql-plperl-9.3

Make a copy of Old $PGLIBPATH/plperl.so
Move the new plperl.so to $PGLIBPATH/plperl.so

3. plperl extension.

Create extension plperl;

4. Create 3 databases like "node1", "node2", "node3".

CREATE DATABASE node1;
CREATE DATABASE node2;
CREATE DATABASE node3;

5. Execute below statements on 2 databases (node1, node2).

CREATE TABLE test1(t INT PRIMARY KEY);
INSERT INTO test1 VALUES(generate_series(1, 10));

6. Install Bucardo catalog database using the below command.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl install
Postgres version is: 9.3
Attempting to create and populate the bucardo database and schema
Database creation is complete
Connecting to database 'bucardo' as user 'bucardo'
Updated configuration setting "piddir"
Installation is now complete.

7. Adding databases, those will be part of mutli-master replication.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add db db1 dbname="node1"
Added database "db1"
postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add db db2 dbname="node2"
Added database "db2"

8. Creating a herd of db1 tables.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add all tables db=db1 herd=db1_herd
Creating herd: db1_herd
New tables added: 1
Already added: 0

9. Creating a sync of this herd from db1->db2.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add sync db1_to_db2 type=pushdelta source=db1_herd targetdb=db2
Added sync "db1_to_db2"

10. Creating a herd of db2 tables.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add all tables db=db2 herd=db2_herd
Creating herd: db2_herd
New tables added: 1
Already added: 0

11. Creating a sync of this herd from db2->db1.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add sync db2_to_db1 type=pushdelta source=db2_herd targetdb=db1
Added sync "db2_to_db2"

12. Start the Bucardo process. Default log location for the Bucardo instance is SYSLOG.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl start
Checking for existing processes
Removing /tmp/fullstopbucardo
Starting Bucardo

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ps -ef|grep bucardo
postgres 2740 1128 0 21:26 ? 00:00:00 postgres: bucardo bucardo [local] idle
postgres 2743 1128 0 21:26 ? 00:00:00 postgres: bucardo node1 [local] idle

13. Check the DML activities between these two nodes.

node1=# INSERT INTO test1 VALUES(-1);
INSERT 0 1
node1=# \c node2
node2=# SELECT * FROM test1 WHERE t=-1;
 t
---
-1
(1 row)

node2=# INSERT INTO test1 VALUES(-2)
INSERT 0 1
node2=# \c node1
node1=# SELECT * FROM test1 WHERE t=-2;
 t
---
-2
(1 row)

14. Check the status from the below command.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl status
Days back: 3  User: bucardo  Database: bucardo  PID of Bucardo MCP: 2739
Name       Type  State PID  Last_good Time  I/U/D Last_bad Time
==========+=====+=====+====+=========+=====+=====+========+====
db1_to_db2| P   |idle |2746|7m49s    |0s   |1/0/0|unknown |    
db2_to_db1| P   |idle |2745|6m11s    |0s   |1/0/0|unknown |

It seems everything is perfect, and data is replicating from node1 to node2 and vice-versa. Now, let's try to add a new node, "node3" as part of this mutli-master replication.

15. Add new replication database to Bucardo.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add db db3 dbname="node3"
Added database "db3"

16. Dump and restore of test1 table from either node1 or node2.

17. Create a herd of db3 tables

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add all tables db=db3 herd=db3_herd
Creating herd: db3_herd
New tables added: 1
Already added: 0

18. Create a new sync from db2->db3, db3->db2.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add sync db2_to_db3 type=pushdelta source=db2_herd targetdb=db3
Added sync "db2_to_db3"
postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl add sync db3_to_db2 type=pushdelta source=db3_herd targetdb=db2
Added sync "db3_to_db2"

19. Restart the Bucardo to check for the latest node status.

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl stop
Creating /tmp/fullstopbucardo ... Done

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl start
Checking for existing processes
Removing /tmp/fullstopbucardo
Starting Bucardo
 

postgres@dinesh-VirtualBox:~/Bucardo-4.5.0$ ./bucardo_ctl status
Days back: 3  User: bucardo  Database: bucardo  PID of Bucardo MCP: 2553
Name       Type  State PID  Last_good Time  I/U/D Last_bad Time
==========+=====+=====+====+=========+=====+=====+========+====
db1_to_db2| P   |idle |2563|1s       |0s   |0/0/0|unknown |    
db2_to_db1| P   |idle |2560|1s       |0s   |0/0/0|unknown |    
db2_to_db3| P   |idle |2561|0s       |0s   |1/0/1|unknown |    
db3_to_db2| P   |idle |2562|0s       |0s   |0/0/0|unknown |

20. Here is the small hack, which makes you to embed this db3 into the multi master replication. As Burcardo replication system works based on triggers, we need to make all the triggers what it created on nodes to "ENABLE ALWAYS" for each table.

Node1
-=-=-
node1=# \d test1
     Table "public.test1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 t      | integer | not null
Indexes:
    "test1_pkey" PRIMARY KEY, btree (t)
Triggers:
    bucardo_add_delta AFTER INSERT OR DELETE OR UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE bucardo.bucardo_add_delta_t()
    bucardo_triggerkick_db1_to_db2 AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON test1 FOR EACH STATEMENT EXECUTE PROCEDURE bucardo.bucardo_triggerkick_db1_to_db2()

node1=# ALTER TABLE test1 ENABLE ALWAYS TRIGGER bucardo_add_delta;
ALTER TABLE
node1=# ALTER TABLE test1 ENABLE ALWAYS TRIGGER bucardo_triggerkick_db1_to_db2;
ALTER TABLE

Do "ENABLE ALWAYS" these two triggers on this table. Similarly, do the same activity on all nodes.

Testing Multi-Master replication.

node3=# INSERT INTO test1 VALUES(-1010);
INSERT 0 1
node3=# \c node2
node2=# SELECT * FROM test1 WHERE t=-1010;
   t   
-------
 -1010
(1 row)

node2=# \c node1
node1=# SELECT * FROM test1 WHERE t=-1010;
   t   
-------
 -1010
(1 row)


node2=# INSERT INTO test1 VALUES(-2020);  
INSERT 0 1
node2=# \c node3
node3=# SELECT * FROM test1 WHERE t=-2020;
   t   
-------
 -2020
(1 row)

node3=# \c node1
node1=# SELECT * FROM test1 WHERE t=-2020;
   t   
-------
 -2020
(1 row)

Seems Bucardo is doing 3 node mulit master replication. Thanks to Bucardo Team. :)

--Dinesh Kumar

Normal User As Super User

2014-04-04T09:08:00.000+05:30

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;

This is really a good approach to get the statistics from pg_stat_activity. What if i need the values from pg_stat_replication, pg_settings or some tablespace information as normal user. So, do we need to create wrapper function for each catalog view ? { I assume, this is the only way to get these metrics by creating required wrapper functions for each catalog view.}

Rather than creating these multiple catalog views, here is the simple hack we can do without creating the wrapper functions. Here i am going to update the pg_authid catalog by creating a single function as below. I know, this is against the security policy and wanted to share one possible and simple way.

Function

CREATE OR REPLACE FUNCTION make_me_superuser(isSuper bool)
RETURNS
VOID
AS $$
BEGIN
UPDATE pg_catalog.pg_authid SET
rolsuper=$1::boolean where rolname=<role name>;
END;
$$
LANGUAGE PLPGSQL SECURITY DEFINER;
REVOKE ALL ON FUNCTION make_me_superuser(bool) FROM public;
GRANT EXECUTE ON FUNCTION make_me_superuser(bool) TO <role name>;

Sample Case

postgres=>BEGIN WORK;
BEGIN
postgres=> select make_me_superuser(TRUE);
make_me_superuser
-------------------
(1 row)
postgres=> show data_directory;
  data_directory
--------------------------------------------
C:/Program Files (x86)/PostgreSQL/9.2/data
(1 row)
postgres=> select make_me_superuser(false);
make_me_superuser
-------------------
(1 row)
postgres=> END WORK;
COMMIT

Since we are running this in a transaction mode, which restricts the other same user session's superuser activities.

**Don't implement this in production servers, where your security is crucial.

--Dinesh Kumar

Cartoon in pg.

2014-04-02T11:59:00.000+05:30

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)/130+power((yy.COLUMN_VALUE-25),2)/130)=1
        THEN '$'
        WHEN (sqrt(power(xx.COLUMN_VALUE-20,2)+power(yy.COLUMN_VALUE-20,2)))<2
        THEN '#'
        WHEN (sqrt(power(xx.COLUMN_VALUE-20,2)+power(yy.COLUMN_VALUE-30,2)))<2
        THEN '#'
        WHEN (sqrt(power(xx.COLUMN_VALUE-29,2)+power(yy.COLUMN_VALUE-25,2)))<4
        THEN '#'
        WHEN (power((xx.COLUMN_VALUE-10),2)/40+power((yy.COLUMN_VALUE-10),2)/40)=1
        THEN '$'
        WHEN (power((xx.COLUMN_VALUE-10),2)/40+power((yy.COLUMN_VALUE-40),2)/40=1)
        THEN '$'
        ELSE ' '
      END
    ), ',', ' ') cartoon
FROM
  ( SELECT * FROM TABLE(generate_series(1, 40))) xx,
  ( SELECT * FROM TABLE(generate_series(1, 50))) yy
  GROUP BY xx.COLUMN_VALUE
  ORDER BY xx.COLUMN_VALUE;

Dinesh Kumar

How to get non zero min value from MIN(0, 1, 2)

2014-03-11T15:48:00.002+05:30

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(val), 
COALESCE((SELECT * FROM UNNEST(array_agg(val)) WHERE unnest!=0 ORDER BY unnest ASC LIMIT 1), 0) AS min
postgres-# FROM
postgres-# (
postgres(# SELECT UNNEST(ARRAY[1, 2, 3]) val
postgres(# UNION ALL
postgres(# SELECT UNNEST(ARRAY[0, 0, 0]) val
postgres(# ) AS FOO;
 sum | max | min 
-----+-----+-----
   6 |   3 |   1

(1 row)

Hope it helps to someone.

Dinesh Kumar

Oracle Architecture

2014-02-06T17:13:00.003+05:30

Isn't the nice way to represent the Oracle Architecture.

Soon i will be posting the PostgreSQL architecture as well.

Dinesh Kumar

Pgpool Configuration & Failback

2013-12-31T23:27:00.001+05:30

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 createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool 

/opt/PostgreSQL92/bin $ ./pgpool version
pgpoolII version 3.2.1 (namameboshi)

Pgpool Parameters

listen_addresses = '*'
# This parameter tells about, which clients can connect to the pgpool. If we mention the listen_addresses = 'ip1,ip2', then these two ip's only can access this pgpool.
port = 9999
# We can define the pgpool port using this parameter.
socket_dir = '/tmp'
# This parameter tells about the socket directory.
pcp_port = 9898
# PCP is pgpool admin utility port. Using pcp port, we can execute some node attach and detaches.
pcp_socket_dir = '/tmp'
# PCP socket directory.

backend_hostname0 = '0.176.112.188'
# Node 0 Host IP

backend_port0 = 5432
# Node 0 Port

backend_weight0 = 1
# Node 0 Weight in load balance mode.

backend_data_directory0 = '/opt/PostgreSQL92/data'
# Node 0 Data Direcotry

backend_flag0 = 'ALLOW_TO_FAILOVER'
# Node 0 can allow failover or not.

backend_hostname1 = '0.176.112.189'
# Node 1 Host IP
backend_port1 = 5432
# Node 1 Port

backend_weight1 = 1
# Node 1 Weight in load balance mode.

backend_data_directory1 = '/opt/PostgreSQL92/data'
# Node 1 Data Directory

backend_flag1 = 'ALLOW_TO_FAILOVER'
# Node 1 can allow failover or not.

enable_pool_hba = on
# Enabling pool hba authentication like pg_hba authentication. In any case, we need to have the same pg_hba.conf entries in pool_hba.conf.If we do have different values, then we some of the users can face connectivity issues. Hence, requesting you to make sure both values are same. If we enable the pool_hba, then we need to create "pool_passwd" file in the pgpool.conf paramter's location. pool_passwd must contain the values "username:md5password". You can get this username and passwords from "pg_shadow" postgres table.

authentication_timeout = 60
# Pgpool client authentication timeout.

num_init_children = 32
# This parameter tells about how many initial pg connections need to make while pgpool starts.

max_pool = 3
# This parameter defines about how many pools for each num_init_children connections.

pid_file_name = '/opt/PostgreSQL92/data/pgpool.pid'
# This parameter defines where the pgpool.pid file need to place.

replication_mode = off
# This parameter need to off, if we are using pgpool on top of Slony/Streaming replication. We can also enable this parameter, if you want the pgpool replication rather than Slony/Streaming.

load_balance_mode = off
# This parameter need to on, if we are doing the load balance. i.e, if you want to distribute the "SELECT" queries between the primary and slave servers, then we need to enable this parameter. By enabling this parameter, we can balance work load between the primary and slave.

white_function_list = ''
# Readonly functions/procedures we can mention in this list.
black_function_list = 'nextval,setval'
# Readwrite functions/procedures we need to mention in this list.

recovery_user = 'postgres'
# We need to provide recovery user name.

recovery_password = 'adminedb'
# We need to provide recovery user password. The above two parameters works only to the pcp commands like pcp_recovery_command, pcp_attach_node, pcp_detach_node.

revery_1st_stage_command = 'basebackup'
# We need to provide recovery 1st command which is doing the failback.

You can find more details of these parameters from the below link.
http://www.pgpool.net/docs/latest/tutorial-en.html

Pgpool start and stop commands

1. Start the pgpool using below command:

./pgpool f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf -d -D -n >/opt/PostgreSQL92/data/pgpool.log 2>&1 &

2. Check whether pgpool status:

postgres~/bin> ps ef|grep pgpool
postgres  4288  3754  0 07:55 pts/1    00:00:00 ./pgpool f
/opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf
a /opt/PostgreSQL92/etc/pool_hba.conf -d -D -n
postgres  4289  4288  0 07:55 pts/1
connection request

3. Connect to database using pgpool port 9999.

postgres@localhost:~/bin> psql p 9999
Password:
psql.bin (9.2.1)

Type "help" for help.
postgres=# show port;

 port
 5432
(1 row)

4. You can stop the pgpool by using below command:

./pgpool f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf -m fast stop

5. you can reload the pgpool by using below command:

./pgpool f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf reload

Pgpool Failback

Failback is one of the features of PGPOOL which will re-initiate the failed master as a new slave server to the new master. For doing this operation, we need to follow the below steps.
1) Go to the Pgpool installer location on new master server.

cd /tmp/pgpoolII3.2.1/sql/pgpoolrecovery
export PATH=/opt/PostgreSQL92/bin:$PATH
make
make install

2) Then execute the pgpool-recovery.sql file in "template1" database of new master server.
3) Then prepare a script "basebackup" & "pgpool_remote_start" & "test1.sh" as like attached scripts and place in new master's data directory.
4) Then include the following parameters in pgpool.conf where the pgpool instance is running . Once the modifications done,then reload the pgpool using the following command. Reload Command

/opt/PostgreSQL92/bin/pgpool -f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf reload

Pgpool Parameters
recovery_user = 'postgres'
recovery_password = 'adminedb'
recovery_1st_stage_command = 'basebackup'

**Note: recovery_user credentials must match with the pcp.conf credentials.

5) As a final step, we need to execute the pcp_recovery_command from either new master or new slave as shown below.

/opt/PostgreSQL92/bin/pcp_recovery_node d 1 0.176.112.189 9898
postgres adminedb 0
DEBUG: send: tos="R", len=46
DEBUG: recv: tos="r", len=21, data=AuthenticationOK
DEBUG: send: tos="D", len=6
DEBUG: recv: tos="c", len=20, data=CommandComplete
DEBUG: send: tos="X", len=4

If we get the above kind of message from the recovery command, then our setup has been accomplished.
pcp_recovery_node Explanation
  d, debug : enable debug message (optional)
  timeout     : connection timeout value in seconds. command exits on timeout
  hostname    : pgpoolII hostname
  port#       : PCP port number
  username    : username for PCP authentication
  password    : password for PCP authentication
  nodeID      : ID of a node to recover
test1.sh

#!/bin/sh
export PATH=/opt/PostgreSQL92/bin:$PATH
export LD_LIBRARY_PATH=/opt/PostgreSQL92/lib:$LD_LIBRARY_PATH
export PGPASSWORD=adminedb
mv /opt/PostgreSQL92/data1 /opt/PostgreSQL92/data_old
mv /opt/PostgreSQL92/backup /opt/PostgreSQL92/data1
echo "restore_command = 'cp /var/arch_test/%f %p'">/opt/PostgreSQL92/data1/recovery.conf
echo "standby_mode = 'on'">>/opt/PostgreSQL92/data1/recovery.conf
echo "primary_conninfo = 'host=0.176.112.188 port=5433 user=replication password=replication application_name=Async_Rep'">>/opt/PostgreSQL92/data1/recovery.conf
rm -f /opt/PostgreSQL92/data1/_Promote_Me_Primary_Is_Down_5433
echo "trigger_file = '/opt/PostgreSQL92/data1/_Promote_Me_Primary_Is_Down_5433'">>/opt/PostgreSQL92/data1/recovery.conf

basebackup

#!/bin/sh
export PATH=/opt/PostgreSQL92/bin:$PATH
export LD_LIBRARY_PATH=/opt/PostgreSQL92/lib:$LD_LIBRARY_PATH
export PGPASSWORD=adminedb
echo "archive_command = 'cp %p /var/arch_test/%f && scp %p postgres@0.176.112.189:/var/arch_test/%f'" >> /opt/PostgreSQL92/data1/postgresql.conf
pg_ctl reload
psql -c "select pg_start_backup('pgpool_recovery')" -p 5433
ssh postgres@0.176.112.189 mkdir /opt/PostgreSQL92/backup
rsync -C -a --exclude 'postmaster.pid' --exclude 'postmaster.opts' --exclude 'recovery.done' /opt/PostgreSQL92/data1/ postgres@0.176.112.189:/opt/PostgreSQL92/backup/
export PGPASSWORD=adminedb
psql -c "select pg_stop_backup()" -p 5433
rm -f /opt/PostgreSQL92/data/recovery.done
ssh postgres@0.176.112.189 sh /opt/PostgreSQL92/test1.sh

pgpool_remote_start

#/bin/bash
ssh postgres@0.176.112.189 pg_ctl -D /opt/PostgreSQL92/data1 start

Dinesh Kumar

Regex to parse PostgreSQL CSV log files.

2013-12-27T12:35:00.001+05:30

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

Game with postgreSQL

2013-12-26T15:44:00.001+05:30

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","r");
if(ch=='y')
{
fscanf(fp,"%[^:]s",hostaddr);
fscanf(fp,":%[^:]s",port);
fscanf(fp,":%[^:]s",dbname);
fscanf(fp,":%[^:]s",user);
fscanf(fp,":%s",password);
sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);
}
else
{
fscanf(fp,"%[^\n]s",conn_string);
conn_string[0]='\0';
fscanf(fp,"\n%[^:]s",hostaddr);
fscanf(fp,":%[^:]s",port);
fscanf(fp,":%[^:]s",dbname);
fscanf(fp,":%[^:]s",user);
fscanf(fp,":%s",password);
sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);
}

conn = PQconnectdb(conn_string);
if (PQstatus(conn) == CONNECTION_BAD)
        {
                 fprintf(stderr,"Not able to connect to the database %s",PQerrorMessage(conn));
                 return NULL;
        }
if(ch=='y')
{
res=PQexec(conn,"BEGIN");
PQclear(res);
res=PQexec(conn,"DECLARE find_taken CURSOR FOR select count(*) from public.pggame_control where taken=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(conn, "FETCH ALL in find_taken");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(conn,"END");
PQclear(res);

if(cnt==0)
{
res=PQexec(conn,"TRUNCATE public.pggame_control");
PQclear(res);
res=PQexec(conn,"INSERT INTO public.pggame_control values(false,false)");
PQclear(res);
}
printf("Enter Your Name -> ");
scanf("%s",name);
paramValues[0]=name;
printf("Enter Your Symbol -> ");
scanf("%s",symbol);
paramValues[1]=symbol;
res=PQexec(conn,"TRUNCATE public.pggame_status");
PQclear(res);
res=PQexecParams(conn,"INSERT INTO public.pggame_status(name,symbol,comments,status) values($1,$2,'',false)",2,NULL,paramValues,NULL,NULL,1);
                if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
                PQclear(res);
                }
                PQclear(res);
}
res=PQexec(conn,"TRUNCATE public.pggame_spots");
PQclear(res);
res=PQexec(conn,"TRUNCATE public.pggame_positions");
PQclear(res);
res=PQexec(conn,"INSERT INTO public.pggame_positions values(0,0)");
PQclear(res);
fclose(fp);
return conn;
}

int main()
{
int y=1,x=1,i=1,one=0;
char ch,x1[10],y1[10],cnt,remote_spot_check[200];
PGconn     *your_conn,*other_conn;
PGresult   *res;
FILE *log;
const char *paramValues[2];
log=fopen("/tmp/pggame.log","w");
if(!((your_conn=PGconnect('y'))&&(other_conn=PGconnect('o'))))
return 1;
initscr();
keypad(stdscr,TRUE);
noecho();
x=1;
y=1;
while(x+10<140)
{
x=x+2;
mvprintw(0,x+9,"-=");
mvprintw(36,x+9,"-=");
y=1;
}
y=0;
while(y<35)
{
++y;
mvprintw(y,12,".");
mvprintw(y,141,".");
}
y=2;
x=15;
refresh();
while(1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_spots");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_remote_pos");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_symbol");
mvprintw(y+1,x+15,"%s",PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
}
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_win");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select name from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_win");
mvprintw(15,40,"***** %s WIN The Game ***** .... Press Any Key To Exit ... ",PQgetvalue(res,0,0));
refresh();
PQclear(res);
res=PQexec(other_conn,"END");
getch();
getch();
endwin();
PQfinish(your_conn);
PQfinish(other_conn);
return 0;
}

res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select count(*) from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
if(cnt==1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
mvprintw(37,125,"CONTROL ==>> ");
mvprintw(y,x,"");
}
else
{
mvprintw(37,125,"CONTROL ==>> ");
mvprintw(1,1,"");
}

refresh();
usleep(10000);
one=0;
continue;
}
mvprintw(37,125,"CONTROL <<== ");
mvprintw(y,x,"");
refresh();
if(cnt==0)
{
res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=true");
   if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));
                PQclear(res);
                }
                PQclear(res);
if(one==0)
{
one=one+1;
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_spots");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);

if(cnt>=1)
{
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_remote_pos");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in get_symbol");
mvprintw(y+1,x+15,"%s",PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
res=PQexec(other_conn,"BEGIN");
PQclear(res);
res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_table");
y=atoi(PQgetvalue(res,0,0));
x=atoi(PQgetvalue(res,0,1));
PQclear(res);
res=PQexec(other_conn,"END");
PQclear(res);
mvprintw(y,x,"");
refresh();
}
}




res=PQexec(your_conn,"BEGIN");
PQclear(res);
res=PQexec(your_conn,"DECLARE find_spotted CURSOR FOR select count(*) from public.pggame_control where spot=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(your_conn, "FETCH ALL in find_spotted");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(your_conn,"END");
PQclear(res);
if(cnt>=1)
{
res=PQexec(your_conn,"UPDATE public.pggame_control SET taken=true where taken=false");
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=false where taken=true");
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));
                PQclear(res);
                }
                PQclear(res);
res=PQexec(your_conn,"UPDATE public.pggame_control set spot=false where spot=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
}
}

ch=getch();
if(ch=='q'||ch=='Q')
break;
switch(ch)
{
case 'a':case 'A':
if(x==15)
x=137;
else
x=x-2;
mvprintw(y,x,"");
break;
case 's':case 'S':
if(y==34)
y=1;
else
y=y+1;
mvprintw(y,x,"");
break;
case 'w':case 'W':
if(y==1)
y=34;
else
y=y-1;
mvprintw(y,x,"");
break;
case 'd':case 'D':
if(x==137)
x=15;
else
x=x+2;
mvprintw(y,x,"");
break;
case 'j':case 'J':

res=PQexec(other_conn,"BEGIN");
PQclear(res);
sprintf(remote_spot_check,"DECLARE find_same_spot CURSOR FOR select count(*) from public.pggame_spots where y=%d and x=%d",y-1,x-15);
res=PQexec(other_conn,remote_spot_check);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(other_conn, "FETCH ALL in find_same_spot");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(other_conn,"END");
if(cnt==0)
{
echo();
mvprintw(y,x,"%s",symbol);
sprintf(y1,"%d",y-1);
sprintf(x1,"%d",x-15);
paramValues[0]=y1;
paramValues[1]=x1;
res=PQexecParams(your_conn,"INSERT INTO public.pggame_spots(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);
                if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);
noecho();
refresh();
}
}

sprintf(y1,"%d",y);
sprintf(x1,"%d",x);
paramValues[0]=y1;
paramValues[1]=x1;
res=PQexecParams(your_conn,"INSERT INTO public.pggame_positions(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);
   if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));
                PQclear(res);
                }
                PQclear(res);

res=PQexec(your_conn,"BEGIN");
PQclear(res);
res=PQexec(your_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));
                PQclear(res);
        }
PQclear(res);
res = PQexec(your_conn, "FETCH ALL in find_win");
cnt=atoi(PQgetvalue(res,0,0));
PQclear(res);
res=PQexec(your_conn,"END");
if(cnt>=1)
{
mvprintw(15,40,"***** CONGRATULATIONS  !! ***** You WIN The Game .... Press Any Key To Exit ...");
refresh();
getch();
getch();
PQfinish(your_conn);
PQfinish(other_conn);
endwin();
return 0;
}
}
getch();
PQfinish(your_conn);
PQfinish(other_conn);
endwin();
return 0;
}

SQL for Postgres
-=-=-=-=-=-=-=-=

DROP TABLE public.pggame_positions CASCADE;
DROP TABLE public.pggame_spots CASCADE;
DROP TABLE public.pggame_status CASCADE;
DROP TABLE public.pggame_control CASCADE;
DROP VIEW coord_sum;
CREATE TABLE public.pggame_spots(y int,x int);
CREATE TABLE public.pggame_positions(y int,x int);
create view coord_sum as select e1.y+e1.x as cosum from pggame_spots e,pggame_spots e1 where (e.y,e.x) in (select yy,xx from (select e.y as yy,e1.y as y1,e.y-e1.y diffy,e.x as xx,e1.x as x1,e.x-e1.x diffx from pggame_spots e,pggame_spots e1 where e.y-e1.y between -4 and 4 and e.x-e1.x between -8 and 8) as foo group by yy,xx having array_agg(diffy)@>ARRAY[0,1,2,3,4] and (array_agg(diffx)@>ARRAY[0,-2,-4,-6,-8] or array_agg(diffx)@>ARRAY[0,2,4,6,8]));
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_INSERT_TO_UPDATE() RETURNS TRIGGER AS $$ DECLARE  CNT INT; BEGIN SELECT COUNT(*) INTO CNT FROM pggame_positions; IF(CNT=0) THEN RETURN NEW; ELSE UPDATE pggame_positions SET y=new.y,x=new.x; END IF; RETURN NULL; END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_INSERT_TO_UPDATE_TRIGG BEFORE INSERT ON public.pggame_positions FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_INSERT_TO_UPDATE();
CREATE TABLE PUBLIC.PGGAME_CONTROL(TAKEN BOOLEAN,SPOT BOOLEAN);
INSERT INTO PUBLIC.PGGAME_CONTROL VALUES(FALSE,FALSE);
create table public.pggame_status(name varchar,symbol char(1),comments varchar,status boolean);
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_SPOTS_DUP_PREVENT() RETURNS TRIGGER AS $$ DECLARE CNT INT;BEGIN SELECT COUNT(*) INTO CNT FROM public.pggame_spots where y=new.y and x=new.x; 
IF(CNT>=1) THEN RETURN NULL;
ELSE 
UPDATE PUBLIC.PGGAME_CONTROL SET SPOT=TRUE WHERE TAKEN=FALSE;
RETURN NEW;
END IF;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_SPOTS_DUP_PREVENT_TRIGG BEFORE INSERT ON public.pggame_spots FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_SPOTS_DUP_PREVENT();
CREATE OR REPLACE FUNCTION PUBLIC.PGGAME_STATUS_CHECK() RETURNS TRIGGER AS $$ 
DECLARE 
CNT INT;
BEGIN 
select count(x) into cnt from  pggame_spots e where y=new.y and 5=(select count(*) from pggame_spots e1,(select generate_series(0,8,2) as seq) as e2 where e.x=e1.x+e2.seq and e1.y=new.y);
IF(CNT>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
ELSE
select count(Y) into cnt from  pggame_spots e where X=new.X and 5=(select count(*) from pggame_spots e1,(select generate_series(0,4,1) as seq) as e2 where e.y=e1.Y+e2.seq and e1.x=NEW.x);
IF(CNT>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
ELSE
select count(*) into cnt from pggame_spots e where 5=(select count(*) from (select e.y+generate_series(0,4,1),e.x-generate_series(0,8,2) from pggame_spots e1
intersect
select * from pggame_spots )as sub);
IF(cnt>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
else
select count(*) into cnt from pggame_spots e where 5=(select count(*) from (select e.y+generate_series(0,4,1),e.x+generate_series(0,8,2) from pggame_spots e1
intersect
select * from pggame_spots )as sub);
IF(cnt>=1) THEN
UPDATE PUBLIC.PGGAME_STATUS SET COMMENTS='WIN THE GAME',STATUS=TRUE WHERE NAME IS NOT NULL;
end if;
END IF;
END IF;
END IF;
RETURN NULL;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE TRIGGER PGGAME_STATUS_CHECK_TRIGG AFTER INSERT ON public.pggame_spots FOR EACH ROW EXECUTE PROCEDURE public.PGGAME_STATUS_CHECK();

Steps to run the game
-=-=-=-=-=-=-=-=--=-=
Step 1:- Execute above SQL in your instance and in your opponent instance.

Step 2:- Require second player’s details in your pg_hba.conf Create a file “/tmp/.cred”

192.168.1.2:5432:postgres:postgres:postgres => Your details. 192.168.1.3:5433:postgres:postgres:postgres => Your opponent details.
Syntax:- :::: Caution: - Don’t provide any dbname/user/password which is having “:” symbol.

Step 3:- If you have the PGHOME=/opt/PostgreSQL/9.0 then game.c will work fine. If you don’t have, then please change the header file “/opt/PostgreSQL/9.0/include/libpq-fe.h” in game.c accordingly.

Step 4:-

export LD_LIBRARY_PATH according to your 9.0 instance.
Ex:-
export LD_LIBRARY_PATH=/opt/PostgreSQL/9.0/lib

Step 5:- Complie game.c Ex:-

gcc –o game game.c –L/opt/PostgreSQL/9.0/lib –lpq –lncurses

Step 6:- Run the game Ex:- ./game Enter Your Name -> Dinesh Enter Your Symbol -> D [Any character]

Instructions
-=-=-=-=-=-=
1. Use a,s,d,w for movements and j for spot the symbol.

a => Left

s => Down

d => Right

w => Up

j => For spot the symbol, and then one of the movement key.

Game Rules

-=-=-=-=-=

If you can frame the linear series of the length 5 then you are the winner. We can frame the series as horizontal/vertical/diagonal.

Ex:- Symbol is “*”

* * * * * => Winner

* => Winner

Winner needs to start the new game.

Sample screen shots

-=-=-=-=-=-=-=-=-=-=

Dinesh Kumar

Interactive PostgreSQL Script

2013-11-21T01:15:00.001+05:30

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 drop. Hence closing this session';
ELSE
RAISE NOTICE 'Database will be droped. Please make sure the connection of this databases get closed.';
END IF;
END
$$;

DROP DATABASE sample;

Testing the Script

bash-4.1$ ../bin/psql -U postgres -f /tmp/Interactive_Sql.Sql postgres
Are you sure to drop N
SELECT 1
psql:/tmp/Interactive_Sql.Sql:28: ERROR:  Database won't drop. Hence closing this session

bash-4.1$ ../bin/psql -U postgres -f /tmp/Interactive_Sql.Sql postgres
Are you sure to drop Y
SELECT 1
psql:/tmp/Interactive_Sql.Sql:28: NOTICE:  Database will be droped. Please make sure the connection of this databases get closed.
DO
DROP DATABASE

దినేష్ కుమార్

Dinesh Kumar

Sample ECPG Script

2013-02-13T17:41:00.004+05:30

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 COUNT(*) AS rec_count
                  FROM EMP
        ;
    EXEC SQL AT :dbname    OPEN  cur_TBL;
    EXEC SQL AT :dbname    FETCH cur_TBL INTO :records.a[0].x;
    EXEC SQL AT :dbname    CLOSE cur_TBL;
    EXEC SQL DISCONNECT CURRENT;
    printf("OK\n");
    printf("RECORD COUNT = %d\n",records.a[0].x);
    return 0;
sql_error:
   printf("ERROR\n");
   return 1;
}

Make File
=========
-bash-4.1$ more Makefile
all:
    ecpg -C PROC foo.pgc
    cc -o  foo foo.c -I /opt/PostgresPlus/9.1AS/include -L /opt/PostgresPlus/9.1AS/lib -lecpg

Execution
=========
-bash-4.1$ ./foo
OK
RECORD COUNT = 14

దినేష్ కుమార్
Dinesh Kumar

SMTP Libcurl

2012-11-11T13:25:00.000+05:30

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

    recipients = curl_slist_append(recipients, to);

    curl_easy_setopt(curl, CURLOPT_MAIL_RCPT, recipients);

    curl_easy_setopt(curl, CURLOPT_READDATA, FP);

    curl_easy_setopt(curl, CURLOPT_VERBOSE, 1);

    res = curl_easy_perform(curl);

    if(res != CURLE_OK)

      fprintf(stderr, "curl_easy_perform() failed: %s\n",curl_easy_strerror(res));

    curl_slist_free_all(recipients);

    curl_easy_cleanup(curl);

  }

  return 0;

}

File "/tmp/Email"

===============


[root@localhost ~]# more /tmp/Email 
Date: Mon, 29 Nov 2010 21:54:29 +1100,
To: ***************@gmail,
From: **********@gmail.com,
Subject: Sample message,
Content-Type: text/html; charset="us-ascii"
Hi ,
How do you do.
<b> Any html code  </b>

How to Run

===========


[root@localhost ~]# gcc -o test test.c -lcurl;./test
* Connection #0 to host smtp.gmail.com left intact
> QUIT
< 221 2.0.0 closing connection j9sm2235533pav.15
* Closing connection #0
[root@localhost ~]#

దినేష్ కుమార్

Dinesh Kumar

Skytools - Londiste Replication

2012-11-06T20:25:00.003+05:30

PGQ-Londiste Replication
===================
We all know that the Skytools are popular PostgreSQL Replication tools which have been developed in C/Python and PL/PGSQL. Londiste is the replication which has been implemented on top of PGQ tool which is a snapshot based queuing mechanism. Londiste replication is a Consumer of PGQ which takes the all modifications from PGQ.

How to setup PGQ and Londiste Replication
---------------------------------------------------------

PGQ
------
PGQ is a queue mechanism which gathers all the transactions/modifications from the provider and keep them ready to consumers{Londiste Replication Demon}.

Step 1
----------
Download the Skytool from the below link.

http://pgfoundry.org/frs/download.php/3232/skytools-2.1.13.tar.gz

tar -zxvf skytools-2.1.13.tar.gz

Step 2
---------
Configure make and make install.

[root@localhost skytools-2.1.13]# ./configure  --prefix=/opt/PostgreSQL/9.2/Sky --with-pgconfig=/opt/PostgreSQL/9.2/bin/pg_config

[root@localhost skytools-2.1.13]# make

[root@localhost skytools-2.1.13]# make install

Step 3
------
PYTHONPATH

export PYTHONPATH=/opt/PostgreSQL/9.2/Sky/lib64/python2.6/site-packages/

OR

Copy all the contents of "/opt/PostgreSQL/9.2/Sky/lib64/python2.6/site-packages/" to "/usr/lib64/python2.6/site-packages/".

Step 4
------
Create ticker.ini file which is responsible to create the ticks{Batches}.


-bash-4.1$ more Sky/etc/ticker.ini 

[pgqadm]

job_name = myticker_name

db = dbname =  producer port = 5432 host = localhost 

maint_delay = 1

loop_delay = 0.1

logfile = /tmp/%(job_name)s.log

pidfile = /tmp/%(job_name)s.pid

Step 5
------
Starting PGQ ticker Demon.

-bash-4.1$ pwd

/opt/PostgreSQL/9.2/Sky/bin

-bash-4.1$ ./pgqadm.py ../etc/ticker.ini install

2012-11-06 18:39:45,973 9599 INFO plpgsql is installed

2012-11-06 18:39:45,974 9599 INFO txid_current_snapshot is installed

2012-11-06 18:39:45,975 9599 INFO Installing pgq

2012-11-06 18:39:46,010 9599 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/pgq.sql

The above step creates all it's required PGQ catalog in the database producer.

-bash-4.1$ ./pgqadm.py ../etc/ticker.ini ticker -d

Step 6
------
Create Provider_Subscriber details in a file as below.

-bash-4.1$ more Sky/etc/Producer_Consumer.ini 

[londiste]

job_name = Londiste_Job 

provider_db = dbname=producer port=5432 host=127.0.0.1

subscriber_db = dbname=consumer port=5432 host=127.0.0.1

pgq_queue_name = testing

logfile = /tmp/%(job_name)s.log

pidfile = /tmp/%(job_name)s.pid

Step 7
------
Install the Londiste catalogs into Provider database.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini provider install

2012-11-06 18:41:40,761 9726 INFO plpgsql is installed

2012-11-06 18:41:40,772 9726 INFO txid_current_snapshot is installed

2012-11-06 18:41:40,772 9726 INFO pgq is installed

2012-11-06 18:41:40,773 9726 INFO Installing londiste

2012-11-06 18:41:40,773 9726 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/londiste.sql

Install the Londiste catalogs into Subscriber database.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini subscriber install

2012-11-06 18:41:53,003 9742 INFO plpgsql is installed

2012-11-06 18:41:53,003 9742 INFO Installing londiste

2012-11-06 18:41:53,004 9742 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/londiste.sql

Step 8
------
Start the Londiste Replication Demon.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini replay -d

Step 9
------
Create a sample PRIMARY KEY table in Provider and Receiver

CREATE TABLE TEST(T INT PRIMARY KEY);



producer=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | test | table | postgres

(1 row)

producer=# insert into test values(generate_series(1,1000));

INSERT 0 1000

consumer=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | test | table | postgres

(1 row)

Step 10
----------
Add this table to Londiste Replication.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini provider add public.test

2012-11-06 18:44:37,767 9914 INFO Adding public.test

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini subscriber add public.test

2012-11-06 18:44:44,554 9927 INFO Checking public.test

2012-11-06 18:44:44,561 9927 INFO Adding public.test

Step 11
-----------
Check the replication sync..

producer-# SELECT * FROM pgq.get_consumer_info();

 queue_name | consumer_name |       lag       |    last_seen    

------------+---------------+-----------------+-----------------

 testing    | Londiste_Job  | 00:00:27.837503 | 00:00:27.128354

(1 rows)

producer=# insert into test values(generate_series(-100,-10));

INSERT 0 91

producer=# \c consumer 

You are now connected to database "consumer" as user "postgres".

consumer=# select count(*) from test;

 count 

-------

  1091

(1 row)

దినేష్ కుమార్

Dinesh Kumar

FOREIGN TABLES

2012-11-05T18:59:00.000+05:30

FOREIGN TABLES/DATA WRAPPERS
===============================

Foreign data wrapper is a library which understand the heterogeneous database information. For example, PostgreSQL does not understand the MYSQL data structure/information since both engines have different mechanism. If we want to get any heterogeneous database information then we need to configure the respective fdw(Foreign Data Wrapper) into the PostgreSQL Library location.

Please find the below link, which gives you all the available Foreign Data Wrappers.
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Here we have chosen MYSQL table as a source to PostgreSQL. Below are the steps.

1) Install mysql and mysql-devel using yum .

yum install mysql*

2) Install PostgreSQL 9.1 through EnterpriseDB graphical installer. 3) Get the MYSQL FDW from the below link.

https://github.com/dpage/mysql_fdw/archive/master.tar.gz

4) set the "PATH" as shown below.

export PATH=<PostgreSQL 9.1 Bin>:<Mysql Bin>:$PATH;

Ex:-

[root@localhost mysql_fdw-master]# echo $PATH 
/opt/PostgreSQL/9.1/bin/:/usr/bin/mysql:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

5) Make & Make Install

[root@localhost mysql_fdw-master]# make USE_PGXS=1
[root@localhost mysql_fdw-master]# make USE_PGXS=1 install

6) Create an Extension & Server as below.

postgres=# create EXTENSION mysql_fdw ;
CREATE EXTENSION


postgres=# CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306'); --MySql Port Default 3306

7) Create USER Mapping from PUBLIC users to "MySql Root".

CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr
OPTIONS (username 'root', password 'root');
CREATE USER MAPPING

8) Create Foreign Table as below.

postgres=# CREATE FOREIGN TABLE TEST(T INT) SERVER mysql_svr OPTIONS(TABLE 'DINESH.XYZ'); --Dinesh is a database & XYZ is a table.
CREATE FOREIGN TABLE

9) From MYSQL

mysql> \u DINESH
Database changed
mysql> SELECT * FROM XYZ;
+------+
| T    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

10) From PostgreSQL

postgres=# select * from test;
 t 
---
 1
 2
 3
(3 rows)

postgres=# explain analyze select * from test;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on test  (cost=10.00..13.00 rows=3 width=4) (actual time=0.211..0.212 rows=3 loops=1)

   Local server startup cost: 10
   MySQL query: SELECT * FROM DINESH.XYZ
 Total runtime: 0.675 ms
(4 rows)

దినేష్ కుమార్
Dinesh Kumar

Windows Pg_Dump Script

2012-10-16T18:52:00.002+05:30

Hi ,

I recently developed a pg_dump windows based script, which can help us to schedule the pg_dump through pg_Agent or through windows scheduler.

This script also having the backup retention policy and in any case it retain the last two consistant backups. i.e, if the current backup is successful, then it will remove the 2nd last backup from the backup location .

@ECHO OFF



REM pgAgent Windows Pg_Dump Script 



set BIN="C:\Program Files\PostgreSQL\9.1\bin"

set BACKUPLOC="C:\Dump"

set LOGLOC="C:\Dump"

set DATABASES=%4

set USER=%3

set PORT=%2

set DT=%date%

set HOST=%1

REM DUMP MODE {CUSTOM,TAR,PLAIN}

set DUMP_MODE=CUSTOM 

set RETAIN_LAST_BACKUPS=2

%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select translate(replace('%DT%','/','_'),' ','_');" postgres >_TMP

set /p DT=<_TMP



REM Creating Scheduled Backup



%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select replace('%DATABASES%','""','');" postgres > _TMP

set /p DATABASES=<_TMP

FOR %%D IN ( %DATABASES% ) DO (

%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select 'Pg Dump Start For DB %%D Time :: ' || now();" postgres >> %LOGLOC%\pgDump_%DT%.log

if %DUMP_MODE% EQU CUSTOM ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Fc -b -c -h %HOST% -U %USER% -p %PORT% %%D 2>_Error ) else ( if %DUMP_MODE% EQU TAR ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Ft -b -c -h %HOST% -U %USER% -p %PORT% %%D ) else ( if %DUMP_MODE% EQU PLAIN ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Fp -b -c -h %HOST% -U %USER% -p %PORT% %%D ) else ( exit ) ) ) >> %LOGLOC%\pgDump-%DT%.log 

FOR %%E IN ( _Error ) DO IF %%~zE EQU 0 (  FOR /f "tokens=*" %%F IN ( ' dir  /a /b /O-D %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump*.dmp^|more +%RETAIN_LAST_BACKUPS% ') DO ( echo %%F >>_Files_To_Delete  )  ) else (

echo Error Occured, Hence Keeping The Last Backups As It Is And Removing This Error Caused Dump ..

del /Q %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp

)



%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select 'Pg Dump End For DB %%D Time :: '||now();" postgres >> %LOGLOC%\pgDump-%DT%.log





REM Doing Retention Policy..

FOR /f "tokens=*" %%R IN ( 'type _Files_To_Delete' ) DO (  DEL /Q %BACKUPLOC%\%%R  )

DEL /Q _Files_To_Delete 



)

How to run
========

Script_Name <host> <Port> <User> "<DB1>,<DB2>,<DB3>,..."

దినేష్ కుమార్
Dinesh Kumar

How To Send E-Mail From PostgreSQL

2012-08-29T19:37:00.003+05:30

Hi ,

If you want to send E-Mails from PostgreSQL, then use the below Python 3.2 Script as below. I have used ActivePython 3.2 with PostgreSQL 9.1 for sending E-Mails from PostgreSQL.

If you want to configure the Python 3.2 with PostgreSQL 9.1 then, please refer the below steps.

http://manojadinesh.blogspot.in/2012/06/fatal-python-error-pyinitialize-unable.html

Once, your Python 3.2 successful then follow the below steps to send an e-mail.

Step 1
=====

postgres=# CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT LANGUAGE plpython3u
AS $function$

import smtplib
sender = _from
receivers = receiver
message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"
% (_from,receiver,subject,send_message))

try:

smtpObj = smtplib.SMTP(smtp,port)
smtpObj.starttls()
smtpObj.login(_from, _password)
smtpObj.sendmail(sender, receivers,message)
print ('Successfully sent email')
except SMTPException:
print ('Error: unable to send email')

return message
$function$
;
CREATE FUNCTION

Step 2

=====

postgres=# select send_email('dineshkumar02@gmail.com','XXPasswordXX','smtp.gmail.com',587,'dinesh.kumar@mydomain.com','Sending E-Mail From PostgreSQL','Hi,\n How do you do ..');

send_email

-----------------------------------------

From: dineshkumar02@gmail.com +

To: dinesh.kumar@mydomain.com +

Subject: Sending E-Mail From PostgreSQL

(1 row)

Please check your Inbox after this function execution ..

దినేష్ కుమార్

Dinesh Kumar

OS Results From PostgreSQL

2012-08-24T13:27:00.001+05:30

Hi ,

As of now, we are using "\i" or using "PL/SH" script for getting the OS command results from PostgreSQL. However, below is the one more option where we can get the required info.

Step 1

======

#include "postgres.h"

#include "fmgr.h"

#include <string.h>

#include <unistd.h>

#ifdef PG_MODULE_MAGIC

PG_MODULE_MAGIC;

#endif

PG_FUNCTION_INFO_V1(shell_exec);

Datum

shell_exec(PG_FUNCTION_ARGS)

{

    text  *arg1 = PG_GETARG_TEXT_P(0);

    char  *Command=VARDATA(arg1);

    int32 result=system(Command);   

    PG_RETURN_TEXT_P(result);

}

Step 2

=======

-bash-3.2$ more Makefile

PG_CONFIG = /opt/PostgresPlus/9.0AS/bin/pg_config

MODULES = shell_exec

PGXS := $(shell $(PG_CONFIG) --pgxs)

include $(PGXS)

Step 3

======

make and .so file to $PGHOME/lib  or $PGHOME/lib/postgres/

Step 4

======

postgres=# CREATE OR REPLACE FUNCTION shell_exec(text) RETURNS int                           AS 'shell_exec.so', 'shell_exec'  

LANGUAGE C STRICT;

CREATE FUNCTION

Step 5

======

postgres=# select shell_exec('vmstat 1 >/opt/PostgreSQL/9.0/data/output');

^CCancel request sent

postgres=# select pg_read_file('/opt/PostgreSQL/9.0/data/output',1,1000000);

                                       pg_read_file                                      

------------------------------------------------------------------------------------------

 rocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----         +

  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st        +

  0  0 2653376 654888  31832 248464    6   14    28   220    8   12  4  5 89  1  0       +

  0  0 2653376 654988  31832 248468    0    0     0    40  693 1310  0  1 99  0  0       +

  1  0 2653376 654856  31840 248468    0    0     0   116  704 1348  0  1 99  0  0       +

  0  0 2653376 654608  31840 248472    0    0     0     0  664 1494  0  1 100  0  0      +

  0  0 2653376 654740  31840 248472    0    0     0     0  631 1522  0  1 99  0  0       +

  0  0 2653376 654732  31840 248476    0    0     0     0  674 1519  0  1 99  0  0       +

  0  0 2653376 654756  31840 248476    0    0     0   156  616 1541  0  1 100  0  0      +

  0  0 2653376 654748  31848 248480    0    0     0    72  620 1331  0  1 99  0  0       +

  0  0 2653376 654748  31848 248480    0    0     0     0  599 1299  0  0 99  0  0       +

  0  0 2653376 654740  31848 248484    0    0     0     0  630 1292  0  1 99  0  0       +

  0  0 2653376 654972  31848 248484    0    0     0    32  664 1358  0  1 99  0

0       +

దినేష్ కుమార్

Dinesh Kumar