Upgrade Postgresql 9.2 to 9.3

PostgreSQL Upgrading Type:

1. pg_upgrade utility
2. pg_dumpall -taking full cluster backup and restoring new version of software
3. slony  -upgrading postgres with Zero Down Time

pg_upgrade utility
In this article on upgrading postgresql using pg_upgrade utility, I used a 9.2 instance that was installed using the methodology in that earlier article.  If your Postgresql instance was installed using a different manner you will likely need to modify these instructions to make it work.

Step 1 – Complete Backup of Postgresql System

Please use your typical backup procedures for backing up your system prior to using these instructions.  Use pg_dump, a full filesystem and operating system dump or whatever your typical procedures are so that you can get things back up and running should something not go wrong.

Step 2 – Install New Version of Postgres

The first step in that process is to download the Postgresql repository for the new version that we are installing.  You can find the repository for your version at: 

 http://yum.postgresql.org
# wget http://yum.postgresql.org/9.3/redhat/rhel-6.4-x86_64/pgdg-centos93-9.3-1.noarch.rpm
# rpm -ivh ./pgdg-centos91-9.1-4.noarch.rpm

Now that your repository is installed, we need to determine which postgresql packages we need to install.  The best way to do this is to determine which packages you have currently installed for the version you are already running. So we will run “rpm -qa” and grep to our current version numbers to find the packages currently installed:

# rpm -qa | grep postgre | grep 92
postgresql92-server-9.2.3-2PGDG.rhel6.x86_64
postgresql92-contrib-9.2.3-2PGDG.rhel6.x86_64
postgresql92-libs-9.2.3-2PGDG.rhel6.x86_64
postgresql92-9.2.3-2PGDG.rhel6.x86_64
postgresql92-devel-9.2.3-2PGDG.rhel6.x86_64 

Now, we need to get the list of available packages for the new Postgresql version. Since I am installing 9.3, I will grep on 93:

# yum list postgres* | grep 93
postgresql93.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-contrib.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-debuginfo.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-devel.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-docs.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-jdbc.x86_64 9.3.1100-1PGDG.rhel6 pgdg93
postgresql93-jdbc-debuginfo.x86_64 9.3.1100-1PGDG.rhel6 pgdg93
postgresql93-libs.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-odbc.x86_64 09.02.0100-1PGDG.rhel6 pgdg93
postgresql93-odbc-debuginfo.x86_64 09.02.0100-1PGDG.rhel6 pgdg93
postgresql93-plperl.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-plpython.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-pltcl.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-server.x86_64 9.3.4-1PGDG.rhel6 pgdg93
postgresql93-test.x86_64 9.3.4-1PGDG.rhel6 pgdg93

There are tons of available packages, but I only need to install the ones that match the packages I have currently installed for the old version.

# yum install postgresql93-server.x86_64 postgresql93-contrib.x86_64 postgresql93-libs.x86_64 postgresql93.x86_64 postgresql93-devel.x86_64 

Step 3 – Prepare Postgresql for Upgrade

Now that we have the new Postgresql installed, we need to get finishing touches on the configuration and initialize the new 9.3 database.

I like to install my Postgresql database files in the /data/pgsql directory. So, I need to edit the /etc/init.d/postgresql-9.3 file and set the location.

Change these 2 lines:

PGDATA=/var/lib/pgsql/9.2/data
PGLOG=/var/lib/pgsql/9.2/pgstartup.log

to

PGDATA=/data/pgsql/9.3/data
PGLOG=/data/pgsql/9.3/pgstartup.log 

Change the Postgresql Port on the New Postgresql Instance

Because we have the current postgresql still up and running on the default port of 5432, we need to modify the port that our new postgresql is running on until we get the whole thing complete.  I will change it to posrt 5433.

Edit the /etc/init.d/postgresql-9.3 file and change:

PGPORT=5432

to

PGPORT=5433 

Now we need to inititalize the new 9.3 Postgresql Instance:

# /etc/init.d/postgresql-9.3 initdbVerify that it created the new directory wherever you have it listed in your 

/etc/init.d/postgresql-9.3 file.
# ls /data/pgsql/9.3/data
 base pg_hba.conf pg_multixact pg_snapshots pg_subtrans PG_VERSION
 global pg_ident.conf pg_notify pg_stat pg_tblspc pg_xlog
 pg_clog pg_log pg_serial pg_stat_tmp pg_twophase postgresql.conf 

