User Tools

Site Tools


zzz_archive:upgrading_postgresql_8-4_to_postgresql_9.0_on_ubuntu_10.04

1. 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

pkill -f 'postgres: postgres <database_name>

pkill -f 'postgres: postgres evergreen

2. Make your life easier by adding python-software-properties

sudo apt-get install python-software-properties

3. Add the backport repository to your repository sources list:

sudo add-apt-repository ppa:pitti/postgresql

4. Update the sources list on your server.

apt-get update

5. Check that you are getting all your databases

psql

postgres # \l+

#that is a lower-case L, and the plus sign gives you extended information about the databases you are running.

6. As the postgres user, backup your PostgreSQL databases.

pg_dumpall > data.sql # This is the simplest way to get everything connected with the current instance of PostgreSQL

# 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

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

aptitude remove postgresql-8.4

You could use aptitude purge postgresql-8.4 but because this removes the configuration files of postgresql-8.4 as well as the application packages, it may make your life more difficult. You may wish you had your configuration files for review later in the upgrade process.

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

8. 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

9. 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) these directories, rather than straight-out deletion, is you may well discover that your pg_hba.conf file is empty of all your changes when you complete the pg_updatecluster command. 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

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

psql < data.sql # This command loads your databases into the new version of PostgreSQL

# If you are updating specific databases, rather than upgrading the entire collection…

psql -U username -W database < db.sql

11. Restart Evergreen and Apache.

12. 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;

13. 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 postgresql.conf to make sure your PostgreSQL server is listening on the right ports. If everything is communicating properly, it is safe to delete the old folders.

rm -rf /var/lib/postgresql/9.0/oldmain

rm -rf /etc/postgresql/9.0/oldmain

rm -rf /var/lib/postgresql/8.4

rm -rf /etc/postgresql/8.4

Note: There is no need to be in a huge hurry to delete these old files. It might be just as wise to hold on to them until you have used Evergreen for a month or two. It is easy to remove them later, but very difficult to recreate them if you delete them too early.

zzz_archive/upgrading_postgresql_8-4_to_postgresql_9.0_on_ubuntu_10.04.txt · Last modified: 2022/02/10 13:34 by 127.0.0.1

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

© 2008-2022 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a U.S. 501(c)3 non-profit organization.