Database Technologies

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

Good News Postgres Plus Advanced Server Beta Version is now availabale, which has all the new feature of PostgreSQL 9.2, and it also has new features which are specific to Advanced Server. I will cover/explain New features of PostgreSQL 9.2 later as per release Notes. However, the following link covers major features of PostgreSQL 9.2 and can be taken as reference. http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2 In this Blog, I am going to cover only specific features of Advanced Server 9.2 Core. Which are: 1. INSERT APPEN HINT in PPAS 9.2 PPAS 9.2, now supports INSERT append hint. This is a very interesting feature and it is very useful for users who frequently delete records in Bulk and do bulk of INSERTs. This hint can provide some benefits in INSERTs. This hint makes PPAS not use Free Space Map and Append the rows at the end of relation(table). Its usage is given below: INSERT /*+append*/ INTO tab21115 VALUES(1,'abc',sysdate); 2. Procedure Called l...

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link: http://www.postgresql.org/docs/9.1/static/functions-info.html Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false. Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn't have a view, which DBA can use to list users privileges on objects for a particular database. Therefore, I thought about making some functions, which can be used to list users privileges, based on what is available in PostgreSQL/PPAS 9.1. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION. Following are functions which can use to get the privileges of a particular user: 1. Function for table privileges: C...

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

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

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

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