Overview - PostgreSQL 9.3

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

PostgreSQL 9.3 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.3, 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 Machine on demand so that you have a fresh installation to test on. If you need more information about setting up a staging VM, please contact eApps Sales.

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

PostgreSQL 9.3 and above are only available for Virtual Machines running CentOS 6 (ISPmanager). If you are on a CentOS 5 (Webmin) or CentOS 4 (VPS) plan and need PostgreSQL 9.3.4, you will need to migrate to a CentOS 6 (ISPmanager) plan. Contact eApps Sales for more information.

phpPgAdmin users please note - phpPgAdmin does not officially support PostgreSQL 9.3. If you use phpPgAdmin to manage your PostgreSQL 9.3 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.3.9
eApps Release Notes for PostgreSQL 9.3.7
eApps Release Notes for PostgreSQL 9.3.6
eApps Release Notes for PostgreSQL 9.3.5
eApps Release Notes for PostgreSQL 9.3.4

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

Highlighted Features and Fixes in PostgreSQL 9.3.9

Features

PostgreSQL 9.3.9 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 for a possible failure to recover from an inconsistent database state

The official Release Notes for PostgreSQL 9.3.9 are available here - http://www.postgresql.org/docs/9.3/static/release-9-3-9.html

Highlighted Features and Fixes in PostgreSQL 9.3.7

Features

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

Fixes

• Fix a failure in pg_dump to honor -Z compression level option together with -Fd
• Time zone data files updated to tzdata release 2015d for DST law changes in several countries
• Improved detection of system-call failures

The Official Release Notes for PostgreSQL 9.3.7 are available here - http://www.postgresql.org/docs/9.3/static/release-9-3-7.html

Highlighted Features and Fixes in PostgreSQL 9.3.6

Features

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

Fixes

• Fix for an information leak due to constraint-violation error messages
• Fix to avoid corrupting tables when ANALYZE is rolled back inside a transaction
• Performance improvements for EXPLAIN with large range tables

The Official Release Notes for PostgreSQL 9.3.6 are available here - http://www.postgresql.org/docs/9.3/static/release-9-3-6.html

Highlighted Features and Fixes in PostgreSQL 9.3.5

Features

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

Fixes

• Fix for a possible race condition when updating a tuple concurrently locked by another process
• Fix for client host name lookups when processing pg_hba.conf entries that use host names and not IP addresses
• Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.

The Official Release Notes for PostgreSQL 9.3.5 are available here - http://www.postgresql.org/docs/current/static/release-9-3-5.html

Highlighted Features and Fixes in PostgreSQL 9.3.4

Features

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

Fixes

• Update time zone data files to tzdata release 2014a for DST law changes in Fiji and Turkey, plus historical changes in Israel and Ukraine
• Remove the correct per-database statistics file during DROP DATABASE
• Improve performance of index endpoint probes during planning

The Official Release Notes for PostgreSQL 9.3.4 are available here - http://www.postgresql.org/docs/current/static/release-9-3-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/postgres9#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 to version 9.3

The upgrade from PostgreSQL 9.2 to 9.3 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

• For CentOS 7, use the systemctl stop postgresql command:

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

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.3.x to PostgreSQL 9.3.y (newer version)

To update PostgreSQL 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

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 stop