Archive | October 2010

Ubuntu and PostgreSQL upgrade from 8.3 to 8.4

I wanted to install postgis 1.4 and postgres 8.4 and had to upgrade ubuntu from 9.04 to 9.10, then to 10.04. After the long wait I realized that I had painted myself into a corner and my data in postgres version 8.3 was not usable with the new postgres 10.04. I could not uninstall 8.4 and reinstall 8.3 to dump the data. After searching I found the help that I needed and after following the steps below got my databases back up and running.

After adding the following two lines into my /etc/apt/sources.list:

deb http://archive.ubuntu.com/ubuntu/ karmic main restricted universe
deb-src http://archive.ubuntu.com/ubuntu/ karmic main restricted universe

I was able to install postgresql 8.3 once again.

apt-get update && apt-get install postgresql-8.3

Then while posotgres 8.3 was running again on my new system I was able to dump all my databases like so:

bash # psql -U postgres
postrgres # \l

Then I copied my dbs into a file called dbs.txt like so:

contineo          
mantis            
opennms           
redmine           
rtcache_build_8030
rtcache_build_8031

Then I simply dumped all of the databases:

bash # mkdir databases ; cd databases
bash # for db in $(cat dbs.txt); do pg_dump -U postgres $db > ${db}.sql; done

Then I uninstall postgres 8.3:

bash # sudo apt-get remove postgresql-8.3
bash # sudo apt-get remove postgresql-client-8.3

I ran into some problems with postgres not fully uninstalling so I had some issues so I had to run the following to fully remove all versions of postgres. You may not experience these problems so if you do, you may need to fully

bash # sudo dpkg -r postgresql-8.3 && sudo dpkg -P postgresql-8.3
bash # sudo dpkg -r postgresql-8.4 && sudo dpkg -P postgresql-8.4

I then removed the two lines that I added to the /etc/apt/sources.list above. Then I updated the apt:

bash # sudo apt-get update

Then I reinstall postgres 8.4:

bash # sudo apt-get install postgresql-8.4

I then had to modify the /etc/postgresql/8.4/main/pg_hba.conf and for my needs. Note this is not a secure configuration and leaves postgres very open:

...
local   all         postgres                          trust
local   all         all                                     trust
host    all         all         0.0.0.0/0           trust 
...

Then I added my database user:

CREATE USER myuser WITH PASSWORD 'jw8s0F4' CREATEDB;

After removing some databases that I no longer need in addition to the postgres database as well as the default templates, I imported the dumps into the new database.

Now, the moment that we all have been waiting for:

for db in $(cat dbs.txt); do echo ${db}; createdb -U myuser ${db}; psql -U myuser ${db} < ${db}.sql; done

With any luck we now have a system that works.

Advertisements