Santosh Kangane

Recently I have evaluated different tools to Cluster PostgreSQL Database. Key Criteria of Evaluation Open source Impact on Application Performance gain, connection pooling support, High Availability, Support to advance feature such as Sharding.   Documentation and Community support  Available Platform and Features Matrix Program License Platform supported Maturity Replication Method Sync scalability Connection Pooling Load Balancing Query Partitioning Architecture Support Postgres-xc BSD All Linux Stable Master-Master Synchronous read and write No Yes Yes shared-nothing NTT Open Source Software Center (Japanese) EnterpriseDB Pgpool-II BSD CentOS Stable Statement-Based Middleware Synchronous read Yes Yes Yes Proxy between application and database SRA OSS, Inc. Japan   PgCluster BSD   Died Master-Master Synchronous   No Yes No Its not more active project   slony BSD   Stable Master-Slave Asynchronous   No No No Its replication based tool Slony Bucardo BSD   Stable M

PostgreSQL can be upgrade can be done in following two methods. A. using pg_upgrade  B. using pg_dump and pg_restore   Detail steps are as explained bellow A)   pg_upgrade 9.3 from 9.1 1. Take full system downtime and stop streaming replication 2. Take full dump of database from Old 9.1 version. 3. Install New version PostgreSQL9.3 under new location say  /usr/lib/postgresql/9.3/ 4. Stop both(V9.1 and 9.3) the databases. $/usr/lib/postgresql/ 9.1 /bin/pg_ctl -D /usr/lib/postgresql/ 9.1 /main stop $/usr/lib/postgresql/ 9.3 /bin/pg_ctl -D /usr/lib/postgresql/ 9.3 /main stop 5. Run pg_upgrade Please make sure that the port no.  50432 is free and no other process is using it.  50432 is used for pg_upgread. Command format pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...] Consider old as 9.1 database and new as 9.3 database directory  /usr/lib/postgresql/ 9.3 /bin/pg_upgrade -b /usr/lib/postgresql/ 9.1 /b

In last post I explained  PostgreSQL 9.1 Vs 9.2 .    In this post I will explain 9.3. PostgreSQL 9.1 Vs 9.3 I)      Stability:  PostgreSQL 9.3 released on 9 th  Sept 2013. II)     Performance Improvement: Collect and use histograms of upper and lower bounds, as well as range lengths, for range types  Improve optimizer's cost estimation for index access Improve optimizer's hash table size estimate for doing DISTINCT via hash aggregation Suppress no-op Result and Limit plan nodes  Reduce optimizer overhead by not keeping plans on the basis of cheap start-up cost when the optimizer only cares about total cost overall III)     Other Key New Features: Streaming-Only Remastering: "Remastering" is the process whereby a replica in a set of replicas becomes the new master for all of the other replicas. For example: Master M1 is replicating to replicas R1, R2 and R3. Master M1 needs to be taken down for a hardware upgrade. The DBA promotes R1 to be the ma