Release Notes - PostgreSQL 9.2

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 - http://www.postgresql.org/docs/9.2/static/release-9-2.html. 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

Features

• 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

Fixes

• 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 - http://www.postgresql.org/docs/9.2/static/release-9-2-4.html

How to upgrade

Make a backup of your PostgreSQL database before upgrading! For ISPmanager, see here - http://support.eapps.com/ispmgr/postgres9#backing_up_postgresql_databases

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 - http://support.eapps.com/ispmgr/ssh

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.

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:

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.

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

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.

Once you have done this, restart PostgreSQL.

The update should be complete.