Release Notes - PostgreSQL 9.4

Overview - PostgreSQL 9.4 (CentOS 6 only)

This Release Note covers all point releases for the PostgreSQL 9.4 branch that are offered by eApps. Upgrade instructions are included.

NOTE - eApps maintains PostgreSQL 9.4 for CentOS 6 OS Templates. If you are using a CentOS 7 OS Template the latest version of PostgreSQL that is currently available from the CentOS community will be installed, or you can install a specific version if needed. If you are using a CentOS 6 OS Template and wish to move to a CentOS 7 OS Template, please contact eApps Sales.

PostgreSQL 9.4 is a major release of the PostgreSQL database server. This release has introduced new functionality and significant changes to the existing PostgreSQL database server. If you are upgrading from any prior version of PostgreSQL to PostgreSQL 9.4, there is a HIGH RISK of data loss and downtime if you do not carefully test your existing database and application with this new version.

eApps recommends, as a best practice, to maintain a staging server where you can test software upgrades before moving them into production. This approach will allow you to minimize the risk of data loss and downtime of your production service when performing software upgrades. If necessary, you can rebuild your staging Virtual Server on demand so that you have a fresh installation to test on. If you need more information about setting up a staging Virtual Server, please contact eApps Sales.

IMPORTANT: PostgreSQL 9.4 has an extensive list of new features and changes. See the Official PostgreSQL 9.4 Release Notes for a list of changes that may cause issues when upgrading: http://www.postgresql.org/docs/9.4/static/release-9-4.html. Please read this document carefully to understand all the changes in the PostgreSQL 9.4 release. Upgrading to this version may significantly impact the operation of your database and applications. All PostgreSQL 9.4 documentation is located here: http://www.postgresql.org/docs/9.4/static/index.html

phpPgAdmin users please note - phpPgAdmin does not officially support PostgreSQL 9.4. If you use phpPgAdmin to manage your PostgreSQL 9.4 database, please contact eApps Support for assistance. A workaround is available to allow phpPgAdmin to work with this new version of PostgreSQL.

eApps Release Notes for PostgreSQL 9.4.6
eApps Release Notes for PostgreSQL 9.4.5
eApps Release Notes for PostgreSQL 9.4.4
eApps Release Notes for PostgreSQL 9.4.2
eApps Release Notes for PostgreSQL 9.4.1
eApps Release Notes for PostgreSQL 9.4.0

How to Upgrade
PostgreSQL 9.2 or 9.3 to PostgreSQL 9.4
PostgreSQL 9.4.x to PostgreSQL 9.4.y (newer version)

Highlighted Features and Fixes in PostgreSQL 9.4.6

NOTE - PostgreSQL 9.4.6 has a security fix for CVE-2015-7499. From the release notes:

Add variant regression test expected-output file to match behavior of current libxml2 (Tom Lane)

The fix for libxml2's CVE-2015-7499 causes it not to output error context reports in some cases where it used to do so. This seems to be a bug, but we'll probably have to live with it for some time, so work around it.

Features

PostgreSQL 9.4.6 is a bug fix and security release, no new features have been added.

Fixes

• tzdata time zone files updated to include DST law changes in certain countries
• Fix for buffer overruns and infinite loops problems in regular expressions

The official Release Notes for PostgreSQL 9.4.6 are here - http://www.postgresql.org/docs/9.4/static/release-9-4-6.html

Highlighted Features and Fixes in PostgreSQL 9.4.5

Features

PostgreSQL is a bug fix and security release, no new features have been added.

Fixes

• Fix to guard against stack overflows in json parsing
• Fixed handling of DOW and DOY in datetime input
• Potential infinite loop in regex execution fixed

The official Release Notes for PostgreSQL 9.4.5 are here - http://www.postgresql.org/docs/9.4/static/release-9-4-5.html

Highlighted Features and Fixes in PostgreSQL 9.4.4

Features

PostgreSQL 9.4.4 is a bug fix and security release, no new features have been added.

Fixes

• Fix to avoid deadlock between incoming sessions and CREATE/DROP DATABASE
• Fix to improve planner's cost estimates for semi-joins and anti-joins with inner indexscans

The official Release Notes for PostgreSQL 9.4.4 are here - http://www.postgresql.org/docs/9.4/static/release-9-4-4.html

Highlighted Features and Fixes in PostgreSQL 9.4.2

Features

PostgreSQL 9.4.2 is a bug fix and security release, no new features have been added.

Fixes

• Fix for possible crash when client disconnects before authentication timeout expires
• Fixed json/jsonb populate_record() and to_record() functions to handle empty input properly
• Time zone data files updated to tzdata release 2015d for DST law changes in several countries

The official Release Notes for PostgreSQL 9.4.2 are here - http://www.postgresql.org/docs/9.4/static/release-9-4-2.html

Highlighted Features and Fixes in PostgreSQL 9.4.1

Features

PostgreSQL 9.4.1 is a bug fix and security release, no new features have been added.

Fixes

• Fixed Unicode processing in jsonb
• Fixed replacement *printf() buffer overrun
• Improved performance of EXPLAIN with large range tables

The official Release Notes for PostgreSQL 9.4.1 are here - http://www.postgresql.org/docs/9.4/static/release-9-4-1.html

Highlighted Features and Fixes in PostgreSQL 9.4.0

Features

Some major feature enhancements for PostgreSQL 9.4:

• Added jsonb, a more efficient data type for storing JSON data
• Added new SQL command ALTER SYSTEM for changing postgresql.conf configuration file entries
• Reduced lock strength for some ALTER TABLE commands

Fixes

