DevAdmin

Hi, Find the below DBLINK concepts in PostgreSQL, PostgresPlus as well. PostgreSQL ---------- Foreign Data Wrapper -------------------- This is the one utility which encapsulates the data from the different Database servers using Handler functions. Ex:- In PG 9.1, we can encapsulates the data from My Sql and store to PostgreSQL using handlers. CREATE FOREIGN DATA WRAPPER HANDLER VALIDATOR ; Currently(<= 9.0) PostgreSQL compatible with only remote PostgreSQL. From 9.1 onwards, we can make the PostgreSQL to communicate with SQL Server/My SQL through these wrappers. Servers ------- This is the one object which is having the Data wrapper methodology as well the remote host connectivity details. Ex:- CREATE SERVER FOREIGN DATA WRAPPER OPTIONS (address , port ,dbname ); User Mapping ------------ This is the one object which maps the current dbserver user/role to remote dbserver user. Ex:-CREATE USER MAPPING FOR SERVER OPTIONS (username , password ); DBLINK ...

Hi , I have tried a lot for getting the cpu load, but seems there is no any specific mechanism for getting this. So finall developed the c# code using performance counters. Find the snnipet here.. +++++++++++++++++++++++++++++++++++++ using System; using System.Collections.Generic; using System.Text; using System.Diagnostics; using System.Threading; namespace cpu_load { class Program { static void Main(string[] args) { System.Diagnostics.PerformanceCounter cpuusage = new System.Diagnostics.PerformanceCounter("Processor", "% Processor Time", "_Total"); cpuusage.NextValue(); Thread.Sleep(1000); Console.WriteLine(Convert.ToInt64(cpuusage.NextValue())); } } } ++++++++++++++++++++++++++++++++++++ Compile this code and run the .exe file under /debug/bin folder. --Dinesh

Hi , While scheduling any activity in windows through "schtasks" it's by default prompting the cmd window. To get rid of it use "/ru System in Schtasks command" which makes it as a system NT Authority/System Job and won't prompt any window at any time. Ex:- schtasks /create /tn "My App" /tr c:\apps\myapp.exe /sc monthly /d 15 /ru System --Dinesh

Hi , Please find the useful command for sending e-mails through "mailsend" utility.. D:\\mailsend.exe -to dinesh.kumar@abc.com -from dineshkumar02@abc.com -starttls -smtp smtp.gmail.com -port 587 -sub HighAlert +cc +bc -auth-login -user username -pass password Here pgalert.txt is the file for the message body.. --Dinesh

Hi, Here is the function can calculate the no.of files difference between the two wail files, and will give you an exact results. CREATE OR REPLACE FUNCTION public.hot_standby_lag(primary_xlog_loc text, slave_xlog_replay_loc text)  RETURNS integer  LANGUAGE plpgsql AS $function$ declare Digit_Length int; Query text; No_of_Bits int; No_of_Walfiles int; No_of_Zeros int:=0; Cnt int:=0; Re_Formatted_Primary_Log text; Re_Formatted_Slave_Log text; _1 int; _2 int; _3 int; BEGIN select GREATEST(length(trim(substring($1 for 8),'0')||trim(substring($1 from 9 for 8),'0')||ltrim(substring($1 from 17 for 8),'0')),length(ltrim(substring($2 for 8),'0')||trim(substring($2 from 9 for 8),'0')||ltrim(substring($2 from 17 for 8),'0'))) into Digit_Length; select GREATEST(length(ltrim(substring($1 for 8),'0')),length(ltrim(substring($2 for 8),'0'))) into _1; select GREATEST(length(trim(substring($1 from 9 for 8),'0')),length(trim(...

Hi, Here is the script in windows for refference and will the PostgreSQL DB availability. If configure mail_send command in windows, we can send e-mails as well. @ECHO OFF set PSQL="C:\Program Files\PostgreSQL\9.1\bin" set DBNAME="template1" set USER="postgres" set PORT="5432" set RES="Not Pinging" %PSQL%\psql -Atq -c "SELECT 'ping'" -p %PORT% -U %USER% %DBNAME% > _Res.txt set /p RES= echo %RES% IF %RES% EQU ping (echo "No need to send any exceptional e-mail") else (echo "PostgreSQL seems not pinging.. Need to send an e-mail to RDBA") Hope this helps you a lot ... --Dinesh

Hi All, Further to DBGrowth ratio, we have implemented the table and indexes growth ration and growth calculation percentages. Step 1:- Create the following structure CREATE TABLE ostats.tblgr(timestmp timestamp,tablename text,indexname text,table_size bigint,index_size bigint,total_table_size bigint,n_rows bigint,table_growth varchar(100),table_growth_percent real,index_growth varchar(100),index_growth_percent real); Step 2:- Create the following Trigger Source CREATE OR REPLACE FUNCTION ostats.FEED_TAB_GROWTH() RETURNS TRIGGER LANGUAGE PLPGSQL AS $FUNCTION$ DECLARE CHECK_TAB_PRESENCE INT; PREV_TAB_SIZE BIGINT; PREV_IND_SIZE BIGINT; BEGIN SELECT COUNT(*) INTO CHECK_tab_PRESENCE FROM ostats.tblgr WHERE LOWER(tablename)=NEW.tablename; IF(CHECK_TAB_PRESENCE=0) THEN RETURN NEW; ELSE SELECT table_size INTO PREV_tab_SIZE FROM ostats.tblgr WHERE(CTID,tablename) IN (SELECT MAX(CTID),tablename FROM ostats.tblgr GROUP BY tablename HAVING tablename=NEW.tablename); SELEC...