Monday, March 7, 2011

Postgresql query to find out how long any open transaction has been running for

0 comments
I use the following query to find out how long any open transaction has been running for in Postgresql 9.0.1:

SELECT
client_addr,
application_name
current_timestamp - xact_start AS running_time,
current_query
FROM pg_stat_activity
ORDER BY running_time;
Continue reading →
Thursday, March 3, 2011

Postgresql parameters for a new dedicated server

0 comments


I got the new server PowerEdge M605 with 32 GB RAM  and CentOS 5 as a dedicated server for Postgresql 9.
After reading a lot, I got to a point in setting up postgresql.conf parameters that I described below:

1.     Set a good value for shared_buffers. I prefer to start from 25% of system memory and then go upwards or downwards based on the performance. I set it to 6 GB.

2.     Set max_connection according to the requirement. I set it as default that is 100. 

3.     Start the server and find out how much memory is still available for the OS filesystem cache.

4.     Set effective_cache_size based on the result of step 3. I set it to 10 GB.

5.     Set work_mem according to the formula: (free + cached memory from the result of free)/(max_connection*2). I set it to 300 MB.

6.     Set a good value for maintenance_work_mem for faster data import. I set it to 1 GB.

7.     Increase checkpoint_segment. I set it to 100.

8.     Increase wal_buffers to 16MB. 

9.     Increase  default_statistics_target. I set it to 200.

10. Set log_min_duration_statement to find out the slow query logs. I set it to 4000, that is 4s.

11. I set log_connection and log_disconnections to true.    

12. Set listen_address to '*'.

13. Set random_page_cost and seq_page_cost to 1.0

The server has been running smoothly for the last 3 months. 
There were some other important parameters such as wal_sync_method and max_fsm_pages. I did not change those values. Besides, before setting these postgresql.conf values, I set shmall and shmmax according to my earlier post http://airumman.blogspot.com/2011/02/set-unix-shared-memory-parameters-shm.html.




Continue reading →
Wednesday, March 2, 2011

Postgresql Database Schema Comparison

0 comments

I really like the "Another Postgresql Diff Tool (apgdiff)" in order to find out the differences between two databases or schemas in Postgresql. It is a java based application.
I downloaded it from http://apgdiff.com/download.php

Databases' Structure

I have two databases testdb and testdb1 as follows.

postgres=# \c testdb
You are now connected to database "testdb".
testdb=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | emp            | table    | postgres
 public | pg_buffercache | view     | postgres
 public | t              | table    | postgres
 public | t_s_seq        | sequence | postgres
(4 rows)
testdb=# \d emp
              Table "public.emp"
 Column  |          Type          | Modifiers
---------+------------------------+-----------
 empno   | integer                | not null
 empname | character varying(100) |
 city    | character varying(100) |
Indexes:
    "empno_pk" PRIMARY KEY, btree (empno)

\c testdb1
testdb1=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)
testdb1=# \d emp
      Table "public.emp"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 name   | name    | 

I used apgdiff to fund out the difference between the two databases.
At first, I had to take sql dumps using pg_dump.

pg_dump testdb > /tmp/testdb.sql
pg_dump testdb1 > /tmp/testdb1.sql

Make testdb as testdb1

java -jar apgdiff-2.3.jar  /tmp/testdb.sql /tmp/testdb1.sql > /tmp/make_testdb_as_testdb1.sql
less /tmp/make_testdb_as_testdb1.sql
-----------------------------------
DROP FUNCTION pg_buffercache_pages();
DROP FUNCTION reffunc(refcursor);
DROP VIEW pg_buffercache;
DROP TABLE t;
DROP SEQUENCE t_s_seq;
ALTER TABLE emp
        DROP COLUMN empno,
        DROP COLUMN empname,
        DROP COLUMN city,
        ADD COLUMN id integer,
        ADD COLUMN name name;
-----------------------------------


Make testdb1 as testdb

java -jar apgdiff-2.3.jar  /tmp/testdb1.sql /tmp/testdb.sql > /tmp/make_testdb1_as_testdb.sql
less /tmp/make_testdb1_as_testdb.sql
---------------------------------------
CREATE SEQUENCE t_s_seq
        START WITH 1
        INCREMENT BY 1
        NO MAXVALUE
        NO MINVALUE
        CACHE 1;
CREATE TABLE t (
        s integer DEFAULT nextval('t_s_seq'::regclass) NOT NULL,
        i integer
);
ALTER TABLE emp
        DROP COLUMN id,
        DROP COLUMN name,
        ADD COLUMN empno integer NOT NULL,
        ADD COLUMN empname character varying(100),
        ADD COLUMN city character varying(100);
CREATE OR REPLACE FUNCTION pg_buffercache_pages() RETURNS SETOF record
    LANGUAGE c
    AS '$libdir/pg_buffercache', 'pg_buffercache_pages';

CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $_$
BEGIN
    OPEN $1 FOR
  
    SELECT  *
    FROM emp
;
    RETURN $1;
END;
$_$;
---------------------------------------

Continue reading →
Tuesday, March 1, 2011

Sendmail during Pgpool II failover and failback operation

0 comments

I already discussed the steps that I followed for Pgpool II replication and failover system at http://airumman.blogspot.com/2011/02/postgresql-failover-with-pgpool-ii.html.

In addition to this, we can easily send mail whenever failover ot failback happen using pgpool.
We only need to change two parameters at pgpool.conf which are described below.

Start sendmail service

service sendmail start

Modify pgpool.conf :

....

failover_command = 'echo  -e " Pgpool II failover executed successfully at $(hostname):$(hostname -i) for backend node %d down on the $(date).\n\n Details of Backend Node %d :-\n Host = %h \n Port = %p \n Cluster Path =  %D \n\nFor Pgpool II at $(hostname):$(hostname -i) current master node is Backend_node%m (Old master node was Backend_node%M)">/opt/pgpoolII/log/failover.log;mail -s "Pgpool II failover executed at $(hostname):$(hostname -i)" edf@rumman.com abc@rumman.com < /opt/pgpoolII/log/failover.log'
....
failback_command = 'echo  -e " Pgpool II failback done successfully at $(hostname):$(hostname -i) for backend node %d down on the $(date).\n\n Details of Backend Node %d :-\n Host = %h \n Port = %p \n Cluster Path =  %D \n\nFor Pgpool II at $(hostname):$(hostname -i) current master node is Backend_node%m (Old master node was Backend_node%M)">/opt/pgpoolII/log/failover.log;mail -s "Pgpool II failback done at $(hostname):$(hostname -i)" edf@rumman.com < /opt/pgpoolII/log/failback.log'

....


I get the following mails when any node gets down.

Pgpool II failover executed successfully on the Mon Feb  1 19:30:31 BDT 2010 at dev02.dhaka.rumman.com:192.168.200.32 for backend node 0 down.
 Details of Backend Node 0 :-
 Host = 192.168.200.217
 Port = 5432
 Cluster Path =  /var/lib/pgsql/data

After successfull online recovery, I get the following mail.

For Pgpool II at dev02.dhaka.rumman.com:192.168.200.32 current master node is Backend_node 1
Pgpool II failback done successfully on the Wed Feb  3 10:51:16 BDT 2010 at dev02.dhaka.rumman.com:192.168.200.32 for backend node 0 down.
 Details of Backend Node 0 :-
 Host = 192.168.200.217
 Port = 5432
 Cluster Path =  /var/lib/pgsql/data
For Pgpool II at dev02.dhaka.rumman.com:192.168.200.32 current master node is Backend_node0 (Old master node was Backend_node1)
Continue reading →

Labels