dblink

dblink

The dblink module supports connections to other Greenplum Database databases from within a database session. These databases can reside in the same Greenplum Database system, or in a remote system.

Greenplum Database supports dblink connections between databases in Greenplum Database installations with the same major version number. You can also use dblink to connect to other Greenplum Database installations that use compatible libpq libraries.

Note: dblink is intended for database users to perform short ad hoc queries in other databases. dblink is not intended for use as a replacement for external tables or for administrative tools such as gpcopy.

The Greenplum Database dblink module is a modified version of the PostgreSQL dblink module. There are some restrictions and limitations when you use the module in Greenplum Database.

Installing and Registering the Module

The dblink module is installed when you install Greenplum Database. Before you can use any of the functions defined in the module, you must register the dblink extension in each database in which you want to use the functions.

Greenplum Database Considerations

In this release of Greenplum Database, statements that modify table data cannot use named or implicit dblink connections. Instead, you must provide the connection string directly in the dblink() function. For example:
gpadmin=# CREATE TABLE testdbllocal (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
gpadmin=# INSERT INTO testdbllocal select * FROM dblink('dbname=postgres', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
INSERT 0 2
The Greenplum Database version of dblink disables the following asynchronous functions:
  • dblink_send_query()
  • dblink_is_busy()
  • dblink_get_result()

Using dblink

The following procedure identifies the basic steps for configuring and using dblink in Greenplum Database. The examples use dblink_connect() to create a connection to a database and dblink() to execute an SQL query.

  1. Begin by creating a sample table to query using the dblink functions. These commands create a small table in the postgres database, which you will later query from the testdb database using dblink:
    $ psql -d postgres
    psql (9.4.20)
    Type "help" for help.
    
    postgres=# CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a);
    CREATE TABLE
    postgres=# INSERT INTO testdblink VALUES (1, 'Cheese'), (2, 'Fish');
    INSERT 0 2
    postgres=# \q
    $
  2. Log into a different database as a superuser. In this example, the superuser gpadmin logs into the database testdb. If the dblink functions are not already available, register the dblink extension in the database:
    $ psql -d testdb
    psql (9.4beta1)
    Type "help" for help.
    
    testdb=# CREATE EXTENSION dblink;
    CREATE EXTENSION
  3. Use the dblink_connect() function to create either an implicit or a named connection to another database. The connection string that you provide should be a libpq-style keyword/value string. This example creates a connection named mylocalconn to the postgres database on the local Greenplum Database system:
    testdb=# SELECT dblink_connect('mylocalconn', 'dbname=postgres user=gpadmin');
     dblink_connect
    ----------------
     OK
    (1 row)
    Note: If a user is not specified, dblink_connect() uses the value of the PGUSER environment variable when Greenplum Database was started. If PGUSER is not set, the default is the system user that started Greenplum Database.
  4. Use the dblink() function to query a database using a configured connection. Keep in mind that this function returns a record type, so you must assign the columns returned in the dblink() query. For example, the following command uses the named connection to query the table you created earlier:
    testdb=# SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
     id | product
    ----+---------
      1 | Cheese
      2 | Fish
    (2 rows)

To connect to the local database as another user, specify the user in the connection string. This example connects to the database as the user test_user. Using dblink_connect(), a superuser can create a connection to another local database without specifying a password.

testdb=# SELECT dblink_connect('localconn2', 'dbname=postgres user=test_user');
To make a connection to a remote database system, include host and password information in the connection string. For example, to create an implicit dblink connection to a remote system:
testdb=# SELECT dblink_connect('host=remotehost port=5432 dbname=postgres user=gpadmin password=secret');

Additional Module Documentation

Refer to dblink in the PostgreSQL documentation for detailed information about the individual functions in this module.