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

Now, you must be thinking why I am including Slony SCHEMA name in SQL Code. Well, reason is that slony_schema name represents Slony Cluster name and if user is having more than one slony replication in Database, then those can be monitor with small modification of SQL Code. Also, PEM data table and PEM History can be use for monitoring multiple slony replication cluster. Lets start with creating probe based on SQL Code:

Standard way of creating probe in Postgres Enterprise Manager:
1. Insert the detail of probe in probe table with SQL Code for monitoring:

INSERT INTO probe(display_name,
internal_name,
collection_method, 
target_type_id, 
applies_to_id,
probe_code,
enabled_by_default, default_execution_frequency,default_lifetime,any_server_version,force_enabled,probe_key_list) 
VALUES('slon replication monitor','slony_replication','s',300,600,'select ''_test_slony'' as schema_name, st_origin,st_received, st_lag_num_events , st_lag_time from 	_test_slony.sl_status',false,300,180,true,false,ARRAY['st_origin','st_received']);

Comlumn Description of probe table is given below:
display_name: Name as presented in Alerting Box.
internal_name: Internal Name of probe to PEM server. Based on that function: pem.create_data_and_history_tables(), creates pemdata and pemhistory table
collection_method: is Collection based on SQL: ‘s’ or based on internal code of pemagent: ‘i’
target_type_id: Target type id, Type id and its description below:

		Global 	: 50
		Agent  	: Agent
		Server 	: 200
		Database: 300
		Schema	:   400
		Table	:   500
		Index	:  600
		Sequence: 700
		Function: 800

If probe is for database level then target_type_id would be 300.

applies_to_id: probe is apply to which type id, i.e if user can make database level probe which can be apply to table (like IndexSize can be database level and can be table level alert).
probe_code: SQL Code/function to collect data
enabled_by_default: if true then it will be enable for all pemagent servers
default_execution_frequency: Interval
default_lifetime: Data retention period
any_server_version: Is it PPAS specific alert or PG/PPAS both (true/false)
force_enabled: By default enable (true/false).
probe_key_list: Key columns

2. Insert the detail of data column, returns by SQL Code, in probe column, as given below:

INSERT INTO pem.probe_column (probe_id, internal_name, display_name, display_position, classification,
       sql_data_type, unit_of_value, calculate_pit, discard_history, pit_by_default, is_graphable)

SELECT 
   (SELECT max(id) FROM PEM.probe), v.internal_name, v.display_name, v.display_position, v.classification,
       v.sql_data_type, v.unit_of_value, v.calculate_pit, v.discard_history, v.pit_by_default, v.is_graphable FROM (
    VALUES('database_name','Database Name',1,'k','text','',false,false,false,false),
    ('schema_name','Slony Cluster',2,'k','text','',false,false,false,false),
    ('st_origin','Master Node ID',3,'k','INTEGER','',false,false,false,false),
    ('st_received','Slave Node ID',4,'k','INTEGER','',false,false,false,false),
    ('st_lag_num_events','Lag Events',5,'m','INTEGER','',false,false,false,false),
    ('st_lag_time','Lag Interval',6,'m','INTERVAL','',false,false,false,false)
)
v(internal_name, display_name, display_position, classification,
               sql_data_type, unit_of_value, calculate_pit, discard_history, pit_by_default, is_graphable);

