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 the logical xlogfile.

So, whenerver user sees information for xlog location, he gets xlog information in following format:

(hexadecimal) xlog/ (hexadecimal) offset

To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal:

(FF000000 * xlog + offset) - (FF000000 * xlog + offset)

where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)

Based on above formula, following is plpgsql function which can be use to get the difference:

CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text)
 RETURNS numeric
 LANGUAGE plpgsql
AS 
 $function$
    DECLARE
       offset1 text;
       offset2 text;
       xlog1 text;
       xlog2 text;
       SQL text;
       diff text;
    BEGIN
       /* Extract the Offset and xlog from input in
          offset and xlog variables */
          
       offset1=split_part($1,'/',2);
         xlog1=split_part($1,'/',1);
       offset2=split_part($2,'/',2);
         xlog2=split_part($2,'/',1);
       
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog + offset) - (FF000000 * xlog + offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
       
       SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint 
                                +  x'''||offset1||'''::bigint)'||' 
                - 
                   (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint 
                                +  x'''||offset2||'''::bigint)';
       EXECUTE SQL into diff;
       
       /* Return the value in numeric by explicit casting  */
       
       RETURN diff::numeric;
    END;
 $function$;
 

Usage example is given below:

worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578');
 pg_xlog_location_diff_sql 
---------------------------
                  16230472
(1 row)

I hope this will help PostgreSQL/PPAS 9.0/9.1 users.

List user privileges in PostgreSQL/PPAS 9.1

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:

CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])
AS
$$
SELECT  $1,c.oid::regclass, array(select privs from unnest(ARRAY [ 
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='r' and
has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;

Example:

edb=# select * from table_privs('test_user');
 username  | relname |         privs          
-----------+---------+------------------------
 test_user | test_id | {SELECT,UPDATE,DELETE}
(1 row)

2. Database privileges:

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE 
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname <> 'template0';
$$ language sql;

Example:

edb=# select * from database_privs('test_user');
 username  |  dbname   |         privileges          
-----------+-----------+-----------------------------
 test_user | template1 | {CONNECT}
 test_user | edb       | {CONNECT,TEMPORARY,CONNECT}
(2 rows)

3. Tablespace privileges:

CREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text[])
AS
$$
   SELECT $1, spcname, ARRAY[
(CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE');
$$ language sql;

Example:

edb=# select * from tablespace_privs('test_user');
 username  | spcname | privileges 
-----------+---------+------------
 test_user | test    | {CREATE}
(1 row)

4. Foreign Dataa Wrapper privileges

CREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text[])
AS
$$
  SELECT $1, fdwname, ARRAY[
(CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');
$$ language sql;

Example:

edb=# select * from fdw_wrapper_privs('test_user');
 username  |   fdwname    | privleges 
-----------+--------------+-----------
 test_user | libpq_dblink | {USAGE}
(1 row)

5. To find foreign server privileges following functions can be use:

CREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text[])
AS
$$
  SELECT $1, s.srvname ,  ARRAY[
(CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s  WHERE has_server_privilege ($1,srvname,'USAGE');
$$
language sql;

6. To find language priveleges, following function can be use:

CREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text[])
AS
$$
SELECT $1, l.lanname, ARRAY[(CASE WHEN has_language_privilege($1,lanname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE');
$$ language sql;

7. To find schema privileges of a user following can be use:

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges text[])
AS
$$
  SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE') AND c.nspparent=0;
$$ language sql;


Note:: Above function can be use in Advanced Server. For schema_privilege in PostgreSQL, user can try following function:

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges text[])
AS
$$
  SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');
$$ language sql;

8. To get privilege of a particular with view name, following function can be use:

CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])
AS
$$
SELECT  $1, c.oid::regclass, array(select privs from unnest(ARRAY [ 
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;

9. For Sequence Privilege following function can be use:

CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])
AS
$$
  SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [ 
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]),
(CASE WHEN has_table_privilege($1,c.oid,'USAGE') THEN 'UPDATE' ELSE NULL END) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='S' and 
has_table_privilege($1,c.oid,'SELECT,UPDATE,USAGE')  AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;

User can also make join on above functions to view the privileges on objects something like given below:

select * from ( 
select username,'SCHEMA' as object_type,schemaname as object_name,privieleges 
    FROM schema_privs('test_user') 
 UNION ALL
SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs 
    FROM table_privs('test_user')
 ) order by 2;

Above will give table and schema privileges of a user test_user. Below is output:

edb=# select * from  
edb-# (select username,'SCHEMA' as object_type,schemaname as object_name,privieleges FROM schema_privs('test_user') 
edb(#  UNION ALL
edb(# SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs  FROM table_privs('test_user')
edb(# ) order by 2;

 username  | object_type |    object_name     |      privieleges       
-----------+-------------+--------------------+------------------------
 test_user | SCHEMA      | pg_catalog         | {USAGE}
 test_user | SCHEMA      | public             | {CREATE,USAGE}
 test_user | SCHEMA      | information_schema | {USAGE}
 test_user | SCHEMA      | sys                | {USAGE}
 test_user | SCHEMA      | dbo                | {USAGE}
 test_user | SCHEMA      | test               | {USAGE}
 test_user | TABLE       | test_id            | {SELECT,UPDATE,DELETE}
(7 rows)

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 plperlu, connect to Database as super user and execute following command:

edb=# CREATE EXTENSION plperlu;
CREATE EXTENSION

Now create following functions:

CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float) 
as
$$
  my $ps = "ps aux";
  my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
  my $cmd = $ps."|".$awk;
  $output = `$cmd 2>&1`;
  @output = split(/[\n\r]+/,$output);
  foreach $out (@output)
  { 
    my @line = split(/:/,$out);
    return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
    return undef;
  }
   return;
 $$ language plperlu;

Note:: Above function is made for PostgreSQL running on Linux/Unix System.

Now user can use above function with pg_stat_activity to monitor the percentage of cpu and memory used by particular user/process.

Following is one snapshot:

edb=# select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------
procpid          | 12991
usename          | enterprisedb
application_name | psql
cpu_perc         | 0
mem_perc         | 0.6
current_query    | select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;

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.

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.

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 extension_name DROP member_object

where member_object is:

  AGGREGATE agg_name (agg_type [, ...] ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  CONVERSION object_name |
  DOMAIN object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  TABLE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TYPE object_name |
  VIEW object_name

Advantages of having this feature are given below:
i. Easy to find installed Contrib Modules and its new operators, and new index operator classes.
Following query can be use to find the installed contrib modules:

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;

Short command of psql is given below (with snapshot):

postgres=# \dx
                                       List of installed extensions
   Name    | Version |     Schema      |                            Description
-----------+---------+-----------------+-------------------------------------------------------------------
 adminpack | 1.0     | pg_catalog      | administrative functions for PostgreSQL
 file_fdw  | 1.0     | public          | foreign-data wrapper for flat file access
 pg_trgm   | 1.0     | public          | text similarity measurement and index searching based on trigrams
 pgcrypto  | 1.0     | contrib_modules | cryptographic functions
 plperl    | 1.0     | pg_catalog      | PL/Perl procedural language
 plperlu   | 1.0     | pg_catalog      | PL/PerlU untrusted procedural language
 plpgsql   | 1.0     | pg_catalog      | PL/pgSQL procedural language
 xml2      | 1.0     | public          | XPath querying and XSLT
(8 rows)

ii. pg_dump command knows about all objects of the extension and it will just include a CREATE EXTENSION command in dumps, at the place of increasing size of dump file by including DDLs of objects of module, which will be helpful in migration to new version.
iii. Extension won’t allow drop individual object of an extension, except DROP EXTENSION.

For more detail on Extension, I would recommend to go through following link:

http://www.postgresql.org/docs/9.1/static/extend-extensions.html

2. Foreign Tables.
This is a new features which has been introduced in 9.1. Foreign tables features gives ability to user for accessing data outside of database. Simple Example is, if you have data in a file on server and you want to access that data inside postgreSQL, then you can create a foreign tables to access the data of file in PostgreSQL.
For using this feature, there are multiple extensions available. For accessing of data from a file default extension file_fdw comes with PostgreSQL installation. However, there are other extension also available for accessing data from Different Databases like Oracle, MySQL, CouchDB, Redis, Twitter etc. User can get those extension and details from following link:

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Below is an example of using file_fdw for accessing data from plain file:
i. Create file_fdw extension as given below:

psql -c "CREATE EXTENSION file_fdw" -d postgres
CREATE EXTENSION

ii. Create foreign Data wrapper as given below:

psql -c "CREATE FOREIGN DATA WRAPPER file_data_wrapper HANDLER file_fdw_handler;" -d postgresCREATE FOREIGN DATA WRAPPER

iii. Create Server using Foreign data wrapper, as given below:

psql -c " CREATE SERVER file FOREIGN DATA WRAPPER file_fdw;" -d postgres
CREATE SERVER

iv. Now, lets create foreign table to access file: my_data.file content is given below:

File: my_data.file
1,3,'First Row'
4,5,'Second Row'
7,8,'Third Row'
9,10,'Fourth Row'
11,12,'Fifth Row'

psql -c "CREATE FOREIGN TABLE my_data(field1 numeric, field2 numeric, field_detail text) server file options (filename '/Users/postgres/my_data.file', format 'csv', delimiter ',');" -d postgres

Now, we have foreign table which we can use to access data of my_data.file in database as given below:

postgress-MacBook-Pro:~ postgres$ psql -c "select * from my_data;" -d postgres
 field1 | field2 | field_detail
--------+--------+--------------
      1 |      3 | 'First Row'
      4 |      5 | 'Second Row'
      7 |      8 | 'Third Row'
      9 |     10 | 'Fourth Row'
     11 |     12 | 'Fifth Row'
(5 rows)

3. Improvement in ALTER TYPE for ENUM.

Till 9.0, ALTER TYPE for ENUM requires dropping of dependent columns and then after modification of ENUM recreating of those dependent columns, which was big block for modifying of ENUM.
Now in 9.1, user can modify ENUM without dropping dependent columns as given below:

postgres=# CREATE TYPE allowed_color AS ENUM ('RED','WHITE');
CREATE TYPE
postgres=# CREATE TABLE possible_color(color allowed_color);
CREATE TABLE
postgres=# INSERT INTO possible_color VALUES('RED');
INSERT 0 1
postgres=# INSERT INTO possible_color VALUES('WHITE');
INSERT 0 1
postgres=# INSERT INTO possible_color VALUES('BLACK');
ERROR:  invalid input value for enum allowed_color: "BLACK"
LINE 1: INSERT INTO possible_color VALUES('BLACK');
                                          ^
postgres=# ALTER TYPE allowed_color ADD VALUE 'BLACK' AFTER 'WHITE';
ALTER TYPE
postgres=# INSERT INTO possible_color VALUES('BLACK');
INSERT 0 1
postgres=# SELECT * FROM possible_color ;
 color
-------
 RED
 WHITE
 BLACK
(3 rows)

4. ALTER TYPE ADD/DROP/ALTER/RENAME ATTRIBUTE for composite types:

In 9.1, users would be able to do modification in composite type using ALTER TYPE Command. Example is given below:

i. Create a composite type:

postgres=# CREATE TYPE test_alter_type AS (col1 int);
CREATE TYPE

ii. Create a table based on composite type created above:

postgres=# CREATE TABLE test_alter_type_table (initial_col test_alter_type);
CREATE TABLE
postgres=# \d test_alter_type_table
   Table "public.test_alter_type_table"
   Column    |      Type       | Modifiers
-------------+-----------------+-----------
 initial_col | test_alter_type |

postgres=# INSERT INTO test_alter_type_table VALUES(ROW(1));
INSERT 0 1
postgres=# INSERT INTO test_alter_type_table VALUES(ROW(2));
INSERT 0 1
postgres=# SELECT * FROM test_alter_type_table ;
 initial_col
-------------
 (1)
 (2)
(2 rows)

iii. ALTER composite type to add new attribute

postgres=# ALTER TYPE test_alter_type ADD ATTRIBUTE col2 int;
ALTER TYPE
postgres=# SELECT * FROM test_alter_type_table ;
 initial_col
-------------
 (1,)
 (2,)
(2 rows)

iv. Create a function for Composite type to assign a Values to composite type as given below:

</pre>
CREATE FUNCTION test_alter_type_constructor(test_alter_type) RETURNS test_alter_type
AS
$$ SELECT $1::test_alter_type;
$$ language sql;
<pre>

v. Update values in added attribute of Composite type

postgres=# UPDATE test_alter_type_table SET initial_col=ROW(1,2) WHERE initial_col=test_alter_type_constructor(ROW(1,NULL));
UPDATE 1
postgres=# UPDATE test_alter_type_table SET initial_col=ROW(2,2) WHERE initial_col=test_alter_type_constructor(ROW(2,NULL));
UPDATE 1
postgres=# SELECT * FROM test_alter_type_table;
 initial_col
-------------
 (1,2)
 (2,2)
(2 rows)

vi. RENAME Attribute and Composite type as given below:

postgres=# ALTER TYPE test_alter_type RENAME ATTRIBUTE col2 to col3;
ALTER TYPE
postgres=# \d test_alter_type
Composite type "public.test_alter_type"
 Column |  Type   | Modifiers
--------+---------+-----------
 col1   | integer |
 col3   | integer |

postgres=# ALTER TYPE test_alter_type RENAME TO test_alter_type3;
ALTER TYPE
postgres=# \d test_alter_type3
Composite type "public.test_alter_type3"
 Column |  Type   | Modifiers
--------+---------+-----------
 col1   | integer |
 col3   | integer |

postgres=# \d test_alter_type
test_alter_type3      test_alter_type_table
postgres=# \d test_alter_type_table
    Table "public.test_alter_type_table"
   Column    |       Type       | Modifiers
-------------+------------------+-----------
 initial_col | test_alter_type3 |

For more detail on supported options of ALTER TYPE for Composite types, user can use following link:

http://www.postgresql.org/docs/9.1/static/sql-altertype.html

5. ALTER TYPE {OF|NOT OF} support for Typed Table.
This is a new sytax which has been added in PostgreSQL 9.1. Using this syntax user can now switch a standalone table into typed table and can switch typed table into standalone. Example is given below:

postgres=# CREATE TABLE table_type_test(id NUMERIC, test TEXT);
CREATE TABLE

postgres=# CREATE TYPE test_type AS (id NUMERIC, test TEXT);
CREATE TYPE
postgres=# ALTER TABLE table_type_test OF test_type;
ALTER TABLE
postgres=# \d table_type_test
Table "public.table_type_test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | numeric |
 test   | text    |
Typed table of type: test_type
postgres=# ALTER TABLE table_type_test NOT OF;
ALTER TABLE
postgres=# \d table_type_test
Table "public.table_type_test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | numeric |
 test   | text    |

6. Supported Syntax of ALTER SET SCHEMA for other objects

In PostgreSQL 9.1, ALTER SET SCHEMA is supported for following objects:

  a. conversions,
  b. operators,
  c. operator classes,
  d. operator families,
  e. text search configurations,
  f. text search dictionaries,
  g. text search parsers
  h. text search templates.

Simple example is given below:

postgres=# ALTER OPERATOR %(text,text) SET SCHEMA contrib_modules;
ALTER OPERATOR
postgres=# \do contrib_modules.%
                                  List of operators
     Schema      | Name | Left arg type | Right arg type | Result type | Description
-----------------+------+---------------+----------------+-------------+-------------
 contrib_modules | %    | text          | text           | boolean     |
(1 row)
postgres=# ALTER OPERATOR contrib_modules.%(text,text) SET SCHEMA public;
ALTER OPERATOR

7. Modify UNIQUE/Primary KEY using Unique Index.

This is a very interesting feature which has been added in PostgreSQL 9.1.
Till 9.0, user used to use REINDEX command for Rebuilding indexes of Primary/Unique Keys or ALTER TABLE ADD CONSTRAINT for adding Primary/Unique Key, which used to keep TABLE Level lock for long time (depending on amount of data in table).
Now, in 9.1, User can use CREATE UNIQUE INDEX CONCURRENTLY command to create unique index and would be able to use created index for Primay/Unique Key without having Table Level Lock for long time.

Example is given below

For Adding Primary Key:

postgres=# CREATE UNIQUE INDEX CONCURRENTLY emp_pk_idx ON emp_new(empno);
CREATE INDEX
postgres=# ALTER TABLE emp_new ADD PRIMARY KEY USING INDEX emp_pk_idx;
ALTER TABLE
postgres=# \d emp_new;
            Table "public.emp_new"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 empno    | numeric(4,0)          | not null
 ename    | character varying(10) |
 job      | character varying(9)  |
 mgr      | numeric(4,0)          |
 hiredate | date                  |
 sal      | numeric(7,2)          |
 comm     | numeric(7,2)          |
 deptno   | numeric(2,0)          |
Indexes:
    "emp_pk_idx" PRIMARY KEY, btree (empno)

Now, suppose user wants to Rebuild Primary Key Index, then he can try something like given below:

  CREATE UNIQUE INDEX CONCURRENTLY emp_pk_idx_new ON emp_new(empno);
  BEGIN;
  ALTER TABLE emp_new DROP CONSTRAINT emp_pk_idx;
  ALTER TABLE emp_new ADD PRIMARY KEY USING INDEX emp_pk_idx_new;
  END;

Above Lock period would be less than rebuilding Index emp_pk_idx.

Similar kind of idea, I have presented in my old post for PostgreSQL 8.4, which link is given below:

https://vibhorkumar.wordpress.com/2010/12/16/rebuilding-pkey-and-indexes-without-locking-table-in-pg-8-4/

8. Foreign Key without Validation.

9.1 also supports adding foreign key on table without validation of old data, which means the constraint is checked for new or modified rows, but existing data may violate the constraint.
Note:: Converting a NO VALID constraint to VALID may take a long time depending on the amount of data to be validated.
Example is given below:

CREATE TABLE emp_fk_key_table(id NUMERIC);
CREATE TABLE

Lets insert Few Data in Table:

postgres=# INSERT INTO emp_fk_key_table VALUES(1);
INSERT 0 1
postgres=# INSERT INTO emp_fk_key_table VALUES(2);
INSERT 0 1
postgres=# INSERT INTO emp_fk_key_table VALUES(3);
INSERT 0 1

Now, Create Foreign Key with NOT VALID Option:

ALTER TABLE emp_fk_key_table ADD CONSTRAINT fk_constraint FOREIGN KEY(id) REFERENCES emp_new(empno) NOT VALID;

Above Constraint will be checked for new data as given below:

postgres=# INSERT INTO emp_fk_key_table VALUES(5);
ERROR:  insert or update on table "emp_fk_key_table" violates foreign key constraint "fk_constraint"
DETAIL:  Key (id)=(5) is not present in table "emp_new".

For Old data Constraint Check will not happen. However, later if user wants to validate old data in table, then he/she can use VALIDATE CONSTRAINT option as given below:

ALTER TABLE emp_fk_key_table VALIDATE CONSTRAINT fk_constraint;

9. No rewrite in appropriate cases of Change in Datatype

Till 9.0, Any change in data type ALTER TABLE SET DATA TYPE command used to re-write whole table. Now in 9.1, Data type change may not result in re-writing of table, which will result in Fast execution of ALTER TABLE SET DATA TYPE command.
Example is given below:

In 9.0:

postgres=# CREATE TABLE test_rewrite(col VARCHAR(10));
CREATE TABLE
postgres=#
postgres=# INSERT INTO test_rewrite SELECT * FROM generate_series(1,100);
INSERT 0 100
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname
-------------+--------------
       31318 | test_rewrite
(1 row)
postgres=# ALTER TABLE test_rewrite ALTER COLUMN col SET DATA TYPE TEXT;
ALTER TABLE
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname
-------------+--------------
       31324 | test_rewrite     ## Re-write result in new relfilenode
(1 row)

In PostgreSQL 9.1

postgres=# CREATE TABLE test_rewrite(col VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test_rewrite SELECT * FROM generate_series(1,100);
INSERT 0 100
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname
-------------+--------------
       35164 | test_rewrite
(1 row)

postgres=# ALTER TABLE test_rewrite ALTER COLUMN col SET DATA TYPE TEXT;
ALTER TABLE
postgres=# SELECT relfilenode, relname FROM pg_class WHERE relname='test_rewrite';
 relfilenode |   relname
-------------+--------------
       35164 | test_rewrite
(1 row)

10. IF NOT EXISTS in CREATE TABLE
This is a new option which has been added in CREATE TABLE Command. Now, user can use IF NOT EXISTS clause in CREATE TABLE command, which will not throw/show any error message if table exists. Example is given below:

postgres=# CREATE TABLE IF NOT EXISTS test_rewrite(col INTEGER);
NOTICE:  relation "test_rewrite" already exists, skipping
CREATE TABLE

Above is very useful for Programming perspective, where developer needs to verify everytime if table exists or not and accordingly create table or skip table. Following is an example of such scenario.
In 9.0,

DO $$
 DECLARE
   verify boolean;
 BEGIN
   SELECT CASE WHEN count(*) = 1 THEN true ELSE false END INTO verify FROM pg_tables WHERE schemaname='enterprisedb' AND tablename='test_rewrite';
    IF verify THEN
       RAISE NOTICE 'test_rewrite TABLE EXISTS';
    ELSE
       CREATE TABLE test_rewrite(col NUMERIC(20));
    END IF;
  END
$$ language plpgsql;

NOTICE:  test_rewrite TABLE EXISTS
DO

In 9.1, User doesn’t have to write code like above, simple CREATE TABLE IF NOT EXISTS will work.

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:

https://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 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 for Streaming Replication
REPLICATION is a new role has been added in PostgreSQL 9.1 for Streaming Replication. If a user has REPLICATION role granted then user would be able to initiate Streaming Replication. Some example is given below:
a. CREATE USER with REPLICATION Privilege:

     CREATE USER rep_user WITH REPLICATION PASSWORD 'password';

b. Assign REPLICATION Privilege to a user:

      ALTER USER username WITH REPLICATION;

REPLICATION privileged can also used for Online Backup of PostgreSQL.

4. New tool for Base Backup: pg_basebackup
pg_basebackup is a new Backup tool introduce in PostgreSQL 9.1. Using this tool user can take Base Backups of running PostgreSQL database cluster.
To use pg_basebackup, user has to make following in Changes in PostgreSQL Cluster
a. Make following changes in pg_hba.conf file:

      host    replication     postgres        [Ipv4 address of client]/32  trust

b. Make following changes in postgresql.conf file of PostgreSQL Cluster:

    archive_command = 'cp -i %p /Users/postgres/archive/%f'
    archive_mode = on # Require Restart
    max_wal_senders = 3 # Maximum 'wal_senders'
    wal_keep_segments = # How many WAL segments (=files) should be kept on the server

c. After making above changes, user can restart the PostgreSQL Cluster and can use pg_basebackup to take the backup as given below:

     pg_basebackup -D /Users/vibhor/testbackup -v -Fp -l Testbackup -h 127.0.0.1 -U postgres

Note:: User which can use pg_basebackup should have either SUPERUSER or REPLICATION privilege.

5. Functions to Control Streaming Replication replay:
New version of PostgreSQL has New Streaming Replication Control Function. Information on it is given below:
a. pg_xlog_replay_pause():
Using this function user can pause recovery of Standby and would be able to take consistent backup of Standby Data Directory. Example is given below:

postgres=# select  pg_xlog_replay_pause();
 pg_xlog_replay_pause
----------------------

(1 row)

b. pg_is_xlog_replay_paused():
Using this function user would be able to check the Standby/Streaming Replication is paused or not. Example is given below:

 postgres=# select  pg_is_xlog_replay_paused();
 pg_is_xlog_replay_paused
--------------------------
 t
(1 row)

c. pg_xlog_replay_resume():
Using this function user would be able resume replication of standby/streaming replication, if its recovery/replay is paused. Example is given below:

postgres=# select pg_xlog_replay_resume();
 pg_xlog_replay_resume
-----------------------

(1 row)

postgres=# select  pg_is_xlog_replay_paused();
 pg_is_xlog_replay_paused
--------------------------
 f
(1 row)

6. New in Replication Monitoring:
a. View: pg_stat_replication:
This view displays information on WAL sender processes. View contains one row for each WAL sender process shows, information on processid, user (oid), username, application name, host name (if available) and port number, time at which the server process began execution, and the current WAL sender state and transaction log location. Example is given below:

postgres=# select * from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |          backend_start           |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
---------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
    2319 |       10 | postgres | sync_replication | ::1         |                 |       50224 | 2011-11-10 21:39:45.424503+05:30 | streaming | 0/33002798    | 0/33002798     | 0/33002798     | 0/33002798      |             0 | async
(1 row)

Note: pg_stat_replication view will give information on Master. Executing Query against pg_stat_replication view on Standby/streaming will return zero rows.

b. Function: pg_last_xact_replay_timestamp():
Above function gives time stamp of last transaction replayed during recovery, time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last transaction applied during that recovery. When the server has been started normally without recovery the function returns NULL. Example is given below:

postgres=# select * from pg_last_xact_replay_timestamp();
  pg_last_xact_replay_timestamp
----------------------------------
 2011-11-10 22:17:26.431321+05:30
(1 row)

7. New in Hot Standby:
a. New parameter hot_standby_feedback:
This is a new parameter which has been added in postgresql.conf file for Standby server. Using this parameter, now user would be able avoid canceling of Queries. This enable Hot Standby to postpone of cleaning old version of rows if any SELECT query running on Standby and makes Hot Standby to send feedback, once as per wal_receive_status_interval, to primary about queries currently executing on Standby.
Note:: Setting this parameter may result in bloat on primary.

b. New column:(conflicts) in pg_stat_database
In PostgreSQL 9.1, New column conflicts has been added in pg_stat_database. This columns gives the total number of queries canceled due to conflict with recovery on standby. Example is given below:

postgres=# select datname, conflicts from pg_stat_database;
  datname  | conflicts
-----------+-----------
 template1 |         0
 template0 |         0
 postgres  |         0
 korean    |         0
(4 rows)

c. New view: pg_stat_database_conflicts
In 9.1, pg_stat_database_conflicts view has been added for monitoring and finding the cancelled queries due dropped tablespaces/ lock timeouts/old snapshots/pinned buffers/deadlocks.
This view contains one row per database, which gives information on database OID, database name and the number of queries that have been canceled in this database due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks. Example is given below:

postgres=# select * from pg_stat_database_conflicts;
 datid |  datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-------+-----------+------------------+------------+----------------+-----------------+----------------
     1 | template1 |                0 |          0 |              0 |               0 |              0
 12172 | template0 |                0 |          0 |              0 |               0 |              0
 12180 | postgres  |                0 |          0 |              0 |               0 |              0
 25354 | korean    |                0 |          0 |              0 |               0 |              0
(4 rows)

Note: User has to run queries against this view on Standby, since conflicts occurs on Standby.

d. Increase the maximum values for max_standby_archive_delay and max_standby_streaming_delay.
In PostgreSQL 9.0, maximum value for max_standby_archive_delay and max_standby_streaming_delay were 35 minutes. Now in PostgreSQL 9.1, user can mention much larger value. These parameters determines the maximum total time allowed to apply any WAL segment/WAL data.
For more detail about these parameter, please refer following page of document:

   http://www.postgresql.org/docs/9.1/static/runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY

e. New Error Code: ERRCODE_T_R_DATABASE_DROPPED

  Error Code    Condition Name
      57P04	database_dropped

Till 9.0, PostgreSQL used to use ERRCODE_ADMIN_SHUTDOWN for recovery conflict( on Standby) caused by Database Drop on Master. Now, in 9.1, ERRCODE_T_R_DATABASE_DROPPED will be used for Same situation. This change made for poolers to handle such situation (where database on Master no longer exists) correctly like pgpool.

8. New in Recovery Control:

a. pg_create_restore_point(text) function and recovery_target_name parameter:

PostgreSQL 9.1 has come with the special function pg_create_restore_point(text). Using this function, admin/DBA can now create their own recovery/restore point. pg_create_restore_point returns Transaction log location, upto which user can restore their Hotbackup. To support the named restore point, PostgreSQL 9.1, has new parameter recovery_target_name for recovery.conf file
Let see these two in action.
i. Set following parameters in postgresql.conf file of cluster:

    archive_mode=on
    archive_command='cp -i %p /Users/postgres/archive/%f'

ii. Now, lets take base backup using pg_basebackup as given below:

edbs-MacBook-Pro:standby postgres$ pg_basebackup -D /Users/postgres/test_backup -v -Fp -l "Standby Backup" -h localhost
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
pg_basebackup: base backup completed

iii. Now lets connect to database and make Named restore point using pg_create_restore_point

postgres=# select pg_create_restore_point('myrecoverylocation');
 pg_create_restore_point
-------------------------
 0/3B000118
(1 row)

To verify the behavior of recovery_target_name parameter, lets create a table and perform some activities:

postgres=# create table test_id as select id from generate_series(1,1000000) as t(id);
SELECT 1000000
postgres=#

iv. Now, create recovery.conf file as given below in Backup directory:

restore_command = 'cp -i /Users/postgres/archive/%f %p'		# e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_name = myrecoverylocation

and change the port number of restored postgresql.conf as given below:

vi /Users/postgres/test_backup/postgresql.conf
port=5433

v. Now, start the restored PostgreSQL cluster as given below:

pg_ctl -D /Users/postgres/test_backup start

After executing above command, PostgreSQL will perform recovery till recovery_target_name location. To verify this, user can check PostgreSQL logfile, which will display message something like given below:

2011-11-14 17:15:18 IST LOG:  database system was interrupted; last known up at 2011-11-14 17:10:51 IST
2011-11-14 17:15:18 IST LOG:  creating missing WAL directory "pg_xlog/archive_status"
2011-11-14 17:15:18 IST LOG:  starting point-in-time recovery to "myrecoverylocation"

With this, after connecting to restore database, user can see that test_id table which we had created, after pg_create_restore_point(‘myrecoverylocation’), will not be part of restored database as given below:

--Restored Database:
edbs-MacBook-Pro:test_backup postgres$ psql -p 5433
Password:
psql (9.1.1)
Type "help" for help.

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | dept          | table | postgres
 public | emp           | table | postgres
 public | employees     | table | postgres
 public | entities      | table | postgres
 public | jobhist       | table | postgres
 public | like_op_table | table | postgres
 public | person_job    | table | postgres
 public | test          | table | postgres
 public | test_default  | table | postgres
(9 rows)

-- Primary Database:
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | dept          | table | postgres
 public | emp           | table | postgres
 public | employees     | table | postgres
 public | entities      | table | postgres
 public | jobhist       | table | postgres
 public | like_op_table | table | postgres
 public | person_job    | table | postgres
 public | test          | table | postgres
 public | test_default  | table | postgres
 public | test_id       | table | postgres
(10 rows)

b. Standby recovery to switch to a new timeline automatically

Till 9.0, when user specify recovery_target_timeline=’latest’, postgreSQL scan for the latest timeline at the beginning of recovery, and pick that as the target. If new timelines appear during recovery, PostgreSQL stick to the target chosen in the beginning, the new timelines are ignored. To make PostgreSQL to notice about the new timeline, user has to restart the Standby. Now in 9.1, Standby servers scan the archive directory for new timelines periodically and switch to new timeline new timeline appears during recovery.

c. New parameter: restart_after_crash = on/off

By default till 9.0, whenever, there is backend crash PostgreSQL used to automatically get restarted and there was no control on restarting of PostgreSQL (like for clusterware solutions, where clusterware userd to try to restart the PostgreSQL). Adding this parameter gives control on restart of postgreSQL.
If value of restart_after_crash is on, then PostgreSQL will restart automatically, after a backend crash.
If value is off, then PostgreSQL will not restart, after backend crash and will be get shutdown.

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 <preamble.slonik>;
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 <preamble.slonik>;
STORE PATH (SERVER=1, CLIENT=2, CONNINFO='dbname=postgres host=localhost user=postgres port=5432 password=password');
STORE PATH (SERVER=2, CLIENT=1, CONNINFO='dbname=repdb host=localhost user=postgres port=5432 password=passsword');

Master Slon Process conf file:

## Master Slon Conf:

vac_frequency=3
cleanup_interval="10 minutes"
log_level=4
sync_interval=2000
sync_interval_timeout=10000
sync_group_maxsize=6
sync_max_rowsize=8192
sync_max_largemem=5242880
syslog=0
log_pid=false
log_timestamp=true
pid_file='/Users/vibhor/PGtools/slony/master_slon.pid'
syslog_ident=slon
cluster_name='slonytest'
conn_info='dbname=postgres host=localhost user=postgres port=5432 password=postgres'
desired_sync_time=60000
sql_on_connection="SET log_min_duration_statement TO '1000';"

Slave Slon process conf file:

## Slave Slon Conf:
vac_frequency=3
cleanup_interval="10 minutes"
log_level=4
sync_interval=2000
sync_interval_timeout=10000
sync_group_maxsize=6
sync_max_rowsize=8192
sync_max_largemem=5242880
syslog=0
log_pid=false
log_timestamp=true
pid_file='/Users/vibhor/PGtools/slony/slave_slon.pid'
syslog_ident=slon
cluster_name='slonytest'
conn_info='dbname=repdb host=localhost user=postgres port=5432 password=Empid#042'
desired_sync_time=60000
sql_on_connection="SET log_min_duration_statement TO '1000';"

Ater creating above configuration, user can setup Slony Replication.

Now, lets look at the features added in slony 2.1

1. Support for adding tables in bulk

In Previous Version of Slony, if user has to add tables of a particular schema, then he has to write slonik command as given below:

include <preamble.slonik>;
create set (id=1, origin=1, comment='slonytest Tables and Sequences');
set add table (id=1, set id=1, origin=1, fully qualified name='public.dept', comment='slonytest table public.dept');
set add table (id=2, set id=1, origin=1, fully qualified name='public.emp', comment='slonytest table public.emp');
set add table (id=3, set id=1, origin=1, fully qualified name='public.employees', comment='slonytest table public.employees');

Which was a bit work, where user has to list all the tables and then add into slonik command Or user has to write a script which can generate slonik command.

Now, in slony 2.1, user can write single command to add all tables of a schema in slony replication as given below:

include <preamble.slonik>;
create set (id=1, origin=1, comment='slonytest Tables and Sequences');
set add table( set id=1, tables='public.*');

Snapshot of activity is given below:

edbs-MacBook-Pro:slony vibhor$ cat create_set.slonik
include <preamble.slonik>;
create set (id=1, origin=1, comment='slonytest Tables and Sequences');
set add table( set id=1, tables='public.*');
edbs-MacBook-Pro:slony vibhor$
edbs-MacBook-Pro:slony vibhor$ slonik < create_set.slonik

User can verify the added tables in bulk using following command:

postgres=# select * from _slonytest.sl_table;
 tab_id | tab_reloid | tab_relname | tab_nspname | tab_set |   tab_idxname   | tab_altered |   tab_comment
--------+------------+-------------+-------------+---------+-----------------+-------------+------------------
      1 |      16438 | dept        | public      |       1 | dept_pk         | f           | replicated table
      2 |      16445 | emp         | public      |       1 | emp_pk          | f           | replicated table
      3 |      16995 | employees   | public      |       1 | employees_pkey  | f           | replicated table
      4 |      16987 | entities    | public      |       1 | entities_pkey   | f           | replicated table
      5 |      16456 | jobhist     | public      |       1 | jobhist_pk      | f           | replicated table
      6 |      16967 | person_job  | public      |       1 | person_job_pkey | f           | replicated table
(6 rows)

If user wants to add all sequences of a particular schema in Slony Replication, then he can try something like given below:

include <preamble.slonik>;
set add sequences(set id=1, tables='public.*seq'); ## Adding all sequences of Public schema

User can also use some patterns while adding schema as given below:

include <preamble.slonik>;
set add tables(set id=1, tables='public.slonytest_[1234]',add sequences=true); ## adding tables: slonytest_1, slonytest_2, slonytest_3

2. Implicit WAIT FOR

In Previous version of Slony, user has to use WAIT FOR Clause to before executing command like MergeSet/MoveSet

     SUBSCRIBE SET (ID = 999, PROVIDER = 1, RECEIVER = 2);
     WAIT FOR EVENT (ORIGIN = 1, CONFIRMED = ALL, WAIT ON=1);
     SUBSCRIBE SET (ID = 999, PROVIDER = 1, RECEIVER = 3);
     WAIT FOR EVENT (ORIGIN = 1, CONFIRMED = ALL, WAIT ON=1);
     MERGE SET ( ID = 1, ADD ID = 999, ORIGIN = 1 );

Now, in 2.1, if user forgets WAIT FOR clause, then slony will use implicit WAIT FOR whenever it requires. However, user has to keep following in mind:
a. User should not run multiple slonik script parallely. Since, running parallel slonik script may confuse Slonu implicit WAIT FOR behavior.
b. Slonik Command will use WAIT FOR if event node changes.
c. Slonik Command will wait before executing SUBSCRIVE SET/DROP NODE/CLONE NODE Commands, till primary caught up with other slave nodes.
d. Slonik Command CREATE SET will wait, untill all outstanding DROP SET confirmed by master and slaves in a Slony Replication cluster.
e. WAIT FOR command will not work in TRY block. — (Incompatibilities of other version)

3. Support for replicating TRUNCATE
Slony 2.1 has introduced feature of Replication TRUNCATE Command on slaves. TRUNCATE replication is possible for PostgreSQL version >=8.4.
When user subscibe a table using slony 2.1, then slony creates a _slonytest_truncatetrigger trigger on master to replicate those truncate command.
Lets see its work:

edbs-MacBook-Pro:~ vibhor$ psql -c "truncate table person_job" postgres
TRUNCATE TABLE
edbs-MacBook-Pro:~ vibhor$ psql -c "select * from person_job" repdb
 pid | job
-----+-----
(0 rows)

4. Health checks at startup
slony 2.1, now checks the status of Each node during startup and give proper message to fix the issue. In health check it checks the configuration information and returns OK, if there is no problem. Pl/pgSQL function which has been added for health check is given below:

CREATE OR REPLACE FUNCTION _slonytest.slon_node_health_check()
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
declare
		prec record;
		all_ok boolean;
begin
		all_ok := 't'::boolean;
		-- validate that all tables in sl_table have:
		--      sl_table agreeing with pg_class
		for prec in select tab_id, tab_relname, tab_nspname from
		"_slonytest".sl_table t where not exists (select 1 from pg_catalog.pg_class c, pg_catalog.pg_namespace n
				where c.oid = t.tab_reloid and c.relname = t.tab_relname and c.relnamespace = n.oid and n.nspname = t.tab_nspname) loop
				all_ok := 'f'::boolean;
				raise warning 'table [id,nsp,name]=[%,%,%] - sl_table does not match pg_class/pg_namespace', prec.tab_id, prec.tab_relname, prec.tab_nspname;
		end loop;
		if not all_ok then
		   raise warning 'Mismatch found between sl_table and pg_class.  Slonik command REPAIR CONFIG may be useful to rectify this.';
		end if;
		return all_ok;
end
$function$

5. Performance improvement in cases of large backlog
Slony 2.1 came with performance improvement in case of large replication backlog. In previous versions, user used to experience performance impact when sl_log_* tables grows and replication of data to subscriber takes so long. Due to which Slony Replication takes huge time to catch up. Main cause of this performance issue was: Slony used to do sequential scan while pulling data from sl_log_* tables. Now, in 2.1, queries which pulls data from sl_log_* have been modified and now it uses index scan.

6. Monitoring thread to provide better monitoring data
2.1, also came with one monitoring table sl_components. This table is very useful in monitoring the Slony Threads. Description/detail of sl_components can be found in following link:

http://slony.info/documentation/2.1/table.sl-components.html

Snapshot of informantion display by sl_components is given below:

postgres=# select * from sl_components;
       co_actor       | co_pid | co_node | co_connection_pid |   co_activity    |       co_starttime        |  co_event  | co_eventtype
----------------------+--------+---------+-------------------+------------------+---------------------------+------------+--------------
 local_listen         |  26688 |       1 |             26692 | thread main loop | 2011-11-03 12:07:56+05:30 |            | n/a
 local_sync           |  26688 |       0 |             26697 | thread main loop | 2011-11-03 12:08:00+05:30 |            | n/a
 remote listener      |  26688 |       2 |             26695 | thread main loop | 2011-11-03 12:08:00+05:30 |            | n/a
 remoteWorkerThread_2 |  26688 |       2 |             26694 | SYNC             | 2011-11-03 12:08:00+05:30 | 5000004917 | SYNC
 local_monitor        |  26688 |       0 |             26698 | thread main loop | 2011-11-03 00:32:19+05:30 |            | n/a
 local_cleanup        |  26688 |       0 |             26696 | cleanupEvent     | 2011-11-03 12:00:15+05:30 |            | n/a
(6 rows)

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 include INSERT/UPDATE/DELETE in WITH Clause and can pass data to the containing query. Following are some example:

-- INSERT ... RETURNING
WITH t AS (
    INSERT INTO y
    VALUES
        (11),
        (12),
        (13),
        (14),
        (15),
        (16),
        (17),
        (18),
        (19),
        (20)
    RETURNING *
)
SELECT * FROM t;

-- UPDATE ... RETURNING
WITH t AS (
    UPDATE y
    SET a=a+1
    RETURNING *
)
SELECT * FROM t;

-- DELETE ... RETURNING
WITH t AS (
    DELETE FROM y
    WHERE a <= 10
    RETURNING *
)
SELECT * FROM t;

Also, user can attache WITH CLAUSE to INSERT/UPDATE and DELETE Statements as given below:

-- data-modifying WITH in a modifying statement
WITH t AS (
    DELETE FROM y
    WHERE a <= 10
    RETURNING *
)
INSERT INTO y SELECT -a FROM t RETURNING *;

3. GROUP BY enhancement for missing columns

Previous version of PostgreSQL used to throw error message, if user forgets to specify any columns of target list in GROUP BY Clause, even if primary key is specified, as given below:

select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.5 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit

SELECT count(*), entity_name, entity_address as address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
ERROR:  column "entities.entity_address" must appear in the GROUP BY clause or be used in an aggregate function

Now in PostgreSQL 9.1, GROUP BY Clause can gues about the missing Column as given below:

SELECT count(*), entity_name, entity_address as address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
 count | entity_name | address
-------+-------------+----------
     2 | HR          | address1
     2 | SALES       | address2
(2 rows)

4. Per Column Collation.

In Previous version of PostgreSQL, collation (the sort ordering of text string) was supported only at database level. Now, in 9.1, user can set collation per column, index, or expression via COLLATE Clause. Some example is given below:

postgres=#  CREATE TABLE french_messages (message TEXT COLLATE "fr_FR");
CREATE TABLE
postgres=# select * from french_messages order by 1;
 message
---------
 Élève
 élever
 élevé
 élève
(4 rows)

postgres=# SELECT a, b, a < b as lt FROM
postgres-#   (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b);
 a | b | lt
---+---+----
 a | B | f
 A | b | t
(2 rows)

5. Planner Improvements
a. Inheritance Table.
Now in 9.1, Some planner improvements has been done, like MIN/MAX for Inheritance tables. This improvement will boost up performance queries for user, if they are using paritioning.
lets see those improvements:

i) Create partition table as given below:

      CREATE TABLE main(id integer, val text);
      CREATE TABLE parition_1(CHECK(id >=1 and id <=20)) INHERITS(main);
      CREATE TABLE parition_2(CHECK(id >=21 and id <=40)) INHERITS(main);
      CREATE TABLE parition_3(CHECK(id >=41 and id <=60)) INHERITS(main);
      CREATE TABLE parition_other(CHECK(id >=61)) INHERITS(main);
      CREATE INDEX parition_1_idx on parition_1(id);
      CREATE INDEX parition_2_idx on parition_2(id);
      CREATE INDEX parition_3_idx on parition_3(id);
      CREATE INDEX parition_other_idx on parition_other(id);

ii) Create trigger as given below:

       CREATE OR REPLACE FUNCTION main_insert_direct( )
       RETURNS  trigger
       LANGUAGE plpgsql
       AS $function$
            BEGIN
                   IF NEW.id >=1 AND NEW.id <=20 THEN
                       INSERT INTO parition_1 values(NEW.*);
                   ELSIF NEW.id >=21 AND NEW.ID <=40 THEN
			INSERT INTO parition_2 values(NEW.*);
                   ELSIF NEW.id >=41 AND NEW.ID <=60 THEN
			INSERT INTO parition_3 values(NEW.*);
                   ELSE
                         INSERT INTO parition_other VALUES(NEW.*);
                   END IF;
                   RETURN NULL;
           END;
       $function$;

       CREATE TRIGGER insert_on_main
         BEFORE INSERT ON main
         FOR EACH ROW EXECUTE PROCEDURE main_insert_direct();

iii). INSERT some values as given below:

        INSERT INTO main SELECT * FROM generate_series(1,1000000000);

Now lets see plan. On previous version of PostgreSQL:

postgres=# explain analyze select id from main order by id desc limit 10

                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1148.68..1148.70 rows=10 width=4) (actual time=147.799..147.812 rows=10 loops=1)
   ->  Sort  (cost=1148.68..1220.94 rows=28905 width=4) (actual time=147.796..147.800 rows=10 loops=1)
         Sort Key: public.main.id
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Result  (cost=0.00..524.05 rows=28905 width=4) (actual time=0.018..117.908 rows=50000 loops=1)
               ->  Append  (cost=0.00..524.05 rows=28905 width=4) (actual time=0.017..74.136 rows=50000 loops=1)
                     ->  Seq Scan on main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.001..0.001 rows=0 loops=1)
                     ->  Seq Scan on parition_1 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.014..0.073 rows=100 loops=1)
                     ->  Seq Scan on parition_2 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.011..0.070 rows=100 loops=1)
                     ->  Seq Scan on parition_3 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.009..0.057 rows=100 loops=1)
                     ->  Seq Scan on parition_other main  (cost=0.00..434.85 rows=23985 width=4) (actual time=0.021..32.197 rows=49700 loops=1)
 Total runtime: 147.921 ms
