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

3 Responses so far

  1. Good Escort Services in the Mumbai Then Mumbai Escorts

  2. A venture information stockroom might be executed on customary centralized servers, UNIX super servers or parallel engineering stages. It requires broad business displaying and may take a long time to structure and construct. ai courses

  3. Project says:

    Great Article
    Cloud Computing Projects

    Networking Projects

    Final Year Projects for CSE

    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

Leave a Reply