I got a report from someone using pg_upgrade coming from PG 8.3 ---
turns out we didn't rename toast tables to match the new relfilenode in
pre-8.4, so the attached applied patch avoids the check for these cases.
This check is new in pg_upgrade for 9.1.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Search Discussions

  • Panam at Sep 13, 2011 at 3:54 pm
    Hi, just tried to upgrade from 9.0 to 9.1 and got this error during
    pg_upgrade :
    Mismatch of relation id: database "xyz", old relid 465783, new relid 16494
    It seems, I get this error on every table as I got it on another table
    (which I did not need and deleted) before as well. Schmemas seem to be
    migrated but the content is missing.

    I am using Windows 7 64bit (both PG servers are 64 bit as well), everthing
    on the same machine.

    Any ideas?
    Thanks & regards
    panam

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4798957.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 24, 2011 at 2:44 pm

    \panam wrote:
    Hi, just tried to upgrade from 9.0 to 9.1 and got this error during
    pg_upgrade :
    Mismatch of relation id: database "xyz", old relid 465783, new relid 16494
    It seems, I get this error on every table as I got it on another table
    (which I did not need and deleted) before as well. Schmemas seem to be
    migrated but the content is missing.

    I am using Windows 7 64bit (both PG servers are 64 bit as well), everthing
    on the same machine.
    Sorry for the delay in replying. It is odd you got a mismatch of relids
    because pg_upgrade is supposed to preserve all of those. Can you do a
    query to find out what table is relid of 465783 on the old cluster?

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Panam at Sep 25, 2011 at 1:34 pm
    OK, i started once again:


    I hope the following is the correct way of querying the table corresponding
    to a relid:









    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4838427.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 25, 2011 at 9:39 pm

    panam wrote:
    OK, i started once again:


    I hope the following is the correct way of querying the table corresponding
    to a relid:









    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4838427.html
    Yes, that is very close to what I needed. Ideally you would have
    included the oid from pg_class:

    select oid, * from pg_class where oid = 465783 or oid = 16505

    Can you supply that?

    Also can you email me privately the following output from the old
    database? It should only be the schema and not your data:

    pg_dumpall --schema-only --binary-upgrade

    I am looking for something like this in the file:

    -- For binary upgrade, must preserve pg_class oids
    SELECT binary_upgrade.set_next_heap_pg_class_oid('16385'::pg_catalog.oid);

    CREATE TABLE test (
    x integer
    );

    but for your case it would be the 'accounts' file. You can email just
    those lines if you want, and that you can probably email to hackers.
    Thanks.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Panam at Sep 26, 2011 at 10:54 pm
    Hi Bruce,

    on the old DB I've got 465783 as oid whereas on the new one it is 16505.

    is not in the dump file (old db), even 16385 (i guess this is a typo here)
    or 16505 are not.
    The only line in which 465783 could be found is

    Is that enough information or should I send the whole dump? That's a bit of
    work as I have to expunge some sensitive schema data, or is there a
    meaningful way to just do the dump for a single db?

    Thanks & regards,
    panam

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4843289.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 27, 2011 at 12:00 am

    panam wrote:
    Hi Bruce,

    on the old DB I've got 465783 as oid whereas on the new one it is 16505.

    is not in the dump file (old db), even 16385 (i guess this is a typo here)
    or 16505 are not.
    The only line in which 465783 could be found is
    I need to see the lines after this.
    Is that enough information or should I send the whole dump? That's a bit of
    work as I have to expunge some sensitive schema data, or is there a
    meaningful way to just do the dump for a single db?
    You can do:

    pg_dump --binary-upgrade --schema-only dbname


    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Panam at Sep 27, 2011 at 9:52 am
    Hi Bruce,

    here is the whole dump (old DB):
    http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt

    Regards,
    panam

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4844725.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 28, 2011 at 2:57 am

    panam wrote:
    Hi Bruce,

    here is the whole dump (old DB):
    http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt
    Wow, that is interesting. I see this in the dump output:

    -- For binary upgrade, must preserve relfilenodes
    SELECT binary_upgrade.set_next_heap_relfilenode('465783'::pg_catalog.oid);
    SELECT binary_upgrade.set_next_toast_relfilenode('465786'::pg_catalog.oid);
    SELECT binary_upgrade.set_next_index_relfilenode('465788'::pg_catalog.oid);

    CREATE TABLE accounts (
    guid character varying(32) NOT NULL,
    name character varying(2048) NOT NULL,
    account_type character varying(2048) NOT NULL,
    commodity_guid character varying(32),
    commodity_scu integer NOT NULL,
    non_std_scu integer NOT NULL,
    parent_guid character varying(32),
    code character varying(2048),
    description character varying(2048),
    hidden integer,
    placeholder integer
    );

    and it is clearly saying the oid/relfilenode should be 465783, but your
    9.1 query shows:

    C:\Program Files\PostgreSQL\9.1\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5433 -U postgres
    relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
    ----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
    accounts | 2200 | 16507 | 0 | 16417 | 0 | 16505 | 0 | 0 | 0 | 16508 | 0 | t | f | p | r | 11 | 0 | f | t | f | f | f | 3934366 | |
    (1 row)

    and 9.0 says correctly 465783:

    C:\Program Files\PostgreSQL\9.0\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5432 -U postgres
    relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relistemp | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
    ----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+-----------+---------+----------+-----------+------------+------------+-----------------+-------------+----------------+----------------+--------------+--------+------------
    accounts | 465781 | 465785 | 0 | 456619 | 0 | 465783 | 0 | 3 | 122 | 465786 | 0 | t | f | f | r | 11 | 0 | f | t | f | f | f | f | 3934366 | |
    (1 row)

    It is as though the system ignoring the set_next_heap_relfilenode()
    call, but I don't see how that could happen. I don't see any other
    'accounts' table in that dump.

    My only guess at this point is that somehow the -b/IsBinaryUpgrade flag
    is not being processed or regognized, and hence the binary_upgrade 'set'
    routines are not working.

    Is this 9.1 final or later? Can you turn on debug mode and send me the
    pg_upgrade log file that is generated? I am going go look for the
    pg_ctl -o '-b' flag. Are all databases/objects failing or just this
    one?

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Panam at Sep 28, 2011 at 11:48 am
    Here are all generated log files.

    I just removed all other DBs except gnucash (which includes the accounts
    table), but the issue also emerges with other DBs.
    Upgraded the 9.1 instance to the new build (9.1.1.) as well but this
    apparently did not change anything.
    PG versions are (including generated logs):
    PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
    PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 64-bit:
    http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.0.zip
    pg_upgrade_9.1.0.zip
    PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 64-bit:
    http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.1.zip
    pg_upgrade_9.1.1.zip
    I hope that is what you meant with "pg_upgrade log file".

    Regards,
    panam

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4848829.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 28, 2011 at 12:22 pm

    panam wrote:
    Here are all generated log files.

    I just removed all other DBs except gnucash (which includes the accounts
    table), but the issue also emerges with other DBs.
    Upgraded the 9.1 instance to the new build (9.1.1.) as well but this
    apparently did not change anything.
    PG versions are (including generated logs):
    PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
    PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 64-bit:
    http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.0.zip
    pg_upgrade_9.1.0.zip
    PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 64-bit:
    http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.1.zip
    pg_upgrade_9.1.1.zip
    I hope that is what you meant with "pg_upgrade log file".
    OK, so it fails for all tables and you are using the newest version.
    Thanks for all your work. I am now guessing that pg_upgrade 9.1.X is
    just broken on Windows.

    Perhaps the variables set by pg_upgrade_support.so are not being passed
    into the server variables? I know pg_upgrade 9.0.X worked on Windows
    because EnterpriseDB did extensive testing recently on this. Has
    anyone used pg_upgrade 9.1.X on Windows?

    As far as a log file, you need you to use '-l log' and email me that
    file.

    As far as testing, I wonder if we need to load in pg_upgrade_support on
    Windows, and rerun some of the pg_dumpall SQL create table statements to
    see why the pg_class.oid and others are not getting set. For example,
    this:

    -- For binary upgrade, must preserve pg_class oids
    SELECT binary_upgrade.set_next_heap_pg_class_oid('465783'::pg_catalog.oid);
    SELECT binary_upgrade.set_next_toast_pg_class_oid('465786'::pg_catalog.oid);
    SELECT binary_upgrade.set_next_index_pg_class_oid('465788'::pg_catalog.oid);

    CREATE TABLE accounts (
    guid character varying(32) NOT NULL,
    name character varying(2048) NOT NULL,
    account_type character varying(2048) NOT NULL,
    commodity_guid character varying(32),
    commodity_scu integer NOT NULL,
    non_std_scu integer NOT NULL,
    parent_guid character varying(32),
    code character varying(2048),
    description character varying(2048),
    hidden integer,
    placeholder integer
    );

    should set the accounts pg_class.oid as 465783. The server will need to
    be started with -b and this will disable autovacuum. Can someone on
    Windows try this?

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Bruce Momjian at Sep 28, 2011 at 4:48 pm

    Bruce Momjian wrote:
    OK, so it fails for all tables and you are using the newest version.
    Thanks for all your work. I am now guessing that pg_upgrade 9.1.X is
    just broken on Windows.

    Perhaps the variables set by pg_upgrade_support.so are not being passed
    into the server variables? I know pg_upgrade 9.0.X worked on Windows
    because EnterpriseDB did extensive testing recently on this. Has
    anyone used pg_upgrade 9.1.X on Windows?
    OK, I have a new theory. postmaster.c processes the -b
    (binary-upgrade) flag by setting a C variable:

    case 'b':
    /* Undocumented flag used for binary upgrades */
    IsBinaryUpgrade = true;
    break;

    I am now wondering if this variable is not being passed down to the
    sessions during Win32's EXEC_BACKEND. Looking at the other postmaster
    settings, these set GUC variables, which I assume are passed down. Can
    someone confirm this? How should this be fixed?

    FYI, the binary-upgrade set() functions will not operate unless the -b
    option is enabled, which explains the failure the reporter is seeing.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Alvaro Herrera at Sep 29, 2011 at 12:56 am

    Excerpts from Bruce Momjian's message of mié sep 28 13:48:28 -0300 2011:
    Bruce Momjian wrote:
    OK, so it fails for all tables and you are using the newest version.
    Thanks for all your work. I am now guessing that pg_upgrade 9.1.X is
    just broken on Windows.

    Perhaps the variables set by pg_upgrade_support.so are not being passed
    into the server variables? I know pg_upgrade 9.0.X worked on Windows
    because EnterpriseDB did extensive testing recently on this. Has
    anyone used pg_upgrade 9.1.X on Windows?
    OK, I have a new theory. postmaster.c processes the -b
    (binary-upgrade) flag by setting a C variable:

    case 'b':
    /* Undocumented flag used for binary upgrades */
    IsBinaryUpgrade = true;
    break;

    I am now wondering if this variable is not being passed down to the
    sessions during Win32's EXEC_BACKEND. Looking at the other postmaster
    settings, these set GUC variables, which I assume are passed down. Can
    someone confirm this?
    Well, you could compile it with -DEXEC_BACKEND to test it for yourself.
    How should this be fixed?
    Maybe it should be part of struct BackendParameters.

    --
    �lvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Bruce Momjian at Sep 29, 2011 at 3:14 am

    Alvaro Herrera wrote:

    Excerpts from Bruce Momjian's message of mié sep 28 13:48:28 -0300 2011:
    Bruce Momjian wrote:
    OK, so it fails for all tables and you are using the newest version.
    Thanks for all your work. I am now guessing that pg_upgrade 9.1.X is
    just broken on Windows.

    Perhaps the variables set by pg_upgrade_support.so are not being passed
    into the server variables? I know pg_upgrade 9.0.X worked on Windows
    because EnterpriseDB did extensive testing recently on this. Has
    anyone used pg_upgrade 9.1.X on Windows?
    OK, I have a new theory. postmaster.c processes the -b
    (binary-upgrade) flag by setting a C variable:

    case 'b':
    /* Undocumented flag used for binary upgrades */
    IsBinaryUpgrade = true;
    break;

    I am now wondering if this variable is not being passed down to the
    sessions during Win32's EXEC_BACKEND. Looking at the other postmaster
    settings, these set GUC variables, which I assume are passed down. Can
    someone confirm this?
    Well, you could compile it with -DEXEC_BACKEND to test it for yourself.
    How should this be fixed?
    Maybe it should be part of struct BackendParameters.
    Thanks. That's what I did, and tested the failure with -DEXEC_BACKEND,
    and the fix with the patch, which is attached. I am confident this will
    fix Windows as well.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Bruce Momjian at Sep 29, 2011 at 9:22 pm

    Alvaro Herrera wrote:
    Excerpts from Bruce Momjian's message of mi<C3><A9> sep 28 13:48:28 -0300 2011:
    Bruce Momjian wrote:
    OK, so it fails for all tables and you are using the newest version.
    Thanks for all your work. I am now guessing that pg_upgrade 9.1.X is
    just broken on Windows.

    Perhaps the variables set by pg_upgrade_support.so are not being passed
    into the server variables? I know pg_upgrade 9.0.X worked on Windows
    because EnterpriseDB did extensive testing recently on this. Has
    anyone used pg_upgrade 9.1.X on Windows?
    OK, I have a new theory. postmaster.c processes the -b
    (binary-upgrade) flag by setting a C variable:

    case 'b':
    /* Undocumented flag used for binary upgrades */
    IsBinaryUpgrade = true;
    break;

    I am now wondering if this variable is not being passed down to the
    sessions during Win32's EXEC_BACKEND. Looking at the other postmaster
    settings, these set GUC variables, which I assume are passed down. Can
    someone confirm this?
    Well, you could compile it with -DEXEC_BACKEND to test it for yourself.
    How should this be fixed?
    Maybe it should be part of struct BackendParameters.
    Thanks. That's what I did, and tested the failure with -DEXEC_BACKEND,
    and the fix with the patch, which is attached. I am confident this will
    fix Windows as well.
    Applied, and backpatched to 9.1.X. Thanks for the report. The fix will
    be in 9.1.2.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Panam at Sep 30, 2011 at 8:52 am
    Great, thanks!

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4856336.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Panam at Dec 8, 2011 at 7:06 pm
    OK, works now with the recent update.

    Thanks

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p5059777.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Panam at Sep 28, 2011 at 9:06 pm
    Hi Bruce,

    here is the file you asked for:
    http://postgresql.1045698.n5.nabble.com/file/n4850735/pg_upgrade_logfile.txt
    pg_upgrade_logfile.txt

    I guess you are not addressing me here, right?
    The server will need to
    be started with -b and this will disable autovacuum. Can someone on
    Windows try this?
    Thanks
    panam

    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4850735.html
    Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
  • Bruce Momjian at Sep 28, 2011 at 11:14 pm

    panam wrote:
    Hi Bruce,

    here is the file you asked for:
    http://postgresql.1045698.n5.nabble.com/file/n4850735/pg_upgrade_logfile.txt
    pg_upgrade_logfile.txt
    OK, I see it using -b to pg_ctl:

    ""C:\Program Files\PostgreSQL\9.1\bin/pg_ctl" -w -l "nul" -D "D:\applications\postgres\9.1" -o "-p 5432 -b" start >> "nul" 2>&1"

    What I have to find out is whether this is passed to the individual
    session processes. I guess is no.
    I guess you are not addressing me here, right?
    The server will need to
    be started with -b and this will disable autovacuum. Can someone on
    Windows try this?
    No, not really. I think it is a software bug and I need guidance about
    a solution.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 6, '11 at 2:15a
activeDec 8, '11 at 7:06p
posts19
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase