Wednesday, January 16, 2013

Steps to upgrade Postgresql database using pg_upgrade

I had  to upgrade Postgresql 9.0 to 9.2 with less downtime. Earlier I used to complete this kind of upgrade work using pg_dump. But as I have very little downtime for 60 GB database, I chose to go with pg_upgrade and yes, I completed the whole task within an hour.
The steps that I followed. Step 1 to 9 are related to data migration using pg_upgrade and 10 - 14 are related to Postgresql 9.2 installation from yum repo and remove source build version.
Later I added a plan to revert back to Postgresql 9.0, if some thing went wrong.
Steps to upgrade database:
1. Install Postgresql 9.2 from source at /usr/pgsql-9.2 with contrib modules
2. Create Postgresql Cluster directory for 9.2
/usr/pgsql-9.2/bin/initdb -D /var/lib/pgsql/9.2/data
3. Stop Postgresql-9.0
pg_ctl stop -mf
4. Verfy Postgresql 9.2 installation
/usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data
5. Stop Postgresql 9.2 
/usr/pgsql-9.2/bin/pg_ctl stop -mf -D /var/lib/pgsql/9.2/data
6. Both the database servers are stopped. Now upgrade database:
export OLDCLUSTER=/usr/pgsql-9.0
export NEWCLUSTER=/usr/pgsql-9.2
/usr/pgsql-9.2/bin/pg_upgrade --old-bindir=$OLDCLUSTER/bin --new-bindir=$NEWCLUSTER/bin --old-datadir=$OLDCLUSTER/data --new-datadir=$NEWCLUSTER/data
7. Verify Upgradation.
/usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data
Execute some sqls
8. Vacuum and analyze databases.
9. Stop Postresql 9.2
Install Postgresql 9.2 from yum repo:
10. Rename Postgresql 9.2 home to some new name and install Postgresql 9.2 from yum repo
mv /usr/pgsql-9.2 /usr/pgsql-9.2_from_source
11. Uninstall Postgresql 9.0
11. Set $PGDATA to /var/lib/pgsql/9.2/data
12. Verify Postgresql 9.2 installation
13. Verify Postgresql 9.2 startup script at /etc/init.d and enable it with system startup
14. Remove /usr/pgsql-9.2_from_source
If for some reasons, we have to revert back to Postgresql 9.0:
  1. Stop Postgresql 9.2
  2. Uninstall Postgresql 9.2
  3. Install Postgresql 9.0
  4. Change $PGDATA to /var/lib/pgsql/9.0/data
  5. Start Postgresql 9.0
  6. Verify and release
 Important issues that I faced during upgrade:
  1. 9.0 database was running with uuid and pgxml modules. I had to configure 9.2 with these options. uuid-devel lib was missing and I had to install it.
  2. In a database, there was a view named 'slowest_queries' based on pg_stat_activity. As some columns names had been changed in pg_stat_activity, I was getting error during pg_upgrade operation. To avoid this, I saved the view code somewhere else, dropped the view from 9.0, upgrade database using pg_upgrade, modified view code to new column names and created the view in 9.2.

Post upgrade issues:
  1. Set standard_confomation_setting to off for backward compatibility

Leave a Reply