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.
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.
- 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 to
sudo 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 -ibecause the
"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 type
su - postgresWhen you are done “being” that user, just type
and you will be root again.
- 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
- Add python-software-properties
sudo apt-get install python-software-properties
- Add the backport repository to your repository sources list:
sudo add-apt-repository ppa:pitti/postgresql
- Update the sources list on your server.
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.
- As the
postgresuser, 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
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
- as the
postgresuser, 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.
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
- 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:
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.
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
, as it is required to write into /etc/postgresql which is owned by
pg_upgradecluster 8.4 main
- as the
postgresuser, load data from backup into PostgreSQL 9.0.
psql < data.sql # If you are updating specific databases... psql -U username -W database < db.sql
- Restart Evergreen and Apache.
- 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;
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
Thanks again to the great people at the Evergreen-ILS project