PostgreSQL Data Migration from 8.4 to 9.1

postgresql

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?

Best Answer

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

Related Solutions

PostgreSQL – How to Remove Postgres from My Installation

The simplest way to do this is to open a terminal and type:

sudo apt-get --purge remove postgresql

This will also prompt you to remove that software that depends on Postgres, which in this case it appears you would like to do.
I do not personally run 9.10 or Postgres, so it is possible that Postgres installs itself in several parts. In that case, a simple:

dpkg -l | grep postgres

Will get you the list of those packages that Postgres installed. Then, just use the same "apt-get --purge remove ...." command but instead of just postgresql, type each package name, separated by spaces, like:

sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

This is dependent on the list of packages installed, of course.

Ubuntu – How to install Postgres Graphical Installer

My suggestion would be to not use that all-in-one installer in the first place. Ubuntu already includes all the tools you need to make use of PostgreSQL in its software archive, including the graphical administration utility pgAdmin III. And since these programs are part of the software archive, you get the benefit of Ubuntu security updates (which you won't get with a 3rd party installer).

I'm not sure of the best way to clean up after the installer you tried, but I can give some tips on configuring PostgreSQL on a fresh system.

You can install the database with the following command:

sudo apt-get install postgresql

You can then find instructions on creating an account to log in to the database in /usr/share/doc/postgresql-9.1/README.Debian.gz. Once you've done that you can use the graphical administrative front end, which can be installed from the software centre or with the following command:

sudo apt-get install pgadmin3

If you need to access PostgreSQL via JDBC, you can install the Java drivers with the following command:

sudo apt-get install libpg-java

Best Answer

Related Solutions

PostgreSQL – How to Remove Postgres from My Installation

Ubuntu – How to install Postgres Graphical Installer

Related Question