Database Technologies

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

This has been asked me many times. Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification) Well answer is in parameter called default_transaction_read_only. If you want to make a user READ-ONLY, then you can follow steps given below: 1. CREATE normal user. 2. Use ALTER USER command to set this parameter for this user as given below: ALTER USER set default_transaction_read_only = on; 3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go. Below is snapshot: postgres=# create user readonly password 'test'; CREATE ROLE postgres=# alter user readonly set default_transaction_read_only = on; ALTER ROLE postgres=# GRANT select on employees to readonly; GRANT edbs-MacBook-Pro:data postgres$ psql -U readonly -W Password for user readonly: psql (9.1.1) Type "help" for help. postgres=> select * from employees ; employee_name | e...

In Series of New Features in Advanced Server 9.1, today I tought to write about Parition Table Syntax Supported in PPAS. In PostgreSQL and till PPAS 9.0, user has to follow method given below for partitioning a table: 1. CREATE PARENT Table, 2. Create Child tables using Inherit feature 3. Create Trigger on Partition on Parent, so that it can re-direct insert to Right Partition. 4. And if user has to add new child table, then it has to do 2 and 3 steps again. Now, in PPAS 9.0, user doesn't have to perform above activities. PPAS 9.0 supports PARTITION TABLE syntax. Lets see how PARTITION syntax in PPAS can make users/DBAs life easier. We know that PPAS/PostgreSQL supports two types of partition (Range and List). So, we will see how its simple with CREATE PARTITION SYNTAX. Lets CREATE RANGE PARTITION as we used to do in PPAS 9.0/PostgreSQL 1. Create Master table as given below: CREATE TABLE partition_master(id numeric primary key,val text); CREATE TABLE partition_child1(C...

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1 Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below: 1. Create an Application Context 2. Create security policies functions 3. Apply security policies to tables Lets see how user can implement it in Advanced Server. 1. Setup an environment as given below: CREATE user merry identified by edb; CREATE user john identified by edb; CREATE TABLE public.john_merry(userid varchar2(200),val numeric); grant select,update,delete,insert on john_merry to john; grant select,update,delete,insert on john_merry to merry; 2. Now create a Policy Function as given below: CREATE OR REPLACE FUNCTION verify_user ( p_sche...

Till PPAS 9.0, UTL_SMTP package has no specific named Exceptions for Transient_error, Invalid_operation and Permanent Error. However, Oracle provide following types of named exceptions for SMTP: 1. INVALID_OPERATION: Raised when an invalid operation is made. In other words, calling API other than write_data(), write_raw_data() or close_data() after open_data() is called, or calling write_data(), write_raw_data() or close_data() without first calling open_data(). 2. TRANSIENT_ERROR: Raised when receiving a reply code in 400 range. 3. PERMANENT_ERROR: Raised when receiving a reply code in 500 range. Oracle users who use SMTP packages, they also use above exceptions extensively to track the SMTP Error/Message and perform some handling on basis of exception. Since, till now PPAS doesn't have these exceptions, therefore people stuck on finding workaround for such exception. To make this easy, I did some research and made following workaround, which user can use in PPAS for...