Skip to main content

Posts

Showing posts from March, 2012

DBLINK in PostgreSQL

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
------
Dblink is one o…

CPU Load In Windows

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

Background Jobs In Windows

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

mailsend in windows

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

Wal difference in PostgreSQL

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(substring($2 from 9 for…

Database Avilability Check In Windows

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=<_Res.txt
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

Table and Index Growth Ratio in PostgreSQL

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);
SELECT index_size INTO PREV_…