cancel
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
edb
*failure*
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
 Command was: CREATE FOREIGN DATA WRAPPER "dblink_fdw" VALIDATOR
"dblink_fdw_validator";*
 
To reproduce, please do these steps from any Linux machine where Docker is
installed:
#> git clone https://bitbucket.org/ammppp/edb_examples
#> 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. 

 

Solution

The only workaround available today is to drop the dblink extesion in order to proceed further. The extension includes dblink_connect(), dblink_open(), dblink_exec() etc APIs.
It doesn't have any catalog storage - it doesn't create any entry into catalog, whereas postgres_fdw, edb_dblink_* extension have entries - so one should not worry about dropping dblink extension.
Version history
Revision #:
4 of 4
Last update:
‎05-21-2019 08:23 AM
Updated by:
 
Labels (3)
Comments

our organisation want to upgrade the 8x versions into major versions 9x or 10 x  can any one help me in upgrading 8x version of postgresql  that  which compatiable major versions  are recomended

can you give me any proposal on this what are the prosn and cons or any articles where i can know deeplyabout upgrations

Hi Somu,

 

Couple of points should be considered when moving the database to the new version 10.x

1.As mentioned the database version is on 8.x, so we assume this to be on old hardware and non supported version.The hardware compatibility should be considered with the desired database to be upgraded.
2.The pg_upgrade utility can only be used when the source database version is at version 8.4, at a minimum. Else you need to use pg_dump and pg_restore method
3.Space availability and the feasible maintenance decides the option for the pg_upgrade with the link and without link.
4.You can have other option of logical replication for database upgrade through EDB xDB.The database version should be to version 8.4

Details to xDB replication available at below link.

https://www.enterprisedb.com/edb-docs/d/edb-postgres-replication-server/user-guides/user-guide/6.2/t...


You can go through the EDB documentation for your concern related to the recommended database version that can be targeted.

https://www.enterprisedb.com/docs/en/8.4/pg/migration.html

As each database environment is different, the approach for upgrading the database varies.

Please test this at the test environment.

can i get document about how to install and configure slony and how to do switch over when both are servers are same

thank you rahul 

 

Hi @somu,

 

We recommend you to use the XDB for the logical replication or for the upgrade, below is the link :

 

https://www.enterprisedb.com/edb-docs/d/edb-postgres-replication-server/user-guides/user-guide/6.2/t...

 

However, if you want to set up the slony, please refer below link for its documentation:

 

http://slony.info/documentation/
http://slony.info/documentation/failover.html

 

Please let us know in case of any issues/queries.

 

Regards,

Sudhir