Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: PostgreSQL Upgrade Prcoess

    I had PostgreSQL 8.4.4-6 installed and working fine. I saw that version 9.0.1-2 is now available so I did a backup of all my databases in 8.4.4-6 & continued with the upgrade process. When it was done I 'restarted' the PostgreSQL daemon and attempted to connect to the server but it told me it wasn't running. I checked the logs and found:

    Code:
    FATAL:  database files are incompatible with server
    DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1.
    Now my question is I have upgraded to the latest version and I have backed up all my databases. What is the next step? I search the documents and found this:

    E.2.2. Migration to Version 9.0

    A dump/restore using pg_dump, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

    Version 9.0 contains a number of changes that selectively break backwards compatibility in order to support new features and code quality improvements. In particular, users who make extensive use of PL/pgSQL, Point-In-Time Recovery (PITR), or Warm Standby should test their applications because of slight user-visible changes in those areas. Observe the following incompatibilities:
    So what's the next step to get my 9.0.1-2 database up & running with my SQL backups? I can't access PostgreSQL at all because all my data is from 8.4.4-6 & I can't completely remove and reinstall the latest version because I loose ALL my users and configurations.

    Please help!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Now my question is I have upgraded to the latest version and I have backed up all my databases.
    The question is: how did you backup your data? Did you do a pg_dump or did you simply copy the data directory?

    I can't completely remove and reinstall the latest version because I loose ALL my users and configurations.
    How can you have users in the new database if the new one is not starting?

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I used the 'pg_dump' command:

    Code:
    pg_dump finance > /data/postgresql/finance.sql
    As far as users go. If I have 20 users when using 8.4.4-6, when I run the upgrade command, I expect my software to be updated to the latest version but I still expect there to be 20 users with specific and unique grant privileges to specific databases, no? Just because I can't access the new database doesn't mean my users are completely wiped out. Thats not an upgrade process then...I would call that a complete re installation because I would be starting from scratch.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    I used the 'pg_dump' command:
    That can be restore using psql but you will need to have a running system before you do that.

    As far as users go. If I have 20 users when using 8.4.4-6, when I run the upgrade command, I expect my software to be updated to the latest version but I still expect there to be 20 users with specific and unique grant privileges to specific databases, no?
    You can do that, but you should have read the release notes before blindly installing the software.

    As far as I can tell, your only option is to:

    - Install the 8.4 binaries (in addition to the current 9.0) and make sure you do not initialize a data directory ("cluster") during the 8.4 installation.

    - run initdb from the 9.0 installation but specify a directory other than the existing 8.4 data directory (this is a pre-requisite for running pg_upgrade)

    - run pg_upgrade from the 9.0 bin directory to migrate the 8.4 data to the newly initialized 9.0 installation.

    This is all well documented in the manual which I highly recommend you read before doing anything else, especially the chapter about how to use pg_upgrade:
    PostgreSQL: Documentation: Manuals: PostgreSQL 9: pg_upgrade

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Well at this point can I run 'pg_upgrade' w/o loading back to 8.4.4-6? How does one execute the 'pg_upgrade' command?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Well at this point can I run 'pg_upgrade' w/o loading back to 8.4.4-6?
    No.

    You need to have both versions installed in order to run pg_upgrade.
    This is well documented in the manual: http://www.postgresql.org/docs/current/static/pgupgrade.html


    How does one execute the 'pg_upgrade' command?
    This is well documented in the manual: http://www.postgresql.org/docs/current/static/pgupgrade.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •