cancel
Showing results for 
Search instead for 
Did you mean: 

pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

SOLVED
Highlighted
Level 2 Adventurer

pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi All

During the pg_upgrade from PPAS 9.3 to PPAS 10.0, the error”[archiver (db)] Error while PROCESSING TOC:” happened,The upgrade failed. The error detail is as below:
 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  test01
*failure*

Consult the last few lines of "pg_upgrade_dump_13313.log" for
the probable cause of the failure.


pg_restore: creating FOREIGN DATA WRAPPER "dblink_fdw"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 390021; 6618 888028 FOREIGN DATA WRAPPER dblink_fdw enterprisedb
pg_restore: [archiver (db)] could not execute query: ERROR:  function dblink_fdw_validator(text[], oid) does not exist

 

 Command was:

CREATE FOREIGN DATA WRAPPER "dblink_fdw" VALIDATOR "dblink_fdw_validator";
 
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "dblink" ADD FOREIGN DATA WRAPPER "dblink_fdw";
 
Please help to let me know how to address this error? Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

This is a known issue. As a workaround, you will need to drop the FDW from 9.4 before performing the upgrade and recreate them after the upgrade. The issue was caught in 9.6 and fixed in that version as well.

EDB Team Member

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi Roger,

 

Please find below answers to your queries:

 

1: you mentioned “This is a known issue. The issue was caught in 9.6 and fixed in that version as well”, Does this known issue happen on all old released before 9.6? 

 

Answer: This issue will only occur when if you are upgrading from PPAS 9.5 [ and earlier versions ] to EPAS 9.6 [ and above ] and you have dblink installed.

 

2: Does this known issue only happen on PPAS or it did happed on PostgreSQL community version either?

 

Answer: This issue is only happening with PPAS.

 

3: I tried to find this know issue on the EDB Customer Support Portal but didn’t find out any related information, Could you please help to provide the related documentation and bug info’s web link which explains the details including the issue was caught in 9.6 and fixed in that version as well.

 

Answer: 

In a PPAS 9.5 [and earlier versions] and below cluster with extension dblink installed there are three FDWs which use same validator function:

 

select fdwname, fdwvalidator, fdwvalidator::regproc from pg_foreign_data_wrapper;

   fdwname    | fdwvalidator |     fdwvalidator     

--------------+--------------+----------------------

 libpq_dblink |         4122 | dblink_fdw_validator

 oci_dblink   |         4122 | dblink_fdw_validator

 dblink_fdw   |         4122 | dblink_fdw_validator

(3 rows)

 

There are two functions with name 'dblink_fdw_validator' in pg_proc

 

select oid, proname, pronamespace from pg_proc where proname = 'dblink_fdw_validator';

  oid  |       proname        | pronamespace 

-------+----------------------+--------------

  4122 | dblink_fdw_validator |           11

 16551 | dblink_fdw_validator |         2200

(2 rows)

 

The first one is in pg_catalog schema and the other one is in public. The second one is created as part of extension "dblink", and is intended to be the validator function for "dblink_fdw". But at the time of creation of "dblink_fdw" the one in pg_catalog is picked up instead of the one in public schema. So "dblink_fdw" is already pointing to the wrong validator function. When the dump taken from 9.5 is restored on 9.6 or above, "dblink_fdw" creation command doesn't find "dblink_fdw_validator" in the search path set while dumping and hence throw error.

 

We have renamed pg_catalog.dblink_fdw_validator as pg_catalog.edb_dblink_fdw_validator in Redmine #38355 which is fixed in EPAS 9.6.

 

Hope this helps.

5 REPLIES 5
Moderator

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

This is a known issue. As a workaround, you will need to drop the FDW from 9.4 before performing the upgrade and recreate them after the upgrade. The issue was caught in 9.6 and fixed in that version as well.

Level 2 Adventurer

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi Amit

 

Thank you very much for your reply.

One mistake I wrote in my question is pg_upgrade from PPAS 9.3 to PPAS 10.0, not from PPAS9.4.

 

Regarding your reply, I have the below questions:

 

  1. you mentioned “This is a known issue. The issue was caught in 9.6 and fixed in that version as well”, Does this known issue happen on all old released before 9.6? 
  2. Does this known issue only happen on PPAS or it did happed on PostgreSQL community version either?
  3.  I tried to find this know issue on the EDB Customer Support Portal but didn’t find out any related information, Could you please help to provide the related documentation and bug info’s web link which explains the details including the issue was caught in 9.6 and fixed in that version as well.

 Please help to answer it, Thank you!

 

Regards,

Roger

EDB Team Member

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi Roger,

 

Please find below answers to your queries:

 

1: you mentioned “This is a known issue. The issue was caught in 9.6 and fixed in that version as well”, Does this known issue happen on all old released before 9.6? 

 

Answer: This issue will only occur when if you are upgrading from PPAS 9.5 [ and earlier versions ] to EPAS 9.6 [ and above ] and you have dblink installed.

 

2: Does this known issue only happen on PPAS or it did happed on PostgreSQL community version either?

 

Answer: This issue is only happening with PPAS.

 

3: I tried to find this know issue on the EDB Customer Support Portal but didn’t find out any related information, Could you please help to provide the related documentation and bug info’s web link which explains the details including the issue was caught in 9.6 and fixed in that version as well.

 

Answer: 

In a PPAS 9.5 [and earlier versions] and below cluster with extension dblink installed there are three FDWs which use same validator function:

 

select fdwname, fdwvalidator, fdwvalidator::regproc from pg_foreign_data_wrapper;

   fdwname    | fdwvalidator |     fdwvalidator     

--------------+--------------+----------------------

 libpq_dblink |         4122 | dblink_fdw_validator

 oci_dblink   |         4122 | dblink_fdw_validator

 dblink_fdw   |         4122 | dblink_fdw_validator

(3 rows)

 

There are two functions with name 'dblink_fdw_validator' in pg_proc

 

select oid, proname, pronamespace from pg_proc where proname = 'dblink_fdw_validator';

  oid  |       proname        | pronamespace 

-------+----------------------+--------------

  4122 | dblink_fdw_validator |           11

 16551 | dblink_fdw_validator |         2200

(2 rows)

 

The first one is in pg_catalog schema and the other one is in public. The second one is created as part of extension "dblink", and is intended to be the validator function for "dblink_fdw". But at the time of creation of "dblink_fdw" the one in pg_catalog is picked up instead of the one in public schema. So "dblink_fdw" is already pointing to the wrong validator function. When the dump taken from 9.5 is restored on 9.6 or above, "dblink_fdw" creation command doesn't find "dblink_fdw_validator" in the search path set while dumping and hence throw error.

 

We have renamed pg_catalog.dblink_fdw_validator as pg_catalog.edb_dblink_fdw_validator in Redmine #38355 which is fixed in EPAS 9.6.

 

Hope this helps.

Level 2 Adventurer

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi surajk

 

Thank you very much for your quick reply and detail information.

 

 > We have renamed pg_catalog.dblink_fdw_validator as pg_catalog.edb_dblink_fdw_validator in Redmine #38355 which is fixed in EPAS 9.6.

Is Redmine a EDB KB system? Where can I find Redmine #38355?

 

Regards,

Roger

EDB Team Member

Re: pg_upgrade failed with error"[archiver (db)] Error while PROCESSING TOC"?

Hi Roger,

 

Yes, Redmine is our internal Bug / Enhancement request tracking system.  It is not accessible to the users.