Updating to Postgresql-9.0 from Postgresql-8.4

Upgrading PostgreSQL-8.4 to PostgreSQL-9.0 on Ubuntu 10.04

Wolf Halton 8/17/2011

This is something I was working on, on our Ubuntu 10.4LTS servers.  Since I had several to do, I thought it would be fun to add it to the Evergreen-ILS wiki and also to put it here with a little more detail.  The project wikis are likely to be places where the readers are more used to working with the project.  This is less a place for the experienced and more for those who are thinking about doing something with Evergreen-ILS.  Since there is a lot from the wiki page, from before I started updating it, I am including that page link here.

http://evergreen-ils.org/dokuwiki/doku.php?id=upgrading_postgresql_8-4_to_postgresql_9.0_on_ubuntu_10.04

This is not a thing that you will have to do forever, but when you update your PostgreSQL 8.4 databases to PostgreSQL 9.0 You can’t just
sudo aptitude install postgresql-9.0
as your database will be hosed during the process of upgrading to a major revision like this. You will probably not have to go through all this drama when moving from 8.3 to 8.4, or from 9.0 to 9.6 (when there is a Postgresql-9.6) but it probably comes down to how lucky you feel. I find backing up databases always is worth the effort.

Systems Administration

1. Generally when I am going to be bouncing in and out of a lot of system-user accounts, I start by becoming root:sudo -i # This command makes you the root user (because no user is specified in the command) and logs you in as if you were that user logging into a runlevel 3 session.  This is roughly equivalent tosudo su - # which switches the user to root and adds root’s environmental $PATH.  What’s good about it is that since root’s path includes directories that are not in an average user’s path, it is easier to use some tools and commands.  In some cases, missing the environment variable means 3rd-party scripts just won’t work.  I use sudo -i because the "-" in "sudo su -” is easy to forget.When the instructions say to act as user postgres, since you are already root (who can become any user without knowing their password) you just typesu - postgresWhen you are done “being” that user, just type

   exit

   and you will be root again.

2. Stop Evergreen and disconnect clients from database. When you stop Evergreen, the clients will be disconnected automatically, in an abrupt way. It might be better to disconnect the clients first, and then after the users are properly cleared off and their changes saved, stop Evergreen.As postgres user:postgres=# SELECT procpid, (SELECT pg_terminate_backend(procpid)) as killed from pg_stat_activity

3. Add python-software-propertiessudo apt-get install python-software-properties
4. Add the backport repository to your repository sources list:

   sudo add-apt-repository ppa:pitti/postgresql

5. Update the sources list on your server.

    
   apt-get update

6. Check that you are getting all your databases
   psqlpostgres # \l+#that is a lower-case L, and the plus sign gives you extended information about the databases you are running.
7. As the postgres user, backup your PostgreSQL databases.

    
   pg_dumpall > data.sql # This is the simplest way to get everything # or individually.. pg_dump -U username -W database_name > db.sql # for instance.. pg_dump -U evergreen -W evergreen > evergreen.sql pg_dump -U rsyslog -W Syslog > Syslog.sql #and so on

8. As root, remove PostgreSQL 8.4 and install 9.0.

    
   aptitude purge postgresql-8.4
   aptitude install postgresql-9.0 postgresql-client-9.0 postgresql-contrib-9.0 postgresql-plperl-9.0

9. as the postgres user, recreate empty evergreen databases with required language supports.If you do not have Evergreen-ils on the system yet, you can skip this step and go on to step 9.

   dropdb evergreen
   createdb -T template0 --lc-ctype=C --lc-collate=C -E UNICODE evergreen
   createlang plperl evergreen
   createlang plperlu evergreen
   createlang plpgsql evergreen
   psql -f /usr/share/postgresql/9.0/contrib/tablefunc.sql evergreen
   psql -f /usr/share/postgresql/9.0/contrib/tsearch2.sql evergreen
   psql -f /usr/share/postgresql/9.0/contrib/pgxml.sql evergreen

10. Update the 8.4 cluster (with evergreen database) to 9.0. For this to work properly, and not get the error “Target already exists 9.0 main,” you will need to move(rename) or delete the following directory:

    /var/lib/postgresql/9.0/main

    and

    /etc/postgresql/9.0/main

    The rationale for moving(renaming) rather than straight-out delete is you may well discover that your pg_hba.conf file is empty of all your changes when you complete the pg_updatecluster.

    As the root user:

    sudo mv /var/lib/postgresql/9.0/main /var/lib/postgresql/9.0/oldmain

    sudo mv /etc/postgresql/9.0/main /etc/postgresql/oldmain

    You may need to do the following command as root, as it is required to write into /etc/postgresql which is owned by root

     
    pg_upgradecluster 8.4 main

11. as the postgres user, load data from backup into PostgreSQL 9.0.

     
    psql < data.sql # If you are updating specific databases... psql -U username -W database < db.sql 

12. Restart Evergreen and Apache.
13. Test by logging into psql, checking the postgreSQL version and running a simple SELECT query.

     
    psql -U evergreen 
    evergreen=# select version();
    evergreen=# select count(*) from biblio.record_entry;

14. Check pg_hba.conf and to make sure your configurations are right and the database is allowing connections from the machines, IP addresses or IP ranges you expect, and If everything is working properly, and postgresql.conf to make sure your postgresql server is listening on the right ports. If everything is communicating properly, delete
/var/lib/postgresql/8.4/main, /etc/postgresql/8.4/main
and
/var/lib/postgresql/9.0/oldmain, and /etc/postgresql/9.0/oldmain


Thanks again to the great people at the Evergreen-ILS project