Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Unanswered: Upgrading Postgresql from 8.3 to 9.0.3

    Hi,

    Am doing PostgreSQL administration for a ERP product. Here am currently using PostgreSQL-8.3.14 and I wanna upgrade it to 9.0.3. Built latest PostgreSQL from source and passed the below command.

    It dumps out all the databases in the dir /var/lib/pgsql/ and after upgrading, there were no database files in it.

    Do i manually need to reload the datasources from the dump file ? or will it automatically restore the datasources?

    Command :
    bin/pg_upgrade -d "olddatadir" -D "newdatadir" -b "oldbindir" -B "newbindir" -p"oldportno" -P "newportno"


    Ref : PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: pg_upgrade.


    Thanks in advance for your guidance

  2. #2
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by vnandhabe View Post
    Do i manually need to reload the datasources from the dump file ? or will it automatically restore the datasources?
    If the documentation can be believed, then no. It's an in-place upgrade, so there won't be any dump files.

    pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be migrated to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by futurity View Post
    If the documentation can be believed, then no. It's an in-place upgrade, so there won't be any dump files.
    It can be believed
    I have done it two or three times now.


    There are two modes: one which creates a completely new cluster (leaving the old data sitting where it was) or just symlinking to the old (=8.4) files that do not need to be modified which speeds up the migration quite a bit (but won't let you start the old version any longer).

    Of course this is also described in the manual.

  4. #4
    Join Date
    Feb 2011
    Posts
    2
    Hi futurity & shammat,

    Thanks for your reply. Still i can't fix this.

    Did i miss anything from the steps below ?

    1. Installed Postgres-9.0 from source.
    2. Changed authentication method to "trust" from "md5" in both old and new clusters.
    3. stopped both clusters
    4. Given the command as like below,

    bin/pg_upgrade -d "olddatadir" -D "newdatadir" -b "oldbindir" -B "newbindir" -p"oldportno" -P "newportno"

    O/P I got when i ran pg_upgrade :

    Running in verbose mode
    Performing Consistency Checks
    -----------------------------
    Checking old data directory (/old_path/8.3/data) ok
    Checking old bin directory (/old_path/8.3/bin) ok
    Checking new data directory (/new_path/9.0/data) ok
    Checking new bin directory (/new_path/9.0/bin) ok

    "/old_path/8.3/bin/pg_ctl" -l "/dev/null" -D "/old_path/8.3/data" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1

    Checking for reg* system oid user data types ok
    Checking for /contrib/isn with bigint-passing mismatch ok
    Checking for invalid 'name' user columns ok
    Checking for tsquery user columns ok
    Creating script to adjust sequences ok
    Checking for large objects ok
    Creating catalog dump

    "/new_path/9.0/bin/pg_dumpall" --port 5432 --username "user_name" --schema-only --binary-upgrade > "/var/lib/pgsql/pg_upgrade_dump_all.sql"
    ok

    "/old_path/8.3/bin/pg_ctl" -l "/dev/null" -D "/old_path/8.3/data" stop >> "/dev/null" 2>&1

    "/new_path/9.0/bin/pg_ctl" -l "/dev/null" -D "/new_path/9.0/data" -o "-p 5433 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1

    Checking for presence of required libraries fatal

    | Your installation references loadable libraries that are missing
    | from the new installation. You can add these libraries to
    | the new installation, or remove the functions using them
    | from the old installation. A list of the problem libraries
    | is in the file
    | "/var/lib/pgsql/loadable_libraries.txt".

    "/new_path/9.0/bin/pg_ctl" -l "/dev/null" -D "/new_path/9.0/data" -m fast stop >> "/dev/null" 2>&1

Posting Permissions

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