How to use (install) dblink in PostgreSQL?

Question

I am used to Oracle and to create a dblink in my schema and then access to a remote database like this: mytable@myremotedb, is there anyway do to the same with PostgreSQL?

Right now I am using dblink like this:

SELECT logindate FROM dblink('host=dev.toto.com
                              user=toto
                              password=isok
                              dbname=totofamily', 'SELECT logindate FROM loginlog');

When I execute this command I get the following error:

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Does anybody have an idea ? Do we have to "activate" dblinks or do something before using them?

Is there something to do on the remote database we are going to query? Do we have to activate dblink too? I keep having a could not establish connection. This is the line is type:

SELECT dblink_connect_u('host=x.x.x.x dbname=mydb user=root port=5432');

IP Address is correct and Postgres is running on the remote server. Any idea?

Answer 1

Since PostgreSQL 9.1, installation of additional modules is simple. Registered extensions like dblink can be installed with CREATE EXTENSION:

CREATE EXTENSION dblink;

Installs into your default schema, which is public by default. Make sure your search_path is set properly before you run the command. The schema must be visible to all roles who have to work with it. See:

• How does the search_path influence identifier resolution and the "current schema"

Alternatively, you can install to any schema of your choice with:

CREATE EXTENSION dblink SCHEMA extensions;

See:

• Best way to install hstore on multiple schemas in a Postgres database?

Run once per database. Or run it in the standard system database template1 to add it to every newly created DB automatically. Details in the manual.

You need to have the files providing the module installed on the server first. For Debian and derivatives this would be the package postgresql-contrib-9.1 - for PostgreSQL 9.1, obviously. Since Postgres 10, there is just a postgresql-contrib metapackage.

Answer 2

I am using DBLINK to connect internal database for cross database queries.

Reference taken from this article.

Install DbLink extension.

CREATE EXTENSION dblink;

Verify DbLink:

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE '%dblink%';

Test connection of database:

SELECT dblink_connect('host=localhost user=postgres password=enjoy dbname=postgres');

Answer 3

On linux, find dblink.sql, then execute in the postgresql console something like this to create all required functions:

\i /usr/share/postgresql/8.4/contrib/dblink.sql 

you might need to install the contrib packages: sudo apt-get install postgresql-contrib

Answer 4

Installing modules usually requires you to run an sql script that is included with the database installation.

Assuming linux-like OS

find / -name dblink.sql

Verify the location and run it

Answer 5

It can be added by using:

$psql -d databaseName -c "CREATE EXTENSION dblink"

Answer 6

# or even faster copy paste answer if you have sudo on the host 
sudo su - postgres  -c "psql template1 -c 'CREATE EXTENSION IF NOT EXISTS \"dblink\";'"

Answer 7

You shall ask additionally, where to install dblink?

On most cases recommended location where to install common Postgers extensions is pg_catalog schema. If you install extensions in pg_catalog, then all related functions are available within any schema. If you install extension in public, then inside another schema, you have to define also schema.

For example, if dblink is installed in public, then within schema myschema, you have to define public schema.

SELECT public.dblink_get_connections();

For example, if dblink is installed in pg_catalog, then within scheme myschema, you do not have to define public schema:

SELECT dblink_get_connections();

All functions in schema pg_catalog are appended to other schemas automatically.

To install dblink in pg_catalog:

CREATE EXTENSION IF NOT EXISTS dblink WITH SCHEMA pg_catalog;