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

User · Answer

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

User · Answer

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

User · Answer

It can be added by using    psql -d databaseName -c  CREATE EXTENSION dblink

User · Answer

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

User · Answer

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  quot current schema quot    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

User · Answer

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

[sql] How to use (install) dblink in PostgreSQL?

Examples related to sql

Examples related to postgresql

Examples related to dblink