Mini HOWTO: Upgrade PostgreSQL database in Fedora

The main problem in upgrading PostgreSQL databases is downgrading to previous version of the RDBMS, especially if you accidentally updates the software but forget to make the database dump. I have faced the same problem and find it's not so easy task. So let me show my solution for this:
  1. The problem: after OS upgrade I have PostgreSQL 8.4, and can't start it because the database format is incompatible and suited for PSQL 8.3 only.
  2. Install required YUM-configuration for the version of PosgreSQL you want to install, for my case PostgreSQL 8.3. There is an excellent website, which provides required rpms:
    $ sudo rpm -ivh
  3. Remove the current PostgreSQL installation. It requires few steps (there is no easy way to delete postgresql-libs because there are too many dependencies):

    1. Get the list of postgresql packages:
      $ rpm -qa | grep postgresql
    2. Remove these without checking dependencies:
      $ sudo rpm -ev --nodeps postgresql-server-8.4.3-1.fc12.i686 postgresql-8.4.3-1.fc12.i686 postgresql-libs-8.4.3-1.fc12.i686
  4. Now install required packages:
    $ sudo yum install postgresql-server.i386 postgresql-libs-8.3.10-1PGDG.fc12.i386 --disableplugin=protectbase
  5. Start the server:
    $ sudo /sbin/service postgresql start
  6. Make dump:
    $ sudo -u postgres pg_dumpall > /tmp/pg.dump
  7. Stop the server:
    $ sudo /sbin/service postgresql stop
  8. Move the old installation out of the way:
    $ sudo mv /var/lib/pgsql /var/lib/pgsql.old
  9. Remove the server:
    $ sudo yum remove postgresql-server.i386 postgresql-libs-8.3.10-1PGDG.fc12.i386 --disableplugin=protectbase
  10. Install the current server:
    $ sudo yum install postgresql-server
  11. Initialize the new database:
    $ sudo /sbin/service postgresql initdb
  12. Start the current server:
    $ sudo /sbin/service postgresql start
  13. Restore database from the dump:
    $ sudo -u postgres psql -d postgres -f /tmp/pg.dump
That's all - just few steps. Of course, it is not so easy, as might be, but it's much easier than compiling PostgreSQL from sources.


Post a Comment

Popular posts from this blog

Trac Ticket Workflow

Introduction to ReviewBoard

Python vs JS vs PHP for embedded systems