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

24
2014-04
  • nathanvda

    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?

  • Answers
  • Marco Ceppi
    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

  • user51007

    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.

  • maletin

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


  • Related Question

    11.10 - How do I install Postgres Graphical Installer?
  • tusar

    I am a newbie in using Ubuntu (more precisely never used linux except the production deployment ). Now I choose to hold a grip on the system and intalled a latest 11.10 Ubuntu in my 32 bit Intel** desktop. In this machine **I have a 'administrator' user 'tusar'.

    I want to install the PostgreSQL graphical version so downloaded this file postgresql-8.4.9-1-linux.bin from here

    Now If run the file with ./filename command (refuses if I am not root), it opens up the graphical interface and completes the installation (creates user postgres and ask for password, wont complete if I dont give valid password).

    I checked with /etc/inint.d/postgresql-8.4 status , that postgres is running.

    But the problem is I am unable to open the GUI (I installed it in /opt/Postgresql ), its telling permission denied even if I am logged in as 'tusar'. Then I tried using the su postgres command and it directly changed the user without password. Now if I run the psql , createdb , its prompting me to install the postgres-client-package but this utilies are already installed and I can view them in my /opt/Postgresql/8.4/bin folder.

    I can run query through my JDBC utility programs but unable to use neither terminal nor the graphical interface (although I can see pgAdmin3 is there ). Urgent help needed.

    I have used this guide for the installation.


  • Related Answers
  • James Henstridge

    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