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?

3 Answers 3

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

  • 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, 2011 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, 2012 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. Jun 20, 2014 at 12:24
  • Reducing max_connections did not work for me. As an alternative solution provided by the error message, I reduced shared_buffers to 20MB (from 200MB), which worked for me. Oct 5, 2014 at 11:56

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.

  • 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, 2012 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, 2014 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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