Re: fix for pg_upgrade

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

\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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

> 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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.