How to use (install) dblink in PostgreSQL?

Question

I use Oracle and create dblink in my schema and then access the remote database as follows: mytable@myremotedb , anyway, with PostgreSQL?

I am currently using dblink as follows:

 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:

TIP: no function matches the specified name and argument types. You may need to add explicit types.

Does anyone have any ideas? Do I need to “activate” dblinks or do something before using them?

Is there anything to do in the remote database that we will query? Do I need to activate dblink? I keep having could not establish connection . This is a string like:

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

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

Answer 1

check if these links are useful -

http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html

http://www.postgresql.org/docs/current/static/dblink.html

Answer 2

With PostgreSQL 9.1 or later , the installation of additional modules has been simplified. Registered extensions (including dblink) can be installed using CREATE EXTENSION :

 CREATE EXTENSION dblink; 

Installed in the default scheme ( public by default). Make sure your search_path set correctly before running the command, and that the scheme is visible to all users who need to work with it.

Run once for each database. Or run it in the standard system database template1 to automatically add it to each created database. Details in the manual.

First you must have the files that provide the module installed on the local computer. Obviously, for Debian and its derivatives, this will be the postgresql-contrib-9.1 package - for PostgreSQL 9.1.

Answer 3

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

Link taken from this article.

Install the DbLink extension.

 CREATE EXTENSION dblink; 

Check out 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%'; 

Check database connection:

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

Answer 4

In linux, find dblink.sql, then do something like this in the postgresql console to create all the necessary functions:

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

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

Answer 5

To install the modules, you usually need to run the sql script, which is included in the database installation.

Assuming a Linux-like OS

 find / -name dblink.sql 

Check the location and run it

Answer 6

It can be added using:

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

Answer 7

 # 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\";'"