Tuesday, December 17, 2013

PostgreSQL Upgrade - Fedora 20

Fedora 20 will upgrade your PostgreSQL database from 9.2.6-1 to 9.3.1-2.

Since this is a major upgrade it will require that you upgrade your database.

Here is how to do it:

  1. Install the upgrade utilities:  yum install postgresql-upgrade
  2. Become user postgres:  sudo su - postgres
  3. Rename the data directory:  mv /var/lib/pgsql/data/ /var/lib/pgsql/data_9.2/
  4. As root, initialize your 9.3 database:  sudo postgresql-setup initdb
  5. As user postgres, Copy your pg_hba.conf:  cp /var/lib/pgsql/data_9.2/pg_hba.conf/ /var/lib/pgsql/data/pg_hba.conf/
  6. To avoid any potential password issues, temporarily change "md5" to "trust" in both pg_hba.conf files
  7. Run the upgrade process:  pg_upgrade -b /usr/lib64/pgsql/postgresql-9.2/bin/ -B /usr/bin/ -d data_9.2/ -D data
  8. Change "trust" to md5" if you changed in step 6
  9. Enable your postgresql.service:  sudo systemctl enable postgresql.service
  10. Start your postgresql.service:  sudo systemctl start postgresql.service
  11. Run the analyse_new_cluster.sh script as suggested
  12. Run the delete_old_cluster.sh script as suggested