Description of columns are given below:
probe_id : Assigned probe_id for column (its max(id) of pem.probe column.
internal_name : Internal name to PEM server.
display_name : Column Display name to user.
display_position : Position of column in SQL Code.
classification : If column consider as primary key i.e based on this record can be identified, then value would ‘k’ else ‘m’.
sql_data_type : Data type of Column
unit_of_value : Unit of data of column.
calculate_pit : Point in time data
discard_history : Discard any history.
pit_by_default : Default Point in time representation true/false
is_graphable : Can be use for graph (always keep false).

3. Now use PEM server function to create data and history table in pemdata and pemhistory schema of PEM server.

SELECT pem.create_data_and_history_tables();

Above will create table with internal_name mentioned in probe_column, like pemdata.slony_replication and pemhistory.slony_replication.

Above are three simple steps to create probe in PEM Server. Now, user can see that if you know SQL language, you can create probe in PEM. To verify the probe you can see the probe in PEM Client:

Open PEM Client -> Go to PEM Server directory (in Left pane) -> Connect to PostgreSQL Cluster -> expand the databases -> Right click on any database -> select Probe Configuration.
which will popup "Probe configuration" and you would be able to see the slony replication.

Since, we have created probe and based on probe, we can create template alert.
Creating Template Alert is simple. For new template alert, you have to do following:
1. Identify the right SQL code for monitoring based on probe, as given below for slony replication:

SELECT extract ('epoch' from st_lag_time) from  pemdata.slony_replication WHERE server_id=${server_id} AND database_name='${database_name}' AND st_origin='${param_1}' AND st_received='${param_2}' and st_lag_time >=interval '1 sec' AND schema_name='_test_slony'

In above I am using epoch function to calculate seconds and based on that I can have alert which would be having threshold value for Low, Medium and High alert.

2. Use PEM server function, pem.create_alert_template(), to create template alert as given below:

SELECT pem.create_alert_template('Slony Replication','Slony Replication Monitoring Alert',$SQL$ SELECT extract ('epoch' from st_lag_time) from  pemdata.slony_replication WHERE server_id=${server_id} AND database_name='${database_name}' AND st_origin='${param_1}' AND st_received='${param_2}' and st_lag_time >=interval '1 sec' AND schema_name='_test_slony' $SQL$,300,ARRAY['st_origin','st_received'],ARRAY['INTEGER','INTEGER']::alert_param_type[],NULL,'seconds',ARRAY['slony_replication'],2);

Argument description of pem.create_alert_template() function is given below:
1.name : Name of alert in single quotes
2.description : Small Description of Alert
3.sql : SQL code for getting threshold
4.object_type : Alert is of Agent LevelServer Level,
5.param_names : SQL Code is dependent on any parameter name, (like in my SQL Code, its depend ‘${param_1}’ (origin) and ‘{param_2}’ (subscriber node id)
6.param_types : Data type of each parameter (As per SQL code for slony, this would be NULL)
7.param_units : Assigned unit
8.threshold_unit : Unit of threshold (since I am doing Checking the Time lag, there it would seconds).
9.probe_dependency_list: Dependency on Pemdata table (Its slony_replication)
10.snmp_oid : Oid for snmp
11.applicable_on_server: Is this applicable for Advanced Server or ALL
12.default_check_frequency: Interval
13.default_history_retention: data retention in number of days.

Thats it! Now, you can see creating new user defined alert and probe in PEM is very simple. Only thing which user has to do is to focus on identifying right SQL Code for alert/probe.

Have fun with Postgres Enterprise Manager!

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 popup screen for "New serve Registeration" -> After adding server information (Also click on Store PEM Server)

Then in “New server Registeration Window” ->

 Click on tab "PEM Agent" -> Choose agent from drop down window of "Bound Agent", Then user can include the information. 

Well above is one method. However for Company which provides infrastructure support doesn’t want to do this manual work. They want this activity to be automated.

For automating this process, user can do following:
1. Install the PEM agent in unattended mode. For detail of installing pemagent in unattended mode, user can use command like:

./pem_agent-2.1.0.2-linux-x64.run --prefix /opt/PEM --mode unattended --pghost <pem server hostname> --pguser <pem server username and password> --agent_description <Agent Hostname>

2. Create .pgpass file in root/admin user home directory. Like on linux root home directory is /root.

# cat .pgpass
#hostname:port:database:username:password
*:5432:*:postgres:<your password>

Change the permission on .pgpass:

chmod 600 .pgpass

3. After creating the .pgpass file in home directory, execute following SQL:

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "
INSERT INTO pem.server(description,server,port,database) VALUES('New server','IP Address',5432,'postgres',2);" -U postgres

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "INSERT INTO pem.server_option(server_id,pem_user,username,server_group) VALUES((SELECT id FROM pem.server WHERE server='IP address' AND active=true),'postgres','postgres','PEM Server Directory');" -U postgres

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "INSERT INTO pem.agent_server_binding(agent_id,server_id,server,port,username,database)
VALUES((SELECT id FROM pem.agent WHERE description ='Agent Hostname' AND active=true),(SELECT id FROM pem.server WHERE server='IP address' AND active=true),5432,'postgres','postgres');" -U postgres

After executing above SQLs, you have successfully binded agent with Server for monitoring.

User can also include above steps in shell script and can execute while provisioning new server for their client.

Have Fun!

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 monitoring of PostgreSQL:

 1. Average table bloat on host (Bloats impact CPU and Disk)
 2. CPU utilization
 3. Database size on host
 4. Disk Available
 5. Disk busy percentage
 6. Disk Consumption
 7. Disk consumption percentage
 8. Free memory percentage
 9. Highest table bloat on host
 10. Load Average (15 minutes)
 11. Load Average (1 minute)
 12. Load Average (5 minutes)
 13. Load Average per CPU Core (15 minutes)
 14. Load Average per CPU Core (1 minutes)
 15. Load Average per CPU Core (5 minutes)
 16. Memory used percentage
 17. Most used disk percentage
 18. Number of CPUs running higher than a threshold
 19. Swap consumption
 20. Swap consumption percentage
 21. Table size on host
 22. Total table bloat on host

User can see above alerts covered server Monitoring which includes: Memory,Disk,CPU and Bloats & Biggest table on host.

2. PG Cluster Level Alert: These alerts are made for Monitoring PostgreSQL, Cluster Level. i.e. Number of Connections, Database Level Stats, User monitoring etc… Following is list of PG Cluster Level alerts:

 1. A user expires in N days
 2. Average table bloat in server
 3. Buffers allocated per second
 4. Buffers written per second
 5. Committed transactions percentage
 6. Connections in idle-in-transaction state
 7. Connections in idle-in-transaction state, as a percentage of max_connections
 8. Connections in idle state
 9. Database size in server
 10. Dead Tuples
 11. Dead tuples percentage
 12. Function Count
 13. Highest table bloat in server
 14. Hot update percentage
 15. Index Scans
 16. Index size as a percentage of table size
 17. InfiniteCache buffers hit percentage
 18. Largest index by table-size percentage
 19. Largest table (by multiple of unbloated size)
 20. Last Analyze
 21. Last AutoAnalyze
 22. Last AutoVacuum
 23. Last Vacuum
 24. Live Tuples
 25. Long-running autovacuums
 26. Long-running idle connections
 27. Long-running idle connections and idle transactions
 28. Long-running idle transactions
 29. Long-running queries
 30. Long-running transactions
 31. Long-running vacuums
 32. Number of prepared transactions
 33. Number of WAL files
 34. Percentage of buffers written by backends
 35. Percentage of buffers written by backends over last N minutes
 36. Percentage of buffers written by checkpoint
 37. Sequence Count
 38. Sequential Scans
 39. Shared buffers hit percentage
 40. Table Count
 41. Table size in server
 42. Total connections
 43. Total connections as percentage of max_connections
 44. Total table bloat in server
 45. Tuples deleted
 46. Tuples fetched
 47. Tuples hot updated
 48. Tuples inserted
 49. Tuples returned
 50. Tuples updated
 51. Ungranted locks
 52. Unused, non-superuser connections
 53. Unused, non-superuser connections as percentage of max_connections

3. Database Level Alerts: These alerts for Monitoring Specific Database in PostgreSQL Cluster. This is useful when you have database, which is important for your Bussiness and monitoring of that database is important for you:

 1. Average table bloat in database
 2. Committed transactions percentage
 3. Connections in idle-in-transaction state
 4. Connections in idle-in-transaction state, as a percentage of max_connections
 5. Connections in idle state
 6. Database Frozen XID
 7. Database size
 8. Dead Tuples
 9. Dead tuples percentage
 10. Function Count
 11. Highest table bloat in database
 12. Hot update percentage
 13. Index Scans
 14. Index size as a percentage of table size
 15. InfiniteCache buffers hit percentage
 16. Largest index by table-size percentage
 17. Largest table (by multiple of unbloated size)
 18. Last Analyze
 19. Last AutoAnalyze
 20. Last AutoVacuum
 21. Last Vacuum
 22. Live Tuples
 23. Long-running autovacuums
 24. Long-running idle connections
 25. Long-running idle connections and idle transactions
 26. Long-running idle transactions
 27. Long-running queries
 28. Long-running transactions
 29. Long-running vacuums
 30. Sequence Count
 31. Sequential Scans
 32. Shared buffers hit percentage
 33. Table Count
 34. Table size in database
 35. Total connections
 36. Total connections as percentage of max_connections
 37. Total table bloat in database
 38. Tuples deleted
 39. Tuples fetched
 40. Tuples hot updated
 41. Tuples inserted
 42. Tuples returned
 43. Tuples updated
 44. Ungranted locks

4. Schema Level Alerts: User can also configure alerts for specific schema in Database. This is important when you have a schema, related to important Business Objects and you have to monitor the performance of tables in schema. List of those alerts is given below:

 1. Average table bloat in schema
 2. Dead Tuples
 3. Dead tuples percentage
 4. Function Count
 5. Highest table bloat in schema
 6. Hot update percentage
 7. Index Scans
 8. Index size as a percentage of table size
 9. Largest index by table-size percentage
 10. Largest table (by multiple of unbloated size)
 11. Last Analyze
 12. Last AutoAnalyze
 13. Last AutoVacuum
 14. Last Vacuum
 15. Live Tuples
 16. Sequence Count
 17. Sequential Scans
 18. Table Count
 19. Table size in schema
 20. Total table bloat in schema
 21. Tuples deleted
 22. Tuples hot updated
 23. Tuples inserted
 24. Tuples updated

5. Table Level Alerts: User can also create alert table level. Some times, user are interested in monitoring important/specific table which has business importance. For them these alerts are important for maintaining the performance of PG.

 1. Dead Tuples
 2. Dead tuples percentage
 3. Hot update percentage
 4. Index Scans
 5. Index size as a percentage of table size
 6. Last Analyze
 7. Last AutoAnalyze
 8. Last AutoVacuum
 9. Last Vacuum
 10. Live Tuples
 11. Row Count
 12. Sequential Scans
 13. Table bloat
 14. Table Frozen XID
 15. Table size
 16. Table size as a multiple of ubloated size
 17. Tuples deleted
 18. Tuples hot updated
 19. Tuples inserted
 20. Tuples updated

You can see that Postgres Enterprise Manager covers PostgreSQL monitoring from all aspects which directly/indirectly responsible for PostgreSQL performance/Monitoring.

Now, reader of my blog must be thinking, these are the list of alerts. How can they get more information/description on these alerts. Answer is simple, you can get more detail about above alerts by three methods:
1. Using PEM Client HELP

 Open PEM Client -> Go to Help

2. Using Alerting:

  Open PEM Client ->
   For server Level, go to -> PEM Agents -> Right click on particular agent -> click on Alerting

For PG Cluster Level,

go to -> PEM Server Directory -> Right Click on particular PG Cluster -> select Alerting 

Similarly For Database alerts and Table Alerts.

3. Using SQL:
Connect to PEM Server Database:

   psql -p 5432 -U postgres pem

And use following SQLs:
Server Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name AS display_name,description FROM pem.alert_template at WHERE (at.object_type = 100) ORDER BY at.display_name;

CLuster Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description	FROM pem.alert_template at WHERE (at.object_type = 200) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

DB Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description	FROM pem.alert_template at WHERE (at.object_type = 300) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

schema Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description	FROM pem.alert_template at WHERE (at.object_type = 400) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

Table Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description	FROM pem.alert_template at WHERE (at.object_type = 500) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

Have Fun! and Have perfect Monitoring of PostgreSQL.