Database Technologies

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations. First, let’s address the software patches and their descriptions: 1. First patch was committed by Fujii Masao. Patch commit#  985bd7d49726c9f178558491d31a570d47340459 With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master. This means:  a. All WAL records are synced between two servers after the clean shutdown of the master  b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master. 2. Second patch was committed by Heikki Linnakangas in PostgreSQL 9.3. Patch commit# abfd192b1b5ba5216ac4b1f31dcd553106304b19 Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes

The people using PostgreSQL and the Streaming Replication feature seem to ask many of the same questions: 1. How best to monitor Streaming Replication? 2. What is the best way to do that? 3. Are there alternatives, when monitoring on Standby, to using the pg_stat_replication view on Master? 4. How should I calculate replication lag-time, in seconds, minutes, etc.? In light of these commonly asked questions, I thought a blog would help. The following are some methods I’ve found to be useful. Monitoring is critical for large infrastructure deployments where you have Streaming Replication for: 1. Disaster recovery 2. Streaming Replication is for High Availability 3. Load balancing, when using Streaming Replication with Hot Standby PostgreSQL has some building blocks for replication monitoring, and the following are some important functions and views which can be use for monitoring the replication: 1. pg_stat_replication view on master/primary server.    This view helps in

PostgreSQL 9.3  has lot of new improvement like the addition of new operators for JSON data type in postgreSQL, that prompted me to explore its features for NoSQL capabilities. MongoDB is one of NoSQL solutions that have gotten a great deal of attention in the NoSQL market. So, this time I thought to do some benchmarking with the NoSQL capability of JSON in MongoDB and the JSON datatype in PostgreSQL 9.3 For this benchmark, I have used the same machine with no optimization in installation of PostgreSQL and MongoDB (since I wanted to see how things work, out of box with default installation). And I used the sample data from MongoDB's site, around which I had developed the functions which can generate random data using the same sample for Mongo and for PostgreSQL. In this benchmarking, I have verified following: 1. PostgreSQL COPY Vs Mongo-Import 2. Data Disk Size of PostgreSQL and Mongo for same amount of data. 3. PostgreSQL INSERT Vs Mongo Insert Some specification befor