If database were a world full of organisms then PostgreSQL’s streaming replication feature would certainly qualify for evolving humans. The Darwin’s principles of “survival of fittest” and “adapt to survive” has been well understood by the PostgreSQL community. Planned switchovers are quite a common scenario in data-centres. Keeping that in mind PostgreSQL v9.3 had major enhancements in streaming replication which made the switchover smoother and much easy to manage. We shared some details and a demo in our last Postgres Google Hangout
Some great features have been added to PostgreSQL v9.4 which will take this evolution to the next level. One of them is replication slot. Replication slot will help administrators to do away with the need to estimate wal_keep_segments. I have myself struggled a few times to estimate the appropriate size for this particular parameter. Quite a few times, I have seen this to be an issue for customers who opt for our PostgreSQL professional services. These Postgres users generally setup replication with bare minimum configuration and then later their replication goes out of sync. The probability of this happening is more in cases where there is a sudden peak in transaction volume during a specific hour of day or on a particular day. V9.4 onwards, with replication slots one need not pre-calculate this value for wal_keep_segments, primary database will try to maintain WALs which are yet to be replicated.
Moreover, this features will also help while handling the planned downtime and failure of standby database. In such cases if the wal files generated during downtime were more than wal_keep_segments, then we had to rely on wal archives. In case archiving was not setup, one had to rebuild the standby database. But when working with v9.4 a DBA can create a replication slot and associate it with a standby server. This will ensure that wals which are not yet replicated to the standby will be retained on primary server. Hence the wals which needs to be retained are decided in run time depending on load and volume of transactions. So if a primary is being shut down during maintenance or has failed, the primary server will retain the wals which are yet to be replicated.
Another great feature which has come up in v9.4 is time lagging replicas. Now one can have a replica which is lagging behind by certain time. This enhancement takes Point-in-time-recovery to an altogether different height. It makes your life easier in cases when application induces a bug and you would want to go back in time with respect to database transactions. So far what we all did was, restoring from a base backup and then rolling forward the database by applying archive wals till a point-in-time which is just before the bug injecting transaction. But with a time lagging replica of let’s say a 2-hour difference, all we need to do is roll forward the replica for a few minutes or little less than 2 hours in worst case. Another great utility of this feature could be backdated reporting. I can setup a lag of one day for my replica servers and then pause recovery at each end of day. I can ensure than wals generated during this pause period are not lost/discarded. I can use this replica for pulling out reports for let’s say profit bearing accounts as of yesterday. In summary I can open my main database for new “start of day” and still do post “end of day” reporting for previous day from a replica DB.
These enhancements in PostgreSQL replication is like developing and evolving new organs as the environments and situation demands. Due to its this nature of evolving and adapting constantly, the PostgreSQL community has managed to survive and build a strong alternative to proprietary database options backed/sold by strong commercial entities.
If you are one of those who are struggling to adapt to the changing ecosystem where IT budget is expected to be spent in more cost-effective ways to meet rising business demands, then reach out to us. At Ashnik, we identify such great Open Source projects which are backed by strong commercial support model and bring them to our customers.