(12 rows)

However, in 9.1:

    postgres=# explain analyze select id from main order by id desc limit 10                                                          ;
                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.07..6.44 rows=10 width=4) (actual time=6.828..6.849 rows=10 loops=1)
   ->  Result  (cost=1.07..10790.41 rows=20072 width=4) (actual time=6.825..6.844 rows=10 loops=1)
         ->  Merge Append  (cost=1.07..10790.41 rows=20072 width=4) (actual time=6.824..6.840 rows=10 loops=1)
               Sort Key: public.main.id
               ->  Sort  (cost=1.01..1.01 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1)
                     Sort Key: public.main.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Seq Scan on main  (cost=0.00..1.00 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=1)
               ->  Index Scan Backward using parition_1_idx on parition_1 main  (cost=0.00..13.17 rows=61 width=4) (actual time=0.028..0.028 rows=1 loops=1)
               ->  Index Scan Backward using parition_2_idx on parition_2 main  (cost=0.00..13.15 rows=60 width=4) (actual time=0.024..0.024 rows=1 loops=1)
               ->  Index Scan Backward using parition_3_idx on parition_3 main  (cost=0.00..13.15 rows=60 width=4) (actual time=0.024..0.024 rows=1 loops=1)
               ->  Index Scan Backward using parition_other_idx on parition_other main  (cost=0.00..10233.63 rows=19890 width=4) (actual time=6.716..6.727 rows=10 loops=1)
 Total runtime: 6.932 ms
