27

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?

Jorge Castro
  • 71,754
nathanvda
  • 1,593

3 Answers3

27
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

Marco Ceppi
  • 48,101
Marconius
  • 386
  • 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 06: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 '14 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. – Serrano Pereira Oct 05 '14 at 11:56
4

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.

user51007
  • 3
estibordo
  • 1,294
  • 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 '14 at 15:26
1

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

maletin
  • 148