How do I migrate my postgres data from 8.4 to 9.1?

I have upgraded my ubuntu development machine from 11.04 to 11.10. In the process my postgres was also upgraded from 8.4 to 9.1.

But I seem to have lost all my data. If I look, I can see that my data for 8.4 is in folder /var/lib/postgres/8.4/main and my new database is in /var/lib/postgres/9.1/main.

What is the best way to migrate my data to the new version? Can I just copy the files?

Solution 1:

su postgres
pg_dropcluster --stop 9.1 main    
pg_upgradecluster 8.4 main

At first this did not work for me for two reasons. Firstly, postgresql-8.4 had been unistalled at some point, so I had to reinstall it:

sudo apt-get install postgresql-8.4

Then I had to go into the 8.4 postgresql.conf and change max_connections to 10. Then it worked. You should be able to find the conf file at /etc/postgresql/8.4/main/postgresql.conf

Solution 2:

After upgrading to 11.10, the PostgreSQL 9.1 was installed, but the running version was 8.4.

I've tried:

su postgres
pg_dropcluster --stop 9.1 main    
pg_upgradecluster 8.4 main

It reported the error:

Stopping old cluster...
pg_ctl: servidor não desligou
Error: Could not stop old cluster

So, in another window:

$ sudo service postgresql-8.4 stop
 * Stopping PostgreSQL 8.4 database server                               [ OK ] 
jgr@cagliari:~$ sudo service postgresql-8.4 start
 * Starting PostgreSQL 8.4 database server                               [ OK ] 

And then again:

pg_upgradecluster 8.4 main

Errors related with pgRouting were reported. It took a while (all night!), but afterwards the database was upgraded to 9.1. PostGIS was also upgrade to 1.5.3.