Step 4 – Upgrade Postgresql

Now that we have our new Postgresql packages installed, configured and the instance initialized, we are ready for the upgrade.  So, we need to stop our existing Postgresql instance:

# service postgresql-9.2 stop 

We will run the pg_upgrade.  The options that we need to use for pg_upgrade are:

-b   old postgresql binary directory

-B   new postgresql binary directory

-d   old postgresql data directory

-D   new postgresql data directory

So, let’s run the upgrade:

$ /usr/pgsql-9.3/bin/pg_upgrade -v -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ -d /data/pgsql/9.2/data/ -D /data/pgsql/9.3/data/

Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
pg_control values:

First log file ID after reset: 1483
First log file segment after reset: 58
pg_control version number: 922
Catalog version number: 201204301
Database system identifier: 5862655786611398652
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/20320796
Latest checkpoint's NextOID: 1914241
Latest checkpoint's NextMultiXactId: 10
Latest checkpoint's NextMultiOffset: 22
Latest checkpoint's oldestXID: 1798
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
pg_control values:

First log segment after reset: 000000010000000000000002
pg_control version number: 937
Catalog version number: 201306121
Database system identifier: 5992919155432435960
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1809
Latest checkpoint's NextOID: 12897
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1799
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
delete_old_cluster.sh

Step 5 – Finalize Upgrade Cleanup

Our Postgresql is now upgraded, but we still need to work out config file differences. The 2 important files to reconcile are the pg_hba.conf and the postgresql.conf files.

Copy over the old pg_hba.conf file:

cp /data/pgsql/9.2/data/pg_hba.conf ./pg_hba.conf 

Then I find the differences in the postgresql.conf file and make the important changes in the new postgresql.conf file. I do not copy it over because there may be new configuration options or defaults.

diff /data/pgsql/9.2/data/postgresql.conf ./postgresql.conf 

Change the 9.3 port number back to 5432 by editing the /etc/init.d/postgresql-9.3 file:

PGPORT=5433

to

PGPORT=5432

Startup the new Postgresql Instance

# service postgresql-9.3 start

Starting postgresql-9.3 service:                           [  OK  ]
Our Postgresql databases should be up and usable now!

Run analyze_new_cluster.sh

Analyze_new_cluster.sh should be located in the postgres user’s home directory:

$ /data/pgsql/analyze_new_cluster.sh

This script will generate minimal optimizer statistics rapidly

so your system is usable, and then gather statistics twice more

with increasing accuracy. When it is done, your system will

have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for

any tables, you might want to remove them and restore them after

running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel

this script and run:

"/usr/pgsql-9.3/bin/vacuumdb" --all --analyze-only

Generating minimal optimizer statistics (1 target)
--------------------------------------------------
vacuumdb: vacuuming database "db1"
vacuumdb: vacuuming database "db2"

The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.

Generating medium optimizer statistics (10 targets)
---------------------------------------------------
vacuumdb: vacuuming database "db1"
vacuumdb: vacuuming database "db2"

Generating default (full) optimizer statistics (100 targets?)
-------------------------------------------------------------
vacuumdb: vacuuming database "db1"
vacuumdb: vacuuming database "db2"

Done  

Run Delete_old_cluster.sh to Remove the Old Postgresql Directory

delete_old_cluster.sh

Step 6 – Remove Old Postgresql Packages

In step 2 above, we found the list of Postgresql packages that we had installed prior to the upgrade.  At this point, we can delete those packages if we think we are ready to.  Remember, we only want to delete the old packages and not the new ones that we just installed in this article.

# yum remove postgresql92-server-9.2.3-2PGDG.rhel6.x86_64 postgresql92-contrib-9.2.3-2PGDG.rhel6.x86_64 postgresql92-libs-9.2.3-2PGDG.rhel6.x86_64 postgresql92-9.2.3-2PGDG.rhel6.x86_64 postgresql92-devel-9.2.3-2PGDG.rhel6.x86_64

That’s it.  We should be all upgraded and ready to use our newly upgraded Postgresql database.  You should probably backup your database again before you go too much further.