Skip to main content

Posts

Showing posts from 2012

SMTP Libcurl

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, CURLO…

Skytools - Londiste Replication

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.gztar -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-…

FOREIGN TABLES

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

Windows Pg_Dump Script

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%','""'…

How To Send E-Mail From PostgreSQL

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…

OS Results From PostgreSQL

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;

Postgres-XC Setup

Hi ,
Finally i have configured PG-XC in my local box .. Please find the below steps how to configure PG-XC .. 
Steps To Configure PG-XC ======================== Step 1 ====== Download PG-XC Version 1.0 from the below link 
http://sourceforge.net/ projects/postgres-xc/files/ Version_1.0/pgxc-v1.0.0.tar.gz/download
Step 2 ====== mkdir -p /opt/Postgres-xc chown -R postgres:postgres /opt/Postgres-xc/ tar -zxvf pgxc-v1.0.0.tar.gz
Step 3 ====== Pre-Requistes ---------------- Readline,Bison,Flex yum -y install readline* yum -y install bison* yum -y install flex*
./configure --prefix=/opt/Postgres-xc/ make make install
Step 4 ====== Setup of GTM  {Global Transaction Manager} ------------------------------ -------------------- -bash-4.1$ mkdir data_gtm -bash-4.1$ chmod 700 data_gtm/ -bash-4.1$ /opt/Postgres-xc/bin/initgtm -Z gtm -D /usr/local/pgsql/data_gtm   
# It will create gtm.conf file under data_gtm location .. Then change the port of gtm server and listen_addresses if required.
Below ar…

How to get Oracle Error Statements

Hi,

The best way to track the oracle errors is throug it's ORACLE Error log files. However, we do have one more manual mechanism to track all those ERROR Messages in Database using "SERVERERROR" DDL Triggers.

Please find the below steps to achieve this.

Step 1
======
SQL> GRANT ADMINISTER DATABASE TRIGGER TO <USERNAME>;

Step 2
======
create table oraerror (
id NUMBER,
log_date DATE,
log_usr VARCHAR2(30),
terminal VARCHAR2(50),
err_nr NUMBER(10),
err_msg VARCHAR2(4000),
stmt VARCHAR2(4000)
);

Step 3
======
create sequence oraerror_seq
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
nocycle;

Step 4
======
CREATE OR REPLACE TRIGGER after_error
 AFTER SERVERERROR ON DATABASE
 DECLARE
 pragma autonomous_transaction;
 id NUMBER;
 sql_text ORA_NAME_LIST_T;
 v_stmt VARCHAR2(4000);
 n NUMBER;
BEGIN
 SELECT oraerror_seq.nextval INTO id FROM dual;
 n := ora_sql_txt(sql_text);
 IF n >= 1
 THEN
 FOR i IN 1..n LOOP
 v_stmt := v_stmt || sql_text(i);
 END LOOP;
 END IF;

 FOR n IN 1..ora_server_erro…

Dblink From PostgresPlus To Oracle

Hi,

Dblink_Ora_* functions are useful for getting oracle connection into PostgresPlus Advanced Server. i.e, we can retrive oracle data from postgresplus. Please find the steps how to proceed.


Step 1
======
For doing dblink_ora* setup, we need a OCI(Oralce Client Interface which is nothing but libpq in Postgresql)file. If you have oracle 11*, then no need to download this file from any where. if not, we need to donwload this file from oracle site.

Filename Required is : "libclntsh.so" in Linux
      "OCI.DLL" in Windows

In Oracle 11g, you will get this directly from $ORACLE_HOME/lib. In this example, we have created a symlink for this from "/lib64" to "$ORCLE_HOME/lib".

ln -s /home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so /lib64/libclntsh.so
Step 2
======
[root@localhost PGBAR]# chmod -R 766 /home/oracle/

Because, we are accessing this "libclntsh.so" as a enterprisedb user. Hence, we have given the complete permissions …