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

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                     Streaming-Only Remastering                     Fast Failover                     Architecture-Independent Streaming                     pg_basebackup conf setup For Postgre

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

In series of blogging about new features coming in PostgreSQL 9.3, I thought to blog about the server side language improvements in PostgreSQL. Lets see whats coming in server side language. As PostgreSQL user, you know, PostgreSQL supports multiple server side language. In 9.3, there are some interesting features are coming. Lets look at the new improvements about to come. 1. SPI access to number of rows processed by COPY command. This is more like new feature which is introduced in 9.3. Before 9.3, this feature was missing. i.e if user uses COPY command inside the plperl/plpython functions, then there was no way inside the procedure to trace the number of rows processed by COPY. However in 9.3, this limitation is no more exists with procedural language. There are many languages supported in PostgreSQL, However I chose to test this with mostly used language plperl and plpython. Below are some snapshot pre-9.3 and in 9.3. Lets check with plperl. Following is a plperl function w

Long long time. I haven't blogged at all. So, I have decided whenever I get chance I will blog about the cool things going in Database Technology market. I have lot of topics to share. Some got lost in air. Some I retained in mind. Before I lost more. I decided to continue from PostgreSQL 9.3 features and accordingly will go towards about new things as it starts to popup. PostgreSQL users must have heard about release of PostgreSQL 9.3 and they must have started testing PostgreSQL 9.3. In today series, I am going to blog about client application improvements done in PostgreSQL 9.3. Lets take it one by one. 1. New binary/command pg_isready.  PostgreSQL 9.3 is coming with new binary called pg_isready. this command helps user to know the status of server/PostgreSQL cluster. Before 9.3, user used to have their own tool/script to check the status of PostgreSQL, for that they used execute some random SQL like "SELECT 1" and if the status is successful, PostgreSQL is