Showing results for 
Search instead for 
Did you mean: 

Error with pg_upgrade from 9.4 to 9.6 when dblink and hstore extensions exist in 9.4 cluster

When doing a pg_upgrade from the latest EPAS 9.4 to the latest EPAS 9.6, the pg_upgrade fails if both the dblink and the hstore extensions are created.  If only one of the two extensions exist, the pg_upgrade succeeds, but if both exist (are created in the 9.4 cluster), then the pg_upgrade fails.
When the EPAS 9.4 environment has both the dblink and hstore extensions, the pg_upgrade will fail with these errors:
Restoring database schemas in the new cluster
Consult the last few lines of "pg_upgrade_dump_13772.log" for
the probable cause of the failure.
Viewing the specified log file shows this:
pg_restore: creating FOREIGN DATA WRAPPER "dblink_fdw"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2858; 2328 16429 FOREIGN
DATA WRAPPER dblink_fdw enterprisedb
pg_restore: [archiver (db)] could not execute query: ERROR: function
dblink_fdw_validator(text[], oid) does not exist
To reproduce, please do these steps from any Linux machine where Docker is
#> cd edb_examples/pg_upgrade
#> export EDB_YUM_USERNAME=gov-eval
#> export EDB_YUM_PASSWORD=3da3a2165f04620788f03d6f0abfa840
#> docker-compose build pgupgrade
This will recreate the error shown above. If you remove the dblink or the hstore extension, the error goes away and the upgrade succeeds. But if both extensions exist, the error is produced and the upgrade fails. 




The only workaround available today is to drop the dblink extesion in order to proceed further. The extension have dblink_connect(), dblink_open(), dblink_exec() etc APIs.
It doesn't have any catalog storage - it doesn't create any entry into catalog. Where as postgres_fdw, edb_dblink_* extension have  entry - so one should not worry about dropping dblink extension.
Version history
Revision #:
2 of 2
Last update:
‎04-27-2018 02:44 PM
Updated by:
Labels (3)