(13 rows)

Looking at above Plans, in PostgreSQL 9.0, Executor will take all from all child table will do sorting before returning 10 records. However, in PostgreSQL 9.1, Executor will take records from sorted child table and will use indexes (if available) to merge them with the sorted one to return 10 records. Looking at Total runtime, 9.1 is faster.

b. FULL OUTER JOIN improvement:
In 9.1, FULL OUTER JOIN can now use HASH Algorithms, which is faster than old method FULL OUTER JOIN (i.e. 2 Sorts) as given below:

i) Create tables and insert some values, as given below:

     CREATE TABLE test1 (a int);
     CREATE TABLE test2 (a int);
     INSERT INTO test1 SELECT generate_series(1,100000);
     INSERT INTO test2 SELECT generate_series(100,1000);

ii). EXPLAIN ANALYZE:

In Previous version of PostgreSQL:

        postgres=#  EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Merge Full Join  (cost=10952.05..11465.56 rows=100000 width=8) (actual time=214.420..370.898 rows=100000 loops=1)
   Merge Cond: (test1.a = test2.a)
   ->  Sort  (cost=10894.82..11144.82 rows=100000 width=4) (actual time=213.512..269.596 rows=100000 loops=1)
         Sort Key: test1.a
         Sort Method:  external sort  Disk: 1368kB
         ->  Seq Scan on test1  (cost=0.00..1393.00 rows=100000 width=4) (actual time=0.010..50.044 rows=100000 loops=1)
   ->  Sort  (cost=57.23..59.48 rows=901 width=4) (actual time=0.894..1.309 rows=901 loops=1)
         Sort Key: test2.a
         Sort Method:  quicksort  Memory: 38kB
         ->  Seq Scan on test2  (cost=0.00..13.01 rows=901 width=4) (actual time=0.012..0.436 rows=901 loops=1)
 Total runtime: 412.315 ms
(11 rows)

In PostgreSQL 9.1:

postgres=#  EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Full Join  (cost=24.27..1851.28 rows=100000 width=8) (actual time=0.588..74.434 rows=100000 loops=1)
   Hash Cond: (test1.a = test2.a)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..21.683 rows=100000 loops=1)
   ->  Hash  (cost=13.01..13.01 rows=901 width=4) (actual time=0.563..0.563 rows=901 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 32kB
         ->  Seq Scan on test2  (cost=0.00..13.01 rows=901 width=4) (actual time=0.014..0.217 rows=901 loops=1)
 Total runtime: 82.555 ms
(7 rows)

Looking at above EXPLAIN ANALYZE, PostgreSQL 9.1 needs to create a HASH on the smallest table. However, in Previous Version, PostgreSQL required 2 sorts (one on smallest table test2 and one on test1) which is costly, which shows that 9.1 optimizer is smarter and giving better plan.