The dblink extension allows a PostgreSQL database to interact with a remote PostgreSQL database.

Prerequisites and Installation

To use dblink, you must compile and install it from the PostgreSQL source code. 1. Navigate to the contrib/dblink directory within your PostgreSQL source code. 2. Compile the extension using make. 3. Install it with make install.

After installation, a dblink.so file will be available in your PostgreSQL installation's lib directory. ### Enabling and Using dblink

Once the extension is installed, enable it within your target database using psql: ``` CREATE EXTENSION dblink;

This command creates the necessary functions for `dblink` to operate. #### Querying Remote Data

You can query data from a remote table directly. The connection string specifies the remote database details: ```
SELECT *
FROM dblink('dbname=postgres host=localhost port=5432 user=postgres password=123456', 'SELECT * FROM tb1')
AS t(id integer, name character varying);

To simplify repeated access, you can create a view that encapsulates the dblink connection and query: ``` CREATE VIEW view_remote_tb1 AS SELECT * FROM dblink('dbname=postgres host=localhost port=5432 user=postgres password=123456', 'SELECT * FROM tb1') AS t(id integer, name character varying);

You can then query this view as if it were a local table: ```
SELECT * FROM view_remote_tb1;

Modifying Remote Data

For data modification operations (INSERT, UPDATE, DELETE), a transactional approach is recommended. 1. Establish a persistent connection using dblink_connect: SELECT dblink_connect('my_connection', 'hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=123456'); 2. Begin a transaction on the remote database: SELECT dblink_exec('my_connection', 'BEGIN'); 3. Execute your data manipulation statements: SELECT dblink_exec('my_connection', 'INSERT INTO tb1 SELECT generate_series(10,20), ''hello'''); 4. Commit the transaction: SELECT dblink_exec('my_connection', 'COMMIT'); 5. Disconnect from the remote database: SELECT dblink_disconnect('my_connection');

dblink in Greenplum

The usage of dblink in Greenplum is similar to PostgreSQL. However, older Greenplum versions (like 4.3.x) do not include dblink by default. Greenplum 5.0 beta and later versions do. If your Greenplum version is based on an older PostgreSQL version (e.g., 8.3.23), you can compile dblink from the corresponding PostgreSQL source code. #### Compiling dblink for Greenplum

On a Greenplum master node: 1. Download and extract the PostgreSQL source code for the version your Greenlpum is based on (e.g., PostgreSQL 8.3.23). 2. Navigate to the extracted contrib/dblink directory. 3. Modify the Makefile to include the -w flag in PG_CPPFLAGS: PG_CPPFLAGS = -I$(libpq_srcdir) -w 4. Compile and install using make USE_PGXS=1 install, ensuring your Greenplum environment is sourced.

This process generates dblink.so and dblink.sql. Copy dblink.so to the lib/postgresql directory on all Greenplum cluster nodes and ensure it's owned by the Greenplum admin user (e.g., gpadmin). Place dblink.sql in an accessible location on the master. #### Installing dblink in Greenplum Database

To install the extension in a specific Greenplum database (e.g., db_lin), run: ``` psql -f /path/to/dblink.sql db_lin

Repeat this command for each Greenplum database where you need `dblink` functionality. #### Testing dblink in Greenplum

Assuming `dblink` is installed in `db_lin` and a remote table `a` exists in `db1`: ```
-- Connect to db1 from db_lin using a named connection
db_lin=# SELECT dblink_connect('con1', 'dbname=db1');
 dblink_connect
----------------
 OK
(1 row)

-- Query data from remote table 'a' using the named connection
db_lin=# SELECT * FROM dblink('con1', 'SELECT * FROM a') AS t5(a int) LIMIT 5;
 a
----
 11
(1 row)

-- Query data directly without a named connection
db_lin=# SELECT * FROM dblink('dbname=db1', 'SELECT * FROM a') AS t5(a int) LIMIT 5;
 a
----
 11
(1 row)

-- Insert data into a local table 't2' from a remote table 'a'
db_lin=# INSERT INTO t2 SELECT * FROM dblink('dbname=db1', 'SELECT * FROM a') AS t5(i int);
INSERT 0 1

-- Verify the inserted data
db_lin=# SELECT * FROM t2;
 a
----
 11
(1 row)