How to Successfully Create and Use Database Links in PostgreSQL?

Creating Database Links in PostgreSQL

Problem:

Unlike Oracle, PostgreSQL's dblink functionality requires some setup before use. Users may encounter errors like "No function matches the given name and argument types" when creating dblinks.

Solution:

PostgreSQL 9.1 and Later:

1. Install the dblink Extension:
   Execute CREATE EXTENSION dblink; to install the dblink extension in the default public schema. Alternatively, use CREATE EXTENSION dblink SCHEMA extensions; to install it in a specific schema.
2. Ensure Search Path:
   Before using dblinks, ensure that the search_path is set correctly to include the schema containing the dblink extension.

Remote Database Setup:

To access a remote database using dblink, it's essential to:

1. Configure Host Access:
   On the remote database, grant appropriate permissions to the user connecting from the local database.
2. Create a DBLink:
   Create a dblink on the local database using CREATE DB LINK link_name CONNECT TO 'connection_string';.
3. Connect to Remote Database:
   Use SELECT * FROM link_name.table_name; to access the remote table specified in the dblink.

Example:

To create a dblink connecting to a remote database at host=dev.toto.com with the username toto, password isok, and database name totofamily:

CREATE DB LINK toto_dblink CONNECT TO 'host=dev.toto.com user=toto password=isok dbname=totofamily';
SELECT logindate FROM toto_dblink.dbo.loginlog;

Additional Notes:

• The dblink_connect_u function is used to test the connection to the remote database.
• If the remote host is on a different machine, make sure the firewall allows connections on the necessary port (typically 5432).
• For more information on dblink, refer to the PostgreSQL documentation.

The above is the detailed content of How to Successfully Create and Use Database Links in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!