Thread: 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + commit 9e5bed2df1693a46dfaed862d7462ba2379f8f79 Author: Bruce Momjian <bruce@momjian.us> Date: Sat Mar 5 21:12:21 2011 -0500 Restructure pg_upgrade checks because pre-8.4 Postgres did not rename toast file names to match the new relfilenode. diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index a502507..ad7edc4 100644 *** a/contrib/pg_upgrade/info.c --- b/contrib/pg_upgrade/info.c *************** gen_db_file_maps(DbInfo *old_db, DbInfo *** 51,59 **** RelInfo *new_rel = &new_db->rel_arr.rels[relnum]; if (old_rel->reloid != new_rel->reloid) ! pg_log(PG_FATAL, "mismatch of relation id: database \"%s\", old relid %d, new relid %d\n", old_db->db_name, old_rel->reloid, new_rel->reloid); ! create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db, old_rel, new_rel, maps + num_maps); num_maps++; --- 51,68 ---- RelInfo *new_rel = &new_db->rel_arr.rels[relnum]; if (old_rel->reloid != new_rel->reloid) ! pg_log(PG_FATAL, "Mismatch of relation id: database \"%s\", old relid %d, new relid %d\n", old_db->db_name, old_rel->reloid, new_rel->reloid); ! ! /* toast names were not renamed to match their relfilenodes in pre-8.4 */ ! if (GET_MAJOR_VERSION(old_cluster.major_version) >= 804 && ! (strcmp(old_rel->nspname, new_rel->nspname) != 0 || ! strcmp(old_rel->relname, new_rel->relname) != 0)) ! pg_log(PG_FATAL, "Mismatch of relation names: database \"%s\", " ! "old rel %s.%s, new rel %s.%s\n", ! old_db->db_name, old_rel->nspname, old_rel->relname, ! new_rel->nspname, new_rel->relname); ! create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db, old_rel, new_rel, maps + num_maps); num_maps++; *************** create_rel_filename_map(const char *old_ *** 104,115 **** /* new_relfilenode will match old and new pg_class.oid */ map->new_relfilenode = new_rel->relfilenode; - if (strcmp(old_rel->nspname, new_rel->nspname) != 0 || - strcmp(old_rel->relname, new_rel->relname) != 0) - pg_log(PG_FATAL, "mismatch of relation id: database \"%s\", old rel %s.%s, new rel %s.%s\n", - old_db->db_name, old_rel->nspname, old_rel->relname, - new_rel->nspname, new_rel->relname); - /* used only for logging and error reporing, old/new are identical */ snprintf(map->nspname, sizeof(map->nspname), "%s", old_rel->nspname); snprintf(map->relname, sizeof(map->relname), "%s", old_rel->relname); --- 113,118 ----
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@momjian.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 oidsSELECT binary_upgrade.set_next_heap_pg_class_oid('16385'::pg_catalog.oid);CREATETABLE 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. +
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@momjian.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 relfilenodesSELECT 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);CREATETABLE accounts ( guid character varying(32)NOT NULL, name character varying(2048) NOT NULL, account_type character varying(2048) NOT NULL, commodity_guidcharacter varying(32), commodity_scu integer NOT NULL, non_std_scu integer NOT NULL, parent_guid charactervarying(32), code character varying(2048), description character varying(2048), hidden integer, placeholderinteger); 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 postgresrelname | 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 postgresrelname | 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. +
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 oidsSELECT 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);CREATETABLE accounts ( guid character varying(32)NOT NULL, name character varying(2048) NOT NULL, account_type character varying(2048) NOT NULL, commodity_guidcharacter varying(32), commodity_scu integer NOT NULL, non_std_scu integer NOT NULL, parent_guid charactervarying(32), code character varying(2048), description character varying(2048), hidden integer, placeholderinteger); 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 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. +
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@momjian.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@commandprompt.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c new file mode 100644 index 94b57fa..0a84d97 *** a/src/backend/postmaster/postmaster.c --- b/src/backend/postmaster/postmaster.c *************** typedef struct *** 433,438 **** --- 433,439 ---- TimestampTz PgStartTime; TimestampTz PgReloadTime; bool redirection_done; + bool IsBinaryUpgrade; #ifdef WIN32 HANDLE PostmasterHandle; HANDLE initial_signal_pipe; *************** save_backend_variables(BackendParameters *** 4653,4658 **** --- 4654,4660 ---- param->PgReloadTime = PgReloadTime; param->redirection_done = redirection_done; + param->IsBinaryUpgrade = IsBinaryUpgrade; #ifdef WIN32 param->PostmasterHandle = PostmasterHandle; *************** restore_backend_variables(BackendParamet *** 4874,4879 **** --- 4876,4882 ---- PgReloadTime = param->PgReloadTime; redirection_done = param->redirection_done; + IsBinaryUpgrade = param->IsBinaryUpgrade; #ifdef WIN32 PostmasterHandle = param->PostmasterHandle;
> 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. +
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.