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.

Typos

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.

Conclusions

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.