Database Technologies

EnterpriseDB recently launched a new tool for backup and recovery – named simply EDB Backup and Recovery Tool, or  BART . This tool makes the DBA’s life easier by simplifying the tasks for managing their Postgres physical backup and recovery tasks, whether they are PostgreSQL or Postgres Plus Advanced Server deployments. BART has the following advantages over custom scripts for managing backups: 1.  It’s stable and it uses the tool pg_basebackup to take a physical backup. This tool has been well defined and is well-supported by the PostgreSQL community. 2.  It catalogs all of the backups users are taking, which is important in terms of:     i.  Listing the type of backups used    ii.  Listing the status of those backups with server information. 3.  BART also provides functionality to restore backups, with all required archived WAL files. So automation around this tool will make DBAs’ lives easier for restore and recovery. 4....

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

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

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

Postgres Plus Advanced Server 9.3 , released on Tuesday for general availability, contains all of the innovation that was part of PostgreSQL 9.3, which was released in September. These features include the following: 1. Configuration directive 'include_dir' 2. COPY FREEZE for more efficient bulk loading 3. Custom Background Workers 4. Data Checksums 5. JSON: Additional functionality 6. LATERAL JOIN 7. Parallel pg_dump for faster backups 8. 'pg_isready' server monitoring tool 9. Switch to Posix shared memory and mmap() 10. Event Triggers 11. VIEW Features:                    Materialized Views                    Recursive View Syntax                    Updatable Views 12. Writeable Foreign Tables                    postgres_fdw 13. Replication Improvements     ...

In series of writing further on upcoming features in 9.3, I thought about including new improvements coming in data types in PostgreSQL. 1. Increase the maximum length of large objects from 2GB to 4TB PostgreSQL has support of Large Objects from starting. However the limit of large objects in PostgreSQL was limited to 2GB. From 9.3 onwards, PostgreSQL can store large objects up to 4TB. Thats happened due to lifting the limitation of API for large object. APIs like lo_seek(),lo_tell cannot return over 2GB offset and main reason was offset parameters defined for these function is of 4 bytes and results length provided by these functions is 4 bytes. If user do the calculation, he can see 2^31-1=2GB, it resulted in 2GB. To overcome from this limitation, PostgreSQL is coming with new APIs: lo_seek64 and lo_tell64 functions. Libpq interface will check if those lo_tell64/seek64 exits then use it or use the 32 bit of lo_seek/lo_tell functions, this way compatibility has been maintain...

In the series of blogging about new features in 9.3, today, I thought about blogging new functions and improvements coming in PostgreSQL. Lets look whats new in 9.3, in terms of in build functions: 1. New in one array functions for one dimensional array PostgreSQL 9.3, is coming with new functions which can help users to manipulate one dimensional arrays by calling simple functions at the place of crafting their own functions and following some methods to do the modification in it. i. array_remove function This is a new function added in 9.3, which provides ability for removing the elements from array. Function takes two arguments :   a. One dimensional array from which user wants to remove elements   b. element value which user wants to remove . Syntax of this function is given below: ARRAY_REMOVE( , element) Example of array_remove is given below: postgres=# select array_remove(ARRAY['First','Second','Delete','Four'],'D...