1

I have created a dump of a DB using:

pg_dump db_name > db_dump.sql

and proceed to restore it with:

psql --single-transaction db_name < db_dump.sql

Prior to attempting to restore the DB, I create the DB as:

DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name;
GRANT ALL ON DATABASE db_name TO db_user;

Also, prior to creating the DB db_name I add the extension dblink to the DB template1 as the user postgres as per:

CREATE EXTENSION dblink;

The command \df+ executed in the DB db_name shows:

 List of functions
 Schema |  Name  | Result data type | Argument data types |  Type  | Security | Volatility |  Owner   |... 
--------+--------+------------------+---------------------+--------+----------+------------+----------+---
 public | dblink | SETOF record     | text                | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, boolean       | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text          | normal | invoker  | volatile   | postgres |...
 public | dblink | SETOF record     | text, text, boolean | normal | invoker  | volatile   | postgres |...

When I go to execute psql --single-transaction db_name < db_dump.sql I see the following error message:

ERROR:  function dblink(text, text) does not exist

The output from \df+ tells me that the extension has been added though.

Why is this happening? What have I missed?

  • 3
    Maybe the dblink module was installed into a different schema in the source database. Can you show us the complete statement that fails from the input file? Also check the source file for set search_path or set schema statements prior to the failing statement – a_horse_with_no_name Jan 12 '16 at 14:53
1

The comment from @a_horse_with_no_name helped me solve the issue i.e., the extension was not installed in the right schema when attempting to restore the DB. When moved to the right schema the restore finished with no errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.