Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 22 down vote accepted
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

share|improve this answer
    
On one computer the top three lines were enough. On the other I had to reinstall 8.4 to make it work, as you said. Thank you for your help. –  nathanvda Oct 17 '11 at 6:24
    
It should be noted that the "pg_upgradecluster" script doesn't handle the case where the database user must be supplied. (If it does, I sure can't figure out how to provide it.) –  Pointy Jun 18 '12 at 15:15
    
If you get Error: specified cluster is not running when trying again after having changed max_connections, use pg_ctlcluster 8.4 main start. –  Skippy le Grand Gourou Jun 20 at 12:24

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.

share|improve this answer
    
FYI, I found that doing a "du -hs" in the /var/lib/postgresql/9.1 directory and then comparing that with the output of the same command in /var/lib/postgresql/8.4/, I found that I could gauge how much has been copied over based on the size output from that command. –  Joe J Nov 14 '12 at 20:04
    
I know this isn't the question that is being discussed. But I ran into the same issue as you described. The reason pg_upgradecluster couldn't stop the old cluster was because a Tomcat instance was using the connections and Postgres couldn't close them. Hope this helps someone. –  bplayer Apr 14 at 15:26

when there is not enough time for a dump and restore, use pg_upgrade: http://www.postgresql.org/docs/devel/static/pgupgrade.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.