• Improvements to the default ssl_ciphers setting
• The log_connections now shows the SSL encryption information
• Increased the default settings of work_mem and maintenance_work_mem by four times (to 4 MB and 64 MB respectively)

The Official Release Notes for PostgreSQL 9.4.0 are available here - http://www.postgresql.org/docs/9.4/static/release-9-4.html

How to upgrade

It is your responsibility to ensure that your database and application will work on the newer version of PostgreSQL. There may be incompatibilities that will affect how your databases and application works. Please make sure that you have read the official Release Notes and have done any necessary testing before upgrading a production database and application.

Before upgrading PostgreSQL, make a backup of your databases. The instructions for backing up PostgreSQL are here: http://support.eapps.com/ispmgr/postgresql9#backing-up-postgresql-databases

You should also make a backup of your applications, your configurations, and any tunings or changes you have made. If you do not have current backups and something goes wrong during the upgrade process, you could have downtime and data loss.

To upgrade PostgreSQL, you will need to work from the command line using SSH, and you will need to be able to work as the root user. You will need to use yum to upgrade the application.

Upgrading PostgreSQL from version 9.2 or 9.3 to version 9.4

The upgrade from PostgreSQL 9.2 or 9.3 to 9.4 is a major version update. There is no direct upgrade path between major PostgreSQL versions. Please follow the steps below to update PostgreSQL. If you try to upgrade PostgreSQL without following the correct steps you run a high risk of data loss and corruption.

Before starting the upgrade, make sure that no transactions are being written to the PostgreSQL database. You may need to restrict access to the database to just yourself before starting the upgrade, or do the upgrade during a time when no one will be using the database. Any transactions written to the database after running the pg_dumpall command (described below) will be lost.

The steps to upgrade PostgreSQL are as follows:

1. As the postgres user, run the pg_dumpall command to dump all your databases
2. As the root user, copy the existing pg_hba.conf and postgresql.conf files
3. As the root user, stop the postgresql service
4. As the root user, move the existing pgsql file to pgsql.old
5. As the root user, update the postgresql client and postgresql-server applications
6. As the root user, copy the original pg_hba.conf and postgresql.conf files back into place (if necessary)
7. As the postgres user, import the file created by pg_dumpall back into the new version of PostgreSQL

1. As the postgres user, run the pg_dumpall command to dump all your databases

The pg_dumpall command will dump all PostgreSQL databases. This command must be run as the postgres user. The command is pg_dumpall > outputfile.sql. Make sure that the name of the output file is not already in use.

[root@eapps-example ]# su - postgres
-bash-4.1$ pg_dumpall > outputfile.sql
-bash-4.1$

2. As the root user, copy the existing pg_hba.conf and postgresql.conf files

The pg.conf and postgresql.conf files often have customizations that are not preserved across upgrades, and the upgrade will replace your original copies of these files. Make a copy of each of these files so that you can put them back if needed.

Use the cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bak and cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bak commands to copy the files.

[root@eapps-example ~]# cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bak
[root@eapps-example ~]# cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bak

3. As the root user, stop the postgresql service

• For CentOS 6, use the service postgresql stop command:

  [root@eapps-example ~]# service postgresql stop

4. As the root user, move the existing pgsql file to pgsql.old

Before doing the upgrade, move the existing PostgreSQL database file to a new location. This will allow you to restore the old version if something goes wrong with the upgrade. Use the mv /var/lib/pgsql /var/lib/pgsql.old command.

[root@eapps-example ~]# mv /var/lib/pgsql /var/lib/pgsql.old
[root@eapps-example ~]#

5. As the root user, update the postgresql client and postgresql-server applications

Before updating PostgreSQL, update the yum cache with the yum clean all command. Then update both the PostgreSQL client and server using the yum -y update postgres postgresql-server command.

[root@eapps-example ~]# yum clean all
[root@eapps-example ~]# yum -y update postgres postgresql-server

6. As the root user, copy the original pg_hba.conf and postgresql.conf files back into place (if necessary)

If you have customizations in your pg_hba.conf and postgresql.conf files that need to be put back in place, copy those files back to their original locations. You may want to review the file created by the upgrade and your existing files to see if there are new changes that are relevant to your configuration. Because you moved the original pgsql directory to pgsql.old, these files will now be in the .old directory.

Use the cp /var/lib/pgsql.old/data/pg_hba.conf.bak /var/lib/pgsql/data/pg_hba.conf and cp /var/lib/pgsql.old/data/postgresql.conf.bak /var/lib/pgsql/data/postgresql.conf commands to copy the files.

[root@eapps-example ~]# cp /var/lib/pgsql.old/data/pg_hba.conf.bak /var/lib/pgsql/data/pg_hba.conf
[root@eapps-example ~]# cp /var/lib/pgsql.old/data/postgresql.conf.bak /var/lib/pgsql/data/postgresql.conf

7. As the postgres user, import the file created by pg_dumpall back into the new version of PostgreSQL

Once the upgrade completes, import the databases back into the new version of PostgreSQL. This must be done as the postgres user, using the /usr/bin/psql -d postgres -f outputfile.sql command.

[root@eapps-example ~]# su - postgres
-bash-4.1$ /usr/lib/psql -d postgres -f all_db.sql
-bash-4.1$

PostgreSQL 9.4.x to PostgreSQL 9.4.y (newer version)

To update PostgreSQL you will need to connect to your Virtual Machine via SSH. More information can be found in the SSH User Guide

As always, make sure to backup your databases before upgrading.

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

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

You will know that the new version of PostgreSQL is installed when you see a Complete! message and you are returned to the command prompt. Once the installation has completed, restart PostgreSQL.

• For CentOS 6, use the service postgresql restart command:

  [root@eapps-example ~]# service postgresql restart