Posts

Showing posts with the label PostgreSQL 9.1

pg_xlog_location_diff function for PostgreSQL/PPAS

In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link: http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1. Before using formula and developing function, lets understand what is xlog and offset. Let's consider user has used function pg_current_xlog_location() function and he gets following information: worktest=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 1/D1012B80 (1 row) In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside

Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information: 1. datid: database OID 2. datname: database name 3. procpid: process ID 4. usesysid: user OID 5. usename: user name 6. application_name: application name 7. client_addr: client's address 8. client_hostname: host name (if available) 9. client_port: Clients port number 10. backend_start: time at which the server process started 11. xact_start: time at which current transaction started 12: query_start: time at which current query began execution started 13: waiting: process's waiting status 14. current_query: text of the current query. Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function. To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the p

Creating user probe and alert in PEM 2.1

This is also one type of thing in which people are very interested. How to create probes other than PEM inbuilt probe? And how to create alert based on probe? Well answer is simple. Understand what probe is in PEM and then understand PEM Data detail. Probe in PEM requires following: 1. SQL Code which can be use to gathering data by pemagent. 2. Table in pemdata schema, which will be use for storing Current status/data of SQL. 3. History table (specially in pemhistory schema) where all history data will reside. Suppose user wants to monitor the Slony Replication using PEM (since, PEM doesn't have slony replication moniotring), so user can do following: 1. SQL Code which can be use for slony replication monitoring. We know that slony replication monitoring can be done using view sl_status of slony. So, user can create a SQL Code as given below for replication. select '_test_slony' as schema_name, st_origin,st_received, st_lag_num_events , st_lag_time from _test_slony

Automating Binding of Servers with PEM Agent in Postgres Enterprise Manager 2.1

This is second post of Postgres Enterprise Manager 2.1 series. Question which people ask, Is Postgres Enterprise Manager tool useful for Companies, which provide services for PostgreSQL? Answer is yes. What about companies, which provide infrastructure support, where they provide server and PostgreSQL database, for them, is this is a good tool for monitoring PostgreSQL? Answer is yes, you can use it. Companies, which are giving Infrastructure support and providing Database As service always want everything to be automated. i.e with provisioning server, server should have installed PostgreSQL and its components plus they want automatic installation of pemagent, which is also acheivable. However, they stumped on Automatic Bidning of PEM Agent with PostgreSQL. For binding PostgreSQL with PEM Agent, Simple Method is using PEM Client. In PEM Client, Add a PostgreSQL in PEM Directory and then user can bind the PEM agent with PostgreSQL Cluster. File -> Add Server -> which will

Types of Alerts in Postgres Enterprise Manager: 2.1.

Its being two months, that I didn't blog on any topic. So, I thought to share some information on PEM. This post is for users, who always look for type of inbuild alerts of PEM. In this Blog, I am listing categories of Alerts and List of alerts in each category, which one can find in Postgres Enterprise Manager 2.1. Also, if you have list of alerts then you can decide which alert you would like to configure. Postgres Enterprise Manager (PEM) is very useful tool for monitoring PostgreSQL and it has all the alerts which is require for monitoring postgresql. User can define this alerts in following categories: 1. Server Level Alerts. 2. PG Cluster Level Alerts. 3. Database Level Alerts. 4. Schema Level Alerts 5. Table Level Alerts. All the alerts which has been made/defined keeping in mind of postgreSQL. Let see what are the alerts in each categories: 1. Server Level Alerts: These alerts are made for monitoring Server Components like Memory,Disk and CPU and Server Level moni

Postgres Plus Advanced Server 9.1 Beta 1

PPAS 9.1 Beta 1 is released. So, I thought to write about it and New Features which are coming. Best part of PPAS 9.1 is that it has all features of PostgreSQL 9.1, which I have already discribed in my series of 9.1. Links are given below: 1. PostgreSQL Object Manipulation Feature s 2. New Replication and Recovery Feature s 3. Queries improvement of Core PostgreSQL 9.1 4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1 5. Utility Operation Improvements 6. Upsert/Merge using Writeable CTE 7. New Functions/Improvement 8. Object Functions Improvement in PostgreSQL 9.1 9. Client Application Improvement in PostgreSQL 9. 1 10. Asynchronous/Synchronous Replication There are many other features in PostgreSQL 9.1, which requires some time to write with example. However, below is list of those Improvements: 1. Unlogged Tables 2. FOREACH IN ARRAY in PL/pgSQL 3. Pl/Perl Improvement:     a. Record type support     b. Pl/perl array argument map to Perl Argument.

PostgreSQL 9.1:: New in Object Manipulation

In Series of writing Blog on New features in PostgreSQL 9.1, I thought to write and give example on Object Manipulation Changes in 9.1. Let see what we have in Object Manipulation Features in 9.1 1. CREATE/ALTER/DROP EXTENSIONS PostgreSQL 9.1 has different way of managing Contrib Modules. User can now install Contrib Module using Command as given below: psql -c "CREATE EXTENSION pgcrypto;" postgres CREATE EXTENSION If user wants to keep its contrib/Extension module in some other schema then user can try ALTER Command as given below: postgres=# CREATE schema contrib_modules; CREATE SCHEMA postgres=# alter extension pgcrypto set schema contrib_modules; ALTER EXTENSION Simlarly, ALTER EXTENSION has many options, some options are given below: Description: change the definition of an extension Syntax: ALTER EXTENSION extension_name UPDATE [ TO new_version ] ALTER EXTENSION extension_name SET SCHEMA new_schema ALTER EXTENSION extension_name ADD member_object ALTER EXTENSION

New Replication and Recovery Features in PostgreSQL 9.1

1. Streaming Replication and Continuous Archiving Features. a. Synchronous Replication PostgreSQL 9.1 came with Synchronous Replication. In Synchronous Replication, all commited transaction will be transferred to standby synchronously. When Primary is in Synchronous replication, then each commit transaction will wait untill transaction get transfered to Slave/Replication Server. This reduces the loss of any committed transaction. And gives high degree of durability. For setting up synchronous replication you can look at my blog: http://vibhorkumar.wordpress.com/2011/10/20/asynchronoussynchronous-streaming-replication-in-postgresql-9-1/ 2. New parameter replication_timeout:  This is a new parameter which has been added in 9.1. Using this parameter user can terminate replication connection, if connection is inactive more than replication_timeout seconds. This is useful parameter for Primary Server to detect Replication Server Crash or network outage. 3. New role/permission

New Features in Slony 2.1

Slony 2.1 has been released on 19th Oct. 2011. So, I thought to look at some important improvement done in this new release, which can make users life easier. Before discussing about the changes, lets setup slony replication. Following Codes can be use for setting up slony replication. #### Preable Scripts: cluster name=slonytest; NODE 1 ADMIN CONNINFO = 'dbname=postgres host=localhost user=postgres port=5432 password=postgres'; NODE 2 ADMIN CONNINFO = 'dbname=repdb host=localhost user=postgres port=5432 password=postgres'; Adding Node script: ### create_nodes.slonik include ; init cluster (id=1, comment='slonytest node 1'); store node (id=2, comment='slonytest subscriber node 2', event node=1); Storing Path: ### store_paths.slonik include ; STORE PATH (SERVER=1, CLIENT=2, CONNINFO='dbname=postgres host=localhost user=postgres port=5432 password=Empid#042'); STORE PATH (SERVER=2, CLIENT=1, CONNINFO='dbname=repdb host=localhost us

Queries Improvement in PostgreSQL 9.1

1. True serializable isolation level:  Before version 9.1, PostgreSQL did not support a full serializable isolation level. A request for serializable transaction isolation actually provided snapshot isolation. This has well known anomalies which can allow inconsistent views of the data during concurrent transactions; although these anomalies only occur when certain patterns of read-write dependencies exist within a set of concurrent transactions. Where these patterns exist, the anomalies can be prevented by introducing conflicts through explicitly programmed locks or otherwise unnecessary writes to the database. Snapshot isolation is popular because performance is better than serializable isolation and the integrity guarantees which it does provide allow anomalies to be avoided or managed with reasonable effort in many environments.  Following is a link on more details: http://wiki.postgresql.org/wiki/SSI 2. INSERT/UPDATE/DELETE in WITH CLAUSE.  Now in 9.1, User would be able i

Updateable Views in PostgreSQL 9.1 using INSTEAD OF Trigger

About updateable views user ask many times. Is it supported in PostgreSQL? Can we write Complex updateable views? Answer for above is yes . Till 9.0, we have to use RULE for implementing updateable view. Again, RULE Implementation used to be a bit work, since user has to write multiple RULES to implement this feature. Following code can be use to see this. CREATE TABLE person_detail(pid NUMERIC PRIMARY KEY, pname TEXT); CREATE TABLE person_job(pid NUMERIC PRIMARY KEY references person_detail(pid), job TEXT); INSERT INTO person_detail VALUES(1,'Angela'); INSERT INTO person_detail VALUES(2,'Tom'); INSERT INTO person_detail VALUES(3,'Heikki'); INSERT INTO person_job VALUES(1,'Documenter'); INSERT INTO person_job VALUES(2,'Developer'); INSERT INTO person_job VALUES(3,'Commiter'); CREATE VIEW person_detail_job_vw AS SELECT p.pid, p.pname, j.job FROM person_detail p LEFT JOIN person_job j ON (j.pid=p.pid); SELECT * FROM person_detail

Utility Operations improvement in PostgreSQL 9.1

1. Transaction-level advisory locks: PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.  Till 9.0, postgreSQL had only Session level locks. Now, in PostgreSQL 9.1, we have transaction level advisory lock. Some examples are given below: BEGIN; -- grabbing txn locks multiple times SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' ORDER BY classid, objid, objsubid; COMMIT; Advantage of Advisory lock are given below: 1. Advisory locks are faster 2. It avoid