DevAdmin

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

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

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

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

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

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

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

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

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