Skip to main content

PostgreSQL High Performance Cookbook

Sharing knowledge which I have gained from last 6 years.

So glad to be part of PostgreSQL High Performance Cookbook, where I have discussed all the knowledge I have gained from PostgreSQL database.

PostgreSQL High Performance Cookbook

Working with PostgreSQL from last 6 years, I have gained so much of knowledge about database management systems. Being a DBA for several years, I explored so many tools which work great with PostgreSQL database. During this 6 years journey, I got a chance to meet many wonderful peoples who guided me very well. I would like to say thanks to everyone who taught me PostgreSQL database in soft/hard ways :-). Also, would like to say thanks to every PostgreSQL developer, and authors and bloggers, from where I have learned many more things.

Finally thanks to OpenSCG team who always treated me as a brother than an employee. :-)
Thanks to my wife manoja  for her wonderful support, and my friend Baji Shaik for his help in writing the content.

Comments

Post a Comment

Popular posts from this blog

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…

:: Rownum in postgresql ::

Hi All,

As we know,we can generate rownum with window fuctions also(Rank).In oracle we have "rownum" pseduo column,but we don't in postgresql.
Here is the one of the solution for generating rownum in postgresql.

In Oracle
-----------

SQL> CREATE SEQUENCE ROWNUMSEQ;

Sequence created.


SQL> SELECT EMPNO,ROWNUMSEQ.NEXTVAL AS "ROWNUM" FROM EMP;

EMPNO ROWNUM
---------- ----------
7369 1
7499 2
7521 3
7566 4
7654 5
7698 6
7782 7
7788 8
7839 9
7844 10
7876 11
7900 12
7902 13
7934 14


In PostgreSQL
----------------

postgres=# CREATE SEQUENCE ROWNUMSEQ;
CREATE SEQUENCE

postgres=# CREATE TABLE TEST(T INT);
CREATE TABLE

postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,14));
INSERT 0 14


postgres=# SELECT T,NEXTVAL('ROWNUMSEQ') AS ROWNUM FROM TES…

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…