PostgreSQL 9.5 – What's new; what's better?

An Eye for Details

pg_rewind for Admins

Database administrators will find something new in the replication area. For example, assume that a master server has failed and a failover to the slave has taken place. Later, the master server is restored. Previously, it was typically impossible to join the previous master as a slave to the new master because the write-ahead log timelines were divergent. This only worked if you shut down the master gracefully before shutting down the slave, which is not an option in a crash.

The only possibility was to delete the data on the old master and build a slave completely from scratch, which could be very time consuming. During the process, the database did not have a slave, except in cases for which a redundant slave existed. A new program, pg_rewind, now makes it possible to convert the former master into a functional slave:

pg_rewind -D /usr/local/pgsql/data --source-server='host=db2 dbname  =postgres'

The program runs the previous master and edits the specified data directory so that it can be appended to the specified new master, which can be running on a different computer. Subsequently, the previous master can then be restarted using a recovery.conf file.


Finally, an innovation for everyone who enjoys working at the command line. If you are familiar with Git's ability to detect typos and make suggestions for correcting them, PostgreSQL also now has that faculty:

test=> select citx, country from HR; \
  ERROR: column "citx" does not exist \
  LINE 1: select citx, country from HR; \
  HINT: Perhaps you meant to reference the column "HR"."city".

However, this currently only works for column names.


PostgreSQL is a community project without centralized commercial management. The features in each version are not defined by a release plan or roadmap. They come from developers working for different companies and with different plans. Nevertheless, you can still identify trends. One focal topic in PostgreSQL 9.5 was support for large databases with analytics applications, and you can expect more of the same in the next few versions. Additionally, the UPSERT feature fills a function gap in typical web applications.

The upgrade is well worthwhile – even if you do not need the new functions.


  1. Peter Geoghegan, "Why UPSERT is weird":

The Author

Peter Eisentraut is a Debian developer who has been a member of the PostgreSQL Core team for more than 10 years. He works as a database administrator for a social network in the US that operates one of the largest PostgreSQL back ends in the world. His book on PostgreSQL database administration was recently published by O'Reilly.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus
Subscribe to our ADMIN Newsletters
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs

Support Our Work

ADMIN content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.

Learn More”>


		<div class=