Use Code KBALL501M

50% OFF First Month!

on selected monthly services

Release Notes - PostgreSQL 9.2
Posted by on 21 November 2013 02:26 PM

Release Notes - PostgreSQL 9.2

Warning PostgreSQL 9.2 is a major release of the PostgreSQL database server. This release has introduced new functionality and significant changes to the existing PostgreSQL server. Before upgrading, make sure to fully test your existing database(s) to ensure that your applications will still function correctly after the upgrade is complete.

This version of PostgreSQL is only available for Virtual Machines running CentOS 6 (ISPmanager Control Panel). If you are on CentOS 5 (Webmin) and need to move to PostgreSQL 9.2 you will need to migrate to a CentOS 6 VM.

eApps recommends provisioning a test VM, and loading your applications and databases on that VM, and then updating the database on the test VM so that you can see and resolve any issues that might occur when updating your production VM. After the upgrade on the production VM is complete, you can then cancel the test VM.

Before upgrading to PostgreSQL 9.2, please carefully read and review the official documentation related to this release. Failure to read, review, and test before upgrading may result in data loss and downtime for your customers.

The official PostgreSQL 9.2 documentation is located here - Please read this to understand all the changes in the new release of PostgreSQL. There is an extensive list of new features and changes that may significantly impact the functionality of your current database and application configuration.

Highlighted Features and Fixes in PostgreSQL 9.2.4


  • Allow pg_basebackup to make base backups from standby servers
  • Add a JSON data type
  • Allow libpq connection strings to have the format of a URI


  • Fixed race condition in DELETE RETURNING
  • Fixed potential null-pointer dereference in regular expression compilation
  • Fixed performance issue in EXPLAIN (ANALYZE, TIMING OFF)

The official Release Notes for PostgreSQL 9.2.4 are available here -

How to upgrade

Warning Make a backup of your PostgreSQL database before upgrading! For ISPmanager, see here -

ISPmanager Control Panel

To update PostgreSQL in ISPmanager, you will need to connect to your Virtual Machine via SSH, using either the MindTerm SSH client, or a regular SSH client. More information can be found in the User Guide: SSH and MindTerm -

Updating PostgreSQL from version 9.0.X to version 9.2.X

The update from PostgreSQL 9.0.X to version 9.2.X is a major version update. Make sure that you have read all the release notes linked to above, and have performed any necessary testing prior to the update. The new version of PostgreSQL has incompatibilities with previous versions that may cause data loss or service interruptions if you are not prepared.

Once you are connected to the Virtual Machine, you will need to run the following commands as the root user: yum clean all and yum -y update postgresql-server

The first command clears the yum cache, and the second command downloads and installs the update to the PostgreSQL database server.

[root@example ~]# yum clean all
Loaded plugins: fastestmirror, priorities, remove-with-leaves
Cleaning up Everything
Cleaning up list of fastest mirrors
[root@example ~]#

[root@example ~]# yum -y update postgresql-server

You will know that the upgrade is complete when you see a Complete! message and you are returned to the command prompt. At this point PostgreSQL has been updated.

Once the upgrade is complete, the PostgreSQL service will be stopped. If you try to restart it, you will get an error:

[root@example ~]# service postgresql status
postmaster is stopped
[root@example ~]# service postgresql start

An old version of the database format was found.
Use "service postgresql upgrade" to upgrade to version 9.2.
See /usr/share/doc/postgresql-9.2.4/README.rpm-dist for more information.
[root@example ~]#

You will need to make a copy of the pg_hba.conf file created by the upgrade, put the original pg_hba.conf file in place, run a command to upgrade the existing database structure, and then copy the pg_hba.conf file that was created by the upgrade back into place.

First, make a copy of the pg_hba.conf file created by the update, and then copy the original pg_hba.conf file back into place. Do not skip this step - you will need to move the pg_hba.conf file created by the upgrade back into place at the end of this process.

[root@eapps-example ~]# cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf_ispmanager
[root@eapps-example ~]# cp /var/lib/pgsql/data/pg_hba.conf.orig /var/lib/pgsql/data/pg_hba.conf
cp: overwrite `/var/lib/pgsql/data/pg_hba.conf'? y
[root@eapps-example ~]#

Once you have copied the files, run the the service postgresql upgrade command.

[root@eapps-example]# service postgresql upgrade
Stopping postgresql service:                               [  OK  ]
Upgrading database:                                        [  OK  ]
See /var/lib/pgsql/pgupgrade.log for details.

Once the upgrade is complete, you will need to move the version of pg_hba.conf that was created by the update (that you copied to pg_hba.conf_ispmanager) back into place. Note that the pg_hba.conf_ispmanager file is now in a data-old directory.

[root@eapps-example ~]# cp /var/lib/pgsql/data-old/pg_hba.conf_ispmanager /var/lib/pgsql/data/pg_hba.conf
cp: overwrite `/var/lib/pgsql/data/pg_hba.conf'? y
[root@eapps-example ~]

Once you have done this, restart PostgreSQL.

[root@eapps-example ~]# service postgresql start
Starting postgresql service:                               [  OK  ]
[root@eapps-example ~]#

The update should be complete.

(0 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).