Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2014
    Posts
    20

    Unanswered: 9.2.6 to 9.3.3 database migration issue

    Hi all,

    I'm in the process of creating a procedure to migrate our postgresql databases from/to he versions as listed above. I did a simple pg_dump from the 9.2.6 db using:

    /usr/pgsql-9.2/bin/pgdump -Fc csg_db > csg_db.dump

    then, I tried importing into a new 9.3.3 db using pg_restore like:

    /usr/pgql-9.3/bin/pg_restore -C -d csg_db csg_db.dump

    This almost works, but the restore has multiple errors when it tries to alter the newly built tables to the correct ownership. The error says that the specified role does not exist. Now, I don't do a lot of restores, but it seems logical to me that when the new db does not exist and you specify the -C flag to create it during the restore, then the dump script should have everything in it, including the new role, to make this work.

    I know I could create the db manually and then do a createuser to get the proper role generated prior to the restore, but I shouldn't really have to do this, should I? Am I missing a flag in either the dump or the restore or both to make this succeed?

    Wondering,
    Jay

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jay at Verizon View Post
    Now, I don't do a lot of restores, but it seems logical to me that when the new db does not exist and you specify the -C flag to create it during the restore, then the dump script should have everything in it, including the new role, to make this work.
    pg_dump only dumps a single database. Users/Roles are so called "global objects" that are independent of a single database.

    If you want to get all "global objects" but still only dump/restore a single database, you can use pg_dumpall to the the necessary SQL script for them:

    Code:
    pg_dumpall --globals-only --file=global_objects.sql
    and then run the SQL script prior to restoring the database with pg_restore.

    An alternative would be to use pg_upgrade which will migrate a complete database cluster (aka "data dir") from one version to another. Additionally it will also be faster than the dump/restore thing. It could even do an "in-place" upgrade which is even more faster.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2014
    Posts
    20

    Thanks

    Thanks! I'll look into those.

  4. #4
    Join Date
    Mar 2014
    Posts
    20

    Migration potential issue

    Shammat,

    I was looking at the release notes for 9.3.3 and it suggests that some indexes might be corrupted during an upgrade, but I can't find if/where pg_upgrade is mentioned nor if it may leave such a problem. It'd be easy enough to drop the indexes and recreate them, but do you know if that would still be necessary?

    Thanks,
    Jay

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jay at Verizon View Post
    I was looking at the release notes for 9.3.3 and it suggests that some indexes might be corrupted during an upgrade
    That refers to a "binary only" upgrade from 9.3.x to 9.3.3.

    So essentially when you shutdown Postgres, install the new binaries and then start Postgres again (without any dump/restore).

    A minor version upgrade usually doesn't affect the existing data that's why this special case is mentioned for 9.3.3)

    This does not affect a direct migration from an older version using pg_upgrade or pg_dump/pg_restore.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Mar 2014
    Posts
    20

    Thanks for all your help

    Shammat,

    I sincerely appreciate all your assistance, but I do have one final issue. I've have been looking all over the web and in the postgresql repositories for pg_upgrade and just cannot locate it. I did find a possible source download for it, but I'd prefer a binary for Centos 6.4. If worse came to worse, I could build it from souce, I'd just rather not do this as my upgrade procedure must later be run by QA people who don't do that sort of thing often.

    --
    Jay

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jay at Verizon View Post
    I sincerely appreciate all your assistance, but I do have one final issue. I've have been looking all over the web and in the postgresql repositories for pg_upgrade and just cannot locate it.
    I don't use Linux, but it should be part of any binary distribution of Postgres. Did you look in the bin folder of your installed Postgres version?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Mar 2014
    Posts
    20

    Resolved

    Shammat,

    Yes, I had looked in the /usr/pgsql-9.3/bin directory and pg_upgrade wasn't present. I did, however, issue another yum install postgresql93-contrib command and there it was. Maybe I fat-fingered the original installation, but anyway all is the right with the world now.

    Thanks again!

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jay at Verizon View Post
    I did, however, issue another yum install postgresql93-contrib command
    Ah, that makes sense as pg_upgrade is still an "additional supplied program": http://www.postgresql.org/docs/curre...trib-prog.html

    When downloading the binary bundles from EnterpriseDB this is included both in the Linux and the Windows archives. But apparently the packagers of your distribution chose to split it up in two different packages.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.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
  •