Skip to main content

Game with postgreSQL


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
    
       *
     *
   * 
 *
* => Winner

Winner needs to start the new game.

Sample screen shots
-=-=-=-=-=-=-=-=-=-=



Dinesh Kumar

Comments

Post a Comment

Popular posts from this blog

Pgpool Configuration & Failback

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  …

pgBucket - A new concurrent job scheduler

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 jobsCron style sytaxOnline job modificationsRequired cli options

N-Node Mutlimaster Replication With Bucardo